python - 根据 Pandas 中的行匹配，有条件地用来自另一个 DataFrame 的值填充列

Question

我发现自己在尝试解决这个问题(自动化税务文书工作)时迷失了方向。我有两个数据框:一个是欧元/美元汇率的季度历史记录，另一个是我自己的发票，例如:

import pandas as pd
import numpy as np

usdeur = [(pd.Timestamp('20170705'),1.1329),
          (pd.Timestamp('20170706'),1.1385),
          (pd.Timestamp('20170707'),1.1412),
          (pd.Timestamp('20170710'),1.1387),
          (pd.Timestamp('20170711'),1.1405),
          (pd.Timestamp('20170712'),1.1449)]
labels = ['Date', 'Rate']
rates = pd.DataFrame.from_records(usdeur, columns=labels)

transactions = [(pd.Timestamp('20170706'), 'PayPal',     'USD', 100, 1),
                (pd.Timestamp('20170706'), 'Fastspring', 'USD', 200, 1),
                (pd.Timestamp('20170709'), 'Fastspring', 'USD', 100, 1),
                (pd.Timestamp('20170710'), 'EU',         'EUR', 100, 1),
                (pd.Timestamp('20170710'), 'PayPal',     'USD', 200, 1)]
labels = ['Date', 'From', 'Currency', 'Amount', 'Rate']
sales =pd.DataFrame.from_records(transactions, columns=labels)

导致:

我需要用 rates['Rate'] 中的适当汇率填充 sales['Rate'] 列，也就是说:

• 如果 sales['Currency'] 是 'EUR'，不要管它。
• 对于 sales 的每一行，在 rates 中找到匹配 'Date' 的行；获取非常 rates['Rate'] 的值并将其放入 sales['Rate']
• bonus:如果没有匹配的'Date'(例如在假期期间，交易市场关闭)，检查前一行直到找到合适的值。

完整结果应如下所示(请注意第 2 行包含 2017 年 7 月 7 日的汇率):

我尝试遵循其他问题的几个建议解决方案，但没有成功。非常感谢您提前

Answer 1

您可以更改您的汇率数据框以包含所有日期，然后向前填充，在您的汇率数据框中创建一个名为“货币”的列，然后在日期和货币列上加入两个 df。

idx = pd.DataFrame(pd.date_range('2017-07-05', '2017-07-12'),columns=['Date'])
rates = pd.merge(idx,rates,how="left",on="Date")
rates['Currency'] = 'USD'
rates['Rate'] = rates['Rate'].ffill()           

     Date   Rate    Currency
0   2017-07-05  1.1329  USD
1   2017-07-06  1.1385  USD
2   2017-07-07  1.1412  USD
3   2017-07-08  1.1412  USD
4   2017-07-09  1.1412  USD
5   2017-07-10  1.1387  USD
6   2017-07-11  1.1405  USD
7   2017-07-12  1.1449  USD

然后进行左连接会得到:

result = pd.merge(sales,rates,how="left",on=["Currency","Date"])
result['Rate'] = np.where(result['Currency'] == 'EUR', 1, result['Rate_y'])
result = result.drop(['Rate_x','Rate_y'],axis =1)

会给出:

     Date         From      Currency    Amount  Rate
0   2017-07-06  PayPal           USD    100 1.1385
1   2017-07-06  Fastspring       USD    200 1.1385
2   2017-07-09  Fastspring       USD    100 1.1412
3   2017-07-10  EU               EUR    100 1.0000
4   2017-07-10  PayPal           USD    200 1.1387