如果日期在其他DataFrame中不存在，则查找最后可用日期

Question

假设您有两个数据帧，可以使用下面的代码创建:

df1 = pd.DataFrame(data={'start_date': ['2021-07-02', '2021-07-09',
'2021-07-16', '2021-07-23',
'2021-07-30'],
'end_date': ['2021-07-09', '2021-07-16',
'2021-07-23', '2021-07-30',
'2021-08-06']})
price_df = pd.DataFrame(data={'date': ['2021-07-01', '2021-07-03',
'2021-07-08', '2021-07-10',
'2021-07-16', '2021-07-22',
'2021-07-30', '2021-07-31',
'2021-08-06'],
'price': [47, 62, 107, 171, 30, 154, 42,
143, 131]})

使用下面的代码添加日期时间转换

df1['start_date'] = pd.to_datetime(df1['start_date'])
df1['end_date'] = pd.to_datetime(df1['end_date'])
price_df['date'] = pd.to_datetime(price_df['date'])

使用df1['start_date'].isin(price_df['date'])可以检查数据帧df1的start_date列中的日期是否存在于数据帧price_df中。如果不是这样，我想用price_df的最后可用日期替换start_date。需要对数据帧df1的end_date列执行类似的处理。最后，可以使用连接提取价格。

预期输出

+------------+------------+-------------+-----------+
| start_date |  end_date  | start_price | end_price |
+------------+------------+-------------+-----------+
| 01/07/2021 | 08/07/2021 |          47 |       107 |
| 08/07/2021 | 16/07/2021 |         107 |        30 |
| 16/07/2021 | 22/07/2021 |          30 |       154 |
| 22/07/2021 | 30/07/2021 |         154 |        42 |
| 30/07/2021 | 06/08/2021 |          42 |       131 |
+------------+------------+-------------+-----------+

我尝试了这个代码:

df2 = pd.DataFrame()
df2['start_date'] = np.where(df1['start_date']
.isin(price_df['date']),
df1['start_date'],
price_df[
price_df.date < df1['start_date']][
'date'].max())

我得到以下错误:

ValueError: Can only compare identically-labeled Series objects

Answer 1

你可以试试pandas.merge_asof

df1[['start_date', 'end_date']] = df1[['start_date', 'end_date']].apply(pd.to_datetime)
price_df['date'] = pd.to_datetime(price_df['date'])
price_df = price_df.sort_values('date')
df1[['start_date', 'start_price']] = pd.merge_asof(df1, price_df, left_on='start_date', right_on='date')[['date', 'price']]
df1[['end_date', 'end_price']] = pd.merge_asof(df1, price_df, left_on='end_date', right_on='date')[['date', 'price']]
df1[['start_date', 'end_date']] = df1[['start_date', 'end_date']].apply(lambda col: col.dt.strftime('%d/%m/%Y'))

print(df1)
start_date    end_date  start_price  end_price
0  01/07/2021  08/07/2021           47        107
1  08/07/2021  16/07/2021          107         30
2  16/07/2021  22/07/2021           30        154
3  22/07/2021  30/07/2021          154         42
4  30/07/2021  06/08/2021           42        131