<jupyter_start><jupyter_text>1) Business Understanding  a) Motivation Since I am a passionate football fan, I really enjoy taking looks at all different kinds of tables: local leagues, European champions competitions, continental and international tournaments like the UEFA EURO or FIFA world cup.The other day I discovered the All-time table of the FIFA World Cup. I instantly wondered what would happen, if you take different points of view e.g. subtract the home advantage, see if the continent where the tournament is played matters or create a new All-time table which includes scores from continental championships, qualification and friendly matches.  b) Overview of Datasets The excel file 'all_time_world.xlsx' contains the All-time table of the FIFA World Cup. It compares men's national association football teams that have participated in the FIFA World Cup by several criteria including tournaments played, consecutive tournaments played or missed, matches, wins, draws, losses, goals, points, points per match, best finish, and confederation. The csv file 'results.csv' contains a table with International football results from 1872 to 2019 including the FIFA world cup, continental competitions, qualification games, friendly games and many more.  c) The questions I will target Q1: Do hosting countires have an higher probability in winning games than other participating countries due to home advantage? Q2: How does the All-time table change if you extract the games with home advantage? Q3: Which larger active tournament has the highest avg. goals per game?  2) Data Understanding  All-time table of the FIFA World Cup<jupyter_code>df_all_time = pd.read_excel('all_time_world.xlsx')
df_all_time.head()
# number of teams that ever participated in a world cup
df_all_time.shape[0]
df_all_time.columns
df_all_time.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 78 entries, 0 to 77
Data columns (total 15 columns):
Country          78 non-null object
Participation    78 non-null int64
Games            78 non-null object
W                78 non-null object
D                78 non-null object
L                78 non-null object
T+               78 non-null int64
T-               78 non-null int64
Diff.            78 non-null object
Points           78 non-null int64
Ø-Points         78 non-null float64
Penalty − W:L    30 non-null object
Best finish      78 non-null object
Unnamed: 13      0 non-null float64
Confederation    78 non-null object
dtypes: float64(2), int64(4), object(9)
memory usage: 9.3+ KB<jupyter_text>International football results from 1872 to 2019<jupyter_code>df_results = pd.read_csv('results.csv')
df_results.head()
# number of international football games played since 1872
df_results.shape[0]
df_results.info()
# Overview of tournaments in the dataset
tournaments = df_results.tournament.unique()
tournaments
# Top 5 Type of International football matches
df_tournament_count = pd.DataFrame(columns=['Tournament'])
for t in tournaments:
    tournament_count = len(df_results[df_results['tournament'] == t])
    df_tournament_count.loc[t] = tournament_count
df_tournament_count.sort_values(by=['Tournament'], ascending=False).head()<jupyter_output><empty_output><jupyter_text>3) Data Preparation<jupyter_code># drop column with NaN values. Since the entire row is NaN it does not make sense to impute them with 0 or something 
# else.
df_all_time.drop(axis=0, columns=['Unnamed: 13'], inplace=True)
df_all_time.columns
# replace missing values with 0 in 'Penalty − W:L' column
df_all_time['Penalty − W:L'].fillna(0, inplace=True)
df_all_time.head()
df_adjusted = df_all_time.copy()
# droping columns, that are not relevant to the final result and to make table clearer
df_adjusted.drop(axis=0, columns=['Diff.', 'T+', 'T-', 'Participation'], inplace=True)
# converting from string to float
df_adjusted['Games'] = df_adjusted['Games'].astype(float)
df_adjusted['W'] = df_adjusted['W'].astype(float)
df_adjusted['D'] = df_adjusted['D'].astype(float)
df_adjusted['L'] = df_adjusted['L'].astype(float)
# Calculates the win/draw/loose share of all teams including hosts
df_adjusted['win_share'] = (df_adjusted['W']/df_adjusted['Games'])
df_adjusted['draw_share'] = (df_adjusted['D']/df_adjusted['Games'])
df_adjusted['loose_share'] = (df_adjusted['L']/df_adjusted['Games'])
def sub_results(value):
    """
    This method subtracts the won, draws or lost games from the hosting countries.
    value = country (host)
    """
    df_country = df_world_cup_not_neutral[df_world_cup_not_neutral['home_team'] == value]
    df_country['h_win'] = np.where(df_country['home_score'] > 
                                   df_country['away_score'], 1,0)
    df_country['h_draw'] = np.where(df_country['home_score'] == df_country['away_score'], 1,0)
    df_country['h_lost'] = np.where(df_country['home_score'] < 
                                    df_country['away_score'], 1,0)
    d_host_counts_win[value] = d_host_counts_win[value] - (
        d_host_counts_win[value] - df_country.h_win.sum()
    )
    d_host_counts_draw[value] = d_host_counts_draw[value] - (
        d_host_counts_draw[value] - df_country.h_draw.sum()
    )
    d_host_counts_lost[value] = d_host_counts_lost[value] - (
        d_host_counts_lost[value] - df_country.h_lost.sum()
    )
