Python实现同ID配偶行转列并指定列求和方案问询

问题背景 #

现有数千行数据，配偶信息单独成一行但与户主共享相同ID，部分ID对应数十行数据。需求为：当ID匹配时，将配偶行转为列合并到户主行，同时对指定数值列求和。

输入数据示例：

ID  Position  Title  First  Last  SpTitle  SpFirst  SpLast  Address    Value1  Value2  Value3
0  456 HoH       Mr.    John   Doe   NaN      NaN      NaN     123 street  10      NaN     30
1  456 Spouse    Mrs.   Jane   Doe   NaN      NaN      NaN     123 street  10      NaN     30
2  789 HoH       Mrs.   Jane   Doe   NaN      NaN      NaN     456 road    100     200     300
3  789 HoH       Mrs.   Jane   Doe   NaN      NaN      NaN     456 road    400     500     600
4  789 Spouse    Mr.    John   Doe   NaN      NaN      NaN     456 road    NaN     10      30

期望输出示例：

ID  Position  Title  First  Last  SpTitle  SpFirst  SpLast  Address    Value1  Value2  Value3
0  456 HoH       Mr.    John   Doe   Mrs.     Jane     Doe     123 street  20      NaN     60
1  789 HoH       Mrs.   Jane   Doe   Mr.      John     Doe     456 road    500     710     930

解决方案思路 #

核心逻辑是拆分户主与配偶数据后按ID合并，再通过分组聚合完成数值求和与信息合并，规避原代码中transform导致的重复计算问题：

• 拆分户主（HoH）和配偶（Spouse）两个子集
• 重命名配偶的个人信息列为专属字段，避免合并时列冲突
• 按ID将配偶数据合并到户主数据中
• 对同一ID下的户主多行数据，保留唯一基础信息，同时对数值列求和

修正后的代码 #

import pandas as pd
import numpy as np

# 读取并合并所有工作表
df = pd.concat(pd.read_excel("C:/Users/Sheet.xlsx", sheet_name=None), ignore_index=True)

# 过滤空ID行
df = df[df['ID'].notna()]

# 1. 拆分户主和配偶数据
hoh_df = df[df['Position'] == 'HoH'].copy()
spouse_df = df[df['Position'] == 'Spouse'].copy()

# 2. 重命名配偶的个人信息列为配偶专属字段
spouse_rename_map = {
    'Title': 'SpTitle',
    'First Name': 'SpFirstName',
    'Middle Name': 'SpMiddleName',
    'Last Name': 'SpLastName',
    'Date of Birth': 'SpBirthDate',
    'Gender': 'SpGender'
}
spouse_df = spouse_df.rename(columns=spouse_rename_map)

# 只保留配偶的ID和专属字段，避免合并时列冲突
spouse_df = spouse_df[['ID'] + list(spouse_rename_map.values())]

# 3. 按ID合并户主和配偶数据
merged_df = pd.merge(hoh_df, spouse_df, on='ID', how='left')

# 4. 定义聚合规则：个人信息取第一个有效值，数值列求和
agg_rules = {
    # 户主基础信息
    'Position': 'first',
    'Title': 'first',
    'First Name': 'first',
    'Middle Name': 'first',
    'Last Name': 'first',
    'Address': 'first',
    # 配偶信息
    'SpTitle': 'first',
    'SpFirstName': 'first',
    'SpMiddleName': 'first',
    'SpLastName': 'first',
    'SpBirthDate': 'first',
    'SpGender': 'first',
    # 需要求和的数值列
    'Value 2019': np.sum,
    'Value 2020': np.sum,
    'Value 2021': np.sum,
    'Value 2022': np.sum,
    'Fund 2019': np.sum,
    'Fund 2020': np.sum,
    'Fund 2021': np.sum,
    'Fund 2022': np.sum
}

# 按ID分组聚合，得到最终结果
final_df = merged_df.groupby('ID', as_index=False).agg(agg_rules)

# 导出结果
final_df.to_csv("C:/Users/data.csv", index=False)

代码说明 #

• 拆分数据后逻辑更清晰，避免原代码中transform导致的重复赋值问题
• 配偶列重命名后合并，确保字段不冲突
• 聚合规则明确区分个人信息（取唯一有效值）和数值列（求和），完全匹配需求
• 处理效率更高，适配数千行的数据集规模

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