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

Question

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

jezrael · Accepted Answer · 2020-06-29 11:48:41Z

3

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

answered Jun 29, 2020 at 11:48

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tiago Emanuel Pratas Over a year ago

Thank you very much for the help!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related