def bar_plot(column):
    """"
    This method creates bar plots. In this case to display the lost points and position of a host.
    column = name of the column in df_diff_hosts table 'diff_points' or 'position_loss'
    """
    f, ax = plt.subplots(figsize=(20, 10))
    sns.set(style='whitegrid')
    sns.set_context('talk')
    sns.barplot(data=df_diff_hosts, x='Country', y=column)
    ax.set( ylabel=column, xlim=(-0.5,16.5),
           xlabel='Country', ylim=(-40, 5))
    ax.xaxis.set_ticks_position('bottom')
    plt.xticks(rotation=90)
    #sns.despine(left=True, bottom=True)
    sns.set_context('talk')
    for p in ax.patches:
        ax.annotate(
            s='{}'.format(int(p.get_height())),
            xy=(p.get_x() + p.get_width() / 2., p.get_height()), 
            xycoords='data',
            ha='center', 
            va='center', 
            fontsize=16, 
            color='black',
            xytext=(0,9), 
            textcoords='offset points',
            clip_on=True,                   # <---  important
        )<jupyter_output><empty_output><jupyter_text>4) Questions  Q1: Do hosting countires have an higher probability in winning games than other participating countries due to home advantage?<jupyter_code>df_results.head()
# Select from tournament columns 'FIFA World Cup' and only countries that hosted a world cup
df_world_cup_not_neutral = df_results[(df_results['tournament'] == 'FIFA World Cup') & 
                                      (df_results['neutral'] == False)]
# Substract the home column from away column to create boolean column with home wins
df_world_cup_not_neutral['home_win'] = np.where(df_world_cup_not_neutral['home_score'] > 
                                                df_world_cup_not_neutral['away_score'], True, False)
# average percantage of winning for a hosting country 
df_world_cup_not_neutral.home_win.sum() / df_world_cup_not_neutral.shape[0] * 100
df_champions = df_all_time[df_all_time['Best finish'].str.contains("Champion")]
# Creating a new df to save which host won the title
df_hosts = df_world_cup_not_neutral[['date', 'country']]
df_hosts = df_hosts.drop_duplicates(subset='date', keep='first')
df_hosts["year"]= 1
df_hosts["year"]= df_hosts["date"].str.split("-", n = 1, expand = True) 
df_hosts.drop_duplicates(subset='year', keep='first', inplace=True)
df_hosts.drop(axis=0, columns=['date'], inplace=True)
df_hosts.reset_index(inplace=True)
df_hosts['won_title'] = False
# Mannually inserting if a hosting country has won the world cup
df_hosts.loc[0, "won_title"] = True
df_hosts.loc[1, "won_title"] = True
df_hosts.loc[2, "won_title"] = False
df_hosts.loc[3, "won_title"] = False
df_hosts.loc[4, "won_title"] = False
df_hosts.loc[5, "won_title"] = False
df_hosts.loc[6, "won_title"] = False
df_hosts.loc[7, "won_title"] = True
df_hosts.loc[8, "won_title"] = False
df_hosts.loc[9, "won_title"] = True
df_hosts.loc[10, "won_title"] = True
df_hosts.loc[11, "won_title"] = False
df_hosts.loc[12, "won_title"] = False
df_hosts.loc[13, "won_title"] = False
df_hosts.loc[14, "won_title"] = False
df_hosts.loc[15, "won_title"] = False
df_hosts.loc[16, "won_title"] = False
df_hosts.loc[17, "won_title"] = False
df_hosts.loc[18, "won_title"] = False
df_hosts.loc[19, "won_title"] = False
df_hosts.loc[20, "won_title"] = False
df_hosts.drop(axis=0, columns=['index'], inplace=True)
# percentage of hosts winning the world cup
df_hosts.won_title.sum() / df_hosts.shape[0] * 100<jupyter_output><empty_output><jupyter_text>From the result 'average percantage of winning for a hosting country'  (62%) we can see, that hosting countries have a slight advantage against other participating teams, given a simplified 50:50 chance of winning a game.  Regarding the total count of world cups, the hosting countries have a 23% probablitlyt in actually winning the world cup. But since 1978, there has not been a hosting country that won the world cup.   Q2: How does the All-time table change, if you extract the games with home advantage? The idea is, to substract the matches where teams had an home advantage by hosting the world cup. The matches will be substracted in total from 'Games' column, but also from 'W', 'D' and 'L' columns. Then we are going to analyze how it affected the teams by updating the 'Points' column and correcting their position in the table. I expect the top 5 countries of the all-time table to loose most points and positions, since they have attended more world cup games than other hosts, since they mostly did not got disqualified in the group stage, and therefore have accumulated more wins and draws. Substractung these, might hurt the top 5 countries more than the less performant countries.<jupyter_code>df_all_time.head()
# adds a column with the index value + 1 to the table, in order to substract later the newly calculated 
# index from the former one
df_all_time['Position'] = df_all_time.Country.index + 1
d_host_counts = dict(df_world_cup_not_neutral['home_team'].value_counts())
d_host_counts_win = d_host_counts.copy()
d_host_counts_draw = d_host_counts.copy()
d_host_counts_lost = d_host_counts.copy()
# Substract number of world cup games from hosting countries
values = d_host_counts.keys()    
for v in values:
    df_adjusted.loc[df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "Games"] = df_adjusted.loc[
        df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "Games"
    ] - d_host_counts[v]
    
df_adjusted.head()
# Substracts count of wins,draws and losts from host teams. The method sub_results(v) counts of 
# each hosting country the wins, draws and losses. The results are stored in the dictionaries d_host_counts_win, 
# d_host_counts_draw and d_host_counts_lost. These are substracted from the values of each column of the all-time 
# table.
values = d_host_counts.keys()   
for v in values:
    sub_results(v)
    df_adjusted.loc[df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "W"] = df_adjusted.loc[
        df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "W"
    ] - d_host_counts_win[v]
    df_adjusted.loc[df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "D"] = df_adjusted.loc[
        df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "D"
    ] - d_host_counts_draw[v] 
    df_adjusted.loc[df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "L"] = df_adjusted.loc[
        df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "L"
    ] - d_host_counts_lost[v]
df_adjusted
# Calculates the new points for each host team
#values = d_host_counts.keys()    
for v in values:
    df_adjusted.loc[
        df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "Points"
                    ] = df_adjusted.loc[
                        df_adjusted[df_adjusted['Country'].str.contains(v)].index[0], "W"
                        ] * 3 + df_adjusted.loc[df_adjusted[
                        df_adjusted['Country'].str.contains(v)].index[0], "D"
                        ]
# recalculates win/draw/loose share for all teams
df_adjusted['win_share'] = (df_adjusted['W']/df_adjusted['Games'])
df_adjusted['draw_share'] = (df_adjusted['D']/df_adjusted['Games'])
df_adjusted['loose_share'] = (df_adjusted['L']/df_adjusted['Games'])

df_adjusted.sort_values(by=['Points'], ascending=False, inplace=True)

