如何在Pandas中按多列匹配后从一个DataFrame减去另一个？

我有一个Pandas DataFrame（df），包含按School、District、Program、Grade和Month分组的学校出勤总人数信息，数据如下：

School    District    Program    Grade    Month     Count
   123         456          A     9-12       10       100
   123         456          B     9-12       10        95
   321         654          A     9-12       10        23
   321         456          A      7-8       10        40

部分Count数值存在虚高，需要根据另一个DataFrame（ToSubtract）的数据进行扣减，ToSubtract数据如下：

School    District    Program    Grade    Month     Count
   123         456          A     9-12       10        10
   321         654          A     9-12       10         8

两个DataFrame均已完成分组，不存在重复分组。需要将ToSubtract的Count值从df对应行的Count中扣除，最终结果需新增一列标记哪些行的数值被修改（示例中X列用*标记），预期结果如下：

School    District    Program    Grade    Month     Count  X
   123         456          A     9-12       10        90  *
   123         456          B     9-12       10        95
   321         654          A     9-12       10        15  *
   321         456          A      7-8       10        40

我曾尝试使用df.sub()，但发现元素需要严格对齐；另一个想法是用df.iterrows()遍历每一行检查匹配，但效率极低。请问按多列匹配后，从一个DataFrame减去另一个的最优方法是什么？

方法1：使用merge匹配并计算 #

通过merge将两个DataFrame按分组列关联，完成扣减后合并回原数据，同时标记修改行：

import pandas as pd

# 定义分组列
group_cols = ['School', 'District', 'Program', 'Grade', 'Month']

# 重命名ToSubtract的Count列，避免合并后列名冲突
to_subtract_renamed = ToSubtract.rename(columns={'Count': 'Subtract_Count'})

# 左连接原df和ToSubtract
merged = df.merge(to_subtract_renamed, on=group_cols, how='left')

# 计算扣减后的Count：有Subtract_Count则减去，否则保留原数值
merged['Count'] = merged['Count'] - merged['Subtract_Count'].fillna(0)

# 添加X列标记修改行：Subtract_Count不为空则标记*，否则为空
merged['X'] = merged['Subtract_Count'].apply(lambda x: '*' if pd.notna(x) else '')

# 删除临时列，得到最终结果
result = merged.drop(columns=['Subtract_Count'])

方法2：设置分组列为索引后使用sub #

利用Pandas的索引对齐特性，设置分组列为索引后直接做减法，数据量较大时性能更优：

import pandas as pd

group_cols = ['School', 'District', 'Program', 'Grade', 'Month']

# 将两个DataFrame的分组列设为索引，仅保留Count列
df_indexed = df.set_index(group_cols)['Count']
subtract_indexed = ToSubtract.set_index(group_cols)['Count']

# 执行减法，不存在的行用0填充
df_indexed = df_indexed.sub(subtract_indexed, fill_value=0)

# 恢复索引为普通列，得到新的Count列
result = df_indexed.reset_index(name='Count')

# 添加X列标记：检查原df的行是否存在于ToSubtract中
subtract_keys = set(ToSubtract[group_cols].apply(tuple, axis=1))
result['X'] = result[group_cols].apply(tuple, axis=1).map(lambda x: '*' if x in subtract_keys else '')

两种方法都避免了低效的行遍历，其中方法2利用索引对齐特性，在大数据量场景下表现更出色。

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