Merge 2019-2020 Understat Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\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",
    "\n",
    "Base_BPL=teams[(teams.league=='Barclays Premier League')|(teams.league=='English League Championship')]"
   ]
  },
  {
   "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>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>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>64</th>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td>Brentford</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.74</td>\n",
       "      <td>0.83</td>\n",
       "      <td>67.01</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>76</th>\n",
       "      <td>77</td>\n",
       "      <td>77</td>\n",
       "      <td>Norwich City</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.82</td>\n",
       "      <td>0.97</td>\n",
       "      <td>65.03</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>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>113</th>\n",
       "      <td>114</td>\n",
       "      <td>113</td>\n",
       "      <td>Watford</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.52</td>\n",
       "      <td>0.94</td>\n",
       "      <td>59.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>137</td>\n",
       "      <td>136</td>\n",
       "      <td>AFC Bournemouth</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.54</td>\n",
       "      <td>1.07</td>\n",
       "      <td>56.67</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",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>148</td>\n",
       "      <td>143</td>\n",
       "      <td>Barnsley</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.38</td>\n",
       "      <td>0.96</td>\n",
       "      <td>55.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>169</td>\n",
       "      <td>168</td>\n",
       "      <td>Blackburn</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1.19</td>\n",
       "      <td>52.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>179</td>\n",
       "      <td>179</td>\n",
       "      <td>Cardiff City</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.46</td>\n",
       "      <td>1.22</td>\n",
       "      <td>51.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>185</td>\n",
       "      <td>184</td>\n",
       "      <td>Stoke City</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.25</td>\n",
       "      <td>1.03</td>\n",
       "      <td>50.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>188</td>\n",
       "      <td>189</td>\n",
       "      <td>Queens Park Rangers</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.51</td>\n",
       "      <td>1.31</td>\n",
       "      <td>50.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>208</td>\n",
       "      <td>210</td>\n",
       "      <td>Rotherham United</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.31</td>\n",
       "      <td>1.20</td>\n",
       "      <td>48.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>210</td>\n",
       "      <td>212</td>\n",
       "      <td>Reading</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.30</td>\n",
       "      <td>1.20</td>\n",
       "      <td>47.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>217</td>\n",
       "      <td>220</td>\n",
       "      <td>Swansea City</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1.13</td>\n",
       "      <td>47.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>220</td>\n",
       "      <td>222</td>\n",
       "      <td>Preston North End</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1.14</td>\n",
       "      <td>46.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>222</td>\n",
       "      <td>224</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.24</td>\n",
       "      <td>1.19</td>\n",
       "      <td>46.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>229</td>\n",
       "      <td>232</td>\n",
       "      <td>Coventry City</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.27</td>\n",
       "      <td>1.25</td>\n",
       "      <td>46.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>244</td>\n",
       "      <td>243</td>\n",
       "      <td>Derby County</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.17</td>\n",
       "      <td>1.19</td>\n",
       "      <td>44.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>247</td>\n",
       "      <td>246</td>\n",
       "      <td>Millwall</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.25</td>\n",
       "      <td>1.29</td>\n",
       "      <td>44.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>248</td>\n",
       "      <td>247</td>\n",
       "      <td>Luton Town</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1.24</td>\n",
       "      <td>44.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>252</td>\n",
       "      <td>252</td>\n",
       "      <td>Nottingham Forest</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.07</td>\n",
       "      <td>1.11</td>\n",
       "      <td>44.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>264</td>\n",
       "      <td>265</td>\n",
       "      <td>Sheffield Wednesday</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.21</td>\n",
       "      <td>1.31</td>\n",
       "      <td>43.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>271</td>\n",
       "      <td>273</td>\n",
       "      <td>Wycombe Wanderers</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.22</td>\n",
       "      <td>1.35</td>\n",
       "      <td>42.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285</th>\n",
       "      <td>286</td>\n",
       "      <td>286</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1.25</td>\n",
       "      <td>41.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>350</td>\n",
       "      <td>351</td>\n",
       "      <td>Huddersfield Town</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1.45</td>\n",
       "      <td>37.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>450</td>\n",
       "      <td>450</td>\n",
       "      <td>Bristol City</td>\n",
       "      <td>English League Championship</td>\n",
       "      <td>1.02</td>\n",
       "      <td>1.72</td>\n",
       "      <td>30.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     rank  prev_rank                      name                       league  \\\n",
       "0       1          1           Manchester City      Barclays Premier League   \n",
       "2       3          3                   Chelsea      Barclays Premier League   \n",
       "4       5          5                 Liverpool      Barclays Premier League   \n",
       "7       8          8         Manchester United      Barclays Premier League   \n",
       "12     13         13                   Arsenal      Barclays Premier League   \n",
       "24     25         25  Brighton and Hove Albion      Barclays Premier League   \n",
       "25     26         26            Leicester City      Barclays Premier League   \n",
       "26     27         27         Tottenham Hotspur      Barclays Premier League   \n",
       "33     34         34           West Ham United      Barclays Premier League   \n",
       "35     36         36              Leeds United      Barclays Premier League   \n",
       "42     43         43                   Everton      Barclays Premier League   \n",
       "43     44         44               Aston Villa      Barclays Premier League   \n",
       "57     58         57             Wolverhampton      Barclays Premier League   \n",
       "58     59         59                    Fulham      Barclays Premier League   \n",
       "60     61         61                 Newcastle      Barclays Premier League   \n",
       "64     65         65                 Brentford  English League Championship   \n",
       "65     66         66               Southampton      Barclays Premier League   \n",
       "67     68         68                   Burnley      Barclays Premier League   \n",
       "76     77         77              Norwich City  English League Championship   \n",
       "95     96         96            Crystal Palace      Barclays Premier League   \n",
       "110   111        109      West Bromwich Albion      Barclays Premier League   \n",
       "113   114        113                   Watford  English League Championship   \n",
       "136   137        136           AFC Bournemouth  English League Championship   \n",
       "143   144        140          Sheffield United      Barclays Premier League   \n",
       "147   148        143                  Barnsley  English League Championship   \n",
       "168   169        168                 Blackburn  English League Championship   \n",
       "178   179        179              Cardiff City  English League Championship   \n",
       "184   185        184                Stoke City  English League Championship   \n",
       "187   188        189       Queens Park Rangers  English League Championship   \n",
       "207   208        210          Rotherham United  English League Championship   \n",
       "209   210        212                   Reading  English League Championship   \n",
       "216   217        220              Swansea City  English League Championship   \n",
       "219   220        222         Preston North End  English League Championship   \n",
       "221   222        224             Middlesbrough  English League Championship   \n",
       "228   229        232             Coventry City  English League Championship   \n",
       "243   244        243              Derby County  English League Championship   \n",
       "246   247        246                  Millwall  English League Championship   \n",
       "247   248        247                Luton Town  English League Championship   \n",
       "251   252        252         Nottingham Forest  English League Championship   \n",
       "263   264        265       Sheffield Wednesday  English League Championship   \n",
       "270   271        273         Wycombe Wanderers  English League Championship   \n",
       "285   286        286                Birmingham  English League Championship   \n",
       "349   350        351         Huddersfield Town  English League Championship   \n",
       "449   450        450              Bristol City  English League Championship   \n",
       "\n",
       "      off   def    spi  \n",
       "0    2.79  0.25  92.42  \n",
       "2    2.42  0.20  90.53  \n",
       "4    2.69  0.40  89.17  \n",
       "7    2.41  0.46  85.53  \n",
       "12   2.18  0.42  83.72  \n",
       "24   1.97  0.51  78.71  \n",
       "25   2.14  0.64  78.22  \n",
       "26   2.31  0.76  78.06  \n",
       "33   2.18  0.76  76.00  \n",
       "35   2.13  0.78  74.94  \n",
       "42   1.86  0.70  72.28  \n",
       "43   2.02  0.82  72.18  \n",
       "57   1.73  0.72  69.61  \n",
       "58   1.68  0.74  68.01  \n",
       "60   1.93  0.94  67.79  \n",
       "64   1.74  0.83  67.01  \n",
       "65   1.88  0.93  67.01  \n",
       "67   1.85  0.92  66.79  \n",
       "76   1.82  0.97  65.03  \n",
       "95   1.69  1.00  61.55  \n",
       "110  1.78  1.16  59.72  \n",
       "113  1.52  0.94  59.48  \n",
       "136  1.54  1.07  56.67  \n",
       "143  1.44  0.99  56.30  \n",
       "147  1.38  0.96  55.91  \n",
       "168  1.50  1.19  52.88  \n",
       "178  1.46  1.22  51.41  \n",
       "184  1.25  1.03  50.95  \n",
       "187  1.51  1.31  50.33  \n",
       "207  1.31  1.20  48.12  \n",
       "209  1.30  1.20  47.84  \n",
       "216  1.20  1.13  47.10  \n",
       "219  1.20  1.14  46.87  \n",
       "221  1.24  1.19  46.79  \n",
       "228  1.27  1.25  46.03  \n",
       "243  1.17  1.19  44.85  \n",
       "246  1.25  1.29  44.77  \n",
       "247  1.20  1.24  44.65  \n",
       "251  1.07  1.11  44.18  \n",
       "263  1.21  1.31  43.15  \n",
       "270  1.22  1.35  42.59  \n",
       "285  1.10  1.25  41.68  \n",
       "349  1.10  1.45  37.52  \n",
       "449  1.02  1.72  30.18  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Base_BPL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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"
     ]
    },
    {
     "data": {
      "text/plain": [
       "12                     Arsenal\n",
       "43                 Aston_Villa\n",
       "147                   Barnsley\n",
       "285                 Birmingham\n",
       "168                  Blackburn\n",
       "136                Bournemouth\n",
       "64                   Brentford\n",
       "24                    Brighton\n",
       "449               Bristol_City\n",
       "67                     Burnley\n",
       "178               Cardiff_City\n",
       "2                      Chelsea\n",
       "228              Coventry_City\n",
       "95              Crystal_Palace\n",
       "243               Derby_County\n",
       "42                     Everton\n",
       "58                      Fulham\n",
       "349          Huddersfield_Town\n",
       "35                       Leeds\n",
       "25                   Leicester\n",
       "4                    Liverpool\n",
       "247                 Luton_Town\n",
       "0              Manchester_City\n",
       "7            Manchester_United\n",
       "221              Middlesbrough\n",
       "246                   Millwall\n",
       "60            Newcastle_United\n",
       "76                     Norwich\n",
       "251          Nottingham_Forest\n",
       "219          Preston_North_End\n",
       "187        Queens_Park_Rangers\n",
       "209                    Reading\n",
       "207           Rotherham_United\n",
       "143           Sheffield_United\n",
       "263        Sheffield_Wednesday\n",
       "65                 Southampton\n",
       "184                 Stoke_City\n",
       "216               Swansea_City\n",
       "26                   Tottenham\n",
       "113                    Watford\n",
       "110       West_Bromwich_Albion\n",
       "33                    West_Ham\n",
       "57     Wolverhampton_Wanderers\n",
       "270          Wycombe_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=Base_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.replace('AFC_Bournemouth','Bournemouth')\n",
    "t10=t9.sort_values()\n",
    "t10"
   ]
  },
  {
   "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/2019-20/understat/understat_\"+t10.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>date</th>\n",
       "      <th>deep</th>\n",
       "      <th>deep_allowed</th>\n",
       "      <th>draws</th>\n",
       "      <th>h_a</th>\n",
       "      <th>loses</th>\n",
       "      <th>missed</th>\n",
       "      <th>npxG</th>\n",
       "      <th>npxGA</th>\n",
       "      <th>npxGD</th>\n",
       "      <th>...</th>\n",
       "      <th>ppda_allowed</th>\n",
       "      <th>pts</th>\n",
       "      <th>result</th>\n",
       "      <th>scored</th>\n",
       "      <th>wins</th>\n",
       "      <th>xG</th>\n",
       "      <th>xGA</th>\n",
       "      <th>xpts</th>\n",
       "      <th>team</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-08-11 14:00:00</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.133090</td>\n",
       "      <td>0.380551</td>\n",
       "      <td>0.752539</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 383, 'def': 29}</td>\n",
       "      <td>3</td>\n",
       "      <td>w</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.133090</td>\n",
       "      <td>0.380551</td>\n",
       "      <td>2.0730</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-17 12:30:00</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>h</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.164400</td>\n",
       "      <td>1.391720</td>\n",
       "      <td>-0.227320</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 296, 'def': 26}</td>\n",
       "      <td>3</td>\n",
       "      <td>w</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1.164400</td>\n",
       "      <td>1.391720</td>\n",
       "      <td>1.1533</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08-24 17:30:00</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0.985542</td>\n",
       "      <td>2.027040</td>\n",
       "      <td>-1.041498</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 362, 'def': 37}</td>\n",
       "      <td>0</td>\n",
       "      <td>l</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.985542</td>\n",
       "      <td>2.788210</td>\n",
       "      <td>0.3434</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-09-01 16:30:00</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>h</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.925090</td>\n",
       "      <td>1.193970</td>\n",
       "      <td>0.731120</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 207, 'def': 17}</td>\n",
       "      <td>1</td>\n",
       "      <td>d</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1.925090</td>\n",
       "      <td>1.955140</td>\n",
       "      <td>1.2919</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-09-15 15:30:00</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.006160</td>\n",
       "      <td>2.070920</td>\n",
       "      <td>-1.064760</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 254, 'def': 28}</td>\n",
       "      <td>1</td>\n",
       "      <td>d</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1.006160</td>\n",
       "      <td>2.832090</td>\n",
       "      <td>0.3084</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>19-20</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>2020-07-08 17:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.326909</td>\n",
       "      <td>0.470483</td>\n",
       "      <td>-0.143574</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 340, 'def': 18}</td>\n",
       "      <td>0</td>\n",
       "      <td>l</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.326909</td>\n",
       "      <td>0.470483</td>\n",
       "      <td>1.0775</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2020-07-12 11:00:00</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>h</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.106070</td>\n",
       "      <td>0.286561</td>\n",
       "      <td>1.819509</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 354, 'def': 19}</td>\n",
       "      <td>3</td>\n",
       "      <td>w</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2.867240</td>\n",
       "      <td>0.286561</td>\n",
       "      <td>2.8945</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2020-07-15 17:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.285700</td>\n",
       "      <td>0.773179</td>\n",
       "      <td>0.512521</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 201, 'def': 26}</td>\n",
       "      <td>1</td>\n",
       "      <td>d</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.285700</td>\n",
       "      <td>1.534350</td>\n",
       "      <td>1.0986</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2020-07-20 19:15:00</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>h</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.486960</td>\n",
       "      <td>0.440119</td>\n",
       "      <td>1.046841</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 257, 'def': 22}</td>\n",
       "      <td>3</td>\n",
       "      <td>w</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1.486960</td>\n",
       "      <td>0.440119</td>\n",
       "      <td>2.3593</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2020-07-26 15:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.164561</td>\n",
       "      <td>1.159380</td>\n",
       "      <td>-0.994819</td>\n",
       "      <td>...</td>\n",
       "      <td>{'att': 198, 'def': 29}</td>\n",
       "      <td>0</td>\n",
       "      <td>l</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.164561</td>\n",
       "      <td>1.159380</td>\n",
       "      <td>0.4276</td>\n",
       "      <td>Wolverhampton_Wanderers</td>\n",
       "      <td>19-20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>760 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   date  deep  deep_allowed  draws h_a  loses  missed  \\\n",
       "0   2019-08-11 14:00:00    10             1      0   a      0       0   \n",
       "1   2019-08-17 12:30:00     8             4      0   h      0       1   \n",
       "2   2019-08-24 17:30:00     7             9      0   a      1       3   \n",
       "3   2019-09-01 16:30:00    12             6      1   h      0       2   \n",
       "4   2019-09-15 15:30:00     7            12      1   a      0       2   \n",
       "..                  ...   ...           ...    ...  ..    ...     ...   \n",
       "33  2020-07-08 17:00:00     2             7      0   a      1       1   \n",
       "34  2020-07-12 11:00:00     4             3      0   h      0       0   \n",
       "35  2020-07-15 17:00:00     3             6      1   a      0       1   \n",
       "36  2020-07-20 19:15:00     4             1      0   h      0       0   \n",
       "37  2020-07-26 15:00:00     2             7      0   a      1       2   \n",
       "\n",
       "        npxG     npxGA     npxGD  ...             ppda_allowed pts  result  \\\n",
       "0   1.133090  0.380551  0.752539  ...  {'att': 383, 'def': 29}   3       w   \n",
       "1   1.164400  1.391720 -0.227320  ...  {'att': 296, 'def': 26}   3       w   \n",
       "2   0.985542  2.027040 -1.041498  ...  {'att': 362, 'def': 37}   0       l   \n",
       "3   1.925090  1.193970  0.731120  ...  {'att': 207, 'def': 17}   1       d   \n",
       "4   1.006160  2.070920 -1.064760  ...  {'att': 254, 'def': 28}   1       d   \n",
       "..       ...       ...       ...  ...                      ...  ..     ...   \n",
       "33  0.326909  0.470483 -0.143574  ...  {'att': 340, 'def': 18}   0       l   \n",
       "34  2.106070  0.286561  1.819509  ...  {'att': 354, 'def': 19}   3       w   \n",
       "35  1.285700  0.773179  0.512521  ...  {'att': 201, 'def': 26}   1       d   \n",
       "36  1.486960  0.440119  1.046841  ...  {'att': 257, 'def': 22}   3       w   \n",
       "37  0.164561  1.159380 -0.994819  ...  {'att': 198, 'def': 29}   0       l   \n",
       "\n",
       "   scored  wins        xG       xGA    xpts                     team Season  \n",
       "0       1     1  1.133090  0.380551  2.0730                  Arsenal  19-20  \n",
       "1       2     1  1.164400  1.391720  1.1533                  Arsenal  19-20  \n",
       "2       1     0  0.985542  2.788210  0.3434                  Arsenal  19-20  \n",
       "3       2     0  1.925090  1.955140  1.2919                  Arsenal  19-20  \n",
       "4       2     0  1.006160  2.832090  0.3084                  Arsenal  19-20  \n",
       "..    ...   ...       ...       ...     ...                      ...    ...  \n",
       "33      0     0  0.326909  0.470483  1.0775  Wolverhampton_Wanderers  19-20  \n",
       "34      3     1  2.867240  0.286561  2.8945  Wolverhampton_Wanderers  19-20  \n",
       "35      1     0  1.285700  1.534350  1.0986  Wolverhampton_Wanderers  19-20  \n",
       "36      2     1  1.486960  0.440119  2.3593  Wolverhampton_Wanderers  19-20  \n",
       "37      0     0  0.164561  1.159380  0.4276  Wolverhampton_Wanderers  19-20  \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 t10:\n",
    "    check=requests.get(\"https://raw.githubusercontent.com/vaastav/Fantasy-Premier-League/master/data/2019-20/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/2019-20/understat/understat_\"+str(i)+\".csv\")\n",
    "        understat['team']=str(i)\n",
    "        merge=merge.append(understat)\n",
    "    else:\n",
    "        continue\n",
    "\n",
    "merge['Season']='19-20'\n",
    "merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge.to_csv('2019-2020_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
}