Skip to main content
 首页 » 编程设计

python pandas pivot_table 列一级错误名称

2024年11月01日17findumars

我有下表:

       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”。

提前致谢

请您参考如下方法:

通过交换 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