df_diff = df_adjusted[['Country', 'Games', 'W', 'D', 'L', 'Points']]
# calculates how many points were lost by each hosting country
df_diff['diff_points'] =  df_adjusted.Points - df_all_time.Points 
# calculates how many positions were lost by each hosting country
df_diff['position_loss'] = (df_adjusted.index + 1) - df_all_time.Position.values 
# drops non hosting countries since they are not relevant for visualisation
df_diff_hosts = df_diff[df_diff.diff_points != 0.0]
df_diff_hosts.sort_values(by=['diff_points'], inplace=True)
bar_plot('diff_points')
df_diff_hosts.sort_values(by=['position_loss'], ascending = True,inplace=True)
df_diff_hosts
bar_plot('position_loss')
df_adjusted.head()<jupyter_output><empty_output><jupyter_text>Substracting the games with home advantage, affected the teams in different ways. Germany and Brazil hosted the highest amount of games but performed above average in the world cups. Therefore their loss of 34 and 24 points each, did not affect their ranking. Some hosting countries affected each others ranking which can be explained by their overall performance in the world cups. Italy(32) lost 16 more points than Argentina(16) which made them switch positions. In the lost-points graph its clear, that countries that performed poorly in their world cup history were much more affected, than teams that performed over average, even though they hosted more games and lost more points. Their high number of wins can bounce off the substraction of the games with home advantage.   The loss of 4 games with home advantage and 7 points made Japan loose 10 positions. South Africa lost 6 position by just loosing 3 games and 4 points. This underlines that the world cup history (games played besides the hosted world cup) and played games in total are crucial to the overall performance in the all time table.  3) Which larger active tournament has the highest avg. goals per game? Since 1872, there were 109 different tournaments played, with a total of 40.839 games played. I would like to know, regarding tournaments that are active (last game max. 5 years ago), which tournament has the highest avg. goal score per game. I would assume that qualification games or tournaments with an high performance gap, have the highest avg goal score per match.<jupyter_code># this loop calcualtates the avg goal score per game, total games count and last game played for each tournament
tournament = df_results.tournament.unique()
d_avg_goal_per_game = {}
d_games_total = {}
d_t_last = {}
for t in tournaments:
    a =( df_results[
        df_results['tournament'] == t 
    ].home_score.sum() + df_results[
        df_results['tournament'] == t 
    ].away_score.sum()) / df_results[
        df_results['tournament'] == t ].shape[0]
#     avg. goals per game
    d_avg_goal_per_game[t] = a.mean()
#     total games count
    d_games_total[t] = df_results[df_results['tournament'] == t ].shape[0]
#     last game played
    d_t_last[t] =  max(df_results[df_results['tournament'] == t ].date)
# creates a new df to store key and values from d_avg_goal_per_game dictionray
df_avg_goal_per_game = pd.DataFrame(columns=['Tournament','Ø-Goals'])
df_avg_goal_per_game['Tournament'] = d_avg_goal_per_game.keys()
df_avg_goal_per_game['Ø-Goals'] = d_avg_goal_per_game.values()
df_avg_goal_per_game.sort_values('Ø-Goals', ascending=False, inplace=True)
df_avg_goal_per_game.head()
# creates a new df to store key and values from d_games_total dictionray
df_matches_count = pd.DataFrame(columns=['Tournament', 'Count'])
df_matches_count['Tournament'] = d_games_total.keys()
df_matches_count['Count'] = d_games_total.values()
df_matches_count.sort_values('Count', ascending=False, inplace=True)
len(df_matches_count)
# selecting only tournaments with a total count larger than 100, to exclude too small or local tournaments
df_matches_count = df_matches_count[df_matches_count.Count > 100]
df_avg_goals_match_100_plus = df_avg_goal_per_game[
    df_avg_goal_per_game['Tournament'].isin(df_matches_count['Tournament'])
]
# creates a new df to store key and values from d_t_last dictionray
df_still_active = pd.DataFrame(columns=['Tournament', 'Last_year'])
df_still_active['Tournament'] = d_t_last.keys()
df_still_active['Last_year'] = d_t_last.values()
df_still_active['Last_year'] = pd.to_datetime(df_still_active['Last_year'], errors='coerce')
# selecting only tournaments after 2014, assuming they are still active, since some tourmanets 
# have cycles of max. 5 years
df_active = df_still_active[df_still_active['Last_year'].dt.year >= 2015]
df_avg_goals_match_100_plus_active = df_avg_goals_match_100_plus[
    df_avg_goals_match_100_plus['Tournament'].isin(df_active['Tournament'])
]
df_avg_goals_match_100_plus_active.sort_values('Ø-Goals', ascending=False, inplace=True)
df_avg_goals_match_100_plus_active.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10395983110806802, 'fraction_numerical': 0.015063334474495036, 'mean_word_length': 4.661175710594315, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '294527', 'license': 'CNRI-Python', 'n_tokens_mistral': 5826, 'n_tokens_neox': 5386, 'n_words': 1665, 'path': 'Data Science Blog Post.ipynb', 'repo_name': 'nichohelmut/Write-a-Data-Science-Blog-Post', 'size': '17526'}