Calculate percentage of grouped values

Question

I have a Pandas dataframe that looks like"

I calculated the number of Win, Lost and Draw for each year, and now it looks like:

My goal is to calculate the percentage of each score group by year. To become like:

But I stuck here. I looked in this thread but was not able to apply it on my df.

Any thoughts?

score 2 · Accepted Answer · answered Mar 28 '21 at 11:18

Here is quite a simple method I wrote for this task:

Just do as follows:

create a dataframe of the total score within each year:

total_score = df.groupby('year')['score'].sum().reset_index(name = 'total_score_each_year')

merge the original and the new dataframe into a single dataframe:

df = df.merge(total_score, on = 'year')

calculate the percents:

df['percentages'] = 100 * (df['score'] / df['total_score_each_year'])

That's it, I hope it helps :)

score 0 · Answer 2 · answered Mar 28 '21 at 11:00

You could try using : df.iat[row, column]

it would look something like this:

percentages = []
for i in range(len(df) // 3):
  draws = df.iat[i, 2]
  losses = df.iat[i + 1, 2]
  wins = df.iat[i + 2, 2]
  nbr_of_games = draws + losses + wins
  percentages.append[draws * 100/nbr_of_games] #Calculate percentage of draws
  percentages.append[losses * 100/nbr_of_games] #Calculate percentage of losses
  percentages.append[wins * 100/nbr_of_games] #Calculate percentage of wins

df["percentage"] = percentages

This may not be the fastest way to do it but i hope it helps !

score 0 · Answer 3 · answered Mar 28 '21 at 13:26

Similar to @panter answer, but in only one line and without creating any additional DataFrame:

df['percentage'] = df.merge(df.groupby('year').score.sum(), on='year', how='left').apply(
    lambda x: x.score_x * 100 / x.score_y,  axis=1
)

In detail:

df.groupby('year').score.sum() creates a DataFrame with the sum of the score per year.
df.merge creates a Dataframe equal to the original df, but with the column score renamed to score_x and an additional column score_y, that represents the sum of all the scores for the year of each row; the how='left' keeps only row in the left DataFrame, i.e., df.
.apply computes for each the correspondent percentage, using score_x and score_y (mind the axis=1 option, to apply the lambda row by row).

Calculate percentage of grouped values

3 Answers3