Background:More than 18,00 medals have been awarded at the Olympics since 1896, including winter and summer olympics. This total includes medals just awarded in 2021 for the COVID-delayed 2020 Tokyo summer games.

Data:The International Olympic Committee (IOC) Research and Reference Service has published statistics for most years between 1896 and 2020 for both the summer and winter olympics.

In thefirst partof this exam, we will consider the number of medals won by each country in the summer olympics from 1896 through 2020. This data is available in the form of a single text file namedsummer_counts.csv. The file lists the year of the games, each country who competed, and the number of bronze, silver, and gold medals awarded to that country for that years olympic games.

Be explicit in your answers and show your results! Ambiguous responses will not receive full credit.

Question 1: Loading the Data

Create a pandas dataframe calledsummer_countsthat contains the data in the filesummer_counts.csv.

import pandas as pd

import numpy as np

%matplotlib inline

​

​

​

​

How many records are in this file?... [YOUR ANSWER HERE]

Question 2: Most Gold Medals in a Single Olympics

Write some code to determine which country received the most gold medals in a single Olympics. In what year and with how many gold medals did this occur?

​

​

Question 3: Most Gold Medals in 2016

For the year 2016, report the 5 countries (in descending order) who won the most gold medals, along with their gold medal count for that year.

​

​

Question 4: The Medal Count

Add a column to thesummer_countsdataframe called 'Medal Count' that gives the total number of medals awarded to each country for each year.

​

​

Question 5: Total Points

Add a column to thesummer_countsdataframe called 'Total Points' which assigns 5 points for Gold, 3 for silver, and 1 for bronze. In other words,Total Points = 5x(Num Gold Medals) + 3x(Num Silver Medals) + 1x(Num Bronze Medals)

​

​

Question 6: Most Total Points in 2016

For the year 2016, report the 5 countries (in descending order) who received the most "Total Points" (as calculated in Question 5).

​

​

Question 7a: USA Performance (Medals)

What is the total number of medals of each type (gold, silver, bronze) that have been won by the USA across all years?

​

​

Question 7b: USA Performance (Gold Medals)

Rank the years by the number of Gold Medals won by the USA, most to least (i.e. year with most USA gold medals to year with least USA gold medals). Please report not only the year but also the number of gold medals for each year.

​

​

Question 7c: USA Performance (Total Points)

Rank the years by the "Total Points" achieved by the USA, most to least (i.e. year with most USA Total Points achieved to year with least USA Total Points achieved). Please report not only the year but also the number of total points for each year.

​

​

Question 7d: USA Performance (Plot)

Generate a plot to illustrate how the USA has perfromed over the history of the olympics. Plot the number of gold medals won by the USA over time as a simple lineplot.

​

​

Question 8: Who Won the Olympics?

In each Olympics, the "Winner" is the country with the most "Total Points," with Total Points being calculated as in Question 5. For each of the Olympic Games recorded here, which country was the "Winner"?

​

​

Question 9: Most Olympic Wins

What 5 countries have been most frequently crowned the "Winner" over the entire history of the summer olympics (and how many wins does each have)?

Note: for the purposes of this question the Soviet Union (URS) and Russia (RUS) are separate countries.

​

​

​

New Data:population.csv

For questions 10 and 11, we will consider additional information about each country who participated in the olympics. Specifically, we will consider data in the filepopulation.csv, which gives the population for a large list of countries. We assume that the population for any country has remained constant over the years, and thatpopulation.csvprovides this single number.

Question 10a: Missing Data?

Are there countries included insummer_countsthat are NOT included inpopulation.csv, and if so, how many? Print them out. Write code to answer this question.

​

​

Question 10b: Missing Data?

Are there countries included inpopulation.csvthat are NOT included insummer_counts, and if so, how many? Print them out. Write code to answer this question.

​

​

Question 10c: Medal Count Per Capita

We define theMedal Count Per Capita 'MCPC'to be the total number of medals divided by the total country population.

Add a column to thesummer_countsdataframe called 'MCPC' that reports the Medal Count Per Capita. If population statistics are not available, 'MCPC' should be reported as NaN. Display the first 10 rows of the updatedsummer_countsdataframe.

​

​

Question 10d: MCPC Winners

For every year recorded in thesummer_countsdataframe, report the country that had the highest MCPC for that year. Report this in the form of a new dataframe calledMCPC_winnerswhich has index=years and a single column called 'winner' which lists the name of the country that had the highest MCPC for that year. This can also be reported as a Series object with index=years and data giving the name of the country that had the highest MCPC for that year (no need for a column name).

​

​

Question 11: USA Performance in Summer Games¶

Considering ONLY the performance of the USA in the summer games, generate a stacked bar chart of (Gold, Silver, Bronze) medals in each of the olympic games (in order).