有没有办法根据数据框中的日期将美元/CAD汇率附加到数据框中

Question

下面是一个基于数据帧的CSV文件示例，它是一个月度报表：

                      Date       QUANTITY                             DES CURR  
0                   2020-07-06    -500.0  BETAPRO NASDAQ-100 2X DAILY BU  CAD   
1                   2020-07-07     -18.0                      AMAZON.COM  USD   
2                   2020-07-10     -20.0                      AMAZON.COM  USD   
3                   2020-07-13     -30.0                      AMAZON.COM  USD   
4                   2020-07-15     -50.0                      AMAZON.COM  USD   
5                   2020-07-22     -32.0                      AMAZON.COM  USD   
6                   2020-07-23     -25.0                      AMAZON.COM  USD   
7                   2020-07-28     -25.0                      AMAZON.COM  USD   

有没有办法在这个数据框中添加一列基于左边日期的美元/加元汇率？我尝试使用“concat”，但它只是将雅虎的汇率置于数据框的底部

Answer 1

您可以使用pd.concat()和许多其他方法来组合它们，merge()更方便merge

import pandas as pd
import numpy as np
import io

data = '''
Date QUANTITY DES CURR
0 2020-07-06 -500.0 "BETAPRO NASDAQ-100 2X DAILY BU" CAD
1 2020-07-07 -18.0 AMAZON.COM USD
2 2020-07-10 -20.0 AMAZON.COM USD
3 2020-07-13 -30.0 AMAZON.COM USD
4 2020-07-15 -50.0 AMAZON.COM USD
5 2020-07-22 -32.0 AMAZON.COM USD
6 2020-07-23 -25.0 AMAZON.COM USD
7 2020-07-28 -25.0 AMAZON.COM USD
'''

df = pd.read_csv(io.StringIO(data), sep='\s+')

data2 = '''
Date CURR RATE
0 2020-07-06 USD 1.15098
1 2020-07-07 USD 1.16319
2 2020-07-10 USD 1.17112
3 2020-07-13 USD 1.18092
4 2020-07-15 USD 1.16503
5 2020-07-22 USD 1.17809
6 2020-07-23 USD 1.89103
7 2020-07-28 USD 1.91234
8 2020-07-06 CAD 1.5585
9 2020-07-07 CAD 1.5802
10 2020-07-10 CAD 1.5703
11 2020-07-13 CAD 1.5234
12 2020-07-15 CAD 1.5623
13 2020-07-22 CAD 1.5237
14 2020-07-23 CAD 1.5129
15 2020-07-28 CAD 1.5343
'''

df1 = pd.read_csv(io.StringIO(data2), sep='\s+')

df.merge(df1, on=['Date', 'CURR'], how='inner')

Date    QUANTITY    DES CURR    RATE
0   2020-07-06  -500.0  BETAPRO NASDAQ-100 2X DAILY BU  CAD 1.55850
1   2020-07-07  -18.0   AMAZON.COM  USD 1.16319
2   2020-07-10  -20.0   AMAZON.COM  USD 1.17112
3   2020-07-13  -30.0   AMAZON.COM  USD 1.18092
4   2020-07-15  -50.0   AMAZON.COM  USD 1.16503
5   2020-07-22  -32.0   AMAZON.COM  USD 1.17809
6   2020-07-23  -25.0   AMAZON.COM  USD 1.89103
7   2020-07-28  -25.0   AMAZON.COM  USD 1.91234