Summing column values based on multiindex

Question

I have a multiindex dataframe which for simplicity is as follows;

                      Values    
01-01-2010 Belgium    1    
           Belgium    7
           UK         3 
           UK         4
           UK         2
           France     1
           France     3
02-01-2010 Belgium    4
           UK         7
           UK         10
           UK         2
           France     4

I need to try and sum the value for each country for each day. The actual dataframe has approx 10 years of data and 40 countries.

Is there any simple way of using the resample() function to do this? I can't seem to get one working with the multiindex. I could perhaps convert the countries back to a column?

Any help much appreciated.

Answer 1

groupby your indices by specifying the levels

df2 = df.groupby(level=[0,1])['Values'].sum()
print(df2)
01-01-2010   Belgium       8
             France        4
             UK            9
02-01-2010   Belgium       4
             France        4
             UK           19
Name: Values, dtype: int64