Pandas DataFrame分组聚合：行转列并合并同组数据

问题描述 #

尝试在Pandas中将行转换为不同列，现有代码按PARENT_PART和CITY分组统计数量后，通过循环添加城市列，但输出存在重复行。需求是：对每个重复的PARENT_PART保留一行，创建以CITY命名的计数列，合并同组行得到目标结果。

现有代码 #

d = {'PARENT_PART': ['KRC161262', 'KRC161262', 'KRC161833', 'KRC161834', 'KRC161834'], 'CITY': ['BARCELONA', 'MADRID', 'BARCELONA', 'BARCELONA', 'MADRID'], 'GOOD_OR_FAULTY': ['GOOD', 'GOOD', 'GOOD','GOOD','FAULT']}

df = pd.DataFrame(data=d)

grouped1 = df.groupby(['PARENT_PART', 'CITY']).size().reset_index(name='counts')

for index, row in grouped1.iterrows():    
    ciudad = row['CITY']    
    codigo = row['PARENT_PART']
    counts = grouped1.loc[(grouped1['PARENT_PART'] == codigo) & (grouped1['CITY'] == ciudad), 'counts'].values[0]
    df.loc[index, ciudad] = counts
print(df)

当前输出 #

PARENT_PART       CITY GOOD_OR_FAULTY  BARCELONA  MADRID
0   KRC161262  BARCELONA           GOOD        1.0     NaN
1   KRC161262     MADRID           GOOD        NaN     1.0
2   KRC161833  BARCELONA           GOOD        1.0     NaN
3   KRC161834  BARCELONA           GOOD        1.0     NaN
4   KRC161834     MADRID          FAULT        NaN     1.0

期望结果 #

PARENT_PART  GOOD_OR_FAULTY  BARCELONA  MADRID
0   KRC161262  GOOD            1.0        1.0
2   KRC161833  GOOD            1.0        NaN
3   KRC161834  GOOD            1.0        1.0

解决方案 #

不用循环，利用Pandas的unstack方法直接将分组后的CITY转为列，再合并GOOD_OR_FAULTY信息，高效解决重复行问题：

import pandas as pd

d = {'PARENT_PART': ['KRC161262', 'KRC161262', 'KRC161833', 'KRC161834', 'KRC161834'], 
     'CITY': ['BARCELONA', 'MADRID', 'BARCELONA', 'BARCELONA', 'MADRID'], 
     'GOOD_OR_FAULTY': ['GOOD', 'GOOD', 'GOOD','GOOD','FAULT']}
df = pd.DataFrame(data=d)

# 分组统计各PARENT_PART在对应城市的数量，将CITY转为列
city_count_df = df.groupby(['PARENT_PART', 'CITY']).size().unstack()

# 提取每个PARENT_PART的GOOD_OR_FAULTY（这里优先取非FAULT的值，可根据需求调整）
good_faulty_series = df.groupby('PARENT_PART')['GOOD_OR_FAULTY'].apply(
    lambda x: x[x != 'FAULT'].iloc[0] if any(x != 'FAULT') else x.iloc[0]
)

# 合并数据并调整列顺序
result = pd.concat([good_faulty_series, city_count_df], axis=1).reset_index()
result = result[['PARENT_PART', 'GOOD_OR_FAULTY', 'BARCELONA', 'MADRID']]

print(result)

输出结果 #

PARENT_PART GOOD_OR_FAULTY  BARCELONA  MADRID
0   KRC161262           GOOD        1.0     1.0
1   KRC161833           GOOD        1.0     NaN
2   KRC161834           GOOD        1.0     1.0

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