python - 更新/追加 Pandas 数据框

Question

我有一个类似这个数据框的东西(简化):

import pandas as pd
import datetime as dt

date=dt.date(2018,1,18)
purchase_1 = pd.Series({'Store':'Store 1','Date': date,
                        'Item Purchased': 'Dog Food',
                        'Cost': 22.50,'Client':'Mark'})
purchase_2 = pd.Series({'Store':'Store 2','Date': date,
                        'Item Purchased': 'Kitty Litter',
                        'Cost': 2.50,'Client':'Den'})
purchase_3 = pd.Series({'Store':'Store 3','Date': date,
                        'Item Purchased': 'Bird Seed',
                        'Cost': 5.00,'Client':'Max'})


df = pd.DataFrame([purchase_1, purchase_2, purchase_3])
df = df.set_index(['Store', 'Date'])
df.index.names = ['Store', 'Date']


Store   Date        Client Cost   Item Purchased
Store 1 2018-01-18  Mark   22.5   Dog Food
Store 2 2018-01-18  Den    2.5    Kitty Litter
Store 3 2018-01-18  Max    5.0    Bird Seed

我需要从不同的报告更新此数据框。第一个报告应该创建一个新行(商店和日期)并填写客户和项目购买的数据。另一份报告提供成本数据等。报告的顺序发生变化。所以底线是:有时一个新的报告应该更新已经存在的行，有时创建一个新的行。所以我正在寻找一段可以实现这一目的的代码。当然我可以使用切片、if、附加等的组合，但我觉得应该有更好的解决方案。我想 Store 和 Date 应该用作多索引(我的例子中的组合是唯一的)，但我无法进一步推进。

Answer 1

你想要的神奇函数是pd.DataFrame.combine_first

报告

report1 = pd.DataFrame({
    'Client': ['Sean'], 'Item Purchased': ['Hamster Chew']
}, pd.MultiIndex.from_tuples([('Store 4', date)], names=df.index.names))

report1

                   Client Item Purchased
Store   Date                            
Store 4 2018-01-18   Sean   Hamster Chew

<小时/>

report2 = pd.DataFrame({
    'Cost': [3.14]
}, pd.MultiIndex.from_tuples([('Store 4', date)], names=df.index.names))

report2

                    Cost
Store   Date            
Store 4 2018-01-18  3.14

<小时/>

报告 1，然后报告 2

添加行

df.combine_first(report1)

                   Client  Cost Item Purchased
Store   Date                                  
Store 1 2018-01-18   Mark  22.5       Dog Food
Store 2 2018-01-18    Den   2.5   Kitty Litter
Store 3 2018-01-18    Max   5.0      Bird Seed
Store 4 2018-01-18   Sean   NaN   Hamster Chew

填写缺失的内容

df.combine_first(report1).combine_first(report2)

                   Client   Cost Item Purchased
Store   Date                                   
Store 1 2018-01-18   Mark  22.50       Dog Food
Store 2 2018-01-18    Den   2.50   Kitty Litter
Store 3 2018-01-18    Max   5.00      Bird Seed
Store 4 2018-01-18   Sean   3.14   Hamster Chew

<小时/>

颠倒顺序；先报告 2，然后报告 1

添加行

df.combine_first(report2)

                   Client   Cost Item Purchased
Store   Date                                   
Store 1 2018-01-18   Mark  22.50       Dog Food
Store 2 2018-01-18    Den   2.50   Kitty Litter
Store 3 2018-01-18    Max   5.00      Bird Seed
Store 4 2018-01-18    NaN   3.14            NaN

填写缺失的内容

df.combine_first(report2).combine_first(report1)

                   Client   Cost Item Purchased
Store   Date                                   
Store 1 2018-01-18   Mark  22.50       Dog Food
Store 2 2018-01-18    Den   2.50   Kitty Litter
Store 3 2018-01-18    Max   5.00      Bird Seed
Store 4 2018-01-18   Sean   3.14   Hamster Chew