反枢轴多级索引Pandas

vsaztqbk  于 2023-06-28  发布在  其他
关注(0)|答案(1)|浏览(107)

我有如下所示的 Dataframe ,我想做的是反透视的 Dataframe ,有多个索引。这是我目前所尝试的

df = pd.DataFrame([[2016, 2016, 2015, 2015], 
                       ['Dollar Sales', 'Unit Sales', 'Dollar Sales', 'Unit Sales'], 
                       [1, 2, 3, 4], [5, 6, 7, 8]], columns=[*'ABCD'])
    df['Dates'] = ['date','Dates','10/12','06/08']
    new_labels = pd.MultiIndex.from_frame(df.iloc[:2].T.astype(str), names=['Year', 'Sales'])
    df1 = df.set_axis(new_labels, axis=1).iloc[2:]
    df1 = df1.stack()
    df1 = df1.reset_index()

我得到的结果是

Year    level_0 Sales   2015    2016    date
0   2   Dates            NaN    NaN    10/12
1   2   Dollar Sales      3      1      NaN
2   2   Unit Sales        4      2      NaN
3   3   Dates            NaN    NaN     06/08
4   3   Dollar Sales     7       5       NaN
5   3   Unit Sales       8       6       NaN

然而,我想要的最终结果是

Year level_0    Sales       2015    2016     Dates
    1   2     Dollar Sales    3      1       10/12
    2   2     Unit Sales      4      2       10/12
    4   3     Dollar Sales    7      5       06/08
    5   3     Unit Sales      8      6       06/08

我如何才能做到这一点?

zqdjd7g9

zqdjd7g91#

您可以将df1行代码更改为以下内容:

# as before, but also stack by column index level 1
df1 = df.set_axis(new_labels, axis=1).iloc[2:].stack(level=1)

# forward fill date column
df1.date.fillna(method="ffill", inplace=True)

# reset index, same as your code
df1.reset_index(inplace=True)

# drop rows where Sales == "Dates"
df1 = df1.loc[df1.Sales.ne("Dates")]

相关问题