CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
messy748
messy748

Reputation: 327

Pandas: How to combine nunique and sum

I am working with a dataframe similar to this:

id year losses revenue expenses 2 2014 1500 5000 400 1 2013 1000 2000 5600 1 2018 500 10000 2100 3 2019 1500 15000 500 2 2011 100 2100 500 4 2010 1200 400 2000 4 2014 1000 22000 1000

I would like to sort by the unique values in the id column and find the sum of losses revenue and expenses My desired result would like like

id losses revenue expenses 2 1600 7100 900 1 1500 12000 7700 3 1500 15000 500 4 2200 22400 3000

I tried using

df.groupby('id')['losses', 'revenue', 'expenses'].sum().reset_index()

but that returns more columns than it should. I tried to utilize nunique() to get the unique values for id then get the sums of the remaining columns from that, but am struggling to find a way for that to work

Upvotes: 1

Views: 963

Answers (1)

jezrael
jezrael

Reputation: 862921

Add parameters sort=False and as_index=False to DataFrame.groupby:

df = df.groupby('id', sort=False, as_index=False)['losses', 'revenue', 'expenses'].sum()
print (df)
   id  losses  revenue  expenses
0   2    1600     7100       900
1   1    1500    12000      7700
2   3    1500    15000       500
3   4    2200    22400      3000

Upvotes: 1

Related Questions