使用Pandas GroupBy转换后DataFrame配偶信息丢失的问题排查

我的数据集里，配偶记录以单独行存储，和户主共享同一ID。需求是按ID分组后，求和数值列，并将配偶行的信息转移到户主行的配偶列中，实现一行对应一个家庭。目前数值求和功能正常，但配偶列全部显示NaN，求问题原因及解决办法。

输入数据 #

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  Position  Title  First  Last  SpTitle  SpFirst  SpLast  Address    Value1  Value2  Value3
0  456 HoH       Mr.    John   Doe   NaN      NaN      NaN      123 street  20      NaN     60
1  789 HoH       Mrs.   Jane   Doe   NaN      NaN      NaN      456 road    500     710     930

现有代码 #

import pandas as pd
import numpy as np

# Read csv and combine sheets
df = pd.concat(pd.read_excel("C:/Users/Sheet.xlsx", sheet_name=None), ignore_index=True)

# Drop blank IDs
df = df[df['ID'].notna()]

# Insert Spouse columns
df.insert(loc = 10, column='SpTitle', value = '')
df.insert(loc = 11, column='SpFirstName', value = '')
df.insert(loc = 12, column='SpMiddleName', value = '')
df.insert(loc = 13, column='SpLastname', value = '')
df.insert(loc = 14, column='SpBirthDate', value = '')
df.insert(loc = 15, column='SpGender', value = '')

m = df.Position.eq("Spouse")

df.loc[m, ["SpTitle", "SpFirstName", "SpMiddleName", "SpLastName", "SpBirthDate", "SpGender" ]] = df.loc[
    m, ["Title", "First Name", "Middle Name", "Last Name", "Gender", "Date of Birth"]
].values

df[["Value 2019", "Value 2020", "Value 2021", "Value 2022", "Fund 2019", "Fund 2020", "Fund 2022", "Fund 2021"]] = df.groupby("ID", as_index=False)[
    ["Value  2019", "Value 2020", "Value 2021", "Value 2022", "Fund 2019", "Fund 2020", "Fund 2022", "Fund 2021"]
].transform(np.sum, min_count=1)

df[["SpTitle", "SpFirstName", "SpMiddleName", "SpLastName", "SpBirthDate", "SpGender"]] = df.groupby("ID", as_index=False)[
    ["SpTitle", "SpFirstName", "SpMiddleName", "SpLastName", "SpBirthDate", "SpGender"]
].transform(lambda x: x.ffill().bfill())

df = df[~m].drop_duplicates()

df.to_csv("C:/Users/data.csv", index = False)

问题原因分析 #

• 列名不匹配：
  1. 插入配偶列时用的是SpLastname（末尾小写n），但后续赋值和分组时用的是SpLastName（末尾大写N），导致配偶姓氏列始终无法被正确赋值。
  2. 源数据里的名字列是First、Last，但代码里赋值时用的是First Name、Last Name，这些列在输入数据中不存在，导致取到空值，配偶列自然为空。
• 初始值问题：插入配偶列时用空字符串''填充，后续ffill()/bfill()对空字符串无效，无法将配偶行的信息填充到户主行。

修复后的代码 #

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()]

# 插入配偶列，统一列名并用NaN初始化
sp_cols = ['SpTitle', 'SpFirstName', 'SpMiddleName', 'SpLastName', 'SpBirthDate', 'SpGender']
for idx, col in enumerate(sp_cols, start=10):
    df.insert(loc=idx, column=col, value=np.nan)

m = df.Position.eq("Spouse")

# 匹配源数据列名，将配偶行信息赋值到配偶列
df.loc[m, sp_cols] = df.loc[m, ['Title', 'First', 'Middle', 'Last', 'Gender', 'Date of Birth']].values

# 数值列求和（可根据实际数据替换为年份列）
value_cols = ['Value1', 'Value2', 'Value3']
df[value_cols] = df.groupby('ID')[value_cols].transform(np.sum, min_count=1)

# 按ID分组填充配偶信息，同步到户主行
df[sp_cols] = df.groupby('ID')[sp_cols].transform(lambda x: x.ffill().bfill())

# 保留户主行并去重
df = df[~m].drop_duplicates(subset=['ID'])

df.to_csv("C:/Users/data.csv", index=False)

修复说明 #

1. 统一配偶列命名，确保SpLastName在所有操作中一致；
2. 匹配源数据的真实列名，保证赋值时能取到正确数据；
3. 用np.nan初始化配偶列，让ffill()/bfill()可以正常填充空值；
4. 调整数值列求和逻辑匹配输入数据，实际使用时可替换回原代码中的年份列。

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