create new dataframe grouped by one column and new columns

Question

import pandas as pd import numpy as np df = pd.DataFrame( { 'A': ['d','d','d','f','f','f','g','g','g','h','h','h'], 'B': [5,5,6,7,5,6,6,7,7,6,7,7], 'C': [1,1,1,1,1,1,1,1,1,1,1,1], 'S': [2012,2013,2014,2015,2016,2012,2013,2014,2015,2016,2012,2013] } ); df

I'd like to create a new dataframe, where column A is grouped and the sums of columns B and C are shown in new columns 2012, 2013, 2014, 2015 and 2016.

A B C S 0 d 5 1 2012 1 d 5 1 2013 2 d 6 1 2014 3 f 7 1 2015 4 f 5 1 2016 5 f 6 1 2012 6 g 6 1 2013 7 g 7 1 2014 8 g 7 1 2015 9 h 6 1 2016 10 h 7 1 2012 11 h 7 1 2013 — Zanshin
– Zanshin, Commented Nov 18, 2016 at 11:42

jezrael · Accepted Answer · 2016-11-18 11:44:09Z

2

You can use groupby with unstack:

df = (df.B + df.C).groupby([df.A, df.S]).sum().unstack(fill_value=0) print (df) S 2012 2013 2014 2015 2016 A d 6 6 7 0 0 f 7 0 0 8 6 g 0 7 8 8 0 h 8 8 0 0 7

df['D'] = df.B + df.C df = df.groupby(['A','S'])['D'].sum().unstack(fill_value=0) print (df) S 2012 2013 2014 2015 2016 A d 6 6 7 0 0 f 7 0 0 8 6 g 0 7 8 8 0 h 8 8 0 0 7

Another solution with pivot_table:

df['D'] = df.B + df.C df = df.pivot_table(index='A', columns='S', values = 'D', aggfunc='sum', fill_value=0) print (df) S 2012 2013 2014 2015 2016 A d 6 6 7 0 0 f 7 0 0 8 6 g 0 7 8 8 0 h 8 8 0 0 7

answered Nov 18, 2016 at 11:44

jezrael

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

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

6 Comments

Zanshin Over a year ago

thanks jezrael, this works. I do however, have another question regarding the previous one. if I want to have the column fill out -1 when Result is not 'H', how do I amend the np.where function? df3['profit'] = np.where(df3['Result'] == 'H', df3['column value H'],

Zanshin Over a year ago

what if want to only show the lines where all columns are > 0?

jezrael Over a year ago

sorry, I was offline. I add answer to your newest question. Is it correct? If not, can you change sample?

jezrael Over a year ago

Thank you, I edit answer. It is same as another, only difference is | (or) is changed to & and.

Zanshin Over a year ago

hi jezrael. what if I want to add in the count of values that have been summed in df = (df.B + df.C).groupby([df.A, df.S]).sum().unstack(fill_value=0), per year?

|

Collectives™ on Stack Overflow

create new dataframe grouped by one column and new columns

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related