根据要求在熊猫数据透视表中添加几行

以下是我尝试无法获得所需输出的内容,

d = {
'ID': ['A', 'B', 'A', 'B', 'C', 'A Erik', 'B Erik', 'D', 'B Erik'],
'Month': ['20-Apr', '20-May', '20-Jun', '20-Apr', '20-Aug', '20-Apr', '20-May', '20-Aug', '20-Apr'],
'Value1': [10, 10, 10, 10, 10, 10, 10, 10, 10],
'Value2': [20, 20, 20, 20, 20, 20, 20, 20, 20],
'Value3': [30, 30, 30, 30, 30, 30, 30, 30, 30],
'Value4': [40, 40, 40, 40, 40, 40, 40, 40, 40]
}

dfa = pd.DataFrame(d)

dfa = dfa.iloc[pd.to_datetime(dfa['Month'], format='%y-%b').argsort()]
dfa['Month'] = pd.Categorical(dfa['Month'], ordered=True, categories=dfa['Month'].unique())

df_pvt =    pd.pivot_table(dfa, values=['Value1','Value2', 'Value3', 'Value4'],
                        index=['ID'],
                        columns=['Month'],
                        aggfunc=np.sum,
                        fill_value=None)

df_pvt = df_pvt.swaplevel(0,1, axis=1)
df_pvt = df_pvt.sort_index(axis=1, level='Month')
df = df_pvt.sum(axis=1, level=1)
df.columns = pd.MultiIndex.from_product([['Total'], df.columns])

added = df_pvt.columns.levels[0].add_categories(['Total'])
df_pvt.columns = df_pvt.columns.set_levels(added, level=0)

df_pvt = df_pvt.join(df)

df_pvt.loc['Grand Total'] = df_pvt.sum()

added = df_pvt.columns.levels[0].add_categories(['Total of Total'])
df_pvt.columns = df_pvt.columns.set_levels(added, level=0)

df_pvt["Total of Total"] = df_pvt["Total"].sum(axis=1)
df_pvt

预期输出,如红色正方形所示。尝试在数据透视表中添加以下行。总计每月总计,Erik总计总计(包括A_Erik,B_Erik),每月D总计。

enter image description here

评论