<jupyter_start><jupyter_text>Reading World Cup 2018 data<jupyter_code>#Reading the football summary csv files
summary_sum18 = pd.read_csv('/project/2018/1Summary/summarysum.csv', index_col = 0)
offensive_sum18 = pd.read_csv('/project/2018/1Summary/offensivesum.csv', index_col = 0)
defensive_sum18 = pd.read_csv('/project/2018/1Summary/defensivesum.csv', index_col = 0)
clearances_defensive_sum18 = pd.read_csv('/project/2018/1Summary/clearancesdefensivesum.csv', index_col = 0)
ariels_offensive_sum18 = pd.read_csv('/project/2018/1Summary/arielsoffensivesum.csv', index_col = 0)
#Reading the passtypes csv files
assists_pass_type18 = pd.read_csv('/project/2018/3PassTypes/PassTypesAssists.csv')
key_passes_pass_type18 = pd.read_csv('/project/2018/3PassTypes/PassTypesKeyPasses.csv')
detailed_pass_type18 = pd.read_csv('/project/2018/3PassTypes/PassTypesPassDetailed.csv')
#Reading the positional csv files
attack_sides_positional18 = pd.read_csv('/project/2018/4Positional/PositionalAttackSides.csv')
action_zones_positional18 = pd.read_csv('/project/2018/4Positional/PositionalActionZones.csv')
shot_directions_positional18 = pd.read_csv('/project/2018/4Positional/PositionalShotDirections.csv')<jupyter_output><empty_output><jupyter_text>Merge the datasets  All the Summary tables are merged into one table called "all_summary18"<jupyter_code>summary_sum18.head()
offensive_sum18.head()
defensive_sum18.head()
clearances_defensive_sum18.head()
ariels_offensive_sum18.head()
summary_sum18.head()<jupyter_output><empty_output><jupyter_text>Cleaning each table accordingly<jupyter_code>#For each table, we are doing the following cleaning steps:
#1) reseting the index as the index was the team-link and we want it to be a column
#2) renaming the column from team-link to country_name so that it can be easier to use it
#3) splitting the country_name column to separate it from the . and then dropping the second column created called "name"

summary_sum18 = summary_sum18.reset_index()
summary_sum18 = summary_sum18.rename(columns={"team-link": "country_name"})
summary_sum18[['number','country_name']] = summary_sum18.country_name.str.split(".",expand=True,)
summary_sum18 = summary_sum18.drop(columns=["number"])
summary_sum18.head()

offensive_sum18 = offensive_sum18.reset_index()
offensive_sum18 = offensive_sum18.rename(columns={"team-link": "country_name"})
offensive_sum18[['number','country_name']] = offensive_sum18.country_name.str.split(".",expand=True,)
offensive_sum18 = offensive_sum18.drop(columns=["number"])
offensive_sum18.head()

defensive_sum18 = defensive_sum18.reset_index()
defensive_sum18 = defensive_sum18.rename(columns={"team-link": "country_name"})
defensive_sum18[['number','country_name']] = defensive_sum18.country_name.str.split(".",expand=True,)
defensive_sum18 = defensive_sum18.drop(columns=["number"])
defensive_sum18.head()

clearances_defensive_sum18 = clearances_defensive_sum18.reset_index()
clearances_defensive_sum18 = clearances_defensive_sum18.rename(columns={"team-link": "country_name"})
clearances_defensive_sum18[['number','country_name']] = clearances_defensive_sum18.country_name.str.split(".",expand=True,)
clearances_defensive_sum18 = clearances_defensive_sum18.drop(columns=["number"])
clearances_defensive_sum18.head()

ariels_offensive_sum18 = ariels_offensive_sum18.reset_index()
ariels_offensive_sum18 = ariels_offensive_sum18.rename(columns={"team-link": "country_name"})
ariels_offensive_sum18[['number','country_name']] = ariels_offensive_sum18.country_name.str.split(".",expand=True,)
ariels_offensive_sum18 = ariels_offensive_sum18.drop(columns=["number"])
ariels_offensive_sum18.head()


