Pandas按原有列分组统计计数并生成新列实现长表转宽表

需求背景 #

• 输入为CSV格式数据集，包含species（物种名）、origin（数据来源）、count（计数字段）三个字段，样例数据：

species,origin,count
Bacillus acidicola,GenBank,1
Bacillus acidicola,RefSeq,1
Bacillus aerius,GenBank,1
Bacillus aerolatus,RefSeq,1

• 输出要求：按species维度聚合，生成genbank_count、refseq_count两列分别对应两个来源的计数，无数据的位置填充0，期望输出样例：

species,genbank_count, refseq_count
Bacillus acidicola,1, 1
Bacillus aerius,1, 0
Bacillus aerolatus,0,1

• 原有实现存在变量未定义、传参错误、逻辑偏差问题，无法得到正确结果，错误代码如下：

gen_bank = pd.read_csv('res.csv')
print(df.loc[gen_bank['0'] == 'GenBank'])

count = df.groupby(['species', 'origin']).size()

df.count().to_frame('counts').reset_index()

count['GeneBank'] = df.groupby(['species'], ['id']).size()

count['RefSeq'] = df.loc[df.origin == 'RefSeq', 'origin'].count()

正确实现代码 #

直接使用pivot_table做透视聚合是最简洁的实现方式，可自动处理缺失值填充：

import pandas as pd

# 读取原始数据
df = pd.read_csv('res.csv')

# 透视聚合：行索引为物种，列拆分为不同来源，值为count字段求和，空值填0
agg_df = pd.pivot_table(
    df,
    index="species",
    columns="origin",
    values="count",
    aggfunc="sum",
    fill_value=0
).reset_index()

# 重命名列匹配输出要求
agg_df.columns = ["species", "genbank_count", "refseq_count"]

# 打印结果/导出为CSV
print(agg_df)
agg_df.to_csv("species_count_result.csv", index=False)

如果偏好groupby写法，也可以用分组后反堆叠的方式实现，效果完全一致：

agg_df = df.groupby(["species", "origin"])["count"].sum().unstack(fill_value=0).reset_index()
agg_df.columns = ["species", "genbank_count", "refseq_count"]

说明：如果需要统计的是对应来源的记录条数而非累加count字段值，把上述代码中的.sum()替换为.count()即可。

运行后输出结果完全符合预期：

species  genbank_count  refseq_count
0  Bacillus acidicola              1             1
1       Bacillus aerius              1             0
2    Bacillus aerolatus              0             1

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