如何在同一个表(即类别和子类别)中使用第一级和第二级聚合进行透视
这是我的数据
Name Category Sub-Category sum
yuew Food Snack 100
dhjs Food Snack 50
jdsd Food Drink 60
kjkd Food Drink 60
sjdbk Food Snack 70
ndkbjd Clothes Shirt 80
ndcb Clothes Trousers 80
dbkjd Clothes Shirt 90
我试着写代码
table_pivot = table.pivot_table(values='sum',index=['Category','Sub-Category'],aggfunc=np.sum)
这是结果,结果仅针对每个子类别的总和
Category Sub Category Sum
Food Snack 220
Drink 120
Clothes Shirt 170
Trousers 80
我需要的结果不仅是每个子类别的总和,而且是类别级别的结果
Category Sub Category Sum
Food 340
Snack 220
Drink 120
Clothes 250
Shirt 170
Trousers 80
我该怎么做
请您参考如下方法:
首先对每个级别使用sum,然后创建MultiIndex,append到原始和最后sort_index :
df = table_pivot.sum(level=0)
df = df.set_index([df.index.values, [''] * len(df)])
print (df)
sum
Category Sub-Category
Clothes 250
Food 340
df = table_pivot.append(df).sort_index()
print (df)
sum
Category Sub-Category
Clothes 250
Shirt 170
Trousers 80
Food 340
Drink 120
Snack 220
