The Olympic medal data for the following exercises comes from The Guardian. It comprises records of all events held at the Olympic games between 1896 and 2012.¶
In [11]:
import pandas as pd
medals = pd.read_csv('Summer Olympic medallists 1896 to 2008 - ALL MEDALISTS.csv',skiprows=4)
print medals.shape
medals.head()
Out[11]:
In [12]:
medals.info()
Using .value_counts() for ranking¶
- pandas Series method .value_counts()
- determine the top 15 countries ranked by total number of medals.
- .value_counts() sorts by values by default.
- The result is returned as a Series of counts indexed by unique entries from the original Series with values (counts) ranked in descending order.
- .value_counts() sorts by values by default.
- determine the top 15 countries ranked by total number of medals.
In [14]:
# Select the 'NOC' column of medals: country_names
country_names = medals['NOC']
print type(country_names)
country_names.head()
Out[14]:
In [18]:
# Count the number of medals won by each country: medal_counts
medal_counts = country_names.value_counts()
print type(medal_counts)
medal_counts.head()
Out[18]:
Using .pivot_table() to count medals by type¶
- Rather than ranking countries by total medals won and showing that list, you may want to see a bit more detail.
- You can use a pivot table to compute how many separate bronze, silver and gold medals each country won.
- That pivot table can then be used to repeat the previous computation to rank by total medals won.
- You can use a pivot table to compute how many separate bronze, silver and gold medals each country won.
In [20]:
medals.head(3)
Out[20]:
In [23]:
# Construct the pivot table: counted
counted = medals.pivot_table(index='NOC', values='Athlete', columns='Medal', aggfunc='count')
counted.head(7)
Out[23]:
In [25]:
# Create the new column: counted['totals']
counted['totals'] = counted.sum(axis='columns')
counted.head(7)
Out[25]:
In [27]:
# Sort counted by the 'totals' column
counted = counted.sort_values('totals', ascending=False)
counted.head(7)
Out[27]:
Applying .drop_duplicates()¶
- What could be the difference between the 'Event_gender' and 'Gender' columns?
- you should be able to evaluate your guess by looking at the unique values of the pairs (Event_gender, Gender)
- The duplicates can be dropped using the .drop_duplicates() method, leaving behind the unique observations.
In [31]:
# Select columns: ev_gen
ev_gen = medals[['Event_gender', 'Gender']]
print ev_gen.shape
ev_gen.head()
Out[31]:
In [33]:
# Drop duplicate pairs: ev_gen_uniques
ev_gen_uniques = ev_gen.drop_duplicates()
ev_gen_uniques
Out[33]:
Finding possible errors with .groupby()¶
- you will now use .groupby() to continue your exploration. Your job is to group by 'Event_gender' and 'Gender' and count the rows.
- You will see that there is only one suspicious row: This is likely a data error.
In [35]:
# Group medals by the two columns: medals_by_gender
medals_by_gender = medals.groupby(['Event_gender', 'Gender'])
# Create a DataFrame with a group count: medal_count_by_gender
medal_count_by_gender = medals_by_gender.count()
medal_count_by_gender
Out[35]:
Locating suspicious data¶
In [37]:
# Create the Boolean Series: sus
sus = (medals.Event_gender == 'W') & (medals.Gender == 'Men')
sus.head()
Out[37]:
In [38]:
# Create a DataFrame with the suspicious row: suspect
suspect = medals[sus]
suspect
Out[38]:
In [43]:
print len(medals['Sport'].unique())
medals['Sport'].unique()
Out[43]:
In [71]:
# Group medals by 'NOC': country_grouped
country_grouped = medals.groupby('NOC')
# Compute the number of distinct sports in which each country won medals: Nsports
Nsports = country_grouped['Sport'].nunique()
Nsports.head()
Out[71]:
In [75]:
country_grouped['Sport'].count().head()
Out[75]:
In [76]:
# Sort the values of Nsports in descending order
Nsports = Nsports.sort_values(ascending=False)
Nsports.head()
Out[76]:
In [79]:
country_grouped['Sport'].count().sort_values(ascending = False).head()
Out[79]:
In [ ]:
Ranking of distinct events¶
- Top five countries that have won medals in the most sports
- Compare medal counts of USA and USSR from 1952 to 1988
Two new DataFrame methods¶
- idxmax(): Row or column label where maximum value is located
- idxmin(): Row or column label where minimum value is located
In [60]:
count = medals.groupby('NOC')['Medal'].count()
count.head()
Out[60]:
In [61]:
count.index
Out[61]:
In [62]:
count.idxmax()
Out[62]:
In [63]:
count.idxmin()
Out[63]:
Using idxmax() along columns¶
In [80]:
import numpy as np
In [112]:
count2 = medals.groupby('NOC')['Medal','Event'].count()
count2['Event']=count2['Event'].apply(lambda x: x+np.random.randint(-10,5))
count2.head()
Out[112]:
In [113]:
count2.head()
Out[113]:
In [114]:
count2.idxmax(1).value_counts()
Out[114]:
In [117]:
count2.idxmax(axis='columns').head()
Out[117]:
filtering¶
Counting USA vs. USSR Cold War Olympic Sports¶
- The Olympic competitions between 1952 and 1988 took place during the height of the Cold War between the United States of America (USA) & the Union of Soviet Socialist Republics (USSR).
- Your goal in this exercise is to aggregate the number of distinct sports in which the USA and the USSR won medals during the Cold War years.
In [132]:
# Extract all rows for which the 'Edition' is between 1952 & 1988: during_cold_war
during_cold_war = (medals.Edition>=1952) & (medals.Edition<=1988)
print during_cold_war.value_counts()
during_cold_war.head()
Out[132]:
In [133]:
# Extract rows for which 'NOC' is either 'USA' or 'URS': is_usa_urs
is_usa_urs = medals.NOC.isin(['USA', 'URS'])
print is_usa_urs.value_counts()
is_usa_urs.head()
Out[133]:
In [134]:
# Use during_cold_war and is_usa_urs to create the DataFrame: cold_war_medals
cold_war_medals = medals.loc[during_cold_war & is_usa_urs]
print cold_war_medals.shape
cold_war_medals.head()
Out[134]:
Create a Series Nsports from country_grouped using indexing & chained methods:¶
- Extract the column 'Sport'; ### Use .nunique() to get the number of unique elements in each group;
- Apply .sort_values(ascending=False) to rearrange the Series.
In [135]:
# Group cold_war_medals by 'NOC'
country_grouped = cold_war_medals.groupby('NOC')
# Create Nsports
Nsports = country_grouped['Sport'].nunique().sort_values(ascending=False)
print Nsports.shape
Nsports
Out[135]:
In [136]:
country_grouped['Sport'].nunique().head()
Out[136]:
In [139]:
cold_war_medals.groupby('NOC').count()
Out[139]:
Counting USA vs. USSR Cold War Olympic Medals¶
- see which country, the USA or the USSR, won the most medals consistently over the Cold War period.
- ou'll need a pivot table with years ('Edition') on the index and countries ('NOC') on the columns. The entries will be the total number of medals each country won that year. If the country won no medals in a given edition, expect a NaN in that entry of the pivot table.
In [143]:
# Create the pivot table: medals_won_by_country
medals_won_by_country = medals.pivot_table(index='Edition', columns='NOC', values='Athlete', aggfunc='count')
medals_won_by_country.head()
Out[143]:
- ### You'll need to slice the Cold War period and subset the 'USA' and 'URS' columns.
In [145]:
# Slice medals_won_by_country: cold_war_usa_usr_medals
cold_war_usa_usr_medals = medals_won_by_country.loc[1952:1988, ['USA','URS']]
cold_war_usa_usr_medals
Out[145]:
- ### You'll need to make a Series from this slice of the pivot table that tells which country won the most medals in that edition using .idxmax(axis='columns').
- If .max() returns the maximum value of Series or 1D array, .idxmax() returns the index of the maximizing element.
- The argument axis=columns or axis=1 is required because, by default, this aggregation would be done along columns for a DataFrame.
- The final Series contains either 'USA' or 'URS' according to which country won the most medals in each Olympic edition.
- You can use .value_counts() to count the number of occurrences of each.
- The final Series contains either 'USA' or 'URS' according to which country won the most medals in each Olympic edition.
- The argument axis=columns or axis=1 is required because, by default, this aggregation would be done along columns for a DataFrame.
In [146]:
# Create most_medals
most_medals = cold_war_usa_usr_medals.idxmax(axis='columns')
most_medals
Out[146]:
Visualizing¶
- USA Medal Counts by Edition: Line Plot
In [158]:
import matplotlib.pyplot as plt
%matplotlib inline
# Create the DataFrame: usa
usa = medals[medals.NOC == 'USA']
# Group usa by ['Edition', 'Medal'] and aggregate over 'Athlete'
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
usa_medals_by_year.head(10)
Out[158]:
unstack data before plotting¶
In [159]:
# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
usa_medals_by_year.head(10)
Out[159]:
In [160]:
# Plot the DataFrame usa_medals_by_year
usa_medals_by_year.plot()
plt.show()
Visualizing¶
- USA Medal Counts by Edition: Area Plot
In [162]:
# Create the DataFrame: usa
usa = medals[medals.NOC == 'USA']
# Group usa by 'Edition', 'Medal', and 'Athlete'
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
# Create an area plot of usa_medals_by_year
usa_medals_by_year.plot.area(alpha=.5)
plt.show()
Visualizing¶
- USA Medal Counts by Edition: Area Plot with Ordered Medals
- You may have noticed that the medals are ordered according to a lexicographic (dictionary) ordering: Bronze < Gold < Silver.
- However, you would prefer an ordering consistent with the Olympic rules: Bronze < Silver < Gold.
- You can achieve this using Categorical types.
- However, you would prefer an ordering consistent with the Olympic rules: Bronze < Silver < Gold.
- You may have noticed that the medals are ordered according to a lexicographic (dictionary) ordering: Bronze < Gold < Silver.
In [171]:
# Redefine 'Medal' as an ordered categorical
medals.Medal = pd.Categorical(values=medals.Medal, categories=['Bronze', 'Silver', 'Gold'], ordered=True)
medals.Medal.head()
Out[171]:
In [173]:
# Create the DataFrame: usa
usa = medals[medals.NOC == 'USA']
# Group usa by 'Edition', 'Medal', and 'Athlete'
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
# Create an area plot of usa_medals_by_year
usa_medals_by_year.plot.area(alpha=.6)
plt.show()
In [ ]: