<jupyter_start><jupyter_text>Importing libraries & loading dataset<jupyter_code>import pandas as pd
import numpy as np
from geopy.geocoders import Nominatim 
import country_converter as coco
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report
import math
import statsmodels.api as sm
raw_data = pd.read_csv('data/athlete_events.csv',index_col="ID")
regions = pd.read_csv('data/noc_regions.csv')
df = pd.merge(raw_data, regions, on='NOC', how='left')<jupyter_output><empty_output><jupyter_text>Model 2 : Home advantage effect in Olympic games In this model we will try to understand if host effect can bring some advantage to the teams or not.It is important to state that, since there is no suitable independent and dependent variable, we will try to create new independent variables and dependent variable under consideration of the data features.  Since the variables related to the model does not contain missing values, data pre-processing is not applied. However, several filterig can be investigated in the following code. Overview of the notebook can be found as following:1. Some filtering and new variable addition to the dataset2. Logistic regression model approach:2.1 features: sport, team2.2 feature: sport2.3 feature: team3. Discussion about the results4. Logit regression model approach5. Discussion about the results      1. Some filtering and new variable addition to the dataset<jupyter_code># Creating a new dataframe which does not contain non medal data
df_medal = df[df.Medal.isnull() == False]
df_new = df.copy()


# Dropping the unncessary columns
df_medal = df_medal.drop(['Name', 'Sex', 'Age', 'Height', 'Weight', 'Event', 'notes', 'NOC'], axis=1)

# Since 'Individual Olympic Athletes' variable in 'Team' column does not show country information,
# rows with this variable will be dropped.
df_medal.drop( df_medal[ df_medal['Team'] == 'Individual Olympic Athletes' ].index , inplace=True)
df_new.drop( df_new[ df_new['Team'] == 'Individual Olympic Athletes' ].index , inplace=True)

# There are several region names for the same countries, so before converting them to iso3 values
# conversion in the below is applied.
df_medal.loc[df_medal.region == "US", "region"] = "United States"
df_medal.loc[df_medal.region == "USA", "region"] = "United States"
df_medal.loc[df_medal.region == "UK", "region"] = "United Kingdom"
df_medal.dropna(subset = ["region"], inplace=True)
df_new.loc[df_new.region == "US", "region"] = "United States"
df_new.loc[df_new.region == "USA", "region"] = "United States"
df_new.loc[df_new.region == "UK", "region"] = "United Kingdom"
df_new.loc[df_new.region == "Boliva", "region"] = "Bolivia"
df_new.dropna(subset = ["region"], inplace=True)<jupyter_output><empty_output><jupyter_text>Home advantage effect will be examined on the teams that experienced being host in the past. Thus, the following steps will be taken:<jupyter_code># Converting host cities to host countries 
geolocator = Nominatim(user_agent = "geoapiExercises") 
uniq_host_cities = list(df_medal.City.unique())
uniq_host_countries = []
for city in uniq_host_cities:
    location = geolocator.geocode(city, language='en') 
    uniq_host_countries.append(location[0].split(',')[-1])
    
# Converting host countries to iso3 values
iso3_for_host = coco.convert(names=uniq_host_countries, to='ISO3', not_found=None)

# Creating a dictionary in order to match host city to iso3 code
host_city_to_iso3 = {}
for i in range(len(uniq_host_cities)):
    host_city_to_iso3[uniq_host_cities[i]] = iso3_for_host[i]
    
# Creating a list to store corresponding iso3 code of the host city
host_NOC_column = []
for i in range(len(df_medal)):
    host_NOC_column.append(host_city_to_iso3[df_medal.City.iloc[i]])
    
# Adding Team_NOC column to the dataframe
df_medal['Host_NOC'] = host_NOC_column
# Some NOC values mismatch with iso3 values (e.g. Germany -> DEU/GER) 
# Therefore in order to have a consistency iso3 values are found based on region column
region_list = list(df_medal.region.unique())
iso3_for_team = coco.convert(names=region_list, to='ISO3', not_found=None)

# Creating a dictionary in order to match region to iso3 code
region_to_iso3 = {}
for i in range(len(region_list)):
    region_to_iso3[region_list[i]] = iso3_for_team[i]
    
# Creating a list to store corresponding iso3 code of the region(team)
region_column = []
for i in range(len(df_medal)):
    region_column.append(region_to_iso3[df_medal.region.iloc[i]])
    
# Adding Team_NOC column to the dataframe
df_medal['Team_NOC'] = region_column
# Doing the same process with df_new dataframe
region_list = list(df_new.region.unique())
iso3_for_team = coco.convert(names=region_list, to='ISO3', not_found=None)

region_to_iso3 = {}
for i in range(len(region_list)):
    region_to_iso3[region_list[i]] = iso3_for_team[i]
    
# Creating a list to store corresponding iso3 code of the region(team)
region_column = []
for i in range(len(df_new)):
    region_column.append(region_to_iso3[df_new.region.iloc[i]])
    
# Adding New_NOC column to the dataframe
df_new['Team_NOC'] = region_column
# Creating new column to show if team have played in its own country or not 
Host=[]
for i in range(len(df_medal)):
    if df_medal.Team_NOC.iloc[i] == df_medal.Host_NOC.iloc[i]:
        Host.append(1)
    else:
        Host.append(0)

# Adding Host column to the dataframe
df_medal['Host'] = Host
# determining the countries became host anytime
host_countries = df_medal.Host_NOC.unique()

# filtering dataframe with host countries
df_medal_host_only = df_medal.loc[df_medal['Team_NOC'].isin(host_countries)]<jupyter_output><empty_output><jupyter_text>Different than the logit regression model, we will try to understand if sports are affected by home advantage. Therefore, we will filter the teams that became host and visitor at the specific sport.<jupyter_code>no_host = pd.DataFrame(df_medal_host_only[df_medal_host_only.Host==0].groupby(['Team_NOC','Sport','Host']).Medal.count()).rename(
columns={'Medal' : 'Total_number_of_medals'}).reset_index()

yes_host = pd.DataFrame(df_medal_host_only[df_medal_host_only.Host==1].groupby(['Team_NOC','Sport','Host']).Medal.count()).rename(
columns={'Medal' : 'Total_number_of_medals'}).reset_index()

final_df = pd.merge(no_host, yes_host, how ='inner', on =['Team_NOC', 'Sport'])
final_df = final_df.drop(['Host_x', 'Host_y'], axis=1)
final_df.columns = ['Team_NOC','Sport','total_medals', 'total_medals_when_host']<jupyter_output><empty_output><jupyter_text>In this approach y variable will be calculated as following:1. `Avg_medal_when_host`  = Average medal won by the team at the specific sport when host 2. `Avg_medal_when_count` = Average medal won by the team at the specific sport in all times 3. `advantage`            =1, If `Avg_medal_when_host` > `Avg_medal_when_count` ; 0, otherwise<jupyter_code># Finding the average medal won when host and in overall olympics
total_years = []
for i in range(len(final_df)):
    total_years.append(df_new[(df_new['Team_NOC'] == final_df.Team_NOC.iloc[i]) & (df_new['Sport'] == final_df.Sport.iloc[i])].Year.nunique())
final_df['total_years'] = total_years    
final_df['Avg_medal_count']  = final_df.total_medals/final_df.total_years

total_host_years = []
for i in range(len(final_df)):
    total_host_years.append(len(df_medal[df_medal.Host_NOC==final_df.Team_NOC.iloc[i]].groupby('Year').Year.count()))    
final_df['total_host_years'] = total_host_years 
final_df['Avg_medal_when_host'] = final_df.total_medals_when_host/final_df.total_host_years

