<jupyter_start><jupyter_text>Project: Soccer Database Table of ContentsIntroductionData WranglingExploratory Data AnalysisConclusions  Introduction<jupyter_code># Use this cell to set up import statements for all of the packages that you
#   plan to use.

# Remember to include a 'magic word' so that your visualizations are plotted
#   inline with the notebook. See this page for more:
#   http://ipython.readthedocs.io/en/stable/interactive/magics.html
import pandas as pd
import pandasql as ps
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
df_country = pd.read_csv('Country.csv')
df_league = pd.read_csv('League.csv')
df_match = pd.read_csv('Match.csv')
df_player_att = pd.read_csv('Player_Attributes.csv')
df_player = pd.read_csv('Player.csv')
df_team_att = pd.read_csv('Team_Attributes.csv')
df_team = pd.read_csv('Team.csv')<jupyter_output><empty_output><jupyter_text>Data Wrangling General Properties<jupyter_code># Load your data and print out a few lines. Perform operations to inspect data
# types and look for instances of missing or possibly errant data.
# Viewing fist 5 raws of tables:
df_country.head()
df_player.head()
df_league.head()
df_match.head()
df_player_att.head()
df_team_att.head()
df_team.head()
#getting some information about tables:
df_country.info()
df_league.info()
df_player.info()
df_match.info()
df_player_att.info()
df_team.info()
df_team_att.info()
# Seems like match table is too big. 
df_match.columns[df_match.isnull().any()]
df_team.columns[df_team.isnull().any()]
df_player_att.columns[df_player_att.isnull().any()]
df_team_att.columns[df_team_att.isnull().any()]<jupyter_output><empty_output><jupyter_text>There are four tables which have null values.<jupyter_code>sum(df_match.duplicated())
sum(df_country.duplicated())
sum(df_league.duplicated())
sum(df_player.duplicated())
sum(df_player_att.duplicated())
sum(df_team.duplicated())
sum(df_team_att.duplicated())<jupyter_output><empty_output><jupyter_text>There is not any duplicate values.<jupyter_code>df_player.birthday, df_match.date, df_player_att.date, df_team.team_fifa_api_id, df_team_att.date<jupyter_output><empty_output><jupyter_text>These are wrong data types due to above info(). I will fix them.  Data Cleaning<jupyter_code>#changing the dtype in matches
df_match['date'] = pd.to_datetime(df_match['date'])
df_match.shape
#Create new dataframe only with necessary columns of match table:
df_match_score = df_match.iloc[:, :11]
df_match_score.head()
#Filling, drop, dtype with player attributes table:
df_player_att['date'] = pd.to_datetime(df_player_att['date'])
df_player_att.drop(['preferred_foot', 'attacking_work_rate', 'defensive_work_rate'], axis = 1, inplace = True)
df_player_att.fillna(df_player_att.mean(), inplace = True)
df_player.birthday = pd.to_datetime(df_player.birthday)
df_player_att.info()
#Filling, drop, dtype with team attributes table
df_team.fillna(df_team.mean(), inplace = True)
df_team.team_fifa_api_id.astype(int)
df_team_att.date = pd.to_datetime(df_team_att.date)
df_team.info()
df_team_att.fillna(df_team_att.mean(), inplace = True)
df_team_att.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1458 entries, 0 to 1457
Data columns (total 25 columns):
 #   Column                          Non-Null Count  Dtype         
---  ------                          --------------  -----         
 0   id                              1458 non-null   int64         
 1   team_fifa_api_id                1458 non-null   int64         
 2   team_api_id                     1458 non-null   int64         
 3   date                            1458 non-null   datetime64[ns]
 4   buildUpPlaySpeed                1458 non-null   int64         
 5   buildUpPlaySpeedClass           1458 non-null   object        
 6   buildUpPlayDribbling            1458 non-null   float64       
 7   buildUpPlayDribblingClass       1458 non-null   object        
 8   buildUpPlayPassing              1458 non-null   int64         
 9   buildUpPlayPassingClass         1458 non-null   object        
 10  buildUpPlayPositioningClass     1458 non-null   object        
 11  chan[...]<jupyter_text>Exploratory Data Analysis What teams improved the most over the time period?<jupyter_code># Creating new column winner which explains win or lost.
