data_merge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from functools import reduce"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_columns(df):\n",
    "    df.columns = [col.replace(\"header_\", \"\") if \"header_\" in col else col for col in df.columns ]\n",
    "    if \"matches\" in list(df.columns):\n",
    "        del df[\"matches\"]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "big5_std = clean_columns(pd.read_csv(\"./data/big5_standard_stats.csv\"))\n",
    "big5_def = clean_columns(pd.read_csv(\"./data/big5_defense_stats.csv\"))\n",
    "big5_att = clean_columns(pd.read_csv(\"./data/big5_gca_stats.csv\"))\n",
    "big5_misc = clean_columns(pd.read_csv(\"./data/big5_misc_stats.csv\"))\n",
    "big5_pass = clean_columns(pd.read_csv(\"./data/big5_passing_stats.csv\"))\n",
    "big5_play = clean_columns(pd.read_csv(\"./data/big5_playing_time.csv\"))\n",
    "big5_poss = clean_columns(pd.read_csv(\"./data/big5_possesion_stats.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "stats_df = [big5_std, big5_def, big5_att, big5_misc, big5_pass, big5_play, big5_poss]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['player', 'nationality', 'position', 'squad', 'comp_level', 'age',\n",
       "       'birth_year', 'minutes_90s', 'sca_sca', 'sca_sca_per90',\n",
       "       'sca_types_sca_passes_live', 'sca_types_sca_passes_dead',\n",
       "       'sca_types_sca_dribbles', 'sca_types_sca_shots', 'sca_types_sca_fouled',\n",
       "       'gca_gca', 'gca_gca_per90', 'gca_types_gca_passes_live',\n",
       "       'gca_types_gca_passes_dead', 'gca_types_gca_dribbles',\n",
       "       'gca_types_gca_shots', 'gca_types_gca_fouled', 'gca_types_gca_og_for'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big5_att.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged = reduce(lambda  left,right: pd.merge(left,right,on=['player', 'nationality', 'position', 'squad', 'comp_level', 'age', 'birth_year'],\n",
    "                                            how='inner'), stats_df).fillna(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2712, 152)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_cols = list(set([col for col in df_merged.columns if (col.endswith(\"_y\"))]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "rename_cols = list(set([col for col in df_merged.columns if (col.endswith(\"_x\"))]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def modifyCols(df, drop_cols, rename_cols):\n",
    "    df = df.drop(drop_cols, axis=1)\n",
    "    rename = [col.split(\"_x\")[0] for col in rename_cols]\n",
    "    rename = dict(zip(rename_cols, rename))\n",
    "    df = df.rename(columns = rename)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_modify = modifyCols(df_merged, drop_cols, rename_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2712, 145)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_modify.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename Positions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['DF', 'FW,MF', 'MF', 'FW', 'DF,MF', 'GK', 'MF,FW', 'FW,DF',\n",
       "       'MF,DF', 'DF,FW'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_modify.position.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_modify.position = df_modify.position.replace({\"FW,MF\": \"MF,FW\", \"FW,DF\":\"DF,FW\", \"MF,DF\":\"DF,MF\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['DF', 'MF,FW', 'MF', 'FW', 'DF,MF', 'GK', 'DF,FW'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_modify.position.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean up Nationality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_modify.nationality = df_modify.nationality.apply(lambda x: x.split(\" \")[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean up comp_level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_modify.comp_level = df_modify.comp_level.apply(lambda x: ' '.join(x.split(\" \")[1:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.dtype('O')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['player', 'nationality', 'position', 'squad', 'comp_level',\n",
       "       'playing_minutes', 'per90_matches', 'xg_team_success_matches'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_modify.columns[df_modify.dtypes.apply(lambda x: x == np.dtype('O'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modify data types of rest of the column to a floating point"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "non_numeric_cols = ['player', 'position', 'nationality', 'squad', 'comp_level']\n",
    "\n",
    "for col in df_modify.columns:\n",
    "    if col not in non_numeric_cols:\n",
    "        df_modify[col] = df_modify[col].apply(lambda x: float(x.replace(',', '')) if type(x)==\"str\" else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['player',\n",
       " 'nationality',\n",
       " 'position',\n",
       " 'squad',\n",
       " 'comp_level',\n",
       " 'age',\n",
       " 'birth_year',\n",
       " 'games',\n",
       " 'playing_games_starts',\n",
       " 'playing_minutes',\n",
       " 'playing_goals',\n",
       " 'performance_assists',\n",
       " 'performance_pens_made',\n",
       " 'performance_pens_att',\n",
       " 'performance_cards_yellow',\n",
       " 'performance_cards_red',\n",
       " 'performance_goals_per90',\n",
       " 'per90_assists_per90',\n",
       " 'per90_goals_assists_per90',\n",
       " 'per90_goals_pens_per90',\n",
       " 'per90_goals_assists_pens_per90',\n",
       " 'per90_xg',\n",
       " 'expected_npxg',\n",
       " 'expected_xa',\n",
       " 'expected_xg_per90',\n",
       " 'per90_xa_per90',\n",
       " 'per90_xg_xa_per90',\n",
       " 'per90_npxg_per90',\n",
       " 'per90_npxg_xa_per90',\n",
       " 'per90_matches',\n",
       " 'minutes_90s',\n",
       " 'tackles_tackles',\n",
       " 'tackles_tackles_won',\n",
       " 'tackles_tackles_def_3rd',\n",
       " 'tackles_tackles_mid_3rd',\n",
       " 'tackles_tackles_att_3rd',\n",
       " 'dribbles_def_dribble_tackles',\n",
       " 'dribbles_def_dribbles_vs',\n",
       " 'dribbles_def_dribble_tackles_pct',\n",
       " 'dribbles_def_dribbled_past',\n",
       " 'pressures_pressures',\n",
       " 'pressures_pressure_regains',\n",
       " 'pressures_pressure_regain_pct',\n",
       " 'pressures_pressures_def_3rd',\n",
       " 'pressures_pressures_mid_3rd',\n",
       " 'pressures_pressures_att_3rd',\n",
       " 'blocks_blocks',\n",
       " 'blocks_blocked_shots',\n",
       " 'blocks_blocked_shots_saves',\n",
       " 'blocks_blocked_passes',\n",
       " 'interceptions',\n",
       " 'clearances',\n",
       " 'errors',\n",
       " 'sca_sca',\n",
       " 'sca_sca_per90',\n",
       " 'sca_types_sca_passes_live',\n",
       " 'sca_types_sca_passes_dead',\n",
       " 'sca_types_sca_dribbles',\n",
       " 'sca_types_sca_shots',\n",
       " 'sca_types_sca_fouled',\n",
       " 'gca_gca',\n",
       " 'gca_gca_per90',\n",
       " 'gca_types_gca_passes_live',\n",
       " 'gca_types_gca_passes_dead',\n",
       " 'gca_types_gca_dribbles',\n",
       " 'gca_types_gca_shots',\n",
       " 'gca_types_gca_fouled',\n",
       " 'gca_types_gca_og_for',\n",
       " 'minutes_90s',\n",
       " 'performance_cards_yellow_red',\n",
       " 'performance_fouls',\n",
       " 'performance_fouled',\n",
       " 'performance_offsides',\n",
       " 'performance_crosses',\n",
       " 'performance_interceptions',\n",
       " 'performance_tackles_won',\n",
       " 'performance_pens_won',\n",
       " 'performance_pens_conceded',\n",
       " 'performance_own_goals',\n",
       " 'performance_ball_recoveries',\n",
       " 'aerials_aerials_won',\n",
       " 'aerials_aerials_lost',\n",
       " 'aerials_aerials_won_pct',\n",
       " 'passes_total_passes_completed',\n",
       " 'passes_total_passes',\n",
       " 'passes_total_passes_pct',\n",
       " 'passes_total_passes_total_distance',\n",
       " 'passes_total_passes_progressive_distance',\n",
       " 'passes_short_passes_completed_short',\n",
       " 'passes_short_passes_short',\n",
       " 'passes_short_passes_pct_short',\n",
       " 'passes_medium_passes_completed_medium',\n",
       " 'passes_medium_passes_medium',\n",
       " 'passes_medium_passes_pct_medium',\n",
       " 'passes_long_passes_completed_long',\n",
       " 'passes_long_passes_long',\n",
       " 'passes_long_passes_pct_long',\n",
       " 'assists',\n",
       " 'xa',\n",
       " 'xa_net',\n",
       " 'assisted_shots',\n",
       " 'passes_into_final_third',\n",
       " 'passes_into_penalty_area',\n",
       " 'crosses_into_penalty_area',\n",
       " 'progressive_passes',\n",
       " 'playing_minutes_per_game',\n",
       " 'playing_minutes_pct',\n",
       " 'playing_minutes_90s',\n",
       " 'starts_minutes_per_start',\n",
       " 'starts_games_subs',\n",
       " 'subs_minutes_per_sub',\n",
       " 'subs_unused_subs',\n",
       " 'subs_points_per_match',\n",
       " 'team_success_on_goals_for',\n",
       " 'team_success_on_goals_against',\n",
       " 'team_success_plus_minus',\n",
       " 'team_success_plus_minus_per90',\n",
       " 'team_success_plus_minus_wowy',\n",
       " 'team_success_on_xg_for',\n",
       " 'xg_team_success_on_xg_against',\n",
       " 'xg_team_success_xg_plus_minus',\n",
       " 'xg_team_success_xg_plus_minus_per90',\n",
       " 'xg_team_success_xg_plus_minus_wowy',\n",
       " 'xg_team_success_matches',\n",
       " 'minutes_90s',\n",
       " 'touches_touches',\n",
       " 'touches_touches_def_pen_area',\n",
       " 'touches_touches_def_3rd',\n",
       " 'touches_touches_mid_3rd',\n",
       " 'touches_touches_att_3rd',\n",
       " 'touches_touches_att_pen_area',\n",
       " 'touches_touches_live_ball',\n",
       " 'dribbles_dribbles_completed',\n",
       " 'dribbles_dribbles',\n",
       " 'dribbles_dribbles_completed_pct',\n",
       " 'dribbles_players_dribbled_past',\n",
       " 'dribbles_nutmegs',\n",
       " 'carries_carries',\n",
       " 'carries_carry_distance',\n",
       " 'carries_carry_progressive_distance',\n",
       " 'receiving_pass_targets',\n",
       " 'receiving_passes_received',\n",
       " 'receiving_passes_received_pct',\n",
       " 'miscontrols',\n",
       " 'dispossessed']"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(df_modify.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "minutes = df_modify[[\"minutes_90s\"]].iloc[:,1]\n",
    "del df_modify[\"minutes_90s\"]\n",
    "df_modify[\"minutes_90\"] = minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add Metadata about each column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_modify.to_csv(\"./data/big5_full_stats.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}