# here we make a list with all the dataframes we want to merge and allocate
# them in a variable called data_frames and then we merge them all in a variable called all_summary18
data_frames = [summary_sum18, offensive_sum18, defensive_sum18, clearances_defensive_sum18, ariels_offensive_sum18]
all_summary18 = reduce(lambda  left,right: pd.merge(left,right,on=['country_name'],
                                            how='inner'), data_frames)
all_summary18.head()


#after merging, we can see that some tables had the same column called "stat-value"
#we can then proceed in dropping two of them and keeping the third one as same columns.
all_summary18 = all_summary18.drop(columns=["stat-value_x","stat-value_y"])

#there are also some columns where their name format is not ideal so we proceeded in updating them 
all_summary18 = all_summary18.rename(columns={"yellow-card":"yellow_card", 
                                              "red-card-":"red_card",
                                             "possession%":"possession_percentage",
                                             "passSuccess%":"passSuccess_percentage"})
all_summary18.head()

# In order to be easier to merge the tables with each other, we sorted them 
# alphabetically based on their country_name

all_summary18 = all_summary18.sort_values("country_name")
all_summary18 = all_summary18.reset_index(drop=True)

# we also want to remove the space from the country_name to be easier to use
# from ' Argentina' to 'Argentina'
all_summary18["country_name"] = [x.strip() for x in all_summary18['country_name']]
all_summary18.head()<jupyter_output><empty_output><jupyter_text>Checking for null values<jupyter_code>all_summary18.isnull().sum()<jupyter_output><empty_output><jupyter_text>All the pass tables are merged into one table called "all_pass18"<jupyter_code>assists_pass_type18.head()
key_passes_pass_type18.head()
detailed_pass_type18.head()
#For each table, we are doing the following cleaning steps:
#1) renaming the column from team-link to country_name so that it can be easier to use it
#2) splitting the country_name column to separate it from the . and then dropping the second column created called "name"

assists_pass_type18 = assists_pass_type18.rename(columns={"team-link": "country_name"})
assists_pass_type18[['number','country_name']] = assists_pass_type18.country_name.str.split(".",expand=True,)
assists_pass_type18 = assists_pass_type18.drop(columns=["number"])
assists_pass_type18.head()

key_passes_pass_type18 = key_passes_pass_type18.rename(columns={"team-link": "country_name"})
key_passes_pass_type18[['number','country_name']] = key_passes_pass_type18.country_name.str.split(".",expand=True,)
key_passes_pass_type18 = key_passes_pass_type18.drop(columns=["number"])
key_passes_pass_type18.head()

detailed_pass_type18 = detailed_pass_type18.rename(columns={"team-link": "country_name"})
detailed_pass_type18[['number','country_name']] = detailed_pass_type18.country_name.str.split(".",expand=True,)
detailed_pass_type18 = detailed_pass_type18.drop(columns=["number"])
detailed_pass_type18.head()

# here we make a list with all the dataframes we want to merge and allocate
# them in a variable called data_frames2 and then we merge them all in a variable called all_pass18
data_frames2 = [assists_pass_type18, detailed_pass_type18,key_passes_pass_type18]
all_pass18 = reduce(lambda  left,right: pd.merge(left,right,on=['country_name'],
                                            how='inner'), data_frames2)
all_pass18.head()


# after merging, we can see that some tables had the same column called "stat-value"
# we can then proceed in dropping all of them as it's the same stat-value as in all_summary18 df.
all_pass18 = all_pass18.drop(columns=["stat-value_x","stat-value_y","stat-value"])
all_pass18.head()

# In order to be easier to merge the tables with each other, we sorted them 
# alphabetically based on their country_name

all_pass18 = all_pass18.sort_values("country_name")
all_pass18 = all_pass18.reset_index(drop=True)

