我有下表:
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
