如何用Pandas按相同name键汇总字典列表的qty与income字段

原始数据代码 #

import pandas as pd
customer1 = {'name': 'John Smith',"qty": 10, 'income': 35, 'email': 'john.smith1@email.com'}
customer2 = {'name': 'John Smith', "qty": 10,'income': 28, 'phone': '555-555-5555',"other": "something", 'email': 'john.smith@email.com'}
customer3 = {'name': 'Bob Johnson',"qty": 10,'income': 20, 'address': '123 Main St', 'email': 'bob.johnson@email.com',"c2":"abc","c3":"edf"}
customer4 = {'name': 'Joe Johnson', "qty": 10,'income': 8, 'address': '123 Main St', 'email': 'bob.johnson@email.com',"c2":"abc","c3":"edf"}

data = [customer1, customer2, customer3, customer4]
df = pd.DataFrame.from_dict(data)
print(df)

（注：原始代码重复定义customer3，此处修正为customer4避免数据覆盖）

需求说明 #

• 按name字段分组合并记录：
  • 对qty和income字段执行求和操作
  • 重复字段（如email）需添加序号后缀（如email2）区分不同记录的值
  • 其余非数值、非重复字段保留对应信息

期望输出结果 #

result = [
     {'name': 'John Smith', "qty": 20,'income': 63, 'phone': '555-555-5555',"other": "something", 'email': 'john.smith@email.com','email2': 'john.smith1@email.com'},
     {'name': 'Bob Johnson',"qty": 10,'income': 20, 'address': '123 Main St', 'email': 'bob.johnson@email.com',"c2":"abc","c3":"edf"},
     {'name': 'Joe Johnson', "qty": 10,'income': 8, 'address': '123 Main St', 'email': 'bob.johnson@email.com',"c2":"abc","c3":"edf"}
]

解决方案代码 #

import pandas as pd

# 初始化数据
customer1 = {'name': 'John Smith',"qty": 10, 'income': 35, 'email': 'john.smith1@email.com'}
customer2 = {'name': 'John Smith', "qty": 10,'income': 28, 'phone': '555-555-5555',"other": "something", 'email': 'john.smith@email.com'}
customer3 = {'name': 'Bob Johnson',"qty": 10,'income': 20, 'address': '123 Main St', 'email': 'bob.johnson@email.com',"c2":"abc","c3":"edf"}
customer4 = {'name': 'Joe Johnson', "qty": 10,'income': 8, 'address': '123 Main St', 'email': 'bob.johnson@email.com',"c2":"abc","c3":"edf"}

data = [customer1, customer2, customer3, customer4]
df = pd.DataFrame.from_dict(data)

# 1. 聚合数值字段：按name对qty和income求和
agg_df = df.groupby('name')[['qty', 'income']].sum().reset_index()

# 2. 处理重复字段email：给组内每个值添加序号后缀
email_df = df.groupby('name')['email'].apply(
    lambda x: x.reset_index(drop=True).rename(lambda i: f'email{i+1}' if i>0 else 'email')
).unstack()

# 3. 处理其他非数值字段：取组内首个非空值（可根据需求调整逻辑）
other_cols = [col for col in df.columns if col not in ['name', 'qty', 'income', 'email']]
other_df = df.groupby('name')[other_cols].first().reset_index()

# 4. 合并所有结果并转成字典列表
final_df = agg_df.merge(other_df, on='name').merge(email_df, on='name')
result = final_df.to_dict('records')

print(result)

代码说明 #

• 用groupby完成数值字段的求和聚合，得到每个name的核心统计值
• 对重复字段email，通过组内序号重命名后转成多列，实现字段区分
• 非数值字段取组内首个非空值，确保保留有效信息
• 最后合并所有数据模块，转成目标格式的字典列表

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