如何用Pandas将同一邮编的各收入组报税数据合并为单行？

问题背景 #

需要从人口普查数据中创建DataFrame，计算每个邮编下各收入组的报税人数。当前代码通过循环生成对应收入组的列，但同一邮编对应多行不同收入组数据，尝试填充NaN为0后用groupby('zipcode').sum()合并时，出现统计值异常（如zipcode 0的数值远超预期）。

当前代码：

census_df = pd.read_csv('../zip code data/19zpallagi.csv')
sub_census_df = census_df[['zipcode', 'agi_stub', 'N02650', 'A02650', 'ELDERLY', 'A07180']].copy()

num_of_returns = ['Number_of_returns_1_25000', 'Number_of_returns_25000_50000', 'Number_of_returns_50000_75000',
                            'Number_of_returns_75000_100000', 'Number_of_returns_100000_200000', 'Number_of_returns_200000_more']

for i, column_name in zip(range(1, 7), num_of_returns):
    sub_census_df[column_name] = sub_census_df[sub_census_df['agi_stub'] == i]['N02650']

当前DataFrame示例（仅展示zipcode 0的行）：

zipcode agi_stub    N02650  A02650  ELDERLY A07180  Number_of_returns_1_25000   Number_of_returns_25000_50000   Number_of_returns_50000_75000   Number_of_returns_75000_100000  Number_of_returns_100000_200000 Number_of_returns_200000_more   Amount_1_25000  Amount_25000_50000  Amount_50000_75000  Amount_75000_100000 Amount_100000_200000    Amount_200000_more
0   0   1   778140.0    10311099.0  144610.0    2076.0  778140.0    NaN NaN NaN NaN NaN 10311099.0  NaN NaN NaN NaN NaN
1   0   2   525940.0    19145621.0  113810.0    17784.0 NaN 525940.0    NaN NaN NaN NaN NaN 19145621.0  NaN NaN NaN NaN
2   0   3   285700.0    17690402.0  82410.0 9521.0  NaN NaN 285700.0    NaN NaN NaN NaN NaN 17690402.0  NaN NaN NaN
3   0   4   179070.0    15670456.0  57970.0 8072.0  NaN NaN NaN 179070.0    NaN NaN NaN NaN NaN 15670456.0  NaN NaN
4   0   5   257010.0    35286228.0  85030.0 14872.0 NaN NaN NaN NaN 257010.0    NaN NaN NaN NaN NaN 35286228.0  NaN

期望的DataFrame格式（单行展示单个邮编的所有收入组数据）：

zipcode Number_of_returns_1_25000   Number_of_returns_25000_50000   Number_of_returns_50000_75000   Number_of_returns_75000_100000  Number_of_returns_100000_200000 Number_of_returns_200000_more   
0   0   778140.0                              525940.0                         285700.0     179070.0                 257010.0   850.0

错误原因分析 #

1. 循环生成新列的方式存在索引对齐隐患，容易导致NaN填充后数据错位；
2. 直接使用groupby('zipcode').sum()会对所有数值列（包括原表的N02650、A02650等）进行求和，导致无关数据被错误累加，比如zipcode 0作为汇总行，其各收入组的原始值被重复计算，最终统计值异常；
3. 原代码未聚焦核心需求字段，引入了不必要的列（如ELDERLY、A07180），增加了数据处理的复杂度。

正确实现方法 #

使用pivot（或pivot_table）直接完成数据重塑，避免循环和多余列的干扰，高效实现按邮编聚合收入组数据：

import pandas as pd

# 读取数据，仅保留需要的核心字段
census_df = pd.read_csv('../zip code data/19zpallagi.csv')
sub_census_df = census_df[['zipcode', 'agi_stub', 'N02650']].copy()

# 定义agi_stub与收入组列名的映射关系
agi_stub_to_col = {
    1: 'Number_of_returns_1_25000',
    2: 'Number_of_returns_25000_50000',
    3: 'Number_of_returns_50000_75000',
    4: 'Number_of_returns_75000_100000',
    5: 'Number_of_returns_100000_200000',
    6: 'Number_of_returns_200000_more'
}

# 重塑数据：按zipcode分组，将agi_stub转为列，值为N02650（报税人数）
result_df = sub_census_df.pivot(
    index='zipcode',
    columns='agi_stub',
    values='N02650'
)

# 重命名列，匹配需求的收入组名称
result_df = result_df.rename(columns=agi_stub_to_col)

# 填充NaN为0（处理部分邮编无对应收入组数据的情况）
result_df = result_df.fillna(0).reset_index()

# 可选：如果不需要zipcode=0的全国汇总行，可删除
# result_df = result_df[result_df['zipcode'] != 0]

方法优势 #

• 无需循环，代码简洁高效，避免索引对齐错误；
• 仅处理核心字段（zipcode、agi_stub、N02650），避免无关列的干扰；
• 直接生成单行多列的目标格式，无需额外的groupby操作，统计结果准确。

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