Assignment 4

Description

In this assignment you must read in a file of metropolitan regions and associated sports teams from assets/wikipedia_data.html and answer some questions about each metropolitan region. Each of these regions may have one or more teams from the "Big 4": NFL (football, in assets/nfl.csv), MLB (baseball, in assets/mlb.csv), NBA (basketball, in assets/nba.csv or NHL (hockey, in assets/nhl.csv). Please keep in mind that all questions are from the perspective of the metropolitan region, and that this file is the "source of authority" for the location of a given sports team. Thus teams which are commonly known by a different area (e.g. "Oakland Raiders") need to be mapped into the metropolitan region given (e.g. San Francisco Bay Area). This will require some human data understanding outside of the data you've been given (e.g. you will have to hand-code some names, and might need to google to find out where teams are)!

For each sport I would like you to answer the question: what is the win/loss ratio's correlation with the population of the city it is in? Win/Loss ratio refers to the number of wins over the number of wins plus the number of losses. Remember that to calculate the correlation with pearsonr, so you are going to send in two ordered lists of values, the populations from the wikipedia_data.html file and the win/loss ratio for a given sport in the same order. Average the win/loss ratios for those cities which have multiple teams of a single sport. Each sport is worth an equal amount in this assignment (20%*4=80%) of the grade for this assignment. You should only use data from year 2018 for your analysis -- this is important!

Notes

1. Do not include data about the MLS or CFL in any of the work you are doing, we're only interested in the Big 4 in this assignment.
2. I highly suggest that you first tackle the four correlation questions in order, as they are all similar and worth the majority of grades for this assignment. This is by design!
3. It's fair game to talk with peers about high level strategy as well as the relationship between metropolitan areas and sports teams. However, do not post code solving aspects of the assignment (including such as dictionaries mapping areas to teams, or regexes which will clean up names).
4. There may be more teams than the assert statements test, remember to collapse multiple teams in one city into a single value!

Question 1

For this question, calculate the win/loss ratio's correlation with the population of the city it is in for the NHL using 2018 data.

import pandas as pd
import numpy as np
import scipy.stats as stats
import re

nhl_df=pd.read_csv("assets/nhl.csv")
cities=pd.read_html("assets/wikipedia_data.html")[1]
cities=cities.iloc[:-1,[0,3,5,6,7,8]]


def nhl_correlation():  
   
    # just keep 3 columns in the cities dataset: 'Metropolitan area', 'Population', 'NHL'
    population_by_region = cities[['Metropolitan area', 'Population (2016 est.)[8]', 'NHL']]
    population_by_region = population_by_region.rename(columns={'Population (2016 est.)[8]':'Population'})
    
    # remove all the content in the [] in the NHL column 
    population_by_region['NHL'] = population_by_region['NHL'].str.replace("\[.*\]", "")
   
    # data cleaning for nhl_df
    nhl_df['team'] =  nhl_df['team'].str.replace("\*", "")
    
    nhl_df2008 = nhl_df[nhl_df['year'] == 2018]
    
    nhl_df2008.set_index('team', inplace=True)
    
    # keep the numerical data 
    nhl_df_num = nhl_df2008[nhl_df2008['GP'].apply(lambda x:x.isnumeric())]
    
    
    # convert data type of 2 cols to float 
    nhl_df_num[['W', 'L']] = nhl_df_num[['W', 'L']].astype(float)
    
    
    # pass in win/loss ratio from nhl_df in the same order as cities["Metropolitan area"]
    # Win/Loss ratio refers to the number of wins over the number of wins plus the number of losses.
    win_loss_by_region = nhl_df_num['W'] / (nhl_df_num['W'] + nhl_df_num['L'])
    
    win_loss_by_region = win_loss_by_region.to_frame()
    win_loss_by_region.columns = ['win/loss ratio']
      
    win_loss_by_region = win_loss_by_region.reset_index()
    
    # delete non-string data 
    population_by_region['NHL'] = population_by_region['NHL'].replace({"":np.NaN, "—":np.NaN})
    population_by_region = population_by_region.dropna()
    
    # generate new NHL name 
    population_by_region['newNHL'] = population_by_region['Metropolitan area'] + " " + population_by_region['NHL']
    
    # 3 teams in new york city
    # 2 teams in LA
    # copy rows and concat 
    row1 = population_by_region.iloc[[0]]
    row2 = population_by_region.iloc[[1]]
    frames = [population_by_region, row1, row1, row2]
    population_by_region = pd.concat(frames)
    
    # reset index     
    population_by_region.index = range(len(population_by_region ))
    population_by_region.iloc[0]['newNHL'] = 'New York Rangers'
    population_by_region.iloc[1]['newNHL'] = 'Los Angeles Kings'
    population_by_region.iloc[2]['newNHL'] = 'San Jose Sharks'
    population_by_region.iloc[4]['newNHL'] = 'Dallas Stars'
    population_by_region.iloc[5]['newNHL'] = 'Washington Capitals'
    population_by_region.iloc[8]['newNHL'] = 'Minnesota Wild'
    population_by_region.iloc[9]['newNHL'] = 'Colorado Avalanche'
    population_by_region.iloc[10]['newNHL'] = 'Florida Panthers'
    population_by_region.iloc[11]['newNHL'] = 'Arizona Coyotes'
    population_by_region.iloc[14]['newNHL'] = 'Tampa Bay Lightning'
    population_by_region.iloc[26]['newNHL'] = 'Vegas Golden Knights'
    population_by_region.iloc[27]['newNHL'] = 'Carolina Hurricanes'
    population_by_region.iloc[28]['newNHL'] = 'New York Islanders'
    population_by_region.iloc[29]['newNHL'] = 'New Jersey Devils'
    population_by_region.iloc[30]['newNHL'] = 'Anaheim Ducks'
    
    
    # merge 
    population_by_region = pd.merge(population_by_region, win_loss_by_region, how='left', left_on='newNHL', right_on='team')
    win_loss_by_region = pd.merge(population_by_region, win_loss_by_region, how='right', left_on='newNHL', right_on='team')
    
    # get population_by_region dataframe and sort by area 
    population_by_region = population_by_region[['Metropolitan area', 'Population']]
    population_by_region =  population_by_region.sort_values('Metropolitan area')
    population_by_region = population_by_region.drop_duplicates()
    
    # get win_loss_by_region dataframe and sort by area 
    win_loss_by_region = win_loss_by_region[['Metropolitan area', 'win/loss ratio_x']] 
    win_loss_by_region = win_loss_by_region.groupby('Metropolitan area')['win/loss ratio_x'].agg({'win/loss ratio':np.mean})
    win_loss_by_region = win_loss_by_region.sort_values('Metropolitan area')
    
    # convert data type to float 
    population_by_region['Population'] = population_by_region['Population'].astype(float)
    
    # convert dataframe to series 
    population_by_region = population_by_region['Population']
    win_loss_by_region = win_loss_by_region['win/loss ratio']
    
    assert len(population_by_region) == len(win_loss_by_region), "Q1: Your lists must be the same length"
    assert len(population_by_region) == 28, "Q1: There should be 28 teams being analysed for NHL"
    
    
    return stats.pearsonr(population_by_region, win_loss_by_region)
    raise NotImplementedError()
   

nhl_correlation()

(0.012486162921209907, 0.9497182859911791)

Question 2

For this question, calculate the win/loss ratio's correlation with the population of the city it is in for the NBA using 2018 data.

import pandas as pd
import numpy as np
import scipy.stats as stats
import re

nba_df=pd.read_csv("assets/nba.csv")
cities=pd.read_html("assets/wikipedia_data.html")[1]
cities=cities.iloc[:-1,[0,3,5,6,7,8]]

def nba_correlation():
    # YOUR CODE HERE
    #raise NotImplementedError()
    
    # just keep 3 columns in the cities dataset: 'Metropolitan area', 'Population', 'NHL'
    population_by_region = cities[['Metropolitan area', 'Population (2016 est.)[8]', 'NBA']]
    population_by_region = population_by_region.rename(columns={'Population (2016 est.)[8]':'Population'})
    population_by_region['NBA'] = population_by_region['NBA'].str.replace("\[.*\]", "")
    population_by_region['NBA'] = population_by_region['NBA'].replace({"":np.NaN, "—":np.NaN})
    population_by_region = population_by_region.dropna()
    population_by_region['newNBA'] = population_by_region['Metropolitan area'] + " " + population_by_region['NBA']
    
    
    nba_df2008 = nba_df[nba_df['year'] == 2018]
    nba_df2008 = nba_df2008[['team', 'W/L%']]
    nba_df2008['team'] = nba_df2008['team'].str.replace("\*", "")
    nba_df2008['team'] = nba_df2008['team'].str.replace("\(.*\)", "")
    """'Chicago Bulls\xa0' problem"""
    # to remove unique code 
    nba_df2008['team'] = nba_df2008['team'].str.split().str.join(' ')
    
    
    test = pd.merge(population_by_region, nba_df2008, how='outer', left_on='newNBA', right_on='team')
    
    #assert len(population_by_region) == len(win_loss_by_region), "Q2: Your lists must be the same length"
    #assert len(population_by_region) == 28, "Q2: There should be 28 teams being analysed for NBA"

    #return stats.pearsonr(population_by_region, win_loss_by_region)
    return test

nba_correlation()

	Metropolitan area	Population	NBA	newNBA	team	W/L%
0	New York City	20153634	KnicksNets	New York City KnicksNets	NaN	NaN
1	Los Angeles	13310447	LakersClippers	Los Angeles LakersClippers	NaN	NaN
2	San Francisco Bay Area	6657982	Warriors	San Francisco Bay Area Warriors	NaN	NaN
3	Chicago	9512999	Bulls	Chicago Bulls	Chicago Bulls	0.32899999999999996
4	Dallas–Fort Worth	7233323	Mavericks	Dallas–Fort Worth Mavericks	NaN	NaN
5	Washington, D.C.	6131977	Wizards	Washington, D.C. Wizards	NaN	NaN
6	Philadelphia	6070500	76ers	Philadelphia 76ers	Philadelphia 76ers	0.634
7	Boston	4794447	Celtics	Boston Celtics	Boston Celtics	0.6709999999999999
8	Minneapolis–Saint Paul	3551036	Timberwolves	Minneapolis–Saint Paul Timberwolves	NaN	NaN
9	Denver	2853077	Nuggets	Denver Nuggets	Denver Nuggets	0.561
10	Miami–Fort Lauderdale	6066387	Heat	Miami–Fort Lauderdale Heat	NaN	NaN
11	Phoenix	4661537	Suns	Phoenix Suns	Phoenix Suns	0.256
12	Detroit	4297617	Pistons	Detroit Pistons	Detroit Pistons	0.47600000000000003
13	Toronto	5928040	Raptors	Toronto Raptors	Toronto Raptors	0.72
14	Houston	6772470	Rockets	Houston Rockets	Houston Rockets	0.7929999999999999
15	Atlanta	5789700	Hawks	Atlanta Hawks	Atlanta Hawks	0.293
16	Cleveland	2055612	Cavaliers	Cleveland Cavaliers	Cleveland Cavaliers	0.61
17	Charlotte	2474314	Hornets	Charlotte Hornets	Charlotte Hornets	0.439
18	Indianapolis	2004230	Pacers	Indianapolis Pacers	NaN	NaN
19	Milwaukee	1572482	Bucks	Milwaukee Bucks	Milwaukee Bucks	0.537
20	New Orleans	1268883	Pelicans	New Orleans Pelicans	New Orleans Pelicans	0.585
21	Orlando	2441257	Magic	Orlando Magic	Orlando Magic	0.305
22	Portland	2424955	Trail Blazers	Portland Trail Blazers	Portland Trail Blazers	0.598
23	Salt Lake City	1186187	Jazz	Salt Lake City Jazz	NaN	NaN
24	San Antonio	2429609	Spurs	San Antonio Spurs	San Antonio Spurs	0.573
25	Sacramento	2296418	Kings	Sacramento Kings	Sacramento Kings	0.32899999999999996
26	Oklahoma City	1373211	Thunder	Oklahoma City Thunder	Oklahoma City Thunder	0.585
27	Memphis	1342842	Grizzlies	Memphis Grizzlies	Memphis Grizzlies	0.268
28	NaN	NaN	NaN	NaN	Indiana Pacers	0.585
29	NaN	NaN	NaN	NaN	Miami Heat	0.537
30	NaN	NaN	NaN	NaN	Washington Wizards	0.524
31	NaN	NaN	NaN	NaN	New York Knicks	0.354
32	NaN	NaN	NaN	NaN	Brooklyn Nets	0.341
33	NaN	NaN	NaN	NaN	Golden State Warriors	0.7070000000000001
34	NaN	NaN	NaN	NaN	Utah Jazz	0.585
35	NaN	NaN	NaN	NaN	Minnesota Timberwolves	0.573
36	NaN	NaN	NaN	NaN	Los Angeles Clippers	0.512
37	NaN	NaN	NaN	NaN	Los Angeles Lakers	0.42700000000000005
38	NaN	NaN	NaN	NaN	Dallas Mavericks	0.293

Question 3

For this question, calculate the win/loss ratio's correlation with the population of the city it is in for the MLB using 2018 data.

import pandas as pd
import numpy as np
import scipy.stats as stats
import re

mlb_df=pd.read_csv("assets/mlb.csv")
cities=pd.read_html("assets/wikipedia_data.html")[1]
cities=cities.iloc[:-1,[0,3,5,6,7,8]]

def mlb_correlation(): 
    # YOUR CODE HERE
    # raise NotImplementedError()
    population_by_region = cities[['Metropolitan area', 'Population (2016 est.)[8]', 'MLB']]
    population_by_region = population_by_region.rename(columns={'Population (2016 est.)[8]':'Population'})
    population_by_region['MLB'] = population_by_region['MLB'].str.replace("\[.*\]", "")
    population_by_region['MLB'] = population_by_region['MLB'].replace({"":np.NaN, "—":np.NaN})
    population_by_region = population_by_region.dropna()
    population_by_region['newMLB'] = population_by_region['Metropolitan area'] + " " + population_by_region['MLB']
    
    mlb_df2008 = mlb_df[mlb_df['year'] == 2018]
    mlb_df2008 = mlb_df2008[['team', 'W-L%']]
   
    test = pd.merge(population_by_region, mlb_df2008, how='outer', left_on='newMLB', right_on='team')
    
    #assert len(population_by_region) == len(win_loss_by_region), "Q3: Your lists must be the same length"
    #assert len(population_by_region) == 26, "Q3: There should be 26 teams being analysed for MLB"

    #return stats.pearsonr(population_by_region, win_loss_by_region)
    return test

mlb_correlation()

	Metropolitan area	Population	MLB	newMLB	team	W-L%
0	New York City	20153634	YankeesMets	New York City YankeesMets	NaN	NaN
1	Los Angeles	13310447	DodgersAngels	Los Angeles DodgersAngels	NaN	NaN
2	San Francisco Bay Area	6657982	GiantsAthletics	San Francisco Bay Area GiantsAthletics	NaN	NaN
3	Chicago	9512999	CubsWhite Sox	Chicago CubsWhite Sox	NaN	NaN
4	Dallas–Fort Worth	7233323	Rangers	Dallas–Fort Worth Rangers	NaN	NaN
5	Washington, D.C.	6131977	Nationals	Washington, D.C. Nationals	NaN	NaN
6	Philadelphia	6070500	Phillies	Philadelphia Phillies	Philadelphia Phillies	0.494
7	Boston	4794447	Red Sox	Boston Red Sox	Boston Red Sox	0.667
8	Minneapolis–Saint Paul	3551036	Twins	Minneapolis–Saint Paul Twins	NaN	NaN
9	Denver	2853077	Rockies	Denver Rockies	NaN	NaN
10	Miami–Fort Lauderdale	6066387	Marlins	Miami–Fort Lauderdale Marlins	NaN	NaN
11	Phoenix	4661537	Diamondbacks	Phoenix Diamondbacks	NaN	NaN
12	Detroit	4297617	Tigers	Detroit Tigers	Detroit Tigers	0.395
13	Toronto	5928040	Blue Jays	Toronto Blue Jays	Toronto Blue Jays	0.451
14	Houston	6772470	Astros	Houston Astros	Houston Astros	0.636
15	Atlanta	5789700	Braves	Atlanta Braves	Atlanta Braves	0.556
16	Tampa Bay Area	3032171	Rays	Tampa Bay Area Rays	NaN	NaN
17	Pittsburgh	2342299	Pirates	Pittsburgh Pirates	Pittsburgh Pirates	0.509
18	Cleveland	2055612	Indians	Cleveland Indians	Cleveland Indians	0.562
19	Seattle	3798902	Mariners	Seattle Mariners	Seattle Mariners	0.549
20	Cincinnati	2165139	Reds	Cincinnati Reds	Cincinnati Reds	0.414
21	Kansas City	2104509	Royals	Kansas City Royals	Kansas City Royals	0.358
22	St. Louis	2807002	Cardinals	St. Louis Cardinals	St. Louis Cardinals	0.543
23	Baltimore	2798886	Orioles	Baltimore Orioles	Baltimore Orioles	0.290
24	Milwaukee	1572482	Brewers	Milwaukee Brewers	Milwaukee Brewers	0.589
25	San Diego	3317749	Padres	San Diego Padres	San Diego Padres	0.407
26	NaN	NaN	NaN	NaN	New York Yankees	0.617
27	NaN	NaN	NaN	NaN	Tampa Bay Rays	0.556
28	NaN	NaN	NaN	NaN	Minnesota Twins	0.481
29	NaN	NaN	NaN	NaN	Chicago White Sox	0.383
30	NaN	NaN	NaN	NaN	Oakland Athletics	0.599
31	NaN	NaN	NaN	NaN	Los Angeles Angels	0.494
32	NaN	NaN	NaN	NaN	Texas Rangers	0.414
33	NaN	NaN	NaN	NaN	Washington Nationals	0.506
34	NaN	NaN	NaN	NaN	New York Mets	0.475
35	NaN	NaN	NaN	NaN	Miami Marlins	0.391
36	NaN	NaN	NaN	NaN	Chicago Cubs	0.583
37	NaN	NaN	NaN	NaN	Los Angeles Dodgers	0.564
38	NaN	NaN	NaN	NaN	Colorado Rockies	0.558
39	NaN	NaN	NaN	NaN	Arizona Diamondbacks	0.506
40	NaN	NaN	NaN	NaN	San Francisco Giants	0.451

Question 4

For this question, calculate the win/loss ratio's correlation with the population of the city it is in for the NFL using 2018 data.

import pandas as pd
import numpy as np
import scipy.stats as stats
import re

nfl_df=pd.read_csv("assets/nfl.csv")
cities=pd.read_html("assets/wikipedia_data.html")[1]
cities=cities.iloc[:-1,[0,3,5,6,7,8]]

def nfl_correlation(): 
    # YOUR CODE HERE
    raise NotImplementedError()
    
    population_by_region = [] # pass in metropolitan area population from cities
    win_loss_by_region = [] # pass in win/loss ratio from nfl_df in the same order as cities["Metropolitan area"]

    assert len(population_by_region) == len(win_loss_by_region), "Q4: Your lists must be the same length"
    assert len(population_by_region) == 29, "Q4: There should be 29 teams being analysed for NFL"

    return stats.pearsonr(population_by_region, win_loss_by_region)

Question 5

In this question I would like you to explore the hypothesis that given that an area has two sports teams in different sports, those teams will perform the same within their respective sports. How I would like to see this explored is with a series of paired t-tests (so use ttest_rel) between all pairs of sports. Are there any sports where we can reject the null hypothesis? Again, average values where a sport has multiple teams in one region. Remember, you will only be including, for each sport, cities which have teams engaged in that sport, drop others as appropriate. This question is worth 20% of the grade for this assignment.

import pandas as pd
import numpy as np
import scipy.stats as stats
import re

mlb_df=pd.read_csv("assets/mlb.csv")
nhl_df=pd.read_csv("assets/nhl.csv")
nba_df=pd.read_csv("assets/nba.csv")
nfl_df=pd.read_csv("assets/nfl.csv")
cities=pd.read_html("assets/wikipedia_data.html")[1]
cities=cities.iloc[:-1,[0,3,5,6,7,8]]

def sports_team_performance():
    # YOUR CODE HERE
    raise NotImplementedError()
    
    # Note: p_values is a full dataframe, so df.loc["NFL","NBA"] should be the same as df.loc["NBA","NFL"] and
    # df.loc["NFL","NFL"] should return np.nan
    sports = ['NFL', 'NBA', 'NHL', 'MLB']
    p_values = pd.DataFrame({k:np.nan for k in sports}, index=sports)
    
    assert abs(p_values.loc["NBA", "NHL"] - 0.02) <= 1e-2, "The NBA-NFL p-value should be around 0.02"
    assert abs(p_values.loc["MLB", "NFL"] - 0.80) <= 1e-2, "The MLB-NFL p-value should be around 0.80"
    return p_values