# How to sum values grouped by two columns in pandas

I have a Pandas DataFrame like this:

df = pd.DataFrame({
'Date': ['2017-1-1', '2017-1-1', '2017-1-2', '2017-1-2', '2017-1-3'],
'Groups': ['one', 'one', 'one', 'two', 'two'],
'data': range(1, 6)})

Date      Groups     data
0  2017-1-1    one       1
1  2017-1-1    one       2
2  2017-1-2    one       3
3  2017-1-2    two       4
4  2017-1-3    two       5


How can I generate a new DataFrame like this:

    Date       one     two
0  2017-1-1    3        0
1  2017-1-2    3        4
2  2017-1-3    0        5


pivot_table was made for this:

df.pivot_table(index='Date',columns='Groups',aggfunc=sum)


results in

         data
Groups    one  two
Date
2017-1-1  3.0  NaN
2017-1-2  3.0  4.0
2017-1-3  NaN  5.0


Personally I find this approach much easier to understand, and certainly more pythonic than a convoluted groupby operation. Then if you want the format specified you can just tidy it up:

df.fillna(0,inplace=True)
df.columns = df.columns.droplevel()
df.columns.name = None
df.reset_index(inplace=True)


which gives you

       Date  one  two
0  2017-1-1  3.0  0.0
1  2017-1-2  3.0  4.0
2  2017-1-3  0.0  5.0

• Nice! This should be the accepted answer. – tuomastik Jul 20 '17 at 5:40
• @Josh D. This's cool and straightforward! I agree that it takes some brain power to figure out how groupby works. Thank you! – Kevin Jul 20 '17 at 11:55

Pandas black magic:

df = df.groupby(['Date', 'Groups']).sum().sum(
level=['Date', 'Groups']).unstack('Groups').fillna(0).reset_index()

# Fix the column names
df.columns = ['Date', 'one', 'two']


Resulting df:

       Date  one  two
0  2017-1-1  3.0  0.0
1  2017-1-2  3.0  4.0
2  2017-1-3  0.0  5.0

• Holy! The black magic is so powerful! Thanks a lot! – Kevin Jul 10 '17 at 18:59
• You're welcome! See the updated answer; I simplified the expression and added a fix for the column names to be exactly as requested. – tuomastik Jul 10 '17 at 19:11
• I think your previous version has its advantage since it can be applied to other more complicated data sets. I copied it here: df.groupby(['Date', 'Groups', 'data'])['data'].sum().sum(level=['Date', 'Groups']).unstack('Groups').fillna(0) – Kevin Jul 10 '17 at 19:37
df.groupby(['Date', 'Groups']).sum().unstack('Groups', fill_value=0).reset_index()