Pandas合并两个DataFrame按ID更新数据并统计ID累计出现次数

Pandas滚动更新存量DataFrame实现方案 #

核心逻辑 #

完全匹配你的三个需求：

• 右连接保留所有df2的ID，自动过滤仅存在于df1的ID
• Shape字段统一取df2的最新值
• 同时存在于两表的ID，count在原有df1的基础上+1，仅存在于df2的IDcount取默认值

可运行代码 #

import pandas as pd
import numpy as np

# 构造你提供的带count的输入数据
grp1 = {'ID': ['1','2','3','4','5'],
        'Shape': ['Rectangle','Rectangle','Square','Rectangle','Square'],
        'count': [1,1,1,1,1]
       }
grp2 = {'ID': ['3','4','5','6','7'],
        'Shape': ['Rectangle','Rectangle','Square','Rectangle','Square'],
        'count': [1,1,1,1,1]
       }
df1 = pd.DataFrame(grp1, columns= ['ID','Shape','count'])
df2 = pd.DataFrame(grp2, columns= ['ID','Shape','count'])

# 右连接合并，自动过滤仅存在于df1的ID
merged = pd.merge(df1, df2, on='ID', how='right', suffixes=('_old', '_new'))

# 生成最终结果
result = pd.DataFrame()
result['ID'] = merged['ID']
result['Shape'] = merged['Shape_new']
# 高效计算count，数据量大时性能远高于apply
result['count'] = np.where(pd.notna(merged['count_old']), merged['count_old'] + 1, merged['count_new']).astype(int)
result = result.reset_index(drop=True)

print(result)

输出结果 #

ID      Shape  count
0  3  Rectangle      2
1  4  Rectangle      2
2  5     Square      2
3  6  Rectangle      1
4  7     Square      1

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