How to group merge columns based on one row identifier with pandas?

user_12903733 0
Viewed 40

I have a dataset, in which it has a lot of entries for a single location. I am trying to find a way to sum up all of those entries without affecting any of the other columns. So, just in case I'm not explaining it well enough, I want to use a dataset like this:

Locations   Cyclists   maleRunners   femaleRunners   maleCyclists   femaleCyclists
Bedford     10         12            14              17             27
Bedford     11         40            34              9              1
Bedford     7          1             2               3              3
Leeds       1          1             2               0              0
Leeds       20         13            6               1              1
Bath        101        20            33              41             3
Bath        11         2             3               1              0

And turn it into something like this:

Locations   Cyclists   maleRunners   femaleRunners   maleCyclists   femaleCyclists
Bedford     28         53            50              29             31
Leeds       21         33            39              1              1
Bath        111        22            36              42             3

Now, I have read up that a groupby should work in a way, but from my understanding a group by will change it into 2 columns and I don't particularly want to make hundreds of 2 columns and then merge it all. Surely there's a much simpler way to do this?

pandas dataset merge
2 Answers

IIUC, groupby+sum will work for you:

df.groupby('Locations',as_index=False,sort=False).sum()

Output:

  Locations  Cyclists  maleRunners  femaleRunners  maleCyclists  femaleCyclists
0   Bedford        28           53             50            29              31
1     Leeds        21           14              8             1               1
2      Bath       112           22             36            42               3
user_136762020

Pivot table should work for you.

new_df = pd.pivot_table(df, values=['Cyclists', 'maleRunners', 'femalRunners', 
                                     'maleCyclists','femaleCyclists'],index='Locations', aggfunc=np.sum)
user_129690010
Related