# Creating y variable 1 if Avg_medal_count greater than Avg_medal_when_host
adv_binary=[]
for i in range(len(final_df)):
    if final_df.Avg_medal_when_host.iloc[i]> final_df.Avg_medal_count.iloc[i]:
        adv_binary.append(1)
    else:
        adv_binary.append(0)
final_df['advantage'] = adv_binary
final_df.head(5)<jupyter_output><empty_output><jupyter_text>2. Logistic regression model approach: There will be train and test split, 80% to 20% respectively. Splitting will occur randomly, so `random_state` variable taken as 103 in all logistic regression models.  2.1 features: sport, team<jupyter_code>team = pd.get_dummies(final_df['Team_NOC'],drop_first=True)
sport = pd.get_dummies(final_df['Sport'],drop_first=True)
train = final_df.drop(['Team_NOC','Sport','total_medals','total_medals_when_host','total_years',
                       'total_host_years', 'Avg_medal_count', 'Avg_medal_when_host' ],axis=1)
train = pd.concat([train,team,sport],axis=1)
# Splitting data into test and train set 
X_train, X_test, y_train, y_test = train_test_split(train.drop('advantage',axis=1), 
                                                    train['advantage'], test_size=0.20, 
                                                    random_state=103)
logmodel = LogisticRegression()
logmodel.fit(X_train,y_train)
predictions = logmodel.predict(X_test)
print(classification_report(y_test,predictions))<jupyter_output>precision    recall  f1-score   support

           0       0.43      0.16      0.23        19
           1       0.73      0.92      0.81        48

    accuracy                           0.70        67
   macro avg       0.58      0.54      0.52        67
weighted avg       0.65      0.70      0.65        67<jupyter_text>The following execution is made for visualization purposes and to understand the y variable sigmoid function is used. This step will calculated after each model fitting in logistic regression.<jupyter_code>interc = logmodel.intercept_
coefs = logmodel.coef_[0]
sports_team = []
for i in range(1, 22):
    for j in range (22, 75):
        sports_team.append(train.columns[i] +'/'+ train.columns[j])
result = []
for i in range(21):
    for j in range(21,74):
        result.append("{:.2f}".format(1/(1+ math.exp(-(interc + coefs[i] + coefs[j])))))

model_sport_team = pd.DataFrame({'Team/Sport': sports_team,
                   'Probability of advantage': result })
model_sport_team.head(5)<jupyter_output><empty_output><jupyter_text>2.2 feature: sport<jupyter_code>train = final_df.drop(['Team_NOC','Sport','total_medals','total_medals_when_host','total_years',
                       'total_host_years', 'Avg_medal_count', 'Avg_medal_when_host' ],axis=1)
train = pd.concat([train,sport],axis=1)
# Splitting data into test and train set 
X_train, X_test, y_train, y_test = train_test_split(train.drop('advantage',axis=1), 
                                                    train['advantage'], test_size=0.20, 
                                                    random_state=103)
logmodel = LogisticRegression()
logmodel.fit(X_train,y_train)
predictions = logmodel.predict(X_test)
print(classification_report(y_test,predictions))
interc = logmodel.intercept_
coefs = logmodel.coef_[0]
sports = []
for i in range(1, len(train.columns)):
    sports.append(train.columns[i])
result = []
for i in range(len(sports)):
    result.append("{:.2f}".format(1/(1+ math.exp(-(interc + coefs[i])))))

model_sport = pd.DataFrame({'Sport': sports,
                   'Probability of advantage': result })
model_sport.head(5)<jupyter_output><empty_output><jupyter_text>2.3 feature: team<jupyter_code>train = final_df.drop(['Team_NOC','Sport','total_medals','total_medals_when_host','total_years',
                       'total_host_years', 'Avg_medal_count', 'Avg_medal_when_host' ],axis=1)
train = pd.concat([train,team],axis=1)
# Splitting data into test and train set 
X_train, X_test, y_train, y_test = train_test_split(train.drop('advantage',axis=1), 
                                                    train['advantage'], test_size=0.20, 
                                                    random_state=103)
