CODEWITHSUNDEEP
pythonpandasgroup-bysummulti-index
spcol
spcol

Reputation: 433

Summing column values based on multiindex

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.

Upvotes: 2

Views: 42

Answers (2)

spcol
spcol

Reputation: 433

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

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

Upvotes: 1

Umar.H
Umar.H

Reputation: 23099

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

Upvotes: 2

Related Questions