Prediction/Scraping and Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import all the necessary libraries\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import datetime as dt\n",
    "import itertools\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Read data from the CSV into a dataframe\n",
    "\n",
    "loc = \"E:/Data Science/Football Project/Datasets/\"\n",
    "\n",
    "raw_data_1 = pd.read_csv(loc + '2000-01.csv')\n",
    "raw_data_2 = pd.read_csv(loc + '2001-02.csv')\n",
    "raw_data_3 = pd.read_csv(loc + '2002-03.csv')\n",
    "raw_data_4 = pd.read_csv(loc + '2003-04.csv')\n",
    "raw_data_5 = pd.read_csv(loc + '2004-05.csv')\n",
    "raw_data_6 = pd.read_csv(loc + '2005-06.csv')\n",
    "raw_data_7 = pd.read_csv(loc + '2006-07.csv')\n",
    "raw_data_8 = pd.read_csv(loc + '2007-08.csv')\n",
    "raw_data_9 = pd.read_csv(loc + '2008-09.csv')\n",
    "raw_data_10 = pd.read_csv(loc + '2009-10.csv')\n",
    "raw_data_11 = pd.read_csv(loc + '2010-11.csv')\n",
    "raw_data_12 = pd.read_csv(loc + '2011-12.csv')\n",
    "raw_data_13 = pd.read_csv(loc + '2012-13.csv')\n",
    "raw_data_14 = pd.read_csv(loc + '2013-14.csv')\n",
    "raw_data_15 = pd.read_csv(loc + '2014-15.csv')\n",
    "raw_data_16 = pd.read_csv(loc + '2015-16.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Parse data as time \n",
    "\n",
    "def parse_date(date):\n",
    "    if date == '':\n",
    "        return None\n",
    "    else:\n",
    "        return dt.strptime(date, '%d/%m/%y').date()\n",
    "    \n",
    "\n",
    "def parse_date_other(date):\n",
    "    if date == '':\n",
    "        return None\n",
    "    else:\n",
    "        return dt.strptime(date, '%d/%m/%Y').date()\n",
    "\n",
    "raw_data_1.Date = raw_data_1.Date.apply(parse_date)    \n",
    "raw_data_2.Date = raw_data_2.Date.apply(parse_date)    \n",
    "raw_data_3.Date = raw_data_3.Date.apply(parse_date_other)         # The date format for this dataset is different  \n",
    "raw_data_4.Date = raw_data_4.Date.apply(parse_date)    \n",
    "raw_data_5.Date = raw_data_5.Date.apply(parse_date)    \n",
    "raw_data_6.Date = raw_data_6.Date.apply(parse_date)    \n",
    "raw_data_7.Date = raw_data_7.Date.apply(parse_date)    \n",
    "raw_data_8.Date = raw_data_8.Date.apply(parse_date)    \n",
    "raw_data_9.Date = raw_data_9.Date.apply(parse_date)    \n",
    "raw_data_10.Date = raw_data_10.Date.apply(parse_date)\n",
    "raw_data_11.Date = raw_data_11.Date.apply(parse_date)\n",
    "raw_data_12.Date = raw_data_12.Date.apply(parse_date)\n",
    "raw_data_13.Date = raw_data_13.Date.apply(parse_date)\n",
    "raw_data_14.Date = raw_data_14.Date.apply(parse_date)\n",
    "raw_data_15.Date = raw_data_15.Date.apply(parse_date)\n",
    "raw_data_16.Date = raw_data_16.Date.apply(parse_date)\n",
    "\n",
    "\n",
    "\n",
    "#Gets all the statistics related to gameplay\n",
    "                      \n",
    "columns_req = ['Date','HomeTeam','AwayTeam','FTHG','FTAG','FTR']\n",
    "\n",
    "playing_statistics_1 = raw_data_1[columns_req]                      \n",
    "playing_statistics_2 = raw_data_2[columns_req]\n",
    "playing_statistics_3 = raw_data_3[columns_req]\n",
    "playing_statistics_4 = raw_data_4[columns_req]\n",
    "playing_statistics_5 = raw_data_5[columns_req]\n",
    "playing_statistics_6 = raw_data_6[columns_req]\n",
    "playing_statistics_7 = raw_data_7[columns_req]\n",
    "playing_statistics_8 = raw_data_8[columns_req]\n",
    "playing_statistics_9 = raw_data_9[columns_req]\n",
    "playing_statistics_10 = raw_data_10[columns_req]\n",
    "playing_statistics_11 = raw_data_11[columns_req]   \n",
    "playing_statistics_12 = raw_data_12[columns_req]\n",
    "playing_statistics_13 = raw_data_13[columns_req]\n",
    "playing_statistics_14 = raw_data_14[columns_req]\n",
    "playing_statistics_15 = raw_data_15[columns_req]\n",
    "playing_statistics_16 = raw_data_16[columns_req]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "** GOALS SCORED AND CONCEDED AT THE END OF MATCHWEEK, ARRANGED BY TEAMS AND MATCHWEEK  **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:68: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:69: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:70: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:71: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "# Gets the goals scored agg arranged by teams and matchweek\n",
    "def get_goals_scored(playing_stat):\n",
    "    # Create a dictionary with team names as keys\n",
    "    teams = {}\n",
    "    for i in playing_stat.groupby('HomeTeam').mean().T.columns:\n",
    "        teams[i] = []\n",
    "    \n",
    "    # the value corresponding to keys is a list containing the match location.\n",
    "    for i in range(len(playing_stat)):\n",
    "        HTGS = playing_stat.iloc[i]['FTHG']\n",
    "        ATGS = playing_stat.iloc[i]['FTAG']\n",
    "        teams[playing_stat.iloc[i].HomeTeam].append(HTGS)\n",
    "        teams[playing_stat.iloc[i].AwayTeam].append(ATGS)\n",
    "    \n",
    "    # Create a dataframe for goals scored where rows are teams and cols are matchweek.\n",
    "    GoalsScored = pd.DataFrame(data=teams, index = [i for i in range(1,39)]).T\n",
    "    GoalsScored[0] = 0\n",
    "    # Aggregate to get uptil that point\n",
    "    for i in range(2,39):\n",
    "        GoalsScored[i] = GoalsScored[i] + GoalsScored[i-1]\n",
    "    return GoalsScored\n",
    "\n",
    "\n",
    "\n",
    "# Gets the goals conceded agg arranged by teams and matchweek\n",
    "def get_goals_conceded(playing_stat):\n",
    "    # Create a dictionary with team names as keys\n",
    "    teams = {}\n",
    "    for i in playing_stat.groupby('HomeTeam').mean().T.columns:\n",
    "        teams[i] = []\n",
    "    \n",
    "    # the value corresponding to keys is a list containing the match location.\n",
    "    for i in range(len(playing_stat)):\n",
    "        ATGC = playing_stat.iloc[i]['FTHG']\n",
    "        HTGC = playing_stat.iloc[i]['FTAG']\n",
    "        teams[playing_stat.iloc[i].HomeTeam].append(HTGC)\n",
    "        teams[playing_stat.iloc[i].AwayTeam].append(ATGC)\n",
    "    \n",
    "    # Create a dataframe for goals scored where rows are teams and cols are matchweek.\n",
    "    GoalsConceded = pd.DataFrame(data=teams, index = [i for i in range(1,39)]).T\n",
    "    GoalsConceded[0] = 0\n",
    "    # Aggregate to get uptil that point\n",
    "    for i in range(2,39):\n",
    "        GoalsConceded[i] = GoalsConceded[i] + GoalsConceded[i-1]\n",
    "    return GoalsConceded\n",
    "\n",
    "def get_gss(playing_stat):\n",
    "    GC = get_goals_conceded(playing_stat)\n",
    "    GS = get_goals_scored(playing_stat)\n",
    "   \n",
    "    j = 0\n",
    "    HTGS = []\n",
    "    ATGS = []\n",
    "    HTGC = []\n",
    "    ATGC = []\n",
    "\n",
    "    for i in range(380):\n",
    "        ht = playing_stat.iloc[i].HomeTeam\n",
    "        at = playing_stat.iloc[i].AwayTeam\n",
    "        HTGS.append(GS.loc[ht][j])\n",
    "        ATGS.append(GS.loc[at][j])\n",
    "        HTGC.append(GC.loc[ht][j])\n",
    "        ATGC.append(GC.loc[at][j])\n",
    "        \n",
    "        if ((i + 1)% 10) == 0:\n",
    "            j = j + 1\n",
    "        \n",
    "    playing_stat['HTGS'] = HTGS\n",
    "    playing_stat['ATGS'] = ATGS\n",
    "    playing_stat['HTGC'] = HTGC\n",
    "    playing_stat['ATGC'] = ATGC\n",
    "    \n",
    "    return playing_stat\n",
    "\n",
    "\n",
    "# Apply to each dataset\n",
    "playing_statistics_1 = get_gss(playing_statistics_1)\n",
    "playing_statistics_2 = get_gss(playing_statistics_2)\n",
    "playing_statistics_3 = get_gss(playing_statistics_3)\n",
    "playing_statistics_4 = get_gss(playing_statistics_4)\n",
    "playing_statistics_5 = get_gss(playing_statistics_5)\n",
    "playing_statistics_6 = get_gss(playing_statistics_6)\n",
    "playing_statistics_7 = get_gss(playing_statistics_7)\n",
    "playing_statistics_8 = get_gss(playing_statistics_8)\n",
    "playing_statistics_9 = get_gss(playing_statistics_9)\n",
    "playing_statistics_10 = get_gss(playing_statistics_10)\n",
    "playing_statistics_11 = get_gss(playing_statistics_11)\n",
    "playing_statistics_12 = get_gss(playing_statistics_12)\n",
    "playing_statistics_13 = get_gss(playing_statistics_13)\n",
    "playing_statistics_14 = get_gss(playing_statistics_14)\n",
    "playing_statistics_15 = get_gss(playing_statistics_15)\n",
    "playing_statistics_16 = get_gss(playing_statistics_16)\n"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "GET RESPECTIVE POINTS:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:54: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "def get_points(result):\n",
    "    if result == 'W':\n",
    "        return 3\n",
    "    elif result == 'D':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "\n",
    "def get_cuml_points(matchres):\n",
    "    matchres_points = matchres.applymap(get_points)\n",
    "    for i in range(2,39):\n",
    "        matchres_points[i] = matchres_points[i] + matchres_points[i-1]\n",
    "        \n",
    "    matchres_points.insert(column =0, loc = 0, value = [0*i for i in range(20)])\n",
    "    return matchres_points\n",
    "\n",
    "\n",
    "def get_matchres(playing_stat):\n",
    "    # Create a dictionary with team names as keys\n",
    "    teams = {}\n",
    "    for i in playing_stat.groupby('HomeTeam').mean().T.columns:\n",
    "        teams[i] = []\n",
    "\n",
    "    # the value corresponding to keys is a list containing the match result\n",
    "    for i in range(len(playing_stat)):\n",
    "        if playing_stat.iloc[i].FTR == 'H':\n",
    "            teams[playing_stat.iloc[i].HomeTeam].append('W')\n",
    "            teams[playing_stat.iloc[i].AwayTeam].append('L')\n",
    "        elif playing_stat.iloc[i].FTR == 'A':\n",
    "            teams[playing_stat.iloc[i].AwayTeam].append('W')\n",
    "            teams[playing_stat.iloc[i].HomeTeam].append('L')\n",
    "        else:\n",
    "            teams[playing_stat.iloc[i].AwayTeam].append('D')\n",
    "            teams[playing_stat.iloc[i].HomeTeam].append('D')\n",
    "            \n",
    "    return pd.DataFrame(data=teams, index = [i for i in range(1,39)]).T\n",
    "\n",
    "def get_agg_points(playing_stat):\n",
    "    matchres = get_matchres(playing_stat)\n",
    "    cum_pts = get_cuml_points(matchres)\n",
    "    HTP = []\n",
    "    ATP = []\n",
    "    j = 0\n",
    "    for i in range(380):\n",
    "        ht = playing_stat.iloc[i].HomeTeam\n",
    "        at = playing_stat.iloc[i].AwayTeam\n",
    "        HTP.append(cum_pts.loc[ht][j])\n",
    "        ATP.append(cum_pts.loc[at][j])\n",
    "\n",
    "        if ((i + 1)% 10) == 0:\n",
    "            j = j + 1\n",
    "            \n",
    "    playing_stat['HTP'] = HTP\n",
    "    playing_stat['ATP'] = ATP\n",
    "    return playing_stat\n",
    "    \n",
    "# Apply to each dataset\n",
    "playing_statistics_1 = get_agg_points(playing_statistics_1)\n",
    "playing_statistics_2 = get_agg_points(playing_statistics_2)\n",
    "playing_statistics_3 = get_agg_points(playing_statistics_3)\n",
    "playing_statistics_4 = get_agg_points(playing_statistics_4)\n",
    "playing_statistics_5 = get_agg_points(playing_statistics_5)\n",
    "playing_statistics_6 = get_agg_points(playing_statistics_6)\n",
    "playing_statistics_7 = get_agg_points(playing_statistics_7)\n",
    "playing_statistics_8 = get_agg_points(playing_statistics_8)\n",
    "playing_statistics_9 = get_agg_points(playing_statistics_9)\n",
    "playing_statistics_10 = get_agg_points(playing_statistics_10)\n",
    "playing_statistics_11 = get_agg_points(playing_statistics_11)\n",
    "playing_statistics_12 = get_agg_points(playing_statistics_12)\n",
    "playing_statistics_13 = get_agg_points(playing_statistics_13)\n",
    "playing_statistics_14 = get_agg_points(playing_statistics_14)\n",
    "playing_statistics_15 = get_agg_points(playing_statistics_15)\n",
    "playing_statistics_16 = get_agg_points(playing_statistics_16)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "GET TEAM FORM:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:31: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "E:\\Installed_Programs\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:32: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "def get_form(playing_stat,num):\n",
    "    form = get_matchres(playing_stat)\n",
    "    form_final = form.copy()\n",
    "    for i in range(num,39):\n",
    "        form_final[i] = ''\n",
    "        j = 0\n",
    "        while j < num:\n",
    "            form_final[i] += form[i-j]\n",
    "            j += 1           \n",
    "    return form_final\n",
    "\n",
    "def add_form(playing_stat,num):\n",
    "    form = get_form(playing_stat,num)\n",
    "    h = ['M' for i in range(num * 10)]  # since form is not available for n MW (n*10)\n",
    "    a = ['M' for i in range(num * 10)]\n",
    "    \n",
    "    j = num\n",
    "    for i in range((num*10),380):\n",
    "        ht = playing_stat.iloc[i].HomeTeam\n",
    "        at = playing_stat.iloc[i].AwayTeam\n",
    "        \n",
    "        past = form.loc[ht][j]               # get past n results\n",
    "        h.append(past[num-1])                    # 0 index is most recent\n",
    "        \n",
    "        past = form.loc[at][j]               # get past n results.\n",
    "        a.append(past[num-1])                   # 0 index is most recent\n",
    "        \n",
    "        if ((i + 1)% 10) == 0:\n",
    "            j = j + 1\n",
    "\n",
    "    playing_stat['HM' + str(num)] = h                 \n",
    "    playing_stat['AM' + str(num)] = a\n",
    "\n",
    "    \n",
    "    return playing_stat\n",
    "\n",
    "\n",
    "def add_form_df(playing_statistics):\n",
    "    playing_statistics = add_form(playing_statistics,1)\n",
    "    playing_statistics = add_form(playing_statistics,2)\n",
    "    playing_statistics = add_form(playing_statistics,3)\n",
    "    playing_statistics = add_form(playing_statistics,4)\n",
    "    playing_statistics = add_form(playing_statistics,5)\n",
    "    return playing_statistics    \n",
    "    \n",
    "# Make changes to df\n",
    "playing_statistics_1 = add_form_df(playing_statistics_1)\n",
    "playing_statistics_2 = add_form_df(playing_statistics_2)\n",
    "playing_statistics_3 = add_form_df(playing_statistics_3)\n",
    "playing_statistics_4 = add_form_df(playing_statistics_4)\n",
    "playing_statistics_5 = add_form_df(playing_statistics_5)\n",
    "playing_statistics_6 = add_form_df(playing_statistics_6)\n",
    "playing_statistics_7 = add_form_df(playing_statistics_7)\n",
    "playing_statistics_8 = add_form_df(playing_statistics_8)\n",
    "playing_statistics_9 = add_form_df(playing_statistics_9)\n",
    "playing_statistics_10 = add_form_df(playing_statistics_10)\n",
    "playing_statistics_11 = add_form_df(playing_statistics_11)\n",
    "playing_statistics_12 = add_form_df(playing_statistics_12)\n",
    "playing_statistics_13 = add_form_df(playing_statistics_13)\n",
    "playing_statistics_14 = add_form_df(playing_statistics_14)\n",
    "playing_statistics_15 = add_form_df(playing_statistics_15)    \n",
    "playing_statistics_16 = add_form_df(playing_statistics_16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Rearranging columns\n",
    "cols = ['Date', 'HomeTeam', 'AwayTeam', 'FTHG', 'FTAG', 'FTR', 'HTGS', 'ATGS', 'HTGC', 'ATGC', 'HTP', 'ATP', 'HM1', 'HM2', 'HM3',\n",
    "        'HM4', 'HM5', 'AM1', 'AM2', 'AM3', 'AM4', 'AM5' ]\n",
    "\n",
    "playing_statistics_1 = playing_statistics_1[cols]\n",
    "playing_statistics_2 = playing_statistics_2[cols]\n",
    "playing_statistics_3 = playing_statistics_3[cols]\n",
    "playing_statistics_4 = playing_statistics_4[cols]\n",
    "playing_statistics_5 = playing_statistics_5[cols]\n",
    "playing_statistics_6 = playing_statistics_6[cols]\n",
    "playing_statistics_7 = playing_statistics_7[cols]\n",
    "playing_statistics_8 = playing_statistics_8[cols]\n",
    "playing_statistics_9 = playing_statistics_9[cols]\n",
    "playing_statistics_10 = playing_statistics_10[cols]\n",
    "playing_statistics_11 = playing_statistics_11[cols]\n",
    "playing_statistics_12 = playing_statistics_12[cols]\n",
    "playing_statistics_13 = playing_statistics_13[cols]\n",
    "playing_statistics_14 = playing_statistics_14[cols]\n",
    "playing_statistics_15 = playing_statistics_15[cols]\n",
    "playing_statistics_16 = playing_statistics_16[cols]\n"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Get Last Year's Position as also an independent variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "Standings = pd.read_csv(loc + \"EPLStandings.csv\")\n",
    "Standings.set_index(['Team'], inplace=True)\n",
    "Standings = Standings.fillna(18)\n",
    "\n",
    "def get_last(playing_stat, Standings, year):\n",
    "    HomeTeamLP = []\n",
    "    AwayTeamLP = []\n",
    "    for i in range(380):\n",
    "        ht = playing_stat.iloc[i].HomeTeam\n",
    "        at = playing_stat.iloc[i].AwayTeam\n",
    "        HomeTeamLP.append(Standings.loc[ht][year])\n",
    "        AwayTeamLP.append(Standings.loc[at][year])\n",
    "    playing_stat['HomeTeamLP'] = HomeTeamLP\n",
    "    playing_stat['AwayTeamLP'] = AwayTeamLP\n",
    "    return playing_stat\n",
    "\n",
    "playing_statistics_1 = get_last(playing_statistics_1, Standings, 0)\n",
    "playing_statistics_2 = get_last(playing_statistics_2, Standings, 1)\n",
    "playing_statistics_3 = get_last(playing_statistics_3, Standings, 2)\n",
    "playing_statistics_4 = get_last(playing_statistics_4, Standings, 3)\n",
    "playing_statistics_5 = get_last(playing_statistics_5, Standings, 4)\n",
    "playing_statistics_6 = get_last(playing_statistics_6, Standings, 5)\n",
    "playing_statistics_7 = get_last(playing_statistics_7, Standings, 6)\n",
    "playing_statistics_8 = get_last(playing_statistics_8, Standings, 7)\n",
    "playing_statistics_9 = get_last(playing_statistics_9, Standings, 8)\n",
    "playing_statistics_10 = get_last(playing_statistics_10, Standings, 9)\n",
    "playing_statistics_11 = get_last(playing_statistics_11, Standings, 10)\n",
    "playing_statistics_12 = get_last(playing_statistics_12, Standings, 11)\n",
    "playing_statistics_13 = get_last(playing_statistics_13, Standings, 12)\n",
    "playing_statistics_14 = get_last(playing_statistics_14, Standings, 13)\n",
    "playing_statistics_15 = get_last(playing_statistics_15, Standings, 14)\n",
    "playing_statistics_16 = get_last(playing_statistics_16, Standings, 15)\n"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Get MatchWeek:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_mw(playing_stat):\n",
    "    j = 1\n",
    "    MatchWeek = []\n",
    "    for i in range(380):\n",
    "        MatchWeek.append(j)\n",
    "        if ((i + 1)% 10) == 0:\n",
    "            j = j + 1\n",
    "    playing_stat['MW'] = MatchWeek\n",
    "    return playing_stat\n",
    "\n",
    "playing_statistics_1 = get_mw(playing_statistics_1)\n",
    "playing_statistics_2 = get_mw(playing_statistics_2)\n",
    "playing_statistics_3 = get_mw(playing_statistics_3)\n",
    "playing_statistics_4 = get_mw(playing_statistics_4)\n",
    "playing_statistics_5 = get_mw(playing_statistics_5)\n",
    "playing_statistics_6 = get_mw(playing_statistics_6)\n",
    "playing_statistics_7 = get_mw(playing_statistics_7)\n",
    "playing_statistics_8 = get_mw(playing_statistics_8)\n",
    "playing_statistics_9 = get_mw(playing_statistics_9)\n",
    "playing_statistics_10 = get_mw(playing_statistics_10)\n",
    "playing_statistics_11 = get_mw(playing_statistics_11)\n",
    "playing_statistics_12 = get_mw(playing_statistics_12)\n",
    "playing_statistics_13 = get_mw(playing_statistics_13)\n",
    "playing_statistics_14 = get_mw(playing_statistics_14)\n",
    "playing_statistics_15 = get_mw(playing_statistics_15)\n",
    "playing_statistics_16 = get_mw(playing_statistics_16)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "FINAL DATAFRAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'Date', u'HomeTeam', u'AwayTeam', u'FTHG', u'FTAG', u'FTR', u'HTGS',\n",
       "       u'ATGS', u'HTGC', u'ATGC', u'HTP', u'ATP', u'HM1', u'HM2', u'HM3',\n",
       "       u'HM4', u'HM5', u'AM1', u'AM2', u'AM3', u'AM4', u'AM5', u'HomeTeamLP',\n",
       "       u'AwayTeamLP', u'MW', u'HTFormPtsStr', u'ATFormPtsStr', u'HTFormPts',\n",
       "       u'ATFormPts', u'HTWinStreak3', u'HTWinStreak5', u'HTLossStreak3',\n",
       "       u'HTLossStreak5', u'ATWinStreak3', u'ATWinStreak5', u'ATLossStreak3',\n",
       "       u'ATLossStreak5'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playing_stat = pd.concat([playing_statistics_1,\n",
    "                          playing_statistics_2,\n",
    "                          playing_statistics_3,\n",
    "                          playing_statistics_4,\n",
    "                          playing_statistics_5,\n",
    "                          playing_statistics_6,\n",
    "                          playing_statistics_7,\n",
    "                          playing_statistics_8,\n",
    "                          playing_statistics_9,\n",
    "                          playing_statistics_10,\n",
    "                          playing_statistics_11,\n",
    "                          playing_statistics_12,\n",
    "                          playing_statistics_13,\n",
    "                          playing_statistics_14,\n",
    "                          playing_statistics_15,\n",
    "                          playing_statistics_16], ignore_index=True)\n",
    "\n",
    "\n",
    "# Gets the form points.\n",
    "def get_form_points(string):\n",
    "    sum = 0\n",
    "    for letter in string:\n",
    "        sum += get_points(letter)\n",
    "    return sum\n",
    "\n",
    "playing_stat['HTFormPtsStr'] = playing_stat['HM1'] + playing_stat['HM2'] + playing_stat['HM3'] + playing_stat['HM4'] + playing_stat['HM5']\n",
    "playing_stat['ATFormPtsStr'] = playing_stat['AM1'] + playing_stat['AM2'] + playing_stat['AM3'] + playing_stat['AM4'] + playing_stat['AM5']\n",
    "\n",
    "playing_stat['HTFormPts'] = playing_stat['HTFormPtsStr'].apply(get_form_points)\n",
    "playing_stat['ATFormPts'] = playing_stat['ATFormPtsStr'].apply(get_form_points)\n",
    "\n",
    "# Identify Win/Loss Streaks if any.\n",
    "def get_3game_ws(string):\n",
    "    if string[-3:] == 'WWW':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "def get_5game_ws(string):\n",
    "    if string == 'WWWWW':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "def get_3game_ls(string):\n",
    "    if string[-3:] == 'LLL':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "def get_5game_ls(string):\n",
    "    if string == 'LLLLL':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "playing_stat['HTWinStreak3'] = playing_stat['HTFormPtsStr'].apply(get_3game_ws)\n",
    "playing_stat['HTWinStreak5'] = playing_stat['HTFormPtsStr'].apply(get_5game_ws)\n",
    "playing_stat['HTLossStreak3'] = playing_stat['HTFormPtsStr'].apply(get_3game_ls)\n",
    "playing_stat['HTLossStreak5'] = playing_stat['HTFormPtsStr'].apply(get_5game_ls)\n",
    "\n",
    "playing_stat['ATWinStreak3'] = playing_stat['ATFormPtsStr'].apply(get_3game_ws)\n",
    "playing_stat['ATWinStreak5'] = playing_stat['ATFormPtsStr'].apply(get_5game_ws)\n",
    "playing_stat['ATLossStreak3'] = playing_stat['ATFormPtsStr'].apply(get_3game_ls)\n",
    "playing_stat['ATLossStreak5'] = playing_stat['ATFormPtsStr'].apply(get_5game_ls)\n",
    "\n",
    "playing_stat.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Get Goal Difference\n",
    "playing_stat['HTGD'] = playing_stat['HTGS'] - playing_stat['HTGC']\n",
    "playing_stat['ATGD'] = playing_stat['ATGS'] - playing_stat['ATGC']\n",
    "\n",
    "# Diff in points\n",
    "playing_stat['DiffPts'] = playing_stat['HTP'] - playing_stat['ATP']\n",
    "playing_stat['DiffFormPts'] = playing_stat['HTFormPts'] - playing_stat['ATFormPts']\n",
    "\n",
    "# Diff in last year positions\n",
    "playing_stat['DiffLP'] = playing_stat['HomeTeamLP'] - playing_stat['AwayTeamLP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Scale DiffPts , DiffFormPts, HTGD, ATGD by Matchweek.\n",
    "cols = ['HTGD','ATGD','DiffPts','DiffFormPts','HTP','ATP']\n",
    "playing_stat.MW = playing_stat.MW.astype(float)\n",
    "\n",
    "for col in cols:\n",
    "    playing_stat[col] = playing_stat[col] / playing_stat.MW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def only_hw(string):\n",
    "    if string == 'H':\n",
    "        return 'H'\n",
    "    else:\n",
    "        return 'NH'\n",
    "    \n",
    "playing_stat['FTR'] = playing_stat.FTR.apply(only_hw)\n",
    "\n",
    "# Testing set (2015-16 season)\n",
    "playing_stat_test = playing_stat[5700:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "playing_stat.to_csv(loc + \"final_dataset.csv\")\n",
    "playing_stat_test.to_csv(loc+\"test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}