logmodel = LogisticRegression()
logmodel.fit(X_train,y_train)
predictions = logmodel.predict(X_test)
print(classification_report(y_test,predictions))
interc = logmodel.intercept_
coefs = logmodel.coef_[0]
teams = []
for i in range(1, len(train.columns)):
    teams.append(train.columns[i])
result = []
for i in range(len(teams)):
    result.append("{:.2f}".format(1/(1+ math.exp(-(interc + coefs[i])))))

model_team = pd.DataFrame({'Team': teams,
                   'Probability of advantage': result })
model_team.head(5)<jupyter_output><empty_output><jupyter_text>3. Discussion about the results  F-score, recall and precision scores found satisfactory for 1 labeled data, in contrast these performance measurements give pretty low scores for 0 labeled data. From the results, it can be understood that support is distrubuted unbalanced while labeling data. The data is labeled mostly as adavantage (1), so in the test data prediction is inclined with 1 label.  In conclusion, it can be said that the models does not show strong relation between home advantage and sport, team variables.   4. Logit regression model approach Similar data filtering will be taken in this section, but the differences can be listed as below:1. Up until 1992, the Winter and Summer Games were held in the same year. Then, they occured in different years. In order to have a same year gap between the games, only Summer Olympics will be considered. Also, there are less medals given in the Winter Olympics.2. Because there will be no investigation in the sport level, extra filtering is not needed like logistic regression models. The filtering only applied to the teams that experienced being host.<jupyter_code>raw_data = pd.read_csv('data/athlete_events.csv',index_col="ID")
regions = pd.read_csv('data/noc_regions.csv')
df = pd.merge(raw_data, regions, on='NOC', how='left')
# Creating a new dataframe which does not contain non medal data
df_medal = df[df.Medal.isnull() == False]
df_medal = df_medal[df_medal.Year >= 1948]
df_medal = df_medal[df_medal.Season == 'Summer']
df_new = df.copy()
df_new = df_new[df_new.Year >= 1948]
df_new = df_new[df_new.Season == 'Summer']

# Dropping the unncessary columns
df_medal = df_medal.drop(['Name', 'Sex', 'Age', 'Height', 'Weight', 'notes', 'NOC'], axis=1)

# Since 'Individual Olympic Athletes' variable in 'Team' column does not show country information,
# rows with this variable will be dropped.
df_medal.drop( df_medal[ df_medal['Team'] == 'Individual Olympic Athletes' ].index , inplace=True)
df_new.drop( df_new[ df_new['Team'] == 'Individual Olympic Athletes' ].index , inplace=True)

df_medal.loc[df_medal.region == "US", "region"] = "United States"
df_medal.loc[df_medal.region == "USA", "region"] = "United States"
df_medal.loc[df_medal.region == "UK", "region"] = "United Kingdom"
df_medal.loc[df_medal.region == "Boliva", "region"] = "Bolivia"
df_medal.dropna(subset = ["region"], inplace=True)
df_new.loc[df_new.region == "US", "region"] = "United States"
df_new.loc[df_new.region == "USA", "region"] = "United States"
df_new.loc[df_new.region == "UK", "region"] = "United Kingdom"
df_new.loc[df_new.region == "Boliva", "region"] = "Bolivia"
df_new.dropna(subset = ["region"], inplace=True)

# Converting host cities to host countries 
geolocator = Nominatim(user_agent = "geoapiExercises") 
uniq_host_cities = list(df_medal.City.unique())
uniq_host_countries = []
for city in uniq_host_cities:
    location = geolocator.geocode(city, language='en') 
    uniq_host_countries.append(location[0].split(',')[-1])
    
# Converting host countries to iso3 values
iso3_for_host = coco.convert(names=uniq_host_countries, to='ISO3', not_found=None)

# Creating a dictionary in order to match host city to iso3 code
host_city_to_iso3 = {}
for i in range(len(uniq_host_cities)):
    host_city_to_iso3[uniq_host_cities[i]] = iso3_for_host[i]
    
