CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
Ragno13
Ragno13

Reputation: 45

Combining rows on a Dataframe based on a specific column value and add other values

So I have a dataframe and I'm looking to combine two of the same column values together but leave all the others as they were.

For example:

c1            c2

shark         20
pizza         25
asteroid      10
shark         90
asteroid      30

And I want it to combine everything in the c1 column with ONLY the value shark and add the values from c2 so I get this:

c1            c2

shark         110
pizza         25
asteroid      10
asteroid      30

groupby combines everything which is not what I want.

I'm guessing using df.loc will get me somewhere but I'm not sure how to apply it back to the dataframe so it's changes it.

Upvotes: 1

Views: 253

Answers (2)

Paul Brennan
Paul Brennan

Reputation: 2706

Here is a go

import pandas as pd
import numpy as np

df = pd.DataFrame({'c1' : ['shark', 'pizza', 'asteroid', 'shark', 'asteroid'], 'c2': [20, 25, 10, 90, 30]})
pd.pivot_table(df,index='c1',aggfunc=np.sum)

           c2
c1           
asteroid   40
pizza      25
shark     110

Upvotes: 0

Mayank Porwal
Mayank Porwal

Reputation: 34086

You can do this using Groupby.sum and df.append:

Pick rows from df where c1 == 'shark', groupby and sum c2. Then append the remaining rows where c1 != 'shark' to the earlier dataframe.

In [2009]: x = df[df.c1 == 'shark'].groupby('c1', as_index=False).sum()
In [2012]: res = x.append(df[df.c1 != 'shark'])

In [2013]: res
Out[2013]: 
         c1   c2
0     shark  110
1     pizza   25
2  asteroid   10
4  asteroid   30

Upvotes: 2

Related Questions