10_Ejercicio2_Liga_Futbol_Resultados.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 10 - Ejercicio 2 - Análisis de Resultados de la Liga de Fútbol (2017/18)\n",
    "\n",
    "\n",
    "* Este ejercicio se va a dividir en 3 partes:\n",
    "    1. Construir la ***clasificación final de la temporada*** (para 1ª o 2ª división)\n",
    "    2. Construir una ***matriz de resultados*** del siguiente estilo:\n",
    "    \n",
    "\n",
    "<img src=\"./imgs/09_01_pivot_table.png\" style=\"width: 1100px;\"/>\n",
    "\n",
    "\n",
    "* Para realizar este ejercicio se dispone del fichero ***'Partidos_Futbol_Temporada_2017_18.csv'*** que tiene los siguientes campos:\n",
    "\n",
    "    - idPartido: Un identificador único de partido.\n",
    "    - temporada: Temporada en la que se jugó el partido\n",
    "    - division: División en la que se jugo el partido (Primera '1', Segunda '2')\n",
    "    - jornada: Jornada en la que se jugó el partido\n",
    "    - EquipoLocal: Nombre del Equipo Local\n",
    "    - EquipoVisitante: Nombre del Equipo Visitante\n",
    "    - golesLocal: Goles que marcó el equipo local\n",
    "    - golesVisitante: Goles que marcó el equipo visitante\n",
    "    - fecha: Fecha en la que se jugó el partido\n",
    "    - timestamp: Timestamp de la fecha en la que se jugó el partido\n",
    "\n",
    "\n",
    "<hr>\n",
    "\n",
    "\n",
    "## Ejercicios:\n",
    "\n",
    "\n",
    "### 1. Construir la clasificación final de la temporada (para 1ª o 2ª división)\n",
    "\n",
    "* Para construir una tabla con la clasificación final de la temporada (Para 1ª o 2ª división) hay que seguir los siguientes pasos:\n",
    "\n",
    "    - **1.1 Filtramos los partidos de 1ª o 2ª División**\n",
    "    - **1.2 Creamos un dataframe (df_local) con los puntos que consigue el equipo local**\n",
    "    - **1.3 Creamos un dataframe (df_visitante) con los puntos que consigue el equipo visitante**\n",
    "    - **1.4 Unimos los dos dataframes (df_result)**\n",
    "    - **1.5 Agregamos por equipo y sumamos sus puntos**\n",
    "    - **1.6 Ordenamos el dataframe por puntos**\n",
    "\n",
    "\n",
    "### 2. Construir una matriz de resultados de la temporada:\n",
    "\n",
    "* Para construir una matriz de resultados tenemos que realizar los siguientes pasos:\n",
    "\n",
    "    - **2.1 Filtramos los partidos de 1ª o 2ª División**\n",
    "    - **2.2 Creamos una nueva columna que sea la concatenación de las columnas 'golesLocal' con 'golesVisitate'**\n",
    "    - **2.3 Realizamos el Pivot Table***\n",
    "\n",
    "\n",
    "<hr> \n",
    "\n",
    "\n",
    "#### Pasos previos:\n",
    "\n",
    "   - Importamos la librería de pandas\n",
    "   - Cargamos los ficheros y los pasamos a un DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:95% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>idPartido</th>\n",
       "      <th>temporada</th>\n",
       "      <th>division</th>\n",
       "      <th>jornada</th>\n",
       "      <th>EquipoLocal</th>\n",
       "      <th>EquipoVisitante</th>\n",
       "      <th>golesLocal</th>\n",
       "      <th>golesVisitante</th>\n",
       "      <th>fecha</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>36350</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>Deportivo</td>\n",
       "      <td>Alaves</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>20/09/2017</td>\n",
       "      <td>1505858400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>36930</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>2</td>\n",
       "      <td>23</td>\n",
       "      <td>Oviedo</td>\n",
       "      <td>Almeria</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>20/01/2018</td>\n",
       "      <td>1516402800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>36680</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>Leganes</td>\n",
       "      <td>Betis</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>19/05/2018</td>\n",
       "      <td>1526680800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>436</th>\n",
       "      <td>36742</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>Sporting de Gijon</td>\n",
       "      <td>Lorca FC</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>23/09/2017</td>\n",
       "      <td>1506117600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>36652</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>1</td>\n",
       "      <td>35</td>\n",
       "      <td>Valencia</td>\n",
       "      <td>Eibar</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29/04/2018</td>\n",
       "      <td>1524952800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     idPartido temporada  division  jornada        EquipoLocal  \\\n",
       "44       36350   2017-18         1        5          Deportivo   \n",
       "624      36930   2017-18         2       23             Oviedo   \n",
       "374      36680   2017-18         1       38            Leganes   \n",
       "436      36742   2017-18         2        6  Sporting de Gijon   \n",
       "346      36652   2017-18         1       35           Valencia   \n",
       "\n",
       "    EquipoVisitante  golesLocal  golesVisitante       fecha   timestamp  \n",
       "44           Alaves           1               0  20/09/2017  1505858400  \n",
       "624         Almeria           2               1  20/01/2018  1516402800  \n",
       "374           Betis           3               2  19/05/2018  1526680800  \n",
       "436        Lorca FC           1               0  23/09/2017  1506117600  \n",
       "346           Eibar           0               0  29/04/2018  1524952800  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:95% !important; }</style>\"))\n",
    "\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('./data/Partidos_Futbol_Temporada_2017_18.csv', header=0)\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "\n",
    "### 1. Construir la clasificación final de la temporada (para 1ª o 2ª división)\n",
    "\n",
    "#### 1.1 Filtramos los partidos de 1ª o 2ª División"
   ]
  },
  {
   "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>idPartido</th>\n",
       "      <th>temporada</th>\n",
       "      <th>division</th>\n",
       "      <th>jornada</th>\n",
       "      <th>EquipoLocal</th>\n",
       "      <th>EquipoVisitante</th>\n",
       "      <th>golesLocal</th>\n",
       "      <th>golesVisitante</th>\n",
       "      <th>fecha</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>36333</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Celta de Vigo</td>\n",
       "      <td>Alaves</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10/09/2017</td>\n",
       "      <td>1504994400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>36960</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>Oviedo</td>\n",
       "      <td>Sporting de Gijon</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>04/02/2018</td>\n",
       "      <td>1517698800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>753</th>\n",
       "      <td>37059</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>2</td>\n",
       "      <td>34</td>\n",
       "      <td>Gimnastic de Tarragona</td>\n",
       "      <td>Valladolid</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>08/04/2018</td>\n",
       "      <td>1523138400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>36882</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>Cordoba</td>\n",
       "      <td>Rayo Vallecano</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>10/12/2017</td>\n",
       "      <td>1512860400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>36809</td>\n",
       "      <td>2017-18</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>Cadiz</td>\n",
       "      <td>Rayo Vallecano</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28/10/2017</td>\n",
       "      <td>1509141600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     idPartido temporada  division  jornada             EquipoLocal  \\\n",
       "27       36333   2017-18         1        3           Celta de Vigo   \n",
       "654      36960   2017-18         2       25                  Oviedo   \n",
       "753      37059   2017-18         2       34  Gimnastic de Tarragona   \n",
       "576      36882   2017-18         2       18                 Cordoba   \n",
       "503      36809   2017-18         2       12                   Cadiz   \n",
       "\n",
       "       EquipoVisitante  golesLocal  golesVisitante       fecha   timestamp  \n",
       "27              Alaves           1               0  10/09/2017  1504994400  \n",
       "654  Sporting de Gijon           2               1  04/02/2018  1517698800  \n",
       "753         Valladolid           1               0  08/04/2018  1523138400  \n",
       "576     Rayo Vallecano           2               2  10/12/2017  1512860400  \n",
       "503     Rayo Vallecano           0               0  28/10/2017  1509141600  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_division = df # TODO\n",
    "df_division.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.2 Creamos un dataframe (df_local) con los puntos que consigue el equipo local\n",
    "\n",
    "* Creamos una copia del DataFrame df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>EquipoLocal</th>\n",
       "      <th>jornada</th>\n",
       "      <th>golesLocal</th>\n",
       "      <th>golesVisitante</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Valencia</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Leganes</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Celta de Vigo</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Girona</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sevilla</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     EquipoLocal  jornada  golesLocal  golesVisitante\n",
       "0       Valencia        1           1               0\n",
       "1        Leganes        1           1               0\n",
       "2  Celta de Vigo        1           2               3\n",
       "3         Girona        1           2               2\n",
       "4        Sevilla        1           1               1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Copia\n",
    "df_local = df_division[['EquipoLocal', 'jornada', 'golesLocal', 'golesVisitante']].copy()\n",
    "\n",
    "# TODO\n",
    "\n",
    "df_local.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.3 Creamos un dataframe (df_visitante) con los puntos que consigue el equipo visitante\n",
    "\n",
    "\n",
    "* Creamos una copia del DataFrame df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>EquipoVisitante</th>\n",
       "      <th>jornada</th>\n",
       "      <th>golesLocal</th>\n",
       "      <th>golesVisitante</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>Leganes</td>\n",
       "      <td>26</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>830</th>\n",
       "      <td>Granada</td>\n",
       "      <td>41</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>Tenerife</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>Alcorcon</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>Rayo Vallecano</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    EquipoVisitante  jornada  golesLocal  golesVisitante\n",
       "252         Leganes       26           4               0\n",
       "830         Granada       41           2               1\n",
       "493        Tenerife       11           2               2\n",
       "397        Alcorcon        2           0               0\n",
       "525  Rayo Vallecano       14           3               2"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Copia\n",
    "df_visitante = df_division[['EquipoVisitante', 'jornada', 'golesLocal', 'golesVisitante']].copy()\n",
    "\n",
    "# TODO\n",
    "\n",
    "df_visitante.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.4 Unimos los dos dataframes (df_result)\n",
    "\n",
    "* Cambiamos los nombres de EquipoLocal y EquipoVisitante a Club para realizar la unión"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_local = df_local.rename(columns={'EquipoLocal': 'Club'})\n",
    "df_visitante = df_visitante.rename(columns={'EquipoVisitante': 'Club'})\n",
    "\n",
    "# TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.5 Agregamos por equipo y sumamos sus puntos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.6 Ordenamos el dataframe por puntos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "\n",
    "### 2. Construir una matriz de resultados de la temporada:\n",
    "\n",
    "\n",
    "#### 2.1 Filtramos los partidos de 1ª o 2ª División\n",
    "\n",
    "\n",
    "* Realizaremos el ejercicio con los partidos de primera división"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.2 Creamos una nueva columna que sea la concatenación de las columnas 'golesLocal' con 'golesVisitate'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.3 Realizamos el Pivot Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "*Este Notebook ha sido desarrollado por **Ricardo Moya García** y registrado en Safe Creative como ***Atribución-NoComercial-CompartirIgual***.*\n",
    "\n",
    "<img src=\"./imgs/CC_BY-NC-SA.png\" alt=\"CC BY-NC\">"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}