Assignment 2 - Pandas Introduction (Olympic Medals)

First the unicode is removed from the column names and the columns are given more human-readable names.

new_columns = ("summer_participations",
	       "summer_gold",
	       "summer_silver",
	       "summer_bronze",
	       "summer_total",
	       "winter_participations",
	       "winter_gold",
	       "winter_silver",
	       "winter_bronze",
	       "winter_total",
	       "total_participations",
	       "total_gold",
	       "total_silver",
	       "total_bronze",
	       "total_combined")
assert len(new_columns) == len(data.columns)

column_remap = dict(zip(data.columns, new_columns))

if data.columns[0] != "summer_participations":
    for column in data.columns:
	data.rename(columns={column:column_remap[column]}, inplace=True)

data.head(1)

                   summer_participations  summer_gold  summer_silver  \
Afghanistan (AFG)                     13            0              0   

                   summer_bronze  summer_total  winter_participations  \
Afghanistan (AFG)              2             2                      0   

                   winter_gold  winter_silver  winter_bronze  winter_total  \
Afghanistan (AFG)            0              0              0             0   

                   total_participations  total_gold  total_silver  \
Afghanistan (AFG)                    13           0             0   

                   total_bronze  total_combined  
Afghanistan (AFG)             2               2

Since the index has both a country name and a country code, we'll create a new column with just the ID's. The indices are split on the first left parenthesis ("(") creating a tuple with the first item being the name of the country and the second containing the country codes. To avoid the trailing parentheses and any other extra characters, the country-code gets sliced. Finally the last row ("Totals") gets dropped

# split the index by '('
names_ids = data.index.str.split('\s\(')

data.index = names_ids.str[0]
data['ID'] = names_ids.str[1].str[:3]

data = data.drop('Totals')

data.head(1)

             summer_participations  summer_gold  summer_silver  summer_bronze  \
Afghanistan                     13            0              0              2   

             summer_total  winter_participations  winter_gold  winter_silver  \
Afghanistan             2                      0            0              0   

             winter_bronze  winter_total  total_participations  total_gold  \
Afghanistan              0             0                    13           0   

             total_silver  total_bronze  total_combined   ID  
Afghanistan             0             2               2  AFG

The autograder will call this function and compare the return value against the correct solution value.

def answer_zero():
    """This function returns the row for Afghanistan

    Returns
    -------

    Series: first row in the data
    """
    return data.iloc[0]

answer_zero().name

Afghanistan

Which country has won the most gold medals in summer games?

def answer_one():
    """
    Returns
    -------

    Str: name of the country with the most summer gold medals
    """
    return data.summer_gold.argmax()

answer_one()

United States

Since we put the NOCs in the index (and most are country names), finding the index for the maximum value returns the country name that we want.

Which country had the biggest difference between their summer and winter gold medal counts?

def answer_two():
    """
    Returns
    -------

    Str: country with most difference between summer and winter gold
    """
    return (data.summer_gold - data.winter_gold).abs().argmax()

answer_two()

United States

Which country has the biggest difference between their summer gold medal counts and winter gold medal counts relative to their total gold medal count?

\(\frac{Summer~Gold - Winter~Gold}{Total~Gold}\)

Only include countries that have won at least 1 gold in both summer and winter.

def answer_three():
    """
    find country with the biggest difference between the
    summer and winter gold counts relative to total gold metal count
    if they have at least one gold medal in both summer and winter

    Returns
    -------

    str: country with biggest summer/winter gold differe
    """
    elegible = data[(data.summer_gold>=1) & (data.winter_gold>=1)]
    ratios = (elegible.summer_gold - elegible.winter_gold).abs()/elegible.total_gold
    return ratios.argmax()

answer_three()

Bulgaria

Write a function that creates a Series called "Points" which is a weighted value where each gold medal (`Gold.2`) counts for 3 points, silver medals (`Silver.2`) for 2 points, and bronze medals (`Bronze.2`) for 1 point. The function should return only the column (a Series object) which you created.

 def answer_four():
     """
     Creates weighted points based on medals
       * Gold: 3 points
       * Silver: 2 points
       * Bronze: 1 point

     Returns
     -------

     Series: column of points for each NOC
     """
     points = numpy.zeros(len(data))
     points += data.total_gold * 3
     points += data.total_silver * 2
     points += data.total_bronze
     return pandas.Series(points, index=data.index)
points = answer_four()
assert points.loc["United States"] == 5684

census_data = pandas.read_csv('census.csv')
census_data.head()

   SUMLEV  REGION  DIVISION  STATE  COUNTY   STNAME         CTYNAME  \
