summing rows in multi-index pandas dataframe

Question

I have a Pandas dataframe with a multiindex

             A         B
year  age  
1895   0     10        12
1895   1     13        14
...
1965   0     34        45
1965   1     41        34
      ...
1965  50     56        22
1966   0     10        34
...

I would like to get all ages between two values (e.g. 10 and 20) summed for column A (and B). I played around a bit with .xs e.g.

pops.xs(20, level='age')

gives all the age 20 for each year, but I cannot get this for multiple ages (and summed).

Eg. for 0 and 1 I would like to get

Any suggetions for an elegant (efficient) way to do that?

          A         B
year    
1895      23        26
...
1965      75        79
...

df.sum(level=0)

cs95
– cs95

2018-05-30 15:47:17 +00:00
Commented May 30, 2018 at 15:47 — cs95
– cs95, Commented May 30, 2018 at 15:47

jezrael · Accepted Answer · 2018-05-30 15:56:13Z

1

Use query for select with sum per first level years:

print (df)
           A   B
year age        
1895 8    10  12
     12   13  14
1965 0    34  45
     14   41  34
     12   56  22
1966 0    10  34

df = df.query('10 <= age <= 20').sum(level=0)
print (df)
       A   B
year        
1895  13  14
1965  97  56

Detail:

print (df.query('10 <= age <= 20'))
           A   B
year age        
1895 12   13  14
1965 14   41  34
     12   56  22

Another solution is use Index.get_level_values for index and filter by boolean indexing:

i = df.index.get_level_values('age')
print (i)
Int64Index([8, 12, 0, 14, 12, 0], dtype='int64', name='age')

df = df[(i >= 10) & (i <= 20)].sum(level=0)
print (df)
       A   B
year        
1895  13  14
1965  97  56

edited May 30, 2018 at 15:56

answered May 30, 2018 at 15:50

jezrael

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

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

Comments

Ben.T · Accepted Answer · 2018-05-30 16:47:22Z

0

You can use loc and slice to select the part of the DF you want such as:

df.loc[(slice(None),slice(10,20)),:].sum(level=0)

where (slice(None),slice(10,20)) allows you to keep all indexes for all years and age between 10 and 20 included

answered May 30, 2018 at 16:47

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

Collectives™ on Stack Overflow

summing rows in multi-index pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related