# Creating a list to store corresponding iso3 code of the host city
host_NOC_column = []
for i in range(len(df_medal)):
    host_NOC_column.append(host_city_to_iso3[df_medal.City.iloc[i]])
    
# Adding Team_NOC column to the dataframe
df_medal['Host_NOC'] = host_NOC_column

# Some NOC values mismatch with iso3 values (e.g. Germany -> DEU/GER) 
# Therefore in order to have a consistency iso3 values are found based on region column
region_list = list(df_medal.region.unique())
iso3_for_team = coco.convert(names=region_list, to='ISO3', not_found=None)

# Creating a dictionary in order to match region to iso3 code
region_to_iso3 = {}
for i in range(len(region_list)):
    region_to_iso3[region_list[i]] = iso3_for_team[i]
    
# Creating a list to store corresponding iso3 code of the region(team)
region_column = []
for i in range(len(df_medal)):
    region_column.append(region_to_iso3[df_medal.region.iloc[i]])
    
# Adding Team_NOC column to the dataframe
df_medal['Team_NOC'] = region_column

# Doing the same process with df_new dataframe
region_list = list(df_new.region.unique())
iso3_for_team = coco.convert(names=region_list, to='ISO3', not_found=None)

region_to_iso3 = {}
for i in range(len(region_list)):
    region_to_iso3[region_list[i]] = iso3_for_team[i]
    
# Creating a list to store corresponding iso3 code of the region(team)
region_column = []
for i in range(len(df_new)):
    region_column.append(region_to_iso3[df_new.region.iloc[i]])
    
# Adding New_NOC column to the dataframe
df_new['Team_NOC'] = region_column

# Creating new column to show if team have played in its own country or not 
Host=[]
for i in range(len(df_medal)):
    if df_medal.Team_NOC.iloc[i] == df_medal.Host_NOC.iloc[i]:
        Host.append(1)
    else:
        Host.append(0)

# Adding Host column to the dataframe
df_medal['Host'] = Host

# determining the countries became host anytime
host_countries = df_medal.Host_NOC.unique()

# filtering dataframe with host countries
df_medal_host_only = df_medal.loc[df_medal['Team_NOC'].isin(host_countries)]<jupyter_output><empty_output><jupyter_text>The following dictionary is created to consist host countries for the each Olympic game. `Year` and `Host` are observed as key and value of the dictionary correspondingly.<jupyter_code>host_df = pd.DataFrame(df_medal_host_only.groupby(['Year','Team_NOC']).Host.sum() > 0).rename(
columns={'Host' : 'Host'}).reset_index()
host_df = host_df[host_df.Host==True]
host_df = host_df.drop('Host',axis=1)
host_df
host_df_dict={}
for i in range(len(host_df)):
    year = str(host_df.Year.iloc[i])
    if host_df.Year.iloc[i] in host_df_dict:
        host_df_dict[host_df.Year.iloc[i]].append(host_df.Team_NOC.iloc[i])
    else:
        host_df_dict[host_df.Year.iloc[i]]= [host_df.Team_NOC.iloc[i]]<jupyter_output><empty_output><jupyter_text>Team games consist more than 1 medal, so the following code is calculated to count 1 medal for the team game also.<jupyter_code>df_clean = df_new.copy()
df_clean['Target_Variable'] = np.where(df_clean['Medal'].isnull(), 0, 1)
team_sports = df_clean.groupby(['Year', 'Event']).Target_Variable.sum().reset_index(name='count')
team_sport_list = team_sports[team_sports['count'] > 4].Event.unique().tolist()

df_clean['sport_type'] = ['team' if event in team_sport_list else 'individual' for event in df_clean.Event.tolist()]

# 2. Do the groupby such that team sports are grouped by year, NOC and event to get the average athlete of that year, 
# event and team

