python pandas pivot_table 列一级错误名称

Question

我有下表:

       ID    Metric   Level    Level(% Change)    Level(Diff)
Index  
0     2016    A       10          NaN                   NaN
1     2017    A       15          0.5                    5
2     2018    A       20          0.3                    5 
3     2016    B       40          NaN                   NaN
4     2017    B       45          0.2                    5
5     2018    B       50          0.1                    5 

我想得到以下信息:

       A_Level   B_Level  A_Level(% Change) B_Level(% Change) A_Level(Diff) B_Level(Diff)
Index
2016     10        40          NaN               NaN              NaN         NaN    
2017     15        45          0.5               0.2               5           5
2018     20        50          0.3               0.1               5           5

我试过:

df = pd.pivot_table(df, index = 'ID', values = ['Level','Level(% Change)','Level(Diff)'], columns = ['Metric'])

df.columns = df.columns.map('_'.join)

但是我只得到下表:

        Level_A   Level_B  Level_A   Level_B    Level_A   Level_B
Index    
2016      10        40       NaN       NaN        NaN       NaN
2017      15        45       0.5       0.2         5         5
2018      20        50       0.3       0.1         5         5

基本上pivot中的数据是正确的，但是第一级列中的标签是错误的。只有“Level”，而“Level(% Change)”、“Level(Diff)”缺失。我也会得到“A_Level”而不是“Level_A”。

提前致谢

Answer 1

通过交换 a,b 和 f-strings 使用列表理解:

df = pd.pivot_table(df, 
                    index = 'ID', 
                    values = ['Level','Level(% Change)','Level(Diff)'], 
                    columns = ['Metric'])

df.columns = [f'{b}_{a}' for a, ab in df.columns]

或添加DataFrame.swaplevel :

df.columns = df.swaplevel(0,1, axis=1).columns.map('_'.join)

---

print (df)
      A_Level  B_Level  A_Level(% Change)  B_Level(% Change)  A_Level(Diff)  \
ID                                                                            
2016       10       40                NaN                NaN            NaN   
2017       15       45                0.5                0.2            5.0   
2018       20       50                0.3                0.1            5.0   

      B_Level(Diff)  
ID                   
2016            NaN  
2017            5.0  
2018            5.0