如何用Pandas基于另一DataFrame的时间区间条件填充成本列？

假设你使用Python的pandas库处理数据，以下是具体实现步骤：

1. 预处理成本DataFrame #

首先将成本表中valid_to为NaT的行替换为当前时间——这类成本规则仍在生效，未结束的消费时段均适用：

import pandas as pd
from datetime import datetime

# 示例成本DataFrame
cost_df = pd.DataFrame({
    'valid_from': pd.to_datetime(['2024-01-01 00:00', '2024-01-03 08:00', '2024-01-05 12:00']),
    'valid_to': pd.to_datetime(['2024-01-03 07:59', '2024-01-05 11:59', pd.NaT]),
    'cost': [1.5, 2.0, 2.5]
})

# 替换NaT为当前时间
cost_df['valid_to'] = cost_df['valid_to'].fillna(datetime.now())

2. 高效匹配消费时段与成本规则 #

推荐使用pd.merge_asof实现批量匹配，适合大数据量场景，前提是先对两个表按valid_from排序：

# 示例消费DataFrame
consumption_df = pd.DataFrame({
    'valid_from': pd.to_datetime(['2024-01-01 00:00', '2024-01-03 08:00', '2024-01-06 00:00']),
    'valid_to': pd.to_datetime(['2024-01-01 00:30', '2024-01-03 08:30', '2024-01-06 00:30']),
    'consumption': [100, 200, 150],
    'cost': [None, None, None]
})

# 对两个表按起始时间排序
cost_df_sorted = cost_df.sort_values('valid_from').reset_index(drop=True)
consumption_df_sorted = consumption_df.sort_values('valid_from').reset_index(drop=True)

# 执行时间匹配
merged = pd.merge_asof(
    consumption_df_sorted,
    cost_df_sorted,
    on='valid_from',
    direction='backward',
    suffixes=('', '_cost')
)

# 过滤无效匹配：排除消费时段结束时间晚于成本规则结束时间的情况
merged = merged[merged['valid_to'] <= merged['valid_to_cost']]

# 填充消费表的cost列
consumption_df['cost'] = merged['cost'].reindex(consumption_df.index)

3. 小数据量替代方案：逐行匹配 #

如果数据量较小，可使用apply逐行检查匹配：

def get_matching_cost(row):
    # 筛选覆盖当前消费时段的成本规则
    mask = (cost_df['valid_from'] <= row['valid_from']) & (cost_df['valid_to'] >= row['valid_to'])
    matching_cost = cost_df.loc[mask, 'cost']
    return matching_cost.iloc[0] if not matching_cost.empty else None

consumption_df['cost'] = consumption_df.apply(get_matching_cost, axis=1)

注意事项 #

• 确保所有时间列均为datetime类型，未转换的话先通过pd.to_datetime()处理
• 若存在多条成本规则同时覆盖一个消费时段，需额外处理冲突（比如取最新生效的规则）
• 大数据量场景下，merge_asof的效率远高于apply，优先选择前者

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