Sum based on criteria in row and column conditions

Question

I have a dataframe in Pandas that looks something like this:

Year  Type  Money  
2012    A   2  
2012    A   3  
2012    B   4  
2012    B   5  
2012    C   7  
2013    A   6  
2013    A   4  
2013    B   3  
2013    B   2  
2013    C   1  
2014    A   3  
2014    A   4  
2014    B   5

I want to sum it up as such:

        A   B   C  
2012    5   9   7  
2013    10  5   1  
2014    7   5   0

For instance, the first entry of 5 is a sum of all entries in the data from year 2012 and with Type A.

Is there a simple way to go about doing this? I know how to go about this using SUMIFS in Excel but want to avoid that if possible.

Welcome to StackOverflow! See also https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe, especially question 4 :) — Jondiedoop, Jun 11 '19 at 20:07

score 1 · Accepted Answer · answered Jun 11 '19 at 19:58

1

Try:

df.groupby(['Year','Type']).Money.sum().unstack(level=1).fillna(0)

Output:

Type     A    B    C
Year                
2012   5.0  9.0  7.0
2013  10.0  5.0  1.0
2014   7.0  5.0  0.0

answered Jun 11 '19 at 19:58

Quang Hoang

146,074
10
56
74

Sum based on criteria in row and column conditions

1 Answers1