Pandas：在两个DataFrame中匹配最近datetime并计算时间差

现有数据 #

我们有以下两个DataFrame：

df1数据： #

date,price
2022-07-23 02:00:00,22834.24
2022-07-23 03:00:00,22808.55
2022-07-23 04:00:00,22895.41
2022-07-23 05:00:00,22902.46
2022-07-23 06:00:00,22827.46
2022-07-23 19:00:00,22272.57
2022-07-23 20:00:00,22325.82
2022-07-23 21:00:00,22243.32
2022-07-23 22:00:00,22469.08
2022-07-23 23:00:00,22451.07
2022-07-24 00:00:00,22549.18
2022-07-24 01:00:00,22423.58
2022-07-24 02:00:00,22469.09
2022-07-24 04:00:00,22396.51
2022-07-24 05:00:00,22749.98
2022-07-24 06:00:00,22679.01
2022-07-24 07:00:00,22701.61

df2数据： #

date,price,passed_bars
2022-07-23 02:00:00,22834.24,30.0
2022-07-23 19:00:00,22272.57,13.0
2022-07-24 04:00:00,22396.51,4.0

可通过以下代码生成这两个DataFrame：

import pandas as pd

li1 = [{'date': '2022-07-23 02:00:00', 'price': 22834.24}, {'date': '2022-07-23 03:00:00', 'price': 22808.55},
       {'date': '2022-07-23 04:00:00', 'price': 22895.41}, {'date': '2022-07-23 05:00:00', 'price': 22902.46},
       {'date': '2022-07-23 06:00:00', 'price': 22827.46}, {'date': '2022-07-23 19:00:00', 'price': 22272.57},
       {'date': '2022-07-23 20:00:00', 'price': 22325.82}, {'date': '2022-07-23 21:00:00', 'price': 22243.32},
       {'date': '2022-07-23 22:00:00', 'price': 22469.08}, {'date': '2022-07-23 23:00:00', 'price': 22451.07},
       {'date': '2022-07-24 00:00:00', 'price': 22549.18}, {'date': '2022-07-24 01:00:00', 'price': 22423.58},
       {'date': '2022-07-24 02:00:00', 'price': 22469.09}, {'date': '2022-07-24 04:00:00', 'price': 22396.51},
       {'date': '2022-07-24 05:00:00', 'price': 22749.98}, {'date': '2022-07-24 06:00:00', 'price': 22679.01},
       {'date': '2022-07-24 07:00:00', 'price': 22701.61}]

li2 = [{'date': '2022-07-23 02:00:00', 'price': 22834.24, 'passed_bars': 30.0},
       {'date': '2022-07-23 19:00:00', 'price': 22272.57, 'passed_bars': 13.0},
       {'date': '2022-07-24 04:00:00', 'price': 22396.51, 'passed_bars': 4.0}]

df1 = pd.DataFrame.from_records(li1)
df2 = pd.DataFrame.from_records(li2)

需求 #

为df1添加新列passed_time，列值计算逻辑：

新列的值为df1当前记录与df2中满足df1.date.iloc[i] >= nearest_to_current(df2.date)的最近记录之间的时间间隔，最终以“X hours”格式呈现。

解决方案 #

实现步骤：

1. 将两个DataFrame的date列转换为datetime类型，确保可进行时间计算
2. 使用pd.merge_asof匹配每个df1记录对应的df2中最近的不晚于当前时间的记录
3. 计算时间差并转换为小时数，最后格式化输出

完整代码：

import pandas as pd

# 转换date列为datetime类型
df1['date'] = pd.to_datetime(df1['date'])
df2['date'] = pd.to_datetime(df2['date'])

# 按时间匹配最近的df2记录
merged = pd.merge_asof(df1.sort_values('date'), 
                       df2.sort_values('date'),
                       on='date',
                       direction='backward')

# 计算时间间隔并格式化
merged['passed_time'] = (merged['date_x'] - merged['date_y']).dt.total_seconds() // 3600
merged['passed_time'] = merged['passed_time'].astype(int).astype(str) + ' hours'

# 整理列顺序并恢复原df1的顺序
result = merged[['date_x', 'price_x', 'passed_time']].rename(columns={'date_x': 'date', 'price_x': 'price'})
result = result.sort_values('date').reset_index(drop=True)

print(result.to_csv(index=False))

最终结果 #

运行代码后得到的目标DataFrame：

date,price,passed_time
2022-07-23 02:00:00,22834.24,0 hours
2022-07-23 03:00:00,22808.55,1 hours
2022-07-23 04:00:00,22895.41,2 hours
2022-07-23 05:00:00,22902.46,3 hours
2022-07-23 06:00:00,22827.46,4 hours
2022-07-23 19:00:00,22272.57,0 hours
2022-07-23 20:00:00,22325.82,1 hours
2022-07-23 21:00:00,22243.32,2 hours
2022-07-23 22:00:00,22469.08,3 hours
2022-07-23 23:00:00,22451.07,4 hours
2022-07-24 00:00:00,22549.18,5 hours
2022-07-24 01:00:00,22423.58,6 hours
2022-07-24 02:00:00,22469.09,7 hours
2022-07-24 04:00:00,22396.51,0 hours
2022-07-24 05:00:00,22749.98,1 hours
2022-07-24 06:00:00,22679.01,2 hours
2022-07-24 07:00:00,22701.61,3 hours

内容的提问来源于stack exchange，提问作者sci9