pandas pivot table how to rearrange columns-CodePudding

I have a pandas df which I am looking to build a pivot table with. Here is a sample table

Name   Week   Category     Amount

ABC     1     Clothing      50
ABC     1     Food          10
ABC     1     Food          10
ABC     1     Auto          20
DEF     1     Food          10
DEF     1     Services      20

The pivot table I am looking to create is to sum up the amounts per Name, per week per category. Essentially, I am looking to land up with a table as follows:

Name  Week   Clothing   Food   Auto   Services   Total
ABC    1      50         20     20     0          90
DEF    1      0          10     0      20         30

If a user has no category value in a particular week, I take it as 0 And the total is the row sum.

I tried some of the options mentioned at https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html but couldnt get it to work...any thoughts on how I can achieve this. I used df.pivot_table(values=['Amount'], index=['Name','Week','Category'], aggfunc=[np.sum]) followed by df.unstack() but that did not yield the desired result as both Week and Category got unstacked. Thanks!

CodePudding user response：

df_pvt = pd.pivot_table(df, values = 'Amount', index = ['Name', 'Week'], columns = 'Category', aggfunc = np.sum, margins=True, margins_name = 'Total', fill_value = 0
df_pvt.columns.name = None
df_pvt = df_pvt.reset_index()

CodePudding user response：

Let us try crosstab

out = pd.crosstab(index = [df['Name'],df['Week']],
                  columns = df['Category'],
                  values=df['Amount'],
                  margins=True,
                  aggfunc='sum').fillna(0).iloc[:-1].reset_index()
Category Name Week  Auto  Clothing  Food  Services  All
0         ABC    1  20.0      50.0  20.0       0.0   90
1         DEF    1   0.0       0.0  10.0      20.0   30