python - 在python中将垂直表格转换为水平表格（展平表格）

Question

 import numpy as np
 import pandas as pd

df = pd.read_csv(“data.csv”)

pd.pivot_table(df, index = ‘Employee ID’ , values = [ ‘ Member ID’, ‘Firstname’, ‘Lastname’] , aggfunc =‘first)

该格式似乎有效，但仅适用于一个值，我如何显示一切？

任何帮助表示赞赏。

标签： pythonpython-3.xpandascsvpivot

Answer 1

您可以使用set_index()and unstack()，但您需要修复列，例如：

In []:
df = pd.read_csv(“data.csv”)

df['ID'] = df['MemberID']   # Copy because you want it in the values too
df = df.set_index(['EmployeeID', 'MemberID']).unstack(level=1, fill_value='').sort_index(level=1, axis=1)
df.columns = df.columns.to_series().apply(lambda x: 'Member{}{}'.format(x[1], x[0]))
print(df)

Out[]:
          Member1ID Member1Lastname Member1firstname Member2ID Member2Lastname Member2firstname Member3ID Member3Lastname Member3firstname 
EmployeeID                                                                                                   
1                  1             Ann              Anu         2             Ann              Aju        3             vAnn              Abi 
2                  1            John             Cini         2            John             Biju             
3                  1           Peter           Mathew         2           Peter           Joseph           

但是我觉得如果你真的不需要 MemberID 在值中（你在列名中有它）或者你不介意MultiIndex那么你可以简化：

In []:
df.set_index(['EmployeeID', 'MemberID']).unstack(level=1, fill_value='').swaplevel(axis=1).sort_index(axis=1)

Out[]:
MemberID          1                  2                  3          
           Lastname firstname Lastname firstname Lastname firstname
EmployeeID                                                         
1               Ann       Anu      Ann       Aju      Ann       Abi
2              John      Cini     John      Biju                   
3             Peter    Mathew    Peter    Joseph