Sum rows of multi-index dataframes but showing 0 in missing data

user_13072752 0
Viewed 44

I have a multi-index dataframe and want to sum the values. If there aren't rows for that I need to show 0 like in the example below

import pandas as pd

d = {'City': ['Tokyo','Tokyo','Lisbon','Tokyo','Madrid','New York','Madrid','London','Tokyo','London','Tokyo'], 
     'Card': ['Visa','Visa','Visa','Master Card','Bitcoin','Master Card','Bitcoin','Visa','Master Card','Visa','Bitcoin'],
     'Colateral':['Yes','Yes','No','No','Yes','No','No','Yes','Yes','No','Yes'],
     'Client Number':[1,2,3,4,5,6,7,8,9,10,11],
     'Total':[100,100,200,300,10,20,40,50,60,100,500]}

df = pd.DataFrame(data=d)

df.set_index(['City','Card','Colateral']).drop(['Client Number'],axis=1)
df.sum(level=[0,1])

The result I wish to accomplish is something like this

enter image description here

multi-index pandas dataframe python-3.x python
1 Answers

Use DataFrame.reindex with MultiIndex.from_product:

df1 = (df.set_index(['City','Card','Colateral'])
         .drop(['Client Number'],axis=1)
         .sum(level=[0,1,2]))


df2 = df1.reindex(pd.MultiIndex.from_product(df1.index.levels), fill_value=0)

print (df2)
         Total
City     Card        Colateral       
Lisbon   Bitcoin     No             0
                     Yes            0
         Master Card No             0
                     Yes            0
         Visa        No           200
                     Yes            0
London   Bitcoin     No             0
                     Yes            0
         Master Card No             0
                     Yes            0
         Visa        No           100
                     Yes           50
Madrid   Bitcoin     No            40
                     Yes           10
         Master Card No             0
                     Yes            0
         Visa        No             0
                     Yes            0
New York Bitcoin     No             0
                     Yes            0
         Master Card No            20
                     Yes            0
         Visa        No             0
                     Yes            0
Tokyo    Bitcoin     No             0
                     Yes          500
         Master Card No           300
                     Yes           60
         Visa        No             0
                     Yes          200
user_29010020
Related