players_table.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sigmoid(x):\n",
    "    return 1/(1+np.exp(-x))\n",
    "\n",
    "\n",
    "def nn_prob(shot_data):\n",
    "    theta1 = np.load('./Neural Net/xG_theta1.npy')\n",
    "    theta2 = np.load('./Neural Net/xG_theta2.npy')\n",
    "    X_cv = shot_data.as_matrix(columns=['x', 'y', 'Header',\n",
    "                                        'Distance', 'Angle'])\n",
    "\n",
    "    X_cv = np.c_[np.ones(X_cv.shape[0]), X_cv]\n",
    "\n",
    "    m = X_cv.shape[0]\n",
    "\n",
    "    a_1 = X_cv.T\n",
    "    z_2 = np.dot(theta1, a_1)\n",
    "    a_2 = sigmoid(z_2)\n",
    "    # WILL ADD A BIAS UNIT SOON\n",
    "    z_3 = np.dot(theta2, a_2)\n",
    "    a_3 = sigmoid(z_3)\n",
    "    h_theta = a_3\n",
    "    return h_theta.T\n",
    "\n",
    "def tidy_and_format_data(shot_data):\n",
    "    '''\n",
    "    takes shot data, adds distance, angle, colour and a probability\n",
    "    :param shot_data:\n",
    "    :return:\n",
    "    '''\n",
    "    for index, row in shot_data.iterrows():\n",
    "        if row['x'] > 240:\n",
    "            shot_data.set_value(index, 'x', 480 - row['x'])\n",
    "\n",
    "    shot_data['y'] = shot_data['y'] - 366/2\n",
    "    shot_data['y'] = shot_data['y'] * -1\n",
    "    # calculate the angle and the distance from the goal\n",
    "    shot_data['Angle'] = np.arctan((np.absolute(shot_data['y'])/shot_data['x']))\n",
    "    shot_data['Distance'] = np.sqrt(shot_data['y']*shot_data['y'] + shot_data['x']*shot_data['x'])\n",
    "\n",
    "    # assign colours and numbers based on whether the shots were scored or missed\n",
    "    for index, row in shot_data.iterrows():\n",
    "        if row['Scored'] == 'Scored':\n",
    "            shot_data.set_value(index, 'Colour', 'magenta')\n",
    "            shot_data.set_value(index, 'ScoredBinary', 1)\n",
    "        else:\n",
    "            shot_data.set_value(index, 'Colour', 'white')\n",
    "            shot_data.set_value(index, 'ScoredBinary', 0)\n",
    "\n",
    "    shot_data['Proba_exp'] = nn_prob(shot_data)\n",
    "\n",
    "    return shot_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('./All shots from 17-18/E0/shots.csv', index_col=0)\n",
    "football_data = pd.read_csv('./Football-data.co.uk/E0/17-18.csv')\n",
    "football_data['Date'] = pd.to_datetime(football_data['Date'], format='%d/%m/%y')\n",
    "data['Date'] = pd.to_datetime(data['Date'], format='%d-%m-%Y')\n",
    "mappings = pd.read_csv('./All shots from 17-18/E0/mappings.csv', index_col=1, header=None)\n",
    "data.replace(mappings[0], inplace=True)\n",
    "\n",
    "data = tidy_and_format_data(data) # add xG values to each shot and a colour etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "new = data.groupby(['PlayerID'], as_index=False)['Proba_exp', 'ScoredBinary'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PlayerID</th>\n",
       "      <th>Proba_exp</th>\n",
       "      <th>ScoredBinary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61</td>\n",
       "      <td>1.412531</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>73</td>\n",
       "      <td>1.134248</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>77</td>\n",
       "      <td>1.983791</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>81</td>\n",
       "      <td>0.042842</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>84</td>\n",
       "      <td>0.329336</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PlayerID  Proba_exp  ScoredBinary\n",
       "0        61   1.412531           2.0\n",
       "1        73   1.134248           2.0\n",
       "2        77   1.983791           1.0\n",
       "3        81   0.042842           0.0\n",
       "4        84   0.329336           0.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PlayerID</th>\n",
       "      <th>Proba_exp</th>\n",
       "      <th>ScoredBinary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>897</td>\n",
       "      <td>5.572367</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>1847</td>\n",
       "      <td>2.487162</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>856</td>\n",
       "      <td>5.176293</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>10705</td>\n",
       "      <td>3.621200</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>564</td>\n",
       "      <td>3.836699</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     PlayerID  Proba_exp  ScoredBinary\n",
       "90        897   5.572367           7.0\n",
       "118      1847   2.487162           6.0\n",
       "79        856   5.176293           6.0\n",
       "239     10705   3.621200           5.0\n",
       "49        564   3.836699           5.0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new.sort_values('ScoredBinary', ascending=False, inplace=True)\n",
    "new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/site-packages/ipykernel_launcher.py:2: 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: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "player_map = data[['PlayerID', 'PlayerName']]\n",
    "player_map.drop_duplicates(subset='PlayerID', keep='first', inplace=True)\n",
    "player_map.set_index('PlayerID', drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'PlayerName': {61: 'Eriksen',\n",
       "  73: 'Wayne Rooney',\n",
       "  77: 'Defoe',\n",
       "  81: 'Ashley Young',\n",
       "  84: 'Milner',\n",
       "  86: 'Jordan Hende',\n",
       "  87: 'Andy Carroll',\n",
       "  88: 'Welbeck',\n",
       "  89: 'Phil Jones',\n",
       "  90: 'Oxlade-Chamb',\n",
       "  91: 'Martin Kelly',\n",
       "  111: 'Cabaye',\n",
       "  113: 'Koscielny',\n",
       "  115: 'Giroud',\n",
       "  123: '\\xc3\\x96zil',\n",
       "  136: 'Ilkay G\\xc3\\xbcndog',\n",
       "  160: 'Holebas',\n",
       "  206: '206',\n",
       "  263: 'Shane Long',\n",
       "  271: 'Stephen Ward',\n",
       "  272: 'Jonathan Wal',\n",
       "  275: 'McClean',\n",
       "  306: 'F\\xc3\\xa0bregas',\n",
       "  308: 'David Silva',\n",
       "  313: 'Juan Mata',\n",
       "  320: 'Rodr\\xc3\\xadguez Le',\n",
       "  342: 'Martin Olsso',\n",
       "  374: 'Kieran Gibbs',\n",
       "  377: 'Ramsey',\n",
       "  380: 'Coquelin',\n",
       "  382: 'Kyle Bartley',\n",
       "  411: 'Ciaran Clark',\n",
       "  413: 'Albrighton',\n",
       "  414: 'Delph',\n",
       "  429: 'Lowton',\n",
       "  437: 'Bertrand',\n",
       "  441: 'Sturridge',\n",
       "  446: 'Chalobah',\n",
       "  448: 'Luiz Moreira',\n",
       "  449: 'Romeu Vidal',\n",
       "  454: 'Eden Hazard',\n",
       "  466: 'Fellaini',\n",
       "  471: 'Shane Duffy',\n",
       "  493: 'Mousa Demb\\xc3\\xa9l',\n",
       "  523: 'Shelvey',\n",
       "  534: 'Raheem Sterl',\n",
       "  540: 'Joe Allen',\n",
       "  543: 'Kompany',\n",
       "  546: 'Gareth Barry',\n",
       "  564: 'Ag\\xc3\\xbcero',\n",
       "  572: 'Darren Fletc',\n",
       "  574: 'Jonny Evans',\n",
       "  576: 'Cleverley',\n",
       "  581: 'Antonio Vale',\n",
       "  582: 'Joshua King',\n",
       "  583: 'Hern\\xc3\\xa1ndez Ba',\n",
       "  584: 'Smalling',\n",
       "  587: 'Robbie Brady',\n",
       "  592: 'Michael Kean',\n",
       "  642: 'Surman',\n",
       "  697: 'Robson-Kanu',\n",
       "  720: 'Mariappa',\n",
       "  724: 'Schneiderlin',\n",
       "  729: 'Jos\\xc3\\xa9 Fonte',\n",
       "  732: 'Puncheon',\n",
       "  738: 'Jack Stephen',\n",
       "  739: 'Ward-Prowse',\n",
       "  743: 'Cork',\n",
       "  750: 'Jay Rodrigue',\n",
       "  753: 'Steven Davis',\n",
       "  758: 'Shawcross',\n",
       "  773: 'Crouch',\n",
       "  778: 'Geoff Camero',\n",
       "  810: 'Ashley Willi',\n",
       "  831: 'Ben Davies',\n",
       "  840: 'Aaron Lennon',\n",
       "  846: 'Livermore',\n",
       "  851: 'Andros Towns',\n",
       "  854: '854',\n",
       "  856: 'Harry Kane',\n",
       "  861: 'Tom Carroll',\n",
       "  863: 'Gylfi Sigurd',\n",
       "  864: 'Vertonghen',\n",
       "  865: 'Adam Smith',\n",
       "  868: 'James Morris',\n",
       "  869: 'Chris Brunt',\n",
       "  871: 'Chris Wood',\n",
       "  884: 'Craig Dawson',\n",
       "  886: 'Berahino',\n",
       "  896: 'Yacob',\n",
       "  897: 'Romelu Lukak',\n",
       "  898: 'Mark Noble',\n",
       "  900: 'Tomkins',\n",
       "  905: 'Winston Reid',\n",
       "  922: 'Mohamed Diam',\n",
       "  925: 'James Collin',\n",
       "  936: 'Moses',\n",
       "  940: 'McArthur',\n",
       "  1098: 'Idrissa Guey',\n",
       "  1151: 'Alexandre La',\n",
       "  1156: 'Lovren',\n",
       "  1174: 'Andr\\xc3\\xa9 Ayew',\n",
       "  1180: '1180',\n",
       "  1182: 'Azpilicueta',\n",
       "  1294: 'Mamadou Sakh',\n",
       "  1335: 'Krychowiak',\n",
       "  1370: 'Abdoulaye Do',\n",
       "  1410: 'Sako',\n",
       "  1419: 'Kurt Zouma',\n",
       "  1434: 'Moussa Sisso',\n",
       "  1436: 'Capoue',\n",
       "  1455: 'Aurier',\n",
       "  1538: '1538',\n",
       "  1581: 'Alexis S\\xc3\\xa1nch',\n",
       "  1683: 'Nyom',\n",
       "  1722: 'G\\xc3\\xa1mez Duarte',\n",
       "  1736: 'Monreal',\n",
       "  1753: 'Hemed',\n",
       "  1847: 'Morata',\n",
       "  2000: 'Choupo-Motin',\n",
       "  2021: 'Roberto Firm',\n",
       "  2028: 'Danny Willia',\n",
       "  2042: 'Mato Sanmart',\n",
       "  2074: 'Chris L\\xc3\\xb6we',\n",
       "  2103: 'Granit Xhaka',\n",
       "  2169: 'Emre Can',\n",
       "  2172: 'Shaqiri',\n",
       "  2178: 'Joel Matip',\n",
       "  2191: 'Christian Fu',\n",
       "  2196: 'Kolasinac',\n",
       "  2241: 'Son',\n",
       "  2277: 'Mame Biram D',\n",
       "  2354: 'Okazaki',\n",
       "  2359: 'R\\xc3\\xbcdiger',\n",
       "  2408: 'Arnautovic',\n",
       "  2431: 'De Bruyne',\n",
       "  2441: 'Gabbiadini',\n",
       "  2610: 'Hegazi',\n",
       "  2673: 'Philippe Cou',\n",
       "  2710: 'Paul Pogba',\n",
       "  2809: 'Miguel Brito',\n",
       "  2911: 'Okaka',\n",
       "  2921: 'Krkic Perez',\n",
       "  2948: 'Pedro Obiang',\n",
       "  2963: 'Mustafi',\n",
       "  3013: 'Darmian',\n",
       "  3049: 'Roberto Pere',\n",
       "  3272: 'Sadio Man\\xc3\\xa9',\n",
       "  3613: 'Dann',\n",
       "  3632: 'Lee Chung-yo',\n",
       "  3635: 'Marcos Alons',\n",
       "  3655: 'Afobe',\n",
       "  3728: 'Manquillo',\n",
       "  3732: 'Yoshida',\n",
       "  3738: 'Christian Be',\n",
       "  3832: 'Beller\\xc3\\xadn',\n",
       "  3837: 'Lingard',\n",
       "  3926: 'Anthony Mart',\n",
       "  4029: 'Ak\\xc3\\xa9',\n",
       "  4094: 'Alberto More',\n",
       "  4112: 'Lemina',\n",
       "  4188: '4188',\n",
       "  4207: 'Stones',\n",
       "  4299: 'Ibe',\n",
       "  4807: 'Yedlin',\n",
       "  4902: 'Andreas Chri',\n",
       "  4920: 'Loftus-Cheek',\n",
       "  4942: 'Zaha',\n",
       "  4949: 'van Aanholt',\n",
       "  4958: 'Luiz Rosa',\n",
       "  5187: 'Joel Ward',\n",
       "  5195: 'Glenn Murray',\n",
       "  5228: 'Rosenior',\n",
       "  5493: 'Dwight Gayle',\n",
       "  5496: 'Nathan Redmo',\n",
       "  5558: 'Benjamin Men',\n",
       "  5563: 'Mkhitaryan',\n",
       "  5573: 'Wanyama',\n",
       "  5574: 'Bony',\n",
       "  5643: 'Femen\\xc3\\xada Far',\n",
       "  7109: 'Rodr\\xc3\\xadguez Ru',\n",
       "  7145: 'Borges Da Si',\n",
       "  7342: 'Rond\\xc3\\xb3n',\n",
       "  7666: 'Klaassen',\n",
       "  7669: 'van der Hoor',\n",
       "  7673: 'Alderweireld',\n",
       "  7674: 'Blind',\n",
       "  7682: 'Hoedt',\n",
       "  7689: 'Berg Gudmund',\n",
       "  7763: 'Dusan Tadic',\n",
       "  7818: 'Martins Indi',\n",
       "  7831: 'Janmaat',\n",
       "  7968: 'Georginio Wi',\n",
       "  7969: 'Zanka',\n",
       "  8088: 'van La Parra',\n",
       "  8105: 'Davy Pr\\xc3\\xb6pper',\n",
       "  8197: 'Tiemou\\xc3\\xa9 Baka',\n",
       "  8206: 'Mounie',\n",
       "  8331: 'Atsu',\n",
       "  9093: 'Batshuayi',\n",
       "  9245: 'Elneny',\n",
       "  9258: 'Mohamed Sala',\n",
       "  9284: 'Nemanja Mati',\n",
       "  9297: 'Virgil van D',\n",
       "  9350: 'Otamendi',\n",
       "  9356: 'Danilo',\n",
       "  9360: 'Defour',\n",
       "  9394: 'Suttner',\n",
       "  9425: 'Nordin Amrab',\n",
       "  9453: 'Milivojevic',\n",
       "  9470: 'Kouyat\\xc3\\xa9',\n",
       "  9471: 'Aleksandar M',\n",
       "  9548: '9548',\n",
       "  9578: 'Iwobi',\n",
       "  9872: 'Riedewald',\n",
       "  10014: 'Demarai Gray',\n",
       "  10072: 'Tom Ince',\n",
       "  10148: 'Simon Franci',\n",
       "  10150: 'Steve Cook',\n",
       "  10162: 'Marc Pugh',\n",
       "  10164: 'Harry Arter',\n",
       "  10166: 'Charlie Dani',\n",
       "  10174: 'Ryan Fraser',\n",
       "  10182: 'Matt Ritchie',\n",
       "  10212: 'Lewis Dunk',\n",
       "  10234: 'Solly March',\n",
       "  10238: 'Ashley Barne',\n",
       "  10256: 'Kieran Tripp',\n",
       "  10264: 'Ben Mee',\n",
       "  10282: 'Junior Stani',\n",
       "  10292: 'Scott Arfiel',\n",
       "  10300: 'Sam Vokes',\n",
       "  10340: 'Dale Stephen',\n",
       "  10408: 'Jeff Hendric',\n",
       "  10504: 'Tommy Smith',\n",
       "  10560: 'Aaron Cressw',\n",
       "  10686: 'Wes Morgan',\n",
       "  10694: 'Matty James',\n",
       "  10695: 'Andy King',\n",
       "  10705: 'Vardy',\n",
       "  10707: 'Jeffrey Schl',\n",
       "  10715: 'Anthony Knoc',\n",
       "  10853: 'Jamaal Lasce',\n",
       "  10899: 'Matt Phillip',\n",
       "  10903: 'Charlie Aust',\n",
       "  10955: 'Michail Anto',\n",
       "  11010: 'Troy Deeney',\n",
       "  11309: 'Mooy',\n",
       "  11399: '11399',\n",
       "  11509: 'Mahrez',\n",
       "  12319: 'Leroy San\\xc3\\xa9',\n",
       "  12372: 'Billing',\n",
       "  12606: 'Veiga de Car',\n",
       "  12669: 'Slimani',\n",
       "  14359: 'Andr\\xc3\\xa9 Carril',\n",
       "  14538: 'Robertson',\n",
       "  14999: \"N'Golo Kant\\xc3\\xa9\",\n",
       "  15035: 'Diafra Sakho',\n",
       "  15144: 'P\\xc3\\xa9rez Guti\\xc3\\xa9r',\n",
       "  15160: 'Tarkowski',\n",
       "  15161: '15161',\n",
       "  15177: 'Andre Gray',\n",
       "  15280: 'Kachunga',\n",
       "  15466: 'Solanke',\n",
       "  15510: 'Iheanacho',\n",
       "  15533: 'Harry Maguir',\n",
       "  15553: 'Dier',\n",
       "  15595: 'Sandro Ram\\xc3\\xadr',\n",
       "  15657: 'Zappacosta',\n",
       "  15786: 'Lewis Cook',\n",
       "  15806: 'Oumar Niasse',\n",
       "  16192: 'C\\xc3\\xa9dric Soare',\n",
       "  16317: 'Izquierdo Me',\n",
       "  16605: 'Eric Bailly',\n",
       "  16633: 'Oliver Burke',\n",
       "  17351: 'Boufal',\n",
       "  18137: 'Gabriel Jesu',\n",
       "  18185: 'Alli',\n",
       "  18311: 'Lanzini',\n",
       "  18316: 'Alexander-Ar',\n",
       "  18343: 'Clucas',\n",
       "  18466: 'Holding',\n",
       "  18702: 'Gro\\xc3\\x9f',\n",
       "  18852: 'Jamie Murphy',\n",
       "  18890: 'Roque Mesa',\n",
       "  19189: 'Depoitre',\n",
       "  19300: 'Edimilson Fe',\n",
       "  19695: 'Luz Sanches',\n",
       "  19728: 'Rashford',\n",
       "  20160: '-Fosu-Mensah',\n",
       "  20368: 'Kasey Palmer',\n",
       "  20389: 'Tom Davies',\n",
       "  20451: 'Tammy Abraha',\n",
       "  20573: 'Richarlison',\n",
       "  20816: 'Mousset',\n",
       "  20871: 'Schindler',\n",
       "  21082: '21082',\n",
       "  21942: 'Ndidi',\n",
       "  22275: 'Calvert-Lewi',\n",
       "  22827: 'Quaner',\n",
       "  23637: 'Sabiri',\n",
       "  24006: 'Vlasic',\n",
       "  24042: 'Lumeka'}}"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_dict = player_map.to_dict()\n",
    "player_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "new.replace(player_dict['PlayerName'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "new.set_index('PlayerID', drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PlayerID</th>\n",
       "      <th>Proba_exp</th>\n",
       "      <th>ScoredBinary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Romelu Lukak</td>\n",
       "      <td>5.572367</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Morata</td>\n",
       "      <td>2.487162</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>5.176293</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>Vardy</td>\n",
       "      <td>3.621200</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Agüero</td>\n",
       "      <td>3.836699</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Raheem Sterl</td>\n",
       "      <td>2.944009</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Alexandre La</td>\n",
       "      <td>3.124712</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>Gabriel Jesu</td>\n",
       "      <td>2.555697</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>Mohamed Sala</td>\n",
       "      <td>3.331386</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Sadio Mané</td>\n",
       "      <td>1.934727</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Welbeck</td>\n",
       "      <td>2.236512</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Hernández Ba</td>\n",
       "      <td>1.952511</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>Richarlison</td>\n",
       "      <td>2.736695</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>Anthony Mart</td>\n",
       "      <td>1.228217</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>Leroy Sané</td>\n",
       "      <td>1.150211</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Abdoulaye Do</td>\n",
       "      <td>0.816386</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Fellaini</td>\n",
       "      <td>1.775232</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Okazaki</td>\n",
       "      <td>2.217475</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>Oumar Niasse</td>\n",
       "      <td>1.016492</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Crouch</td>\n",
       "      <td>0.859219</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         PlayerID  Proba_exp  ScoredBinary\n",
       "90   Romelu Lukak   5.572367           7.0\n",
       "118        Morata   2.487162           6.0\n",
       "79     Harry Kane   5.176293           6.0\n",
       "239         Vardy   3.621200           5.0\n",
       "49         Agüero   3.836699           5.0\n",
       "45   Raheem Sterl   2.944009           5.0\n",
       "99   Alexandre La   3.124712           4.0\n",
       "276  Gabriel Jesu   2.555697           4.0\n",
       "201  Mohamed Sala   3.331386           4.0\n",
       "147    Sadio Mané   1.934727           3.0\n",
       "7         Welbeck   2.236512           3.0\n",
       "55   Hernández Ba   1.952511           3.0\n",
       "293   Richarlison   2.736695           3.0\n",
       "157  Anthony Mart   1.228217           3.0\n",
       "250    Leroy Sané   1.150211           3.0\n",
       "106  Abdoulaye Do   0.816386           3.0\n",
       "41       Fellaini   1.775232           3.0\n",
       "132       Okazaki   2.217475           3.0\n",
       "270  Oumar Niasse   1.016492           2.0\n",
       "71         Crouch   0.859219           2.0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new.to_csv('players.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "meanxg = data.groupby(['PlayerID'], as_index=False)['Proba_exp'].mean().sort_values('Proba_exp', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PlayerID</th>\n",
       "      <th>Proba_exp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>89</td>\n",
       "      <td>0.478478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>922</td>\n",
       "      <td>0.459438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>466</td>\n",
       "      <td>0.443808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>7818</td>\n",
       "      <td>0.370478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>16605</td>\n",
       "      <td>0.365728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>2178</td>\n",
       "      <td>0.354473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>574</td>\n",
       "      <td>0.343033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>22827</td>\n",
       "      <td>0.331167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>18466</td>\n",
       "      <td>0.305082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>3655</td>\n",
       "      <td>0.293693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>884</td>\n",
       "      <td>0.292334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>2911</td>\n",
       "      <td>0.290885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>773</td>\n",
       "      <td>0.286406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>2354</td>\n",
       "      <td>0.277184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>3732</td>\n",
       "      <td>0.274351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>15510</td>\n",
       "      <td>0.272734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>10903</td>\n",
       "      <td>0.270742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>77</td>\n",
       "      <td>0.247974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>15466</td>\n",
       "      <td>0.246390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>840</td>\n",
       "      <td>0.244614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>10705</td>\n",
       "      <td>0.241413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>886</td>\n",
       "      <td>0.237145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>411</td>\n",
       "      <td>0.224475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>382</td>\n",
       "      <td>0.224203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>15533</td>\n",
       "      <td>0.223130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>1174</td>\n",
       "      <td>0.212331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>91</td>\n",
       "      <td>0.208049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>868</td>\n",
       "      <td>0.205669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>9471</td>\n",
       "      <td>0.203659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>10340</td>\n",
       "      <td>0.201194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>4949</td>\n",
       "      <td>0.026852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>111</td>\n",
       "      <td>0.026826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>581</td>\n",
       "      <td>0.026620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>2191</td>\n",
       "      <td>0.026533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>3013</td>\n",
       "      <td>0.026101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>10504</td>\n",
       "      <td>0.025535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>898</td>\n",
       "      <td>0.025284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>854</td>\n",
       "      <td>0.024878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>10166</td>\n",
       "      <td>0.024169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>284</th>\n",
       "      <td>18890</td>\n",
       "      <td>0.023986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>429</td>\n",
       "      <td>0.023499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>10164</td>\n",
       "      <td>0.023455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>16317</td>\n",
       "      <td>0.023316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>7831</td>\n",
       "      <td>0.023237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>1683</td>\n",
       "      <td>0.023231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>4807</td>\n",
       "      <td>0.023217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>274</th>\n",
       "      <td>16633</td>\n",
       "      <td>0.022855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>5643</td>\n",
       "      <td>0.022502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>2948</td>\n",
       "      <td>0.022236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>12372</td>\n",
       "      <td>0.021808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>160</td>\n",
       "      <td>0.021717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>9453</td>\n",
       "      <td>0.021400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>9394</td>\n",
       "      <td>0.021211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>19695</td>\n",
       "      <td>0.021081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>1335</td>\n",
       "      <td>0.021042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>342</td>\n",
       "      <td>0.020707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>18311</td>\n",
       "      <td>0.020539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2359</td>\n",
       "      <td>0.020326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289</th>\n",
       "      <td>20160</td>\n",
       "      <td>0.019575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>2074</td>\n",
       "      <td>0.017604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>303 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     PlayerID  Proba_exp\n",
       "8          89   0.478478\n",
       "94        922   0.459438\n",
       "41        466   0.443808\n",
       "190      7818   0.370478\n",
       "273     16605   0.365728\n",
       "127      2178   0.354473\n",
       "51        574   0.343033\n",
       "299     22827   0.331167\n",
       "281     18466   0.305082\n",
       "151      3655   0.293693\n",
       "87        884   0.292334\n",
       "141      2911   0.290885\n",
       "71        773   0.286406\n",
       "132      2354   0.277184\n",
       "153      3732   0.274351\n",
       "264     15510   0.272734\n",
       "244     10903   0.270742\n",
       "2          77   0.247974\n",
       "263     15466   0.246390\n",
       "75        840   0.244614\n",
       "239     10705   0.241413\n",
       "88        886   0.237145\n",
       "31        411   0.224475\n",
       "30        382   0.224203\n",
       "265     15533   0.223130\n",
       "101      1174   0.212331\n",
       "10         91   0.208049\n",
       "84        868   0.205669\n",
       "211      9471   0.203659\n",
       "232     10340   0.201194\n",
       "..        ...        ...\n",
       "168      4949   0.026852\n",
       "11        111   0.026826\n",
       "53        581   0.026620\n",
       "128      2191   0.026533\n",
       "145      3013   0.026101\n",
       "234     10504   0.025535\n",
       "91        898   0.025284\n",
       "78        854   0.024878\n",
       "221     10166   0.024169\n",
       "284     18890   0.023986\n",
       "34        429   0.023499\n",
       "220     10164   0.023455\n",
       "272     16317   0.023316\n",
       "191      7831   0.023237\n",
       "114      1683   0.023231\n",
       "164      4807   0.023217\n",
       "274     16633   0.022855\n",
       "179      5643   0.022502\n",
       "143      2948   0.022236\n",
       "251     12372   0.021808\n",
       "16        160   0.021717\n",
       "209      9453   0.021400\n",
       "207      9394   0.021211\n",
       "287     19695   0.021081\n",
       "105      1335   0.021042\n",
       "26        342   0.020707\n",
       "278     18311   0.020539\n",
       "133      2359   0.020326\n",
       "289     20160   0.019575\n",
       "123      2074   0.017604\n",
       "\n",
       "[303 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meanxg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}