Python:一次包含重复值的多个列的 Pandas 数据透视表

2022-01-22 00:00:00 python pandas dataframe pivot group-by

问题描述

有一个包含名称、学校和标记列的 pandas 数据框

have a pandas dataframme with columns name , school and marks

name  school  marks

tom     HBS     55
tom     HBS     55
tom     HBS     14
mark    HBS     28
mark    HBS     19
lewis   HBS     88

如何转置和转换成这样的

How to transpose and convert into like this

name  school  marks_1 marks_2 marks_3

tom     HBS     55     55       14
mark    HBS     28     19
lewis   HBS     88

试过这个:

df = df.pivot_table(index='name', values='marks', columns='school') 
    .reset_index() 
    .rename_axis(None, axis=1)

print(df)


df = df.pivot('name','marks','school')

检查了这些链接

https://stackoverflow.com/questions/22798934/pandas-long-to-wide-reshape-by-two-variables
https://stackoverflow.com/questions/62391419/pandas-group-by-and-convert-rows-into-multiple-columns
https://stackoverflow.com/questions/60698109/pandas-multiple-rows-to-single-row-with-multiple-columns-on-2-indexes

由于重复值而出现此错误.如果存在重复如何处理，我们必须保留它们

getting this error due to duplicate values. how to handle if duplicate exists and we have to keep them

ValueError: Index contains duplicate entries, cannot reshape

解决方案

尝试使用 set_index 和 unstack 与 groupby 和 cumcount:

Try using set_index and unstack with groupby and cumcount:

df_out = df.set_index(['name',
                       'school',
                       df.groupby(['name','school'])
           .cumcount() +1]).unstack()
df_out.columns = [f'{i}_{j}' for i, j in df_out.columns]
df_out = df_out.reset_index()
df_out

输出:

    name school  marks_1  marks_2  marks_3
0  lewis    HBS     88.0      NaN      NaN
1   mark    HBS     28.0     19.0      NaN
2    tom    HBS     55.0     55.0     14.0

相关文章