<jupyter_start><jupyter_text>How to complete this assignmentFirst, download [this Kaggle dataset](https://www.kaggle.com/hugomathien/soccer) and extract *sqlite* database. You may need to register at https://www.kaggle.com/ beforehand. Then complete 15 graded tasks below, the score is given in brackets. Finally submit the resulting `.ipynb` file to rs-app Auto-test.- Do not delete or rename the variables given before the inscription `your code here`, they are needed for the correct verification.- Do not change the code in the last Notebook cell, it is required for the server check.- Your Notebook must run completely without errors to be graded! Please check everything before submission by going *Cell -> Run All*  Some important notes- If you need to **calculate the number of "something"** that means we expect you to assign an Integer to the given variable- If you need to **make a list of "something"** we expect you to assign a Python list with appropriate values to the given variable- If you need to find a **specifiс player, day of the week, team, etc.** we expect you to assign a String with the full name of the entity to the given variable (`player_name`, day of week full name, `team_name`, etc.)<jupyter_code>import sqlite3
import pandas as pd
import os
import numpy as np

pd.set_option('display.max_column', None)
# Leave that code unchanged, it is required for the server check!
db = sqlite3.connect(os.environ.get("DB_PATH") or 'database.sqlite')
# You may load the data from SQL table directly to the Pandas dataframe as
player_data = pd.read_sql("SELECT * FROM Player;", db)
# Also we should connect other tables that we have to use in these tasks
player_stats_data = pd.read_sql("SELECT * FROM Player_Attributes;", db)
teams_data = pd.read_sql("SELECT * FROM Team;", db)
match_data = pd.read_sql("SELECT * FROM Match;", db)
league_data = pd.read_sql("SELECT * FROM League;", db)
player_data.head()<jupyter_output><empty_output><jupyter_text>**Task 1 (0.25 point).** Calculate the number of players with a height between 180 and 190 inclusive<jupyter_code># this is my solution

# players_180_190 = int(player_data['height'][(player_data['height'] >= 180.00) & (player_data['height'] <= 190.00)].count())

# this is right solution
players_180_190 = player_data[(player_data['height'] >= 180) & (player_data['height'] <= 190)].shape[0]
assert(isinstance(players_180_190, int))<jupyter_output><empty_output><jupyter_text>**Task 2 (0.25 point).** Calculate the number of players born in 1980. **Hint:** you may want to cast your 'birthday' column to DateTime type by [pandas.to_datetime](https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html)<jupyter_code># this is my solution

# players_1980 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1980-01-01") & (player_data['birthday'] <= "1980-12-31")].count())

# this is right solution
player_data['birthday'] = pd.to_datetime(player_data['birthday'])
players_1980 = player_data[player_data['birthday'].dt.year == 1980].shape[0]
assert(isinstance(players_1980, int))<jupyter_output><empty_output><jupyter_text>**Task 3 (0.25 point).** Make a list of the top 10 players with the highest weight sorted in descending order. If there are several players with the same weight put them in the lexicographic order by name.<jupyter_code># this is my solution

# players = player_data[['player_name', 'weight']].sort_values(by=['weight'], ascending=False).head(10)
# highest_players = players['player_name'].values.tolist()

# this is right solution
highest_players = player_data.sort_values(by=['weight', 'player_name'], ascending=[False, True])[:10]['player_name'].tolist()
assert(len(highest_players) == 10)
assert(isinstance(highest_players, list))
for i in range(10):
    assert(isinstance(highest_players[i], str))<jupyter_output><empty_output><jupyter_text>**Task 4 (0.5 point).** Make a list of tuples containing years along with the number of players born in that year from 1980 up to 1990. **Structure example**: [(1980, 123), (1981, 140) ..., (1990, 83)] -> There were born 123 players in 1980, there were born 140 players in 1981 and etc.<jupyter_code># this is my solution

# data_list = list()
# dataset = pd.to_datetime(player_data['birthday'])
# data_1980 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1980-01-01") & (player_data['birthday'] <= "1980-12-31")].count())
# data_1981 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1981-01-01") & (player_data['birthday'] <= "1981-12-31")].count())
# data_1982 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1982-01-01") & (player_data['birthday'] <= "1982-12-31")].count())
# data_1983 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1983-01-01") & (player_data['birthday'] <= "1983-12-31")].count())
# data_1984 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1984-01-01") & (player_data['birthday'] <= "1984-12-31")].count())
# data_1985 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1985-01-01") & (player_data['birthday'] <= "1985-12-31")].count())
# data_1986 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1986-01-01") & (player_data['birthday'] <= "1986-12-31")].count())
# data_1987 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1987-01-01") & (player_data['birthday'] <= "1987-12-31")].count())
# data_1988 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1988-01-01") & (player_data['birthday'] <= "1988-12-31")].count())
# data_1989 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1989-01-01") & (player_data['birthday'] <= "1989-12-31")].count())
# data_1990 = int(pd.to_datetime(player_data['birthday'])[(player_data['birthday'] >= "1990-01-01") & (player_data['birthday'] <= "1990-12-31")].count())

# data_list.append((1980, data_1980))
# data_list.append((1981, data_1981))
# data_list.append((1982, data_1982))
# data_list.append((1983, data_1983))
# data_list.append((1984, data_1984))
# data_list.append((1985, data_1985))
# data_list.append((1986, data_1986))
# data_list.append((1987, data_1987))
# data_list.append((1988, data_1988))
# data_list.append((1989, data_1989))
# data_list.append((1990, data_1990))

# years_born_players = data_list

# this is right solution
player_data['birthday_year'] = player_data['birthday'].dt.year
born_years = player_data.groupby('birthday_year').size().loc[lambda x: (1980 <= x.index) & (x.index <= 1990)]
years_born_players = list(born_years.items())
assert(len(years_born_players) == 11)
assert(isinstance(years_born_players, list))
for i in range(10):
    assert(isinstance(years_born_players[i], tuple))
    assert(isinstance(years_born_players[i][0], int))
    assert(isinstance(years_born_players[i][1], int))<jupyter_output><empty_output><jupyter_text>**Task 5 (0.5 point).** Calculate the mean and the standard deviation of the players' **height** with the name **Adriano**. **Note:** Name is represented by the first part of `player_name`.<jupyter_code># this is my solution

# adriano = player_data[['player_name', 'height']][(player_data['player_name'].str.match('^Adriano'))]
# adriano_mean, adriano_std = float(adriano.mean()), float(adriano.std())

# this is right solution
mask = player_data['player_name'].str.startswith('Adriano')
adriano_mean, adriano_std = player_data[mask]['height'].mean(), player_data[mask]['height'].std()
assert(isinstance(adriano_mean, float))
assert(isinstance(adriano_std, float))<jupyter_output><empty_output><jupyter_text>**Task 6 (0.75 point).** How many players were born on each day of the week? Find the day of the week with the minimum number of players born.<jupyter_code>dow_with_min_players_born = player_data.groupby(player_data['birthday'].dt.day_name()).size().sort_values().keys()[0]
assert(isinstance(dow_with_min_players_born, str))<jupyter_output><empty_output><jupyter_text>**Task 7 (0.75 point).** Find a league with the most matches in total. If there are several leagues with the same amount of matches, take the first in the lexical order.<jupyter_code>league = league_data[['id', 'name']]
league.columns = ['league_id', 'league_name']

league_most_matches = match_data \
                      .merge(league, 'left') \
                      .groupby('league_name').size() \
                      .reset_index() \
                      .sort_values(by=[0, 'league_name'], ascending=[False, True]).iloc[0]['league_name']
assert(isinstance(league_most_matches, str))<jupyter_output><empty_output><jupyter_text>**Task 8 (1.25 point).** Find a player who participated in the largest number of matches during the whole match history. Assign a `player_name` to the given variable<jupyter_code># this is right solution

import re

home_player_cols = [c for c in match_data.columns if re.match(r'home_player_\d\d?', c)]
away_player_cols = [c for c in match_data.columns if re.match(r'away_player_\d\d?', c)]
player_cols = home_player_cols + away_player_cols

max_matches_player_id = match_data \
                        .melt(value_vars=player_cols) \
                        .groupby('value').size() \
                        .sort_values(ascending=False) \
                        .keys()[0]

max_matches_player = player_data[player_data['player_api_id'] == max_matches_player_id]['player_name'].values[0]
assert(isinstance(max_matches_player, str))<jupyter_output><empty_output><jupyter_text>**Task 9 (1.5 point).** List top-5 tuples of most correlated **player's characteristics** in the descending order of the absolute [Pearson's coefficient](https://en.wikipedia.org/wiki/Pearson_correlation_coefficient) value.**Note 1:** Players characteristics are all the columns in `Player_Attributes` table except `[id, player_fifa_api_id, player_api_id, date, preferred_foot, attacking_work_rate, defensive_work_rate]`). **Note 2:** Exclude duplicated pairs from the list. E.g. ('gk_handling', 'gk_reflexes') and ('gk_reflexes', 'gk_handling') are duplicates, leave just one of them in the resulting list.**Hint:** You may use [dataframe.corr()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.corr.html) for calculating pairwise Pearson correlation.<jupyter_code># this is right solution

other_cols = ['id', 'player_fifa_api_id', 'date', 'preferred_foot', 'attacking_work_rate', 'defensive_work_rate']
char_cols = [c for c in player_stats_data if c not in other_cols]

# corr - this is a correlation matrix
# corr = player_stats_data[char_cols] \
#       .corr().abs().unstack().to_frame().reset_index()
# corr = corr[corr['level_0'] != corr['level_1']]
# corr['feature_pair'] = corr.apply(lambda x: (x['level_0'], x['level_1']), axis=1)
# corr['feature_pair'] = corr.apply(lambda x: (tuple(sorted(x['feature_pair']))), axis=1)

# top_correlated_features = corr[['feature_pair', 0]] \
#                          .drop_duplicates().sort_values(by=0, ascending=False)[:5]['feature_pair'].values.tolist()

# alternative right solution with numpy

player_attributes = pd.read_sql('SELECT * FROM Player_Attributes;', db)
df = player_attributes.copy().drop(['id', 'player_fifa_api_id', 'player_api_id', 'date', 'preferred_foot', 'attacking_work_rate', 'defensive_work_rate'],axis=1).corr(method='pearson').abs()
val = df.values
i,j = np.tril_indices_from(val, -1)
top_correlated_features = pd.Series(val[i, j], [df.index[i], df.columns[j]]).nlargest(5).index.tolist()
assert(len(top_correlated_features) == 5)
assert(isinstance(top_correlated_features, list))
for i in range(5):
    assert(isinstance(top_correlated_features[i], tuple))
    assert(isinstance(top_correlated_features[i][0], str))
    assert(isinstance(top_correlated_features[i][1], str))<jupyter_output><empty_output><jupyter_text>**Task 10 (2 points).** Find top-5 most similar players to **Neymar** whose names are given. The similarity is measured as [Euclidean distance](https://en.wikipedia.org/wiki/Euclidean_distance) between vectors of players' characteristics (described in the task above). Put their names in a vector in ascending order by Euclidean distance and sorted by `player_name` if the distance is the same**Note 1:** There are many records for some players in the `Player_Attributes` table. You need to take the freshest data (characteristics with the most recent `date`). **Note 2:** Use pure values of the characteristics even if you are aware of such preprocessing technics as normalization. **Note 3:** Please avoid using any built-in methods for calculating the Euclidean distance between vectors, think about implementing your own.<jupyter_code>players_extended = player_stats_data \
                   .merge(player_data[['player_api_id', 'player_name']], 'inner', 'player_api_id') \
                   .sort_values('date').drop_duplicates(['player_api_id'], keep='last')

name='Neymar'
neymar_vector = players_extended[players_extended['player_name'] == name]
players_vector = players_extended[char_cols].values
# players_extended['neymar_similar'] = np.sqrt(np.sum((players_vector - neymar_vector) ** 2, axis=1))
# neymar_similarities = players_extended \
#                      .sort_values(by=['neymar_similar', 'player_name'], ascending=True)['player_name'][1:6] \
#                      .values.tolist()

# should works, but fail with error
# Unable to coerce to DataFrame, shape must be (1, 43): given (11060, 36)
assert(len(neymar_similarities) == 5)
assert(isinstance(neymar_similarities, list))
for i in range(5):
    assert(isinstance(neymar_similarities[i], str))<jupyter_output><empty_output><jupyter_text>**Task 11 (1 point).** Calculate the number of home matches played by the **Borussia Dortmund** team in **Germany 1. Bundesliga** in season **2008/2009**<jupyter_code># this is right solution

league = league_data[['id', 'name']]
league.columns = ['league_id', 'league_name']
match_league = match_data.merge(league, on=['league_id'])

home_teams = teams_data[['team_api_id', 'team_long_name']] \
             .rename(columns={'team_api_id': 'home_team_api_id', 'team_long_name': 'home_team_name'})
away_teams = teams_data[['team_api_id', 'team_long_name']] \
             .rename(columns={'team_api_id': 'away_team_api_id', 'team_long_name': 'away_team_name'})

match_team_league = match_league \
                    .merge(home_teams, 'left', ['home_team_api_id']) \
                    .merge(away_teams, 'left', ['away_team_api_id'])

borussia_bundesliga_2008_2009_matches = match_team_league[(match_team_league['league_name'] == 'Germany 1. Bundesliga') &
                                        (match_team_league['season'] == '2008/2009') &
                                        (match_team_league['home_team_name'] == '<NAME>')].shape[0]
assert(isinstance(borussia_bundesliga_2008_2009_matches, int))<jupyter_output><empty_output><jupyter_text>**Task 12 (1 point).** Find a team having the most matches (both home and away!) in the **Germany 1. Bundesliga** in **2008/2009** season. Return number of matches.<jupyter_code># this is right solution

match_team_target_league_season = match_team_league[(match_team_league['league_name'] == 'Germany 1. Bundesliga') &
                                  (match_team_league['season'] == '2008/2009')]

team_most_matches_bundesliga_2008_2009 = match_team_target_league_season.groupby('home_team_name') \
                                         .size() \
                                         .add(match_team_target_league_season.groupby('away_team_name').size()) \
                                         .sort_values(ascending=False)[0].item()
team_most_matches_bundesliga_2008_2009
assert(isinstance(team_most_matches_bundesliga_2008_2009, int))<jupyter_output><empty_output><jupyter_text>**Task 13 (1 point).** Count total number of **Arsenal** matches (both home and away!) in the **2015/2016** season which they have won. **Note:** Winning a game means scoring **more** goals than an opponent.<jupyter_code># this is right solution

def team_won(row):
    if row['home_team_goal'] > row['away_team_goal']:
        return row['home_team_name']
    elif row['home_team_goal'] < row['home_team_goal']:
        return row['away_team_name']
    else:
        return None
    
match_team_league['team_won'] = match_team_league.apply(lambda x: team_won(x), axis=1)


arsenal_won_matches_2015_2016 = \
                                match_team_league[(match_team_league['team_won'] == 'Arsenal') &
                                (match_team_league['season'] == '2015/2016')].shape[0]
assert(isinstance(arsenal_won_matches_2015_2016, int))<jupyter_output><empty_output><jupyter_text>**Task 14 (2 points).** Find a team with the highest win rate in the **2015/2016** season. Win rate means won matches / all matches. If there are several teams with the highest win rate return the first by name in lexical order<jupyter_code>team_highest_winrate_2015_2016 = ''
assert(isinstance(team_highest_winrate_2015_2016, str))<jupyter_output><empty_output><jupyter_text>**Task 15 (2 points).** Determine the team with the maximum days' gap between matches in **England Premier League 2010/2011 season**. Return number of days in that gap. **Note**: a *gap* means the number of days between two consecutive matches of the same team.<jupyter_code>highest_gap_england_2010_2011 = 0
assert(isinstance(highest_gap_england_2010_2011, int))<jupyter_output><empty_output><jupyter_text>Warning! Do not change anything in the area below<jupyter_code>with open('student_answers.txt', 'w') as file:
    file.write(f"{players_180_190}\n")
    file.write(f"{players_1980}\n")
    file.write(f"{highest_players}\n")
    file.write(f"{years_born_players}\n")
    file.write(f"{round(adriano_mean, 3)} {round(adriano_std, 3)}\n")
    file.write(f"{dow_with_min_players_born}\n")
    file.write(f"{league_most_matches}\n")
    file.write(f"{max_matches_player}\n")
    file.write(f"{';'.join(['%s,%s' % tup for tup in top_correlated_features])};\n")
    file.write(f"{neymar_similarities}\n")
    file.write(f"{borussia_bundesliga_2008_2009_matches}\n")
    file.write(f"{team_most_matches_bundesliga_2008_2009}\n")
    file.write(f"{arsenal_won_matches_2015_2016}\n")
    file.write(f"{team_highest_winrate_2015_2016}\n")
    file.write(f"{highest_gap_england_2010_2011}\n")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1441994351509885, 'fraction_numerical': 0.04149467738431458, 'mean_word_length': 5.413444792755137, 'pattern_counts': {'":': 0, '<': 87, '<?xml version=': 0, '>': 87, 'https://': 6, 'lorem ipsum': 0, 'www.': 2, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '506539', 'license': 'MIT', 'n_tokens_mistral': 6661, 'n_tokens_neox': 5906, 'n_words': 1409, 'path': 'Pandas_data_manipulations_tasks.ipynb', 'repo_name': 'vvaxcoder/ML_RS_school', 'size': '18423'}