# we also want to remove the space from the country_name to be easier to use
# from ' Argentina' to 'Argentina'
all_pass18["country_name"] = [x.strip() for x in all_pass18['country_name']]
all_pass18.head()<jupyter_output><empty_output><jupyter_text>Checking for null values in the all_pass18 dataframe<jupyter_code>all_pass18.isnull().sum()<jupyter_output><empty_output><jupyter_text>All the position tables are merged into one table called "all_positions18"<jupyter_code>attack_sides_positional18.head()
action_zones_positional18.head()
shot_directions_positional18.head()
# here we make a list with all the dataframes we want to merge and allocate
# them in a variable called data_frames3 and then we merge them all in a variable called all_positions18
data_frames3 = [attack_sides_positional18, action_zones_positional18,shot_directions_positional18]
all_positions18 = reduce(lambda  left,right: pd.merge(left,right,on=['team-link'],
                                            how='inner'), data_frames3)
all_positions18.head()



#replace all dashes with underscore symbol to be easier to use 
all_positions18.columns = all_positions18.columns.str.replace('-','_')

#rename team_link with country_name to be consistent with the other dataframes
all_positions18 = all_positions18.rename(columns={"team_link": "country_name"})
all_positions18.head()


# In order to be easier to merge the tables with each other, we sorted them 
# alphabetically based on their country_name

all_positions18 = all_positions18.sort_values("country_name")
all_positions18 = all_positions18.reset_index(drop=True)

# we also want to remove the space from the country_name to be easier to use
# from ' Argentina' to 'Argentina'
all_positions18["country_name"] = [x.strip() for x in all_positions18['country_name']]
all_positions18.head()<jupyter_output><empty_output><jupyter_text>All the three merged tables<jupyter_code>all_summary18.head()
all_pass18.head()
all_positions18.head()

all_summary18.to_csv("all_summary18.csv")
all_pass18.to_csv("all_pass18.csv")<jupyter_output><empty_output><jupyter_text>Making heatmaps (correlation matrix) to see the correlation between the variables and decide if we any need to be dropped.<jupyter_code># First correlation matrix for the data in all_summary_18 dataset
dfCorr1 = pd.DataFrame(all_summary18)

corrMatrix1 = dfCorr1.corr()
ax = sns.heatmap(corrMatrix1,annot=False, vmin=-1, vmax=1, center=0)
#ax.set_ylim([0,20])
ax.set_title("Correlation Matrix for summary football", fontsize = 14)

_ = ax.set_yticklabels(
    ax.get_yticklabels(),
    rotation=0,
    horizontalalignment='right')

plt.savefig("/project/Visualisation_graphs/Heatmap_football.png")<jupyter_output><empty_output><jupyter_text>From correlation matrix above, we can see that there are some columns showing the same thing. For example shotsPG and shotsPerGame and also aerialwonPG and duelAerialWon. Therefore, we can proceed in dropping them.<jupyter_code>all_summary18 = all_summary18.drop(columns={"shotsPG", "aerialWonPG"})
all_summary18.info()
# Second correlation matrix for the data in all_pass18 dataset
dfCorr2 = pd.DataFrame(all_pass18)

corrMatrix2 = dfCorr2.corr()
ax = sns.heatmap(corrMatrix2,annot=False, vmin=-1, vmax=1, center=0)
#ax.set_ylim([0,20])
ax.set_title("Correlation Matrix for pass football", fontsize = 14)