0      40       3         6      1       0  Alabama         Alabama   
1      50       3         6      1       1  Alabama  Autauga County   
2      50       3         6      1       3  Alabama  Baldwin County   
3      50       3         6      1       5  Alabama  Barbour County   
4      50       3         6      1       7  Alabama     Bibb County   

   CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010     ...       \
0        4779736            4780127          4785161     ...        
1          54571              54571            54660     ...        
2         182265             182265           183193     ...        
3          27457              27457            27341     ...        
4          22915              22919            22861     ...        

   RDOMESTICMIG2011  RDOMESTICMIG2012  RDOMESTICMIG2013  RDOMESTICMIG2014  \
0          0.002295         -0.193196          0.381066          0.582002   
1          7.242091         -2.915927         -3.012349          2.265971   
2         14.832960         17.647293         21.845705         19.243287   
3         -4.728132         -2.500690         -7.056824         -3.904217   
4         -5.527043         -5.068871         -6.201001         -0.177537   

   RDOMESTICMIG2015  RNETMIG2011  RNETMIG2012  RNETMIG2013  RNETMIG2014  \
0         -0.467369     1.030015     0.826644     1.383282     1.724718   
1         -2.530799     7.606016    -2.626146    -2.722002     2.592270   
2         17.197872    15.844176    18.559627    22.727626    20.317142   
3        -10.543299    -4.874741    -2.758113    -7.167664    -3.978583   
4          0.177258    -5.088389    -4.363636    -5.403729     0.754533   

   RNETMIG2015  
0     0.712594  
1    -2.187333  
2    18.293499  
3   -10.543299  
4     1.107861  

[5 rows x 100 columns]

Which state has the most counties in it? (hint: consider the sumlevel key carefully! You'll need this for future questions too…)

def answer_five():
    """finds state with the most counties

    Returns
    -------

    str name of state with the most counties
    """
    return counties.groupby(
	CensusVariables.state_name).count().COUNTY.argmax()

answer_five()

Texas

Only looking at the three most populous counties for each state, what are the three most populous states (in order of highest population to lowest population)? Use CENSUS2010POP.

def answer_six():
    """finds three most populous states based on top three counties in each

    Returns
    -------

    List: top three state-names (highest to lowest)
    """
    top_threes = counties.groupby(
	CensusVariables.state_name
    )[CensusVariables.census_population].nlargest(3)
    states = top_threes.groupby(level=0).sum()
    return list(states.nlargest(3).index)

answer_six()

Which county has had the largest absolute change in population within the period 2010-2015? (Hint: population values are stored in columns POPESTIMATE2010 through POPESTIMATE2015, you need to consider all six columns.)

e.g. If County Population in the 5 year period is 100, 120, 80, 105, 100, 130, then its largest change in the period would be |130-80| = 50.

This function should return a single string value.

def answer_seven():
    """Find county with largest absolute population variance

    Returns
    -------

    str: name of the county
    """
    return counties.iloc[
	(counties[
	    CensusVariables.population_estimates].max(axis=1) -
	 counties[
	     CensusVariables.population_estimates].min(axis=1)
	).argmax()][CensusVariables.county_name]

answer_seven()

Harris County

In this datafile, the United States is broken up into four regions using the "REGION" column.

Create a query that finds the counties that belong to regions 1 or 2, whose name starts with 'Washington', and whose POPESTIMATE2015 was greater than their POPESTIMATE2014.

This function should return a 5x2 DataFrame with the columns = ['STNAME', 'CTYNAME'] and the same index ID as the census_data (sorted ascending by index).

def answer_eight():
    """find region 1 or 2 counties:

      * with names that start with Washington
      * whose population grew from 2014 to 2015

    .. note:: the index in the final data-frame has to match the original
    census data

    Returns
    -------

    DataFrame: with the county and state-name columns
    """
    regions = counties_original_index[
	(counties_original_index[CensusVariables.region]==1) |
	(counties_original_index[CensusVariables.region]==2)]
    washingtons = regions[
	regions[CensusVariables.county_name].str.startswith("Washington")]
    grew = washingtons[washingtons[CensusVariables.population_2015] >
		       washingtons[CensusVariables.population_2014]]
    return grew[[CensusVariables.state_name,
		 CensusVariables.county_name]]

outcome = answer_eight()
assert outcome.shape == (5,2)
assert list(outcome.columns) == ['STNAME', 'CTYNAME']
print(tabulate(outcome, headers=["index"] + list(outcome.columns),
	       tablefmt="orgtbl"))