Creating a pivot table with multiple columns

Question

I'm trying to create a pivot table with multiple columns; I'm unsure how to explain this better. But the following is the desired output, dataframe setup, and code I have tried so far.

Desired Output:

Dataframe Setup:

data = {
 'WholesalerID': {0: 121, 1: 121, 2: 42, 3: 42, 4: 54, 5: 43, 6: 432, 7: 4245, 8: 4245, 9: 4245, 10: 457},
 'Brand': {0: 'Vans', 1: 'Nike', 2: 'Nike', 3: 'Vans',4: 'Vans', 5: 'Nike', 6: 'Puma', 7: 'Vans', 8: 'Nike', 9: 'Puma', 10: 'Converse'},
 'Shop 1': {0: 'Yes', 1: 'No', 2: 'Yes', 3: 'Maybe', 4: 'Yes', 5: 'No', 6: 'Yes', 7: 'Yes', 8: 'Maybe', 9: 'Maybe', 10: 'No'},
 'Shop 2': {0: 'No', 1: 'Yes', 2: 'Maybe', 3: 'Maybe', 4: 'Yes', 5: 'No', 6: 'No', 7: 'No', 8: 'Maybe', 9: 'Yes', 10: 'Yes'}
}

df = pd.DataFrame.from_dict(data)

Pivoting Attempt:

df = df.assign(count = 1)
pivoted_df = pd.pivot_table(df,
                            index = ['Brand'],
                            columns = ['Shop 1', 'Shop 2'],
                            values = ['count'],
                            aggfunc = {'count': 'count'},
                            fill_value = 0,
                            margins = True,
                            margins_name = 'Total'
)

jezrael · Accepted Answer · 2023-03-03T06:45:02.690

Use DataFrame.melt for unpivot, then convert column value to Categorical for correct sorting final MultiIndex, reshape by crosstab, add subtotal per first level of columns and append to original with add level Total:

df = df.melt(id_vars='Brand', value_vars=['Shop 1', 'Shop 2'])
df['value'] = pd.Categorical(df['value'], ordered=True, categories=df['value'].unique())
                 .add_categories(['Total'])

pivoted_df = pd.crosstab(df['Brand'], [df['variable'], df['value']])

totals = (pd.concat({'Total': pivoted_df.groupby(level=0, axis=1).sum()}, axis=1)
            .swaplevel(axis=1))

pivoted_df = pd.concat([pivoted_df, totals],axis=1).sort_index(axis=1)
print (pivoted_df)
variable Shop 1                Shop 2               
value       Yes No Maybe Total    Yes No Maybe Total
Brand                                               
Converse      0  1     0     1      1  0     0     1
Nike          1  2     1     4      1  1     2     4
Puma          1  0     1     2      1  1     0     2
Vans          3  0     1     4      1  2     1     4

The totals are needed at the individual Shop levels and not combined. — M J, Mar 03 '23 at 06:43

Creating a pivot table with multiple columns

1 Answers1