使用Pandas实现复杂分组与转换的技术求助

需求说明 #

• 按location和box分组，统计每个location下的唯一box数量（即期望输出中的box count）
• 将status列的取值转为列名，统计每个box对应的各状态出现次数

原始数据 #

ID  location    type    box     status          
aa  NY          no      box55   hey         
aa  NY          no      box55   hi          
aa  NY          yes     box66   hello           
aa  NY          yes     box66   goodbye         
aa  CA          no      box11   hey         
aa  CA          no      box11   hi          
aa  CA          yes     box11   hello           
aa  CA          yes     box11   goodbye         
aa  CA          no      box86   hey         
aa  CA          no      box86   hi          
aa  CA          yes     box86   hello           
aa  CA          yes     box99   goodbye         
aa  CA          no      box99   hey         
aa  CA          no      box99   hi  

期望输出 #

location    box count   box     hey hi  hello   goodbye 
NY          2           box55   1   1   0       0   
NY          2           box66   0   0   1       1   
CA          3           box11   1   1   1       1   
CA          3           box86   1   1   1       0   
CA          3           box99   1   1   0       1   

尝试代码 #

df['box count'] = df.groupby(['location','box'])['box'].size()

t = pd.get_dummies(df, prefix_sep='', prefix='', 
columns=['status']).groupby(['box', 'location'], 
as_index=False).sum().assign(count=df.groupby(['box', 'location'],
as_index=False)['status'].size()['size'])

优化建议 #

你的尝试代码存在两个核心问题：

1. 第一行代码统计的是每个(location, box)组的行数，而非location下的唯一box总数，不符合需求
2. 重复执行多次分组操作，代码冗余且效率较低

方案一：分步骤合并（清晰易读） #

import pandas as pd

# 1. 统计每个location下的唯一box数量
location_box_count = df.groupby('location')['box'].nunique().reset_index(name='box count')

# 2. 透视统计每个(location, box)的各status计数
status_summary = df.pivot_table(
    index=['location', 'box'],
    columns='status',
    values='ID',  # 任意非空列均可用于计数
    aggfunc='count',
    fill_value=0
).reset_index()

# 3. 合并结果并调整列顺序
final_result = pd.merge(status_summary, location_box_count, on='location')
final_result = final_result[['location', 'box count', 'box', 'hey', 'hi', 'hello', 'goodbye']]

print(final_result)

方案二：用transform简化（更简洁） #

import pandas as pd

# 直接给每行添加对应location的box总数
df['box count'] = df.groupby('location')['box'].transform('nunique')

# 一次透视完成所有统计
final_result = df.pivot_table(
    index=['location', 'box count', 'box'],
    columns='status',
    values='ID',
    aggfunc='count',
    fill_value=0
).reset_index()

# 调整列顺序匹配期望输出
final_result = final_result[['location', 'box count', 'box', 'hey', 'hi', 'hello', 'goodbye']]

print(final_result)

优化点说明 #

• 用nunique()精准统计唯一box数量，替代错误的size()
• 用pivot_table替代get_dummies+groupby.sum，代码更简洁，逻辑更直观
• 减少重复分组操作，提升运行效率
• 最终列顺序完全匹配期望输出格式

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