25
$\begingroup$

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
$\endgroup$
18
$\begingroup$

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
| improve this answer | |
$\endgroup$
  • 1
    $\begingroup$ Nice! This should be the accepted answer. $\endgroup$ – tuomastik Jul 20 '17 at 5:40
  • $\begingroup$ @Josh D. This's cool and straightforward! I agree that it takes some brain power to figure out how groupby works. Thank you! $\endgroup$ – Kevin Jul 20 '17 at 11:55
9
$\begingroup$

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
| improve this answer | |
$\endgroup$
  • $\begingroup$ Holy! The black magic is so powerful! Thanks a lot! $\endgroup$ – Kevin Jul 10 '17 at 18:59
  • $\begingroup$ You're welcome! See the updated answer; I simplified the expression and added a fix for the column names to be exactly as requested. $\endgroup$ – tuomastik Jul 10 '17 at 19:11
  • $\begingroup$ 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) $\endgroup$ – Kevin Jul 10 '17 at 19:37
  • $\begingroup$ @Kevin If this or any future answer solved your problem, please accept the answer. $\endgroup$ – tuomastik Jul 11 '17 at 9:08
0
$\begingroup$

A (perhaps slightly more idiomatic) alternative to @tuomastik's answer:

df.groupby(['Date', 'Groups']).sum().unstack('Groups', fill_value=0).reset_index()
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.