<jupyter_start><jupyter_text>Import the market_value dataset<jupyter_code>#import market value dataset and all football 18 one
market_value = pd.read_csv('/project/market_value.csv')
all_football_18 = pd.read_csv('/project/all_football_18.csv')
market_value.head()<jupyter_output><empty_output><jupyter_text>Clean the market_value dataset<jupyter_code># Dropping unecessary columns
market_value = market_value.drop(columns=["#"])

# In order to be easier to use, we sorted the table alphabetically based on 
# the country column.
market_value = market_value.sort_values("country")
market_value = market_value.reset_index(drop=True)
market_value.head()
#displaying the all_football_18 dataset so that we can get a list of the countries
# but dropping the unnamed: 0 column
all_football_18 = all_football_18.drop(columns=["Unnamed: 0"])
all_football_18.head()
# Extracting all the 32 countries from the WorldCup and make a list with them in order to use them.
list_of_countries = list(all_football_18["country_name"])
list_of_countries

market_value_all = market_value[market_value['country'].isin(list_of_countries)]

market_value_all = market_value_all.reset_index(drop=True)
market_value_all.head()<jupyter_output><empty_output><jupyter_text>After running this, we can see that we are missing 6 countries. We needed a total of 32 but we only got 26.  To find out which countries we are missing, we merged both tables with a "right"join so that we can see which countries from the all_football_18 dataset didn'thave a match. **The ones missing should have a NaN value.**<jupyter_code>missing_countries = pd.merge(market_value_all, all_football_18, 
  left_on='country', 
  right_on='country_name',
    how="right")

# by running below command, we want to display the rows which have NaN values to 
# see which countries they are.
missing_countries = missing_countries[missing_countries.isna().any(axis=1)]
missing_countries<jupyter_output><empty_output><jupyter_text>After seeing the output of the merge, the countries missing are Australia, Egypt, Iceland, Panama, Saudi-Arabia, Tunisia.Most probably they are written differently<jupyter_code># Below we are trying to find the names of these countries to see how they are written
missing_countries_name = list(market_value['country'])
[t.split() for t in missing_countries_name if t.startswith(('Austra','Saudi','Tune','Pan'))]
[t.split() for t in missing_countries_name if t.endswith(('ypten','land'))]

# Now that we found the names, we want to update them to match the actual names we have
market_value['country'] = market_value['country'].replace({'Australien':'Australia',
                                                          'Ägypten':'Egypt',
                                                          'Island':'Iceland',
                                                          'Saudi-Arabien':'Saudi Arabia',
                                                          'Tunesien':'Tunisia'})

# and we will re run the command to go through the list of countries from the football
# dataset and only match the ones present in both
market_value_all = market_value[market_value['country'].isin(list_of_countries)]

market_value_all = market_value_all.reset_index(drop=True)
market_value_all.head()<jupyter_output><empty_output><jupyter_text>After running this, we can see that we are now only missing 1 country as we needed a total of 32 but we only got 31. **Panama is the missing one.** After some research, we found data for Panama so we will add it.<jupyter_code>#rename the columns by adding "in_euros" part, so that we can remove the € sign from the rows
market_value_all = market_value_all.rename(columns={"market_value":"market_value_in_euros",
                                                   "squad_size 2":"squad_size","avg_age 3":"avg_age"},)

# Cleaning the dataset by replacing some values:
# 1) replacing € with nothing (so basically removing it)
# 2) replacing , with . to keep the same format for the amounts
# 3) replacing "Mio." with "million" to keep the same format 
market_value_all.market_value_in_euros = market_value_all.market_value_in_euros.str.replace('€','')
market_value_all.market_value_in_euros = market_value_all.market_value_in_euros.str.replace(",",".")
market_value_all.market_value_in_euros = market_value_all.market_value_in_euros.str.replace("Mio.","million")


# We used str.extract to pass a regex pattern to extract just the numeric parts.
# the expand=False is added so that it doesn't add a new column.
market_value_all['squad_size'] = market_value_all['squad_size'].str.extract('(\d+)', expand=False)
market_value_all.head()



#Data sources: 
#https://www.businessinsider.com/how-much-each-team-at-the-world-cup-is-worth-2018-6?r=US&IR=T#32-panama-1
#https://en.wikipedia.org/wiki/2018_FIFA_World_Cup_squads#Panama

new_row = {'country':'Panama', 'confederation':"CONCACAF", 'squad_size':23, 'avg_age':28.4, 'market_value_in_euros': "10.17 million"}
#append row to the dataframe
market_value_all = market_value_all.append(new_row, ignore_index=True)
market_value_all.head()<jupyter_output><empty_output><jupyter_text>Feature engineeringWe are going to make a new variable by dividing the market value in euros with the squad size. This will allow us to find the average market value of players in euros for each team. This will be used later for a comparison to see if better paid players perform better.<jupyter_code># making a new column which only contains the digits of the column
market_value_all['market_value_digits'] = market_value_all['market_value_in_euros'].str.extract('(\d+)', expand=False)

# transforming both columns that we will divide to numeric
market_value_all["market_value_digits"] = pd.to_numeric(market_value_all['market_value_digits'])
market_value_all["squad_size"] = pd.to_numeric(market_value_all['squad_size'])

# we replace the value for England because it's billion and not million
market_value_all['market_value_digits'] = market_value_all['market_value_digits'].replace({1.0:1000})
market_value_all.info()

market_value_all['avg_market_value_of_players_in_mill_euros'] = market_value_all['market_value_digits']/market_value_all['squad_size']
market_value_all['avg_market_value_of_players_in_mill_euros'] = market_value_all['avg_market_value_of_players_in_mill_euros'].round(2) 
market_value_all.head()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 32 entries, 0 to 31
Data columns (total 6 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   country                32 non-null     object 
 1   confederation          32 non-null     object 
 2   squad_size             32 non-null     int64  
 3   avg_age                32 non-null     float64
 4   market_value_in_euros  32 non-null     object 
 5   market_value_digits    32 non-null     int64  
dtypes: float64(1), int64(2), object(3)
memory usage: 1.6+ KB<jupyter_text>Exporting clean dataset market_value_all<jupyter_code>market_value_all.to_csv("market_value_all.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09630048009036996, 'fraction_numerical': 0.015108726348489128, 'mean_word_length': 4.559654631083203, 'pattern_counts': {'":': 3, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '421646', 'license': 'Apache-2.0', 'n_tokens_mistral': 2273, 'n_tokens_neox': 2158, 'n_words': 611, 'path': 'Cleaning_MarketValue_Data.ipynb', 'repo_name': 'NataliaChristofi/FIFA_WorldCup_2018', 'size': '7082'}