CODEWITHSUNDEEP
pythonpandas
Vincent
Vincent

Reputation: 1647

Pandas Groupby combining columns for aggregating

I have a Pandas dataframe like below. I what to find the sum of TOTACTVAL value for each OWNNAME(1,2)

    OWNNAME1     OWNNAME2   TOTACTVAL
0     Fred       John           100
1     Scott       Fred          200
2     John         Pascal        150
3     Connor      Fred          50

The result I expect is sorted by TOTACTVAL

Name    TOTACTVAL
Fred    350
John    250
Scott   200
Pascal  150
Connor  50

Upvotes: 0

Views: 66

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28313

Set index on 'TOTACTVAL' and unstack to level -1 and reset index

newdf = df.set_index('TOTACTVAL').unstack(-1).reset_index()

Rename the unstacked column

newdf.rename(columns={0: 'Name'}, inplace=True)

Group, Sum, and Sort. The reset_index is needed to transform the resulting Series (with Name index) back to a DataFrame with two columns (Name, TOTACTVAL)

newdf = newdf.groupby('Name')['TOTACTVAL'].apply(sum).reset_index()
newdf.sort('TOTACTVAL', ascending=False)

This prints:

     Name  TOTACTVAL
1    Fred        350
2    John        250
4   Scott        200
3  Pascal        150
0  Connor         50

Upvotes: 1

Related Questions