plt.savefig("/project/Visualisation_graphs/Heatmap_pass.png")<jupyter_output><empty_output><jupyter_text>From correlation matrix above, we can see that there are some columns which are very correlated. For example shortPassAccurate and passTotal and also keyPassShort and keyPassesTotal.Therefore, we proceeded in dropping them.<jupyter_code>all_pass18 = all_pass18.drop(columns={"keyPassesTotal", "passTotal"})
all_pass18.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 32 entries, 0 to 31
Data columns (total 13 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 0   country_name            32 non-null     object 
 1   assistCross             32 non-null     float64
 2   assistCorner            32 non-null     float64
 3   assistThroughball       32 non-null     float64
 4   assistFreekick          32 non-null     float64
 5   assistOther             32 non-null     float64
 6   assist                  32 non-null     float64
 7   passLongBallAccurate    32 non-null     float64
 8   passLongBallInaccurate  32 non-null     float64
 9   shortPassAccurate       32 non-null     float64
 10  shortPassInaccurate     32 non-null     float64
 11  keyPassLong             32 non-null     float64
 12  keyPassShort            32 non-null     float64
dtypes: float64(12), object(1)
memory usage: 3.4+ KB<jupyter_text>Checking if there are any missing values<jupyter_code>all_pass18.isnull().sum()<jupyter_output><empty_output><jupyter_text>Merging the three dataframes we made (all_summary18, all_pass18, all_positions18) into one dataframe called all_football_18<jupyter_code># below we are going to merge our 3 main dataframes into 1.
# The variable will be called all_football_18
data_frames4 = [all_summary18, all_pass18, all_positions18]
all_football_18 = reduce(lambda left,right: pd.merge(left,right,on=['country_name'],
                                            how='inner'), data_frames4)
all_football_18.head()<jupyter_output><empty_output><jupyter_text>Adding the actual positional ranking for each country in 2018<jupyter_code>actual_ranking = pd.read_csv("Actual_Positional_Rankings_2018.csv")
actual_ranking
actual_ranking = actual_ranking.rename(columns=({"Final positional ranking":"Final_positional_ranking"}))
actual_ranking.head()

#merge main dataframe with the actual ranking

all_football_18 = pd.merge(all_football_18, actual_ranking, 
  left_on='country_name', 
  right_on='Team',
    how="right"
)
all_football_18 = all_football_18.sort_values(by=("country_name")).reset_index(drop=True)
all_football_18 = all_football_18.drop(columns=("Team"))
all_football_18.head()


# moving the Final_positional_ranking column to be just after the country_name so
# that it can be easier for the analysis
my_list = list(all_football_18)
all_football_18 = all_football_18[['country_name', 'Final_positional_ranking',
 'goal',
 'shotsPerGame',
 'yellow_card',
 'red_card',
 'possession_percentage',
 'passSuccess_percentage',
 'shotOnTargetPG',
 'dribbleWonPG',
 'foulGivenPG',
 'shotsConcededPG',
 'tacklePG',
 'interceptionPG',
 'foulsPG',
 'offsideGivenPG',
 'clearanceTotal',
 'duelAerialTotal',
 'duelAerialWon',
 'duelAerialLost',
 'stat-value',
 'assistCross',
 'assistCorner',
 'assistThroughball',
 'assistFreekick',
 'assistOther',
 'assist',
 'passLongBallAccurate',
 'passLongBallInaccurate',
 'shortPassAccurate',
 'shortPassInaccurate',
 'keyPassLong',
 'keyPassShort',
 'left_side',
 'middle_side',
 'right_side',
 'own_third',
 'middle_third',
 'opposition_third',
 'shot_left_side',
 'shot_middle_side',
 'shot_right_side']]
all_football_18.head()
dfCorr4 = pd.DataFrame(all_football_18)

corrMatrix4 = dfCorr4.corr()
#ax = sns.heatmap(corrMatrix4,annot=False)
f, ax = plt.subplots(figsize = (20,20))
ax.set_title("Correlation Matrix for all football", fontsize = 4)
sns.heatmap(corrMatrix4, vmax=1, vmin=-1, annot=True, cmap=sns.diverging_palette(220, 20, sep=20, as_cmap=True));<jupyter_output><empty_output><jupyter_text>Export dataset all_football_18 to be able to use it in other jupyter notebooks<jupyter_code>all_football_18.to_csv("all_football_18.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10367213114754098, 'fraction_numerical': 0.03914754098360656, 'mean_word_length': 5.845152603231598, 'pattern_counts': {'":': 14, '<': 61, '<?xml version=': 0, '>': 61, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '574791', 'license': 'Apache-2.0', 'n_tokens_mistral': 5354, 'n_tokens_neox': 4913, 'n_words': 1114, 'path': 'Cleaning_FootballData.ipynb', 'repo_name': 'NataliaChristofi/FIFA_WorldCup_2018', 'size': '15250'}