Merge 2020-2021 Understat Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/EddyZulkifly/opt/anaconda3/lib/python3.8/site-packages/pandas/core/generic.py:6746: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._update_inplace(new_data)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "#read in 538 SPI data (needs to scheduled after BPL matchday is completed)\n",
    "teams = pd.read_csv(\"https://projects.fivethirtyeight.com/soccer-api/club/spi_global_rankings.csv\")\n",
    "#filtering on Barclays Premier League teams only\n",
    "select_league='Barclays Premier League'\n",
    "drop=['Fulham','West Bromwich Albion','Sheffield United']\n",
    "add=['Brentford','Watford','Norwich City']\n",
    "\n",
    "Base_BPL=teams[(teams.league==select_league)& ~teams.name.isin(drop)]\n",
    "Relegated=teams[teams.name.isin(drop)]\n",
    "Promoted=teams[teams.name.isin(add)]\n",
    "Promoted[\"league\"].replace({\"English League Championship\": \"Barclays Premier League\"}, inplace=True)\n",
    "New_BPL=Base_BPL.append(Relegated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>prev_rank</th>\n",
       "      <th>name</th>\n",
       "      <th>league</th>\n",
       "      <th>off</th>\n",
       "      <th>def</th>\n",
       "      <th>spi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Manchester City</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.79</td>\n",
       "      <td>0.25</td>\n",
       "      <td>92.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Chelsea</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.42</td>\n",
       "      <td>0.20</td>\n",
       "      <td>90.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.69</td>\n",
       "      <td>0.40</td>\n",
       "      <td>89.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.41</td>\n",
       "      <td>0.46</td>\n",
       "      <td>85.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.18</td>\n",
       "      <td>0.42</td>\n",
       "      <td>83.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>Brighton and Hove Albion</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.97</td>\n",
       "      <td>0.51</td>\n",
       "      <td>78.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>26</td>\n",
       "      <td>Leicester City</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.14</td>\n",
       "      <td>0.64</td>\n",
       "      <td>78.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>Tottenham Hotspur</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.31</td>\n",
       "      <td>0.76</td>\n",
       "      <td>78.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>West Ham United</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.18</td>\n",
       "      <td>0.76</td>\n",
       "      <td>76.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.13</td>\n",
       "      <td>0.78</td>\n",
       "      <td>74.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>43</td>\n",
       "      <td>43</td>\n",
       "      <td>Everton</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.86</td>\n",
       "      <td>0.70</td>\n",
       "      <td>72.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>44</td>\n",
       "      <td>Aston Villa</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>2.02</td>\n",
       "      <td>0.82</td>\n",
       "      <td>72.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>58</td>\n",
       "      <td>57</td>\n",
       "      <td>Wolverhampton</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.73</td>\n",
       "      <td>0.72</td>\n",
       "      <td>69.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "      <td>Newcastle</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.93</td>\n",
       "      <td>0.94</td>\n",
       "      <td>67.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>66</td>\n",
       "      <td>66</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.88</td>\n",
       "      <td>0.93</td>\n",
       "      <td>67.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.85</td>\n",
       "      <td>0.92</td>\n",
       "      <td>66.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>96</td>\n",
       "      <td>Crystal Palace</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.69</td>\n",
       "      <td>1.00</td>\n",
       "      <td>61.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "      <td>Fulham</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.68</td>\n",
       "      <td>0.74</td>\n",
       "      <td>68.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>111</td>\n",
       "      <td>109</td>\n",
       "      <td>West Bromwich Albion</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.78</td>\n",
       "      <td>1.16</td>\n",
       "      <td>59.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>144</td>\n",
       "      <td>140</td>\n",
       "      <td>Sheffield United</td>\n",
       "      <td>Barclays Premier League</td>\n",
       "      <td>1.44</td>\n",
       "      <td>0.99</td>\n",
       "      <td>56.30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     rank  prev_rank                      name                   league   off  \\\n",
       "0       1          1           Manchester City  Barclays Premier League  2.79   \n",
       "2       3          3                   Chelsea  Barclays Premier League  2.42   \n",
       "4       5          5                 Liverpool  Barclays Premier League  2.69   \n",
       "7       8          8         Manchester United  Barclays Premier League  2.41   \n",
       "12     13         13                   Arsenal  Barclays Premier League  2.18   \n",
       "24     25         25  Brighton and Hove Albion  Barclays Premier League  1.97   \n",
       "25     26         26            Leicester City  Barclays Premier League  2.14   \n",
       "26     27         27         Tottenham Hotspur  Barclays Premier League  2.31   \n",
       "33     34         34           West Ham United  Barclays Premier League  2.18   \n",
       "35     36         36              Leeds United  Barclays Premier League  2.13   \n",
       "42     43         43                   Everton  Barclays Premier League  1.86   \n",
       "43     44         44               Aston Villa  Barclays Premier League  2.02   \n",
       "57     58         57             Wolverhampton  Barclays Premier League  1.73   \n",
       "60     61         61                 Newcastle  Barclays Premier League  1.93   \n",
       "65     66         66               Southampton  Barclays Premier League  1.88   \n",
       "67     68         68                   Burnley  Barclays Premier League  1.85   \n",
       "95     96         96            Crystal Palace  Barclays Premier League  1.69   \n",
       "58     59         59                    Fulham  Barclays Premier League  1.68   \n",
       "110   111        109      West Bromwich Albion  Barclays Premier League  1.78   \n",
       "143   144        140          Sheffield United  Barclays Premier League  1.44   \n",
       "\n",
       "      def    spi  \n",
       "0    0.25  92.42  \n",
       "2    0.20  90.53  \n",
       "4    0.40  89.17  \n",
       "7    0.46  85.53  \n",
       "12   0.42  83.72  \n",
       "24   0.51  78.71  \n",
       "25   0.64  78.22  \n",
       "26   0.76  78.06  \n",
       "33   0.76  76.00  \n",
       "35   0.78  74.94  \n",
       "42   0.70  72.28  \n",
       "43   0.82  72.18  \n",
       "57   0.72  69.61  \n",
       "60   0.94  67.79  \n",
       "65   0.93  67.01  \n",
       "67   0.92  66.79  \n",
       "95   1.00  61.55  \n",
       "58   0.74  68.01  \n",
       "110  1.16  59.72  \n",
       "143  0.99  56.30  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "New_BPL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12                     Arsenal\n",
       "43                 Aston_Villa\n",
       "24                    Brighton\n",
       "67                     Burnley\n",
       "2                      Chelsea\n",
       "95              Crystal_Palace\n",
       "42                     Everton\n",
       "58                      Fulham\n",
       "35                       Leeds\n",
       "25                   Leicester\n",
       "4                    Liverpool\n",
       "0              Manchester_City\n",
       "7            Manchester_United\n",
       "60            Newcastle_United\n",
       "143           Sheffield_United\n",
       "65                 Southampton\n",
       "26                   Tottenham\n",
       "110       West_Bromwich_Albion\n",
       "33                    West_Ham\n",
       "57     Wolverhampton_Wanderers\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#configure names to fit with understat csv names\n",
    "Name=New_BPL[\"name\"]\n",
    "Name.replace('\\s+', '_',regex=True,inplace=True)\n",
    "t1=Name.replace('Brighton_and_Hove_Albion','Brighton')\n",
    "t2=t1.replace('Leeds_United','Leeds')\n",
    "t3=t2.replace('Leicester_City','Leicester')\n",
    "t4=t3.replace('Newcastle','Newcastle_United')\n",
    "t5=t4.replace('Norwich_City','Norwich')\n",
    "t6=t5.replace('Tottenham_Hotspur','Tottenham')\n",
    "t7=t6.replace('West_Ham_United','West_Ham')\n",
    "t8=t7.replace('Wolverhampton','Wolverhampton_Wanderers')\n",
    "t9=t8.sort_values()\n",
    "t9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reading in data and merging files\n",
    "#define understat which reads csv\n",
    "understat = pd.read_csv(\"https://raw.githubusercontent.com/vaastav/Fantasy-Premier-League/master/data/2020-21/understat/understat_\"+t9.iloc[5]+\".csv\")\n",
    "#define merge which is the variable that stores all data\n",
    "merge=understat.drop(understat.index)\n",
    "#creates column for team\n",
    "merge['team']=\"\"\n",
    "#need to add a loop, within each loop, add team name for identification\n",
    "#merge into new dataframe, repeat till all data pulled from repo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>h_a</th>\n",
       "      <th>xG</th>\n",
       "      <th>xGA</th>\n",
       "      <th>npxG</th>\n",
       "      <th>npxGA</th>\n",
       "      <th>ppda</th>\n",
       "      <th>ppda_allowed</th>\n",
       "      <th>deep</th>\n",
       "      <th>deep_allowed</th>\n",
       "      <th>scored</th>\n",
       "      <th>...</th>\n",
       "      <th>xpts</th>\n",
       "      <th>result</th>\n",
       "      <th>date</th>\n",
       "      <th>wins</th>\n",
       "      <th>draws</th>\n",
       "      <th>loses</th>\n",
       "      <th>pts</th>\n",
       "      <th>npxGD</th>\n",
       "      <th>team</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>2.162870</td>\n",
       "      <td>0.126327</td>\n",
       "      <td>2.162870</td>\n",
       "      <td>0.126327</td>\n",
       "      <td>{'att': 307, 'def': 14}</td>\n",
       "      <td>{'att': 425, 'def': 13}</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>2.8937</td>\n",
       "      <td>w</td>\n",
       "      <td>2020-09-12 11:30:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.036543</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>h</td>\n",
       "      <td>1.329020</td>\n",
       "      <td>2.063770</td>\n",
       "      <td>1.329020</td>\n",
       "      <td>2.063770</td>\n",
       "      <td>{'att': 217, 'def': 17}</td>\n",
       "      <td>{'att': 420, 'def': 23}</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8155</td>\n",
       "      <td>w</td>\n",
       "      <td>2020-09-19 19:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.734750</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a</td>\n",
       "      <td>1.178170</td>\n",
       "      <td>2.738340</td>\n",
       "      <td>1.178170</td>\n",
       "      <td>2.738340</td>\n",
       "      <td>{'att': 502, 'def': 14}</td>\n",
       "      <td>{'att': 256, 'def': 29}</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3972</td>\n",
       "      <td>l</td>\n",
       "      <td>2020-09-28 19:15:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1.560170</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>h</td>\n",
       "      <td>0.667131</td>\n",
       "      <td>0.144994</td>\n",
       "      <td>0.667131</td>\n",
       "      <td>0.144994</td>\n",
       "      <td>{'att': 167, 'def': 12}</td>\n",
       "      <td>{'att': 445, 'def': 23}</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1.8925</td>\n",
       "      <td>w</td>\n",
       "      <td>2020-10-04 13:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.522137</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>0.838322</td>\n",
       "      <td>1.430230</td>\n",
       "      <td>0.838322</td>\n",
       "      <td>1.430230</td>\n",
       "      <td>{'att': 346, 'def': 17}</td>\n",
       "      <td>{'att': 311, 'def': 23}</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8436</td>\n",
       "      <td>l</td>\n",
       "      <td>2020-10-17 16:30:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-0.591908</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>a</td>\n",
       "      <td>0.844138</td>\n",
       "      <td>1.071330</td>\n",
       "      <td>0.844138</td>\n",
       "      <td>1.071330</td>\n",
       "      <td>{'att': 163, 'def': 20}</td>\n",
       "      <td>{'att': 224, 'def': 25}</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0918</td>\n",
       "      <td>d</td>\n",
       "      <td>2021-05-03 17:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.227192</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>h</td>\n",
       "      <td>1.119900</td>\n",
       "      <td>0.275710</td>\n",
       "      <td>1.119900</td>\n",
       "      <td>0.275710</td>\n",
       "      <td>{'att': 193, 'def': 21}</td>\n",
       "      <td>{'att': 269, 'def': 22}</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2.1303</td>\n",
       "      <td>w</td>\n",
       "      <td>2021-05-09 11:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.844190</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>a</td>\n",
       "      <td>1.092060</td>\n",
       "      <td>3.139470</td>\n",
       "      <td>1.092060</td>\n",
       "      <td>3.139470</td>\n",
       "      <td>{'att': 369, 'def': 20}</td>\n",
       "      <td>{'att': 208, 'def': 18}</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.2744</td>\n",
       "      <td>l</td>\n",
       "      <td>2021-05-16 13:05:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-2.047410</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>a</td>\n",
       "      <td>0.265526</td>\n",
       "      <td>0.670997</td>\n",
       "      <td>0.265526</td>\n",
       "      <td>0.670997</td>\n",
       "      <td>{'att': 204, 'def': 23}</td>\n",
       "      <td>{'att': 312, 'def': 27}</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8481</td>\n",
       "      <td>l</td>\n",
       "      <td>2021-05-19 17:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-0.405471</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>h</td>\n",
       "      <td>0.701256</td>\n",
       "      <td>2.052290</td>\n",
       "      <td>0.701256</td>\n",
       "      <td>1.291120</td>\n",
       "      <td>{'att': 264, 'def': 32}</td>\n",
       "      <td>{'att': 321, 'def': 11}</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.4026</td>\n",
       "      <td>l</td>\n",
       "      <td>2021-05-23 15:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-0.589864</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>20-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>760 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   h_a        xG       xGA      npxG     npxGA                     ppda  \\\n",
       "0    a  2.162870  0.126327  2.162870  0.126327  {'att': 307, 'def': 14}   \n",
       "1    h  1.329020  2.063770  1.329020  2.063770  {'att': 217, 'def': 17}   \n",
       "2    a  1.178170  2.738340  1.178170  2.738340  {'att': 502, 'def': 14}   \n",
       "3    h  0.667131  0.144994  0.667131  0.144994  {'att': 167, 'def': 12}   \n",
       "4    a  0.838322  1.430230  0.838322  1.430230  {'att': 346, 'def': 17}   \n",
       "..  ..       ...       ...       ...       ...                      ...   \n",
       "33   a  0.844138  1.071330  0.844138  1.071330  {'att': 163, 'def': 20}   \n",
       "34   h  1.119900  0.275710  1.119900  0.275710  {'att': 193, 'def': 21}   \n",
       "35   a  1.092060  3.139470  1.092060  3.139470  {'att': 369, 'def': 20}   \n",
       "36   a  0.265526  0.670997  0.265526  0.670997  {'att': 204, 'def': 23}   \n",
       "37   h  0.701256  2.052290  0.701256  1.291120  {'att': 264, 'def': 32}   \n",
       "\n",
       "               ppda_allowed  deep  deep_allowed  scored  ...    xpts  result  \\\n",
       "0   {'att': 425, 'def': 13}    11             0       3  ...  2.8937       w   \n",
       "1   {'att': 420, 'def': 23}    16             4       2  ...  0.8155       w   \n",
       "2   {'att': 256, 'def': 29}     3            11       1  ...  0.3972       l   \n",
       "3   {'att': 445, 'def': 23}     4             3       2  ...  1.8925       w   \n",
       "4   {'att': 311, 'def': 23}     3             7       0  ...  0.8436       l   \n",
       "..                      ...   ...           ...     ...  ...     ...     ...   \n",
       "33  {'att': 224, 'def': 25}     8             5       1  ...  1.0918       d   \n",
       "34  {'att': 269, 'def': 22}     9             9       2  ...  2.1303       w   \n",
       "35  {'att': 208, 'def': 18}     5             7       0  ...  0.2744       l   \n",
       "36  {'att': 312, 'def': 27}     1             4       0  ...  0.8481       l   \n",
       "37  {'att': 321, 'def': 11}     5             6       1  ...  0.4026       l   \n",
       "\n",
       "                   date wins  draws  loses  pts     npxGD  \\\n",
       "0   2020-09-12 11:30:00    1      0      0    3  2.036543   \n",
       "1   2020-09-19 19:00:00    1      0      0    3 -0.734750   \n",
       "2   2020-09-28 19:15:00    0      0      1    0 -1.560170   \n",
       "3   2020-10-04 13:00:00    1      0      0    3  0.522137   \n",
       "4   2020-10-17 16:30:00    0      0      1    0 -0.591908   \n",
       "..                  ...  ...    ...    ...  ...       ...   \n",
       "33  2021-05-03 17:00:00    0      1      0    1 -0.227192   \n",
       "34  2021-05-09 11:00:00    1      0      0    3  0.844190   \n",
       "35  2021-05-16 13:05:00    0      0      1    0 -2.047410   \n",
       "36  2021-05-19 17:00:00    0      0      1    0 -0.405471   \n",
       "37  2021-05-23 15:00:00    0      0      1    0 -0.589864   \n",
       "\n",
       "                       team Season  \n",
       "0                   Arsenal  20-21  \n",
       "1                   Arsenal  20-21  \n",
       "2                   Arsenal  20-21  \n",
       "3                   Arsenal  20-21  \n",
       "4                   Arsenal  20-21  \n",
       "..                      ...    ...  \n",
       "33  Wolverhampton_Wanderers  20-21  \n",
       "34  Wolverhampton_Wanderers  20-21  \n",
       "35  Wolverhampton_Wanderers  20-21  \n",
       "36  Wolverhampton_Wanderers  20-21  \n",
       "37  Wolverhampton_Wanderers  20-21  \n",
       "\n",
       "[760 rows x 21 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#need to check if data is good or not status code 200 or nah\n",
    "import requests\n",
    "for i in t9:\n",
    "    check=requests.get(\"https://raw.githubusercontent.com/vaastav/Fantasy-Premier-League/master/data/2020-21/understat/understat_\"+str(i)+\".csv\")\n",
    "    if check.status_code == 200:\n",
    "        understat = pd.read_csv(\"https://raw.githubusercontent.com/vaastav/Fantasy-Premier-League/master/data/2020-21/understat/understat_\"+str(i)+\".csv\")\n",
    "        understat['team']=str(i)\n",
    "        merge=merge.append(understat)\n",
    "    else:\n",
    "        continue\n",
    "\n",
    "merge['Season']='20-21'\n",
    "merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge.to_csv('2020-2021_Team_Understat.csv')"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}