CODEWITHSUNDEEP
pandaspandas-groupby
Danish
Danish

Reputation: 2871

Groupby sum of two column and create new dataframe in pandas

I have a dataframe as shown below

Player       Goal      Freekick
Messi        2         5
Ronaldo      1         4
Messi        1         4
Messi        0         5
Ronaldo      0         9
Ronaldo      1         8
Xavi         1         1
Xavi         0         7

From the above I would like do groupby sum of Goal and Freekick as shown below.

Expected Output:

Player     toatal_goals      total_freekicks
Messi      3                 14
Ronaldo    2                 21
Xavi       1                 8

I tried below code:

df1 = df.groupby(['Player'])['Goal'].sum().reset_index().rename({'Goal':'toatal_goals'})
df1['total_freekicks'] = df.groupby(['Player'])['Freekick'].sum()

But above one does not work, please help me..

Upvotes: 1

Views: 29

Answers (2)

Allen Qin
Allen Qin

Reputation: 19957

You can use namedagg to create the aggregations with customized column names.

(
    df.groupby(by='Player')
    .agg(toatal_goals=('Goal', 'sum'),
         total_freekicks=('Freekick', 'sum'))
    .reset_index()
)

Player     toatal_goals      total_freekicks
Messi      3                 14
Ronaldo    2                 21
Xavi       1                 8

Upvotes: 1

jezrael
jezrael

Reputation: 863166

First aggregate sum by Player, then DataFrame.add_prefix and convert columns names to lowercase:

df = df.groupby('Player').sum().add_prefix('total_').rename(columns=str.lower)
print (df)
         total_goal  total_freekick
Player                             
Messi             3              14
Ronaldo           2              21
Xavi              1               8

Upvotes: 1

Related Questions