Summing column values based on multiindex

user_12684429 0
Viewed 36

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.

multi-index pandas group-by sum python
2 Answers

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
user_93751020

Actually, think this will work if I allow the index to be columns first;

sum_df = df.groupby(['Time','From Country']).agg({'Value': 'sum'})
user_126844290
Related