如何在Pandas中按小时区间将权重合并至日期型DataFrame

需求说明 #

已在Pandas中构建了特定时间戳区间的权重表weight_df，需要将这些权重分配到另一个以小时级datetime为索引的销售数据表price_df中，根据weight_df的时间区间为price_df的每行匹配对应的w1和w2权重。

weight_df（每日权重表） #

import pandas as pd

weight_df = pd.DataFrame({
    'Datetime': ['2021-01-03 05:00:00', '2021-01-04 05:00:00', '2021-01-05 05:00:00'],
    'w1': [0.961538, 0.923077, 0.884615],
    'w2': [0.038462, 0.076923, 0.115385]
})

price_df（小时级销售数据表） #

price_df = pd.DataFrame({
    'Price_1': [2.630859, 2.634766, 2.628906, 2.623047, 2.628906, 2.632812,
                2.634766, 2.638672, 2.626953, 2.619141, 2.615234, 2.619141,
                2.695312, 2.689453],
    'Quantity_1': [3127.0, 601.0, 1162.0, 605.0, 306.0, 496.0,
                   458.0, 673.0, 1903.0, 1500.0, 1075.0, 597.0,
                   1401.0, 1021.0],
    'Price_2': [2.607422, 2.609375, 2.607422, 2.601562, 2.605469, 2.609375,
                2.611328, 2.613281, 2.603516, 2.597656, 2.593750, 2.597656,
                2.660156, 2.658203],
    'Quantity_2': [507.0, 218.0, 369.0, 69.0, 50.0, 35.0,
                   59.0, 128.0, 316.0, 190.0, 231.0, 123.0,
                   289.0, 211.0]
}, index=pd.to_datetime([
    '2021-01-03 18:00:00', '2021-01-03 19:00:00', '2021-01-03 20:00:00',
    '2021-01-03 21:00:00', '2021-01-03 22:00:00', '2021-01-03 23:00:00',
    '2021-01-04 00:00:00', '2021-01-04 01:00:00', '2021-01-04 02:00:00',
    '2021-01-04 03:00:00', '2021-01-04 04:00:00', '2021-01-04 05:00:00',
    '2021-01-05 04:00:00', '2021-01-05 05:00:00'
]))

解决方案 #

普通merge方法无法处理区间匹配，这里用pd.merge_asof实现时间区间的权重映射，步骤如下：

1. 处理权重表的时间区间
   先将weight_df的时间列转为datetime类型，并添加权重生效的结束时间（下一条权重的开始时间即为当前权重的结束时间）：

   # 转换时间类型
   weight_df['Datetime'] = pd.to_datetime(weight_df['Datetime'])
   # 添加结束时间列
   weight_df['end_datetime'] = weight_df['Datetime'].shift(-1)
   # 最后一行的结束时间设为最大时间戳，确保覆盖后续所有数据
   weight_df.loc[weight_df.index[-1], 'end_datetime'] = pd.Timestamp.max
   

2. 准备销售数据表
   确保price_df的索引是datetime类型，并重置索引以便合并：

   # 确保索引为datetime类型（如果未转换的话）
   price_df.index = pd.to_datetime(price_df.index)
   # 重置索引，将Datetime转为列
   price_reset = price_df.reset_index().sort_values('Datetime')
   # 权重表按时间排序（merge_asof要求左右表都按匹配键排序）
   weight_df = weight_df.sort_values('Datetime')
   

3. 执行区间合并
   使用merge_asof匹配每个小时数据对应的权重区间：

   result = pd.merge_asof(
       price_reset,
       weight_df[['Datetime', 'end_datetime', 'w1', 'w2']],
       left_on='Datetime',
       right_on='Datetime',
       right_end='end_datetime',
       direction='backward'
   )
   # 恢复原索引并排序
   result = result.set_index('Datetime').sort_index()
   

示例输出 #

合并后的结果符合预期，部分数据如下：

print(result.loc[['2021-01-03 18:00:00', '2021-01-04 04:00:00', '2021-01-04 05:00:00']])

输出：

Price_1  Quantity_1  Price_2  Quantity_2        w1        w2
Datetime                                                                         
2021-01-03 18:00:00  2.630859      3127.0  2.607422       507.0  0.961538  0.038462
2021-01-04 04:00:00  2.615234      1075.0  2.593750       231.0  0.961538  0.038462
2021-01-04 05:00:00  2.619141       597.0  2.597656       123.0  0.923077  0.076923

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