Sum a column value using multi-index pivot tables in Pandas Python

Question

I have data like this

Employed    Coverage    Education   Amount
No          Basic       Bachelor    541.8029122
No          Extended    Bachelor    312.6400955
No          Premium     Bachelor    427.9560121
No          Basic       Bachelor    91.17931022
No          Basic       Bachelor    533.6890081
Yes         Basic       Bachelor    683.484326
Yes         Basic       College     586.2670885
No          Premium     Master      725.0412884
Yes         Basic       Bachelor    948.3628611

I want to sum the amount with the multi indexed pivot table such that it looks like the following. Here is link I am following but not able to get the correct results

Need your help.

Answer 1

This is one way.

import pandas as pd
import io
import json

s = '''\
Employed    Coverage    Education   Amount
No          Basic       Bachelor    541.8029122
No          Extended    Bachelor    312.6400955
No          Premium     Bachelor    427.9560121
No          Basic       Bachelor    91.17931022
No          Basic       Bachelor    533.6890081
Yes         Basic       Bachelor    683.484326
Yes         Basic       College     586.2670885
No          Premium     Master      725.0412884
Yes         Basic       Bachelor    948.3628611'''

# Recreate the dataframe
df = pd.read_csv(io.StringIO(s), sep='\s+')

Actual code:

df['Coverage'] = df['Coverage'].astype('category')

pd.pivot_table(df, index='Education', columns=['Employed', 'Coverage'],
               values='Amount', aggfunc='sum', fill_value=0)

# Employed            No                                  Yes                 
# Coverage         Basic    Extended     Premium        Basic Extended Premium
# Education                                                                   
# Bachelor   1166.671231  312.640096  427.956012  1631.847187      0.0     0.0
# College       0.000000    0.000000    0.000000   586.267088      0.0     0.0
# Master        0.000000    0.000000  725.041288     0.000000      0.0     0.0

Notes:

• Conversion to categories ensure all scenarios are reported for that series.
• Pivot table default calculation is mean, so sum must be explicitly specified.