conditions = [(df_match_score['home_team_goal'] > df_match_score['away_team_goal']), (df_match_score['home_team_goal'] < df_match_score['away_team_goal'])]
choices = [df_match_score['home_team_api_id'], df_match_score['away_team_api_id']]
df_match_score['winner'] = np.select(conditions, choices, default='draw')
df_match_score = df_match_score[df_match_score.winner != 'draw']
df_match_score.head()
#change the string to int
df_match_score['winner'] = df_match_score['winner'].astype(int)
df_match_score.winner
df_match_score['league_id'].value_counts()
#We do not these columns:
df_match_score.drop(['home_team_api_id', 'away_team_api_id', 'stage', 'match_api_id', 'home_team_goal', 'away_team_goal'], axis=1, inplace=True)
#Merging two tables on country_id column in order to know the name of league
result = pd.merge(df_match_score, df_league[['country_id', 'name']], on = 'country_id')
#I am using pandasql in order to get three best leagues since SQL is kinda comfortable for this:
def sql_name(country_id):
    return ps.sqldf('SELECT name FROM result WHERE country_id = ' + country_id)
sql_name('21518')
sql_name('1729')
sql_name('10257')
result.groupby('name')['winner'].count().plot(kind='bar', title='Teams which has the most Wins');
plt.xlabel('Leagues')
plt.ylabel('Wins')<jupyter_output><empty_output><jupyter_text>As you can see, Spain Liga BBVA's teams improved the most. Second place is for England Premier League's teams. Third place is for Italy Serie A's teams.  Which players had the most penalties?<jupyter_code>#Merge df_player_att table and df_player:
result1 = pd.merge(df_player_att, df_player[['player_api_id', 'player_name']], on = 'player_api_id')
result1
ps.sqldf('SELECT DISTINCT player_name, penalties FROM result1 ORDER BY penalties DESC')<jupyter_output><empty_output><jupyter_text>As you can see, <NAME> had the most penalties with 96,  <NAME> = 95, <NAME> = 95 and so on.   What team attributes lead to the most victories?<jupyter_code>#Looking for spain teams since they are the most powerful
def sql_all(team_api_id):
    return ps.sqldf('SELECT * FROM df_team_att WHERE  team_api_id = ' + team_api_id)
sql_all('8634')
df_team_att.plot(x='buildUpPlaySpeed', y='buildUpPlaySpeedClass', kind='scatter');<jupyter_output><empty_output><jupyter_text>This scatter plot clearly highlights that fast speed is important. However as you can see, balanced speed is also fine in order to win in match.<jupyter_code>#Looking for england teams since they are powerful also:
sql_all('10260')
df_team_att.plot(x='defencePressure', y='defencePressureClass', kind='scatter');<jupyter_output><empty_output><jupyter_text>Looks like defence pressure should be medium as there are lots mentions in medium.<jupyter_code>#Looking for italy teams since they are powerful too:
sql_all('9885')
df_team_att.plot(x='defenceTeamWidth', y='defenceTeamWidthClass', kind='scatter');<jupyter_output><empty_output><jupyter_text>Ok, it seems like normal team width is good for a team.<jupyter_code>df_team_att.plot(x='buildUpPlayPassing', y='buildUpPlayPassingClass', kind='scatter');<jupyter_output><empty_output><jupyter_text>Mixed passes are a good ones.<jupyter_code>df_team_att.plot(x='chanceCreationShooting', y='chanceCreationShootingClass', kind='scatter');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10849242922975642, 'fraction_numerical': 0.0163265306122449, 'mean_word_length': 5.160583941605839, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 53, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '176690', 'license': 'MIT', 'n_tokens_mistral': 2631, 'n_tokens_neox': 2508, 'n_words': 616, 'path': 'investigate a dataset - soccer.ipynb', 'repo_name': 'Ibodullaev/Soccer-Database', 'size': '7618'}