df_individual = df_clean[df_clean.sport_type == 'individual']
df_team = df_clean[df_clean.sport_type == 'team']

df_team_grouped = df_team.groupby(['Year', 'NOC', 'Event']).agg({'Age': 'mean',
                                                                 'Height': 'mean',
                                                                 'Weight': 'mean'})

df_team_grouped.reset_index(level=['Year', 'NOC', 'Event'], inplace=True)

df_team = df_team_grouped.merge(df_team.groupby(['Year', 'NOC', 'Event']).head(1).reset_index(drop=True), 
                      on=['Year', 'NOC', 'Event'], suffixes=(None, '_individual'))

df_team.drop(columns=['Age_individual', 'Height_individual', 'Weight_individual'], inplace=True)

df_clean = pd.concat((df_individual, df_team), ignore_index=True)
# Number of total medal for each year
total_medals = {}
years = df_clean.Year.unique()
years.sort()
for i in years:
    total_medals[i]= df_clean[df_clean.Year == i ].Medal.count()<jupyter_output><empty_output><jupyter_text>Independent variables creation is examined in the following:<jupyter_code>df_final = pd.DataFrame({ 'Team': [] ,'Year': [],
                   'Medals_winning_rate': [], 'prehost' : [], 'host' : [], 'posthost' : [] })
team_column = []
for j in host_countries:    
    df = pd.DataFrame(df_clean[df_clean.Team_NOC == j].groupby('Year').Medal.count()).rename(columns={'Medal':'Medals_winning_rate'}).reset_index()
    host_column = []
    for i in range(len(df)):
        team_column.append(j)
        if j in host_df_dict[df.Year.iloc[i]]:
            host_column.append(1)
        else:
            host_column.append(0)
    df['prehost'] = np.zeros(len(df))
    df['host'] = host_column
    df['posthost'] = np.zeros(len(df))
    df['prehost'] = np.append(list(df['host'])[1:],list([0]))
    df['posthost'] = np.append(list([0]), list(df['host'])[:-1])
    tot_med_col = []
    for i in range(len(df)):
        tot_med_col.append(total_medals[df.Year.iloc[i]])
    df['Medals_winning_rate'] = df['Medals_winning_rate']/tot_med_col
    df_final = pd.concat([df_final, df])
df_final['Team'] = team_column
# in order to see the year column as integer
year = []
for i in df_final.Year:
    year.append(int(i))
df_final['Year'] = year
df_final.head(5)
y = df_final['Medals_winning_rate']
X = df_final.drop(['Year', 'Medals_winning_rate', 'Team' ], axis=1)
logit_model=sm.Logit(y,X)
result=logit_model.fit()
print(result.summary2())<jupyter_output>Optimization terminated successfully.
         Current function value: 0.577850
         Iterations 7
                          Results: Logit
==================================================================
Model:              Logit               Pseudo R-squared: inf     
Dependent Variable: Medals_winning_rate AIC:              328.4401
Date:               2020-12-27 14:14    BIC:              339.3338
No. Observations:   279                 Log-Likelihood:   -161.22 
Df Model:           2                   LL-Null:          0.0000  
Df Residuals:       276                 LLR p-value:      1.0000  
Converged:          1.0000              Scale:            1.0000  
No. Iterations:     7.0000                                        
--------------------------------------------------------------------
            Coef.    Std.Err.      z      P>|z|     [0.025    0.975]
--------------------------------------------------------------------
prehost    -2.7592     0.9959   -2.7707   0.005[...]
{'fraction_non_alphanumeric': 0.12831858407079647, 'fraction_numerical': 0.015353982300884956, 'mean_word_length': 4.20041417395306, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 60, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 21, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '586698', 'license': 'MIT', 'n_tokens_mistral': 7798, 'n_tokens_neox': 7456, 'n_words': 1880, 'path': 'Olympics - Final Project/Model2.ipynb', 'repo_name': 'tanselsimsek/Fundamental-of-Data-Science', 'size': '22600'}