更改DataFrame Pivot_table标头以将其保存在Excel文件中

Question

旋转数据框后，我在数据框架中有一个嵌套的听觉器，我想使其平坦，每个行上都有重复的项目（例如在Excel中） 你可以帮我吗？

import pandas as pd

data = {
  "year": [2022, 2022 , 2021 , 2021 , 2020 ],
  "client": ["A", "B", "B", "C", "C"],
  "product" : [ "P1", "P2" , "P1", "P2", "P1"],
  "sales" : [ 10,20, 20, 22, 25]

}

df = pd.DataFrame(data)

df2 = df.pivot_table ( index = ["year","client"] , columns = ["product"] , values = ["sales"] , aggfunc ="sum")
df2

              sales
product      P1     P2
year    client      
2020    C   25.0    NaN
2021    B   20.0    NaN
        C   NaN     22.0
2022    A   10.0    NaN
        B   NaN     20.0

我想在哪里获得平坦的标题和拔出的行：

year    client P1   P2  
2020    C   25.0    NaN
2021    B   20.0    NaN
2021    C   NaN     22.0
2022    A   10.0    NaN
2022    B   NaN     20.0

非常感谢

Nico

Answer 1

您需要使用droplevel（0），该将删除顶级（销售）。设置columns.name <代码>无将删除products。因此，在创建df2之后，添加以下行...

>> df2.columns = df2.columns.droplevel(0)
>> df2.columns.name = None
>> df2 =df2.reset_index()
>> df2

    year    client  P1  P2
0   2020    C   25.0    NaN
1   2021    B   20.0    NaN
2   2021    C   NaN 22.0
3   2022    A   10.0    NaN
4   2022    B   NaN 20.0

Answer 2

您可以尝试

df = (df2.droplevel(0, axis=1)
      .rename_axis(None, axis=1)
      .reset_index())

print(df)

   year client    P1    P2
0  2020      C  25.0   NaN
1  2021      B  20.0   NaN
2  2021      C   NaN  22.0
3  2022      A  10.0   NaN
4  2022      B   NaN  20.0