python - 货币/日期数据框合并失败

Question

根据 currency_str 和 created_date_time 合并两个数据帧(xrate 和 df)时遇到问题

display(xrate.info())

Int64Index: 1611 entries, 6 to 112
Data columns (total 3 columns):
Date        1611 non-null datetime64[ns]
PX_LAST     1611 non-null object
Currency    1611 non-null object

display(xrate.head(3))

Date       PX_LAST  Currency
2018-05-30  1       CAD
2018-05-29  1       CAD
2018-05-28  1       CAD

我创建了一个新的合并日期:

#df['formatted_created_date_time'] = df['created_date_time'].dt.strftime('%d%m%Y')
df['formatted_created_date_time'] = df['created_date_time'].dt.strftime('%d-%m-%Y')
#convert to date
#df['formatted_created_date_time'] = pd.to_datetime(df['formatted_created_date_time'], format='%d%m%Y')
df['formatted_created_date_time'] = pd.to_datetime(df['formatted_created_date_time'], format='%d-%m-%Y')


display(df.info())

RangeIndex: 3488 entries, 0 to 3487
Data columns (total 43 columns):
created_date_time              3488 non-null datetime64[ns]
rfq_create_date_time           3488 non-null datetime64[ns]
currency_str                   3488 non-null object

display(df.head(3))

现在两个数据框已合并:

result = pd.merge(df, xrate, left_on=['currency_str', 'formatted_created_date_time'], right_on=['Currency', 'Date'], how='left')

display(result.info())

RangeIndex: 3488 entries, 0 to 3487
Data columns (total 43 columns):
created_date_time              3488 non-null datetime64[ns]
rfq_create_date_time           3488 non-null datetime64[ns]
.
.
formatted_created_date_time    3488 non-null datetime64[ns]

比赛失败:

display(result.head(3))

系统日期时间:

对此有什么想法吗？

Answer 1

它应该工作得很好。

但另一种解决方案是通过字符串合并:

df['formatted_created_date_time'] = df['created_date_time'].dt.strftime('%d-%m-%Y')
xrate['Date'] = xrate['Date'].dt.strftime('%d-%m-%Y')

result = pd.merge(df, xrate, left_on=['currency_str', 'formatted_created_date_time'], 
                             right_on=['Currency', 'Date'], how='left')

<小时/>

您的解决方案应简化为 floor或date

df['formatted_created_date_time'] = df['created_date_time'].dt.floor('d')
xrate['Date'] = xrate['Date'].dt.floor('d')

result = pd.merge(df, xrate, left_on=['currency_str', 'formatted_created_date_time'], 
                             right_on=['Currency', 'Date'], how='left')

df['formatted_created_date_time'] = df['created_date_time'].dt.date
xrate['Date'] = xrate['Date'].dt.date

result = pd.merge(df, xrate, left_on=['currency_str', 'formatted_created_date_time'], 
                             right_on=['Currency', 'Date'], how='left')