2

Of all the Medals won by these 5 countries across all olympics, what is the percentage medals won by each one of them?

i have combined all excel file in one using panda dataframe but now stuck with finding percentage

    Country      Gold     Silver    Bronze  Total
0   USA          10       13         11      34
1   China        2        2          4       8
2   UK           1        0          1       2
3   Germany      12       16         8       36
4   Australia    2        0          0       2
0   USA          9        9          7       25
1   China        2        4          5       11
2   UK           0        1          0       1
3   Germany      11       12         6       29
4   Australia    1        0          1       2
0   USA          9        15         13      37
1   China        5        2          4       11
2   UK           1        0          0       1
3   Germany      10       13         7       30
4   Australia    2        1          0       3

Combined data sheet

Code that i have tried till now

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
df= pd.DataFrame()
for f in ['E:\\olympics\\Olympics-2002.xlsx','E:\\olympics\\Olympics- 
2006.xlsx','E:\\olympics\\Olympics-2010.xlsx',
      'E:\\olympics\\Olympics-2014.xlsx','E:\\olympics\\Olympics- 
2018.xlsx']:
data = pd.read_excel(f,'Sheet1')
df = df.append(data)

df.to_excel("E:\\olympics\\combineddata.xlsx")
data = pd.read_excel("E:\\olympics\\combineddata.xlsx")
print(data)

final_Data={}
for i in data['Country']:
x=i
t1=(data[(data.Country==x)].Total).tolist()

print("Name of Country=",i, int(sum(t1)))
final_Data.update({i:int(sum(t1))})


t3=data.groupby('Country').Total.sum()
t2= df['Total'].sum()
t4= t3/t2*100
print(t3)
print(t2)
print(t4)

this how is got the answer....Now i need to pull that in plot i want to put it pie

Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
Saud Ansari
  • 29
  • 1
  • 7

2 Answers2

2

Let's assume you have created the DataFrame as 'df'. Then you can do the following to first group by and then calculate percentages.

df = df.groupby('Country').sum()
df['Gold_percent']   = (df['Gold']   / df['Gold'].sum()) * 100
df['Silver_percent'] = (df['Silver'] / df['Silver'].sum()) * 100
df['Bronze_percent'] = (df['Bronze'] / df['Bronze'].sum()) * 100
df['Total_percent']  = (df['Total']  / df['Total'].sum()) * 100
df.round(2)

print (df)

The output will be as follows:

           Gold  Silver  Bronze  ...  Silver_percent  Bronze_percent  Total_percent
Country                          ...                                               
Australia     5       1       1  ...            1.14            1.49           3.02
China         9       8      13  ...            9.09           19.40          12.93
Germany      33      41      21  ...           46.59           31.34          40.95
UK            2       1       1  ...            1.14            1.49           1.72
USA          28      37      31  ...           42.05           46.27          41.38
Joe Ferndz
  • 8,417
  • 2
  • 13
  • 33
  • Let me know if you have any questions with the answer i posted. If the answer addressed your question, you may want to please read about what to do when someone [answers](https://stackoverflow.com/help/someone-answers) your question. – Joe Ferndz Sep 08 '20 at 05:27
  • Also, you may want to look into reading multiple excel files and creating a single dataframe. Not sure why you have combined data processed while you have already done the single ones. Look at example from [here](https://stackoverflow.com/questions/20908018/import-multiple-excel-files-into-python-pandas-and-concatenate-them-into-one-dat) – Joe Ferndz Sep 08 '20 at 05:31
  • ....thank you for you answer....I have combine them as question is Of all the Medals won by these 5 countries across all olympics, what is the percentage medals won by each one of them..... – Saud Ansari Sep 08 '20 at 08:05
  • 1
    I know this works and its great (already upvoted) but is there a more elegant (or compact) way to do this? May be using lambda. – Vivek Sinha Sep 29 '22 at 23:10
  • Absolutely. Its been a while since I have been here. Will look at updating it. Alternatively go ahead and recommend the lambda version. – Joe Ferndz Oct 01 '22 at 00:36
1

I am not having the exact dataset what you have . i am explaining with similar dataset .Try to add a column with sum of medals across rows.then find the percentage by dividing all the row by sum of entire column.

i am posting this as model check this

import pandas as pd

cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'ExshowroomPrice': [21000,26000,28000,34000],'RTOPrice': [2200,250,2700,3500]}

df = pd.DataFrame(cars, columns = ['Brand', 'ExshowroomPrice','RTOPrice'])

            Brand  ExshowroomPrice  RTOPrice
0     Honda Civic            21000      2200
1  Toyota Corolla            26000       250
2      Ford Focus            28000      2700
3         Audi A4            34000      3500




df['percentage']=(df.ExshowroomPrice +df.RTOPrice) * 100
/(df.ExshowroomPrice.sum() +df.RTOPrice.sum())
print(df)



            Brand  ExshowroomPrice  RTOPrice  percentage
0     Honda Civic            21000      2200   19.719507
1  Toyota Corolla            26000       250   22.311942
2      Ford Focus            28000      2700   26.094348
3         Audi A4            34000      3500   31.874203

hope its clear

  • I tried to add the code i am new to stackoverflow ...dont know add code i tried but was unable to add it... you mean in the code try to sum it right – Saud Ansari Sep 07 '20 at 13:36
  • Finally got it...please help brother...i have only started learning pyton 3 weeks ago so i might be asking a noob question – Saud Ansari Sep 07 '20 at 13:40
  • I was able to sum the columns, but unable to get the percentage – Saud Ansari Sep 07 '20 at 21:49
  • @SaudAnsari i appreciate your interest to learn dont hesitate to ask question. happy learning. –  Sep 08 '20 at 04:14
  • @SaudAnsari by the way edit your question title . how to add percentage column by adding the columns in pandas data frame. –  Sep 08 '20 at 04:18
  • So basically i need total medals i got it above code then by t2=df['Total'].sum() print(t2) now only left is percentage Name of Country= USA 147 Name of Country= China 48 Name of Country= UK 14 Name of Country= Germany 145 Name of Country= Australia 13 Name of Country= USA 147 Name of Country= China 48 Name of Country= UK 14 Name of Country= Germany 145 Name of Country= Australia 13 367 So now i need the percentage – Saud Ansari Sep 08 '20 at 08:14