Pandas按组计算两个DataFrame列差值的索引不兼容问题求解

原代码问题分析 #

• 分组键错误：对df2聚合时误用ID2作为分组键，你需要按ID1+时间粒度聚合才能得到每个ID1对应value2的总和
• 索引不兼容：两个groupby结果的索引范围不完全一致，直接相减会触发索引对齐错误，缺失匹配项的位置会产生NaN甚至报错

实现逻辑 #

1. 按ID1+时间粒度聚合df2，得到每个组合下value2的总和
2. 将聚合结果与df1左连接，保证df1的所有行都保留
3. 计算value2总和 - value1，将缺失匹配项的结果填充为0

完整可运行代码 #

import pandas as pd

# 测试数据
data_1 = {'ID1':['0', '1'], 'timestamp':[pd.Timestamp('2021-01-01-00:00:00'), pd.Timestamp('2021-01-01-00:00:00')], 'value1':[5, 7]}
data_2 = {'ID2':['3', '4'], 'timestamp':[pd.Timestamp('2021-01-01-00:00:00'), pd.Timestamp('2021-01-01-00:00:00')], 'value2':[4, 3], 'ID1':['0', '0']}
df1 = pd.DataFrame(data=data_1)
df2 = pd.DataFrame(data=data_2)

# 定义时间粒度，可按需修改为D、M等
time_freq = 'H'

# 聚合df2得到每个ID1+时间粒度的value2总和
df2_agg = df2.groupby(
    ['ID1', pd.Grouper(key='timestamp', freq=time_freq)],
    as_index=False
)['value2'].sum()

# 先将df1的timestamp对齐到对应时间粒度，避免精度不匹配
df1['time_grp'] = df1['timestamp'].dt.floor(time_freq)
df2_agg['time_grp'] = df2_agg['timestamp'].dt.floor(time_freq)

# 左连接匹配聚合结果
df1 = df1.merge(
    df2_agg[['ID1', 'time_grp', 'value2']],
    on=['ID1', 'time_grp'],
    how='left'
)

# 计算差值，缺失匹配项填0
df1['calculated'] = (df1['value2'] - df1['value1']).fillna(0)

# 清理辅助列
df1 = df1.drop(columns=['time_grp', 'value2'])

# 可选：转换为整数类型
df1['calculated'] = df1['calculated'].astype(int)

print(df1)

运行输出 #

ID1           timestamp  value1  calculated
0   0 2021-01-01 00:00:00       5           2
1   1 2021-01-01 00:00:00       7           0

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