07_Pandas_Dataframe_Join_Union.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7 - Pandas Dataframe - Join y Uniones\n",
    "\n",
    "\n",
    "* En este Notebook vamos a ver las operaciones de ***Join y Uniones***:\n",
    "\n",
    "    7. Joins\n",
    "    8. Uniones\n",
    "\n",
    "\n",
    "* Para más información ver el siguiente enlace:\n",
    "    - https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html\n",
    "\n",
    "\n",
    "<hr>\n",
    "\n",
    "\n",
    "## Funciones para el Análisis Exploratorio de Datos - Parte III\n",
    "\n",
    "\n",
    "* Veamos a continuación una serie de ejemplos de cada una de estas operaciones y/o funciones, usando los datos de los ficheros:\n",
    "\n",
    "    - *vehicles.txt*\n",
    "    - *users.txt*\n",
    "    \n",
    "### Importamos la librería de Pandas:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pasamos los datos de los Ficheros a un DataFrame:\n",
    "\n",
    "\n",
    "* Fichero ***vehicles.txt***: Es un fichero de texto plano en formato CSV.:\n",
    "\n",
    "\n",
    "* Fichero ***users.txt***: Fichero de texto plano con las mismas características que el fichero de vehicles.txt."
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>brand</th>\n",
       "      <th>model</th>\n",
       "      <th>kilometers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Megane</td>\n",
       "      <td>23000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Leon</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Opel</td>\n",
       "      <td>Corsa</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>34000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Cordoba</td>\n",
       "      <td>99999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>88888</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id    brand    model  kilometers\n",
       "0        1  Renault     Clio          10\n",
       "1        2  Renault   Megane       23000\n",
       "2        3     Seat    Ibiza        9000\n",
       "3        2     Seat     Leon          20\n",
       "4        5     Opel    Corsa         999\n",
       "5        4  Renault     Clio       34000\n",
       "6        1     Seat    Ibiza        2000\n",
       "7        2     Seat  Cordoba       99999\n",
       "8        3  Renault     Clio       88888"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_vehicles = pd.read_csv('./data/ejemplo_EDA/vehicles.txt', header=0)\n",
    "df_vehicles"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Don Pepito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Don Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Fofo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Miliki</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Fofito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Gaby</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id        name\n",
       "0        1  Don Pepito\n",
       "1        2    Don Jose\n",
       "2        3        Fofo\n",
       "3        4      Miliki\n",
       "4        5      Fofito\n",
       "5        6        Gaby"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_users = pd.read_csv('./data/ejemplo_EDA/users.txt', header=0)\n",
    "df_users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "\n",
    "## 7. Joins (Merge)\n",
    "\n",
    "\n",
    "* La operación Join (o Merge en Pandas) permite ***combinar registros de 2 o más DataFrames*** (o tablas) a partir de una o más ***claves comunes***.\n",
    "\n",
    "\n",
    "* La ***sintaxis*** para realizar los merges seria:\n",
    "\n",
    "\n",
    "```python\n",
    "pd.merge(left_data_frame, right_data_frame, how='tipo_de_join', on='id')\n",
    "```\n",
    "\n",
    "\n",
    "* O en caso de que los ***ids*** tuviesen ***nombres diferentes*** sería:\n",
    "\n",
    "\n",
    "```python\n",
    "pd.merge(left_data_frame, right_data_frame, how='tipo_de_join', left_on='id_left', right_on='id_right')\n",
    "```\n",
    "\n",
    "\n",
    "* Siendo los tipos de Join permitidos:\n",
    "    - ***inner***\n",
    "    - ***left***\n",
    "    - ***right***\n",
    "    - ***outer***\n",
    "\n",
    "\n",
    "* Veamos a continuación como se realizan y los resultados que arrojan los diferentes tipos de Joins, utilizando los siguientes dos DataFrames de ejemplo:\n",
    "\n",
    "\n",
    "<img src=\"./imgs/04_02_join.png\" style=\"width: 300px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_x = pd.DataFrame({'id_x': ['a', 'b'], 'long': ['aaaa', 'bbb']})\n",
    "df_y = pd.DataFrame({'id_y': ['b', 'c'], 'sort': ['bb', 'cc']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7.1 INNER JOIN\n",
    "\n",
    "\n",
    "* ***INNER JOIN*** selecciona todas las filas de los dos DataFrames siempre y cuando haya ***una coincidencia entre las columnas en ambos DataFrames***.\n",
    "\n",
    "\n",
    "<img src=\"./imgs/04_03_inner.png\" style=\"width: 700px;\"/>\n"
   ]
  },
  {
   "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>id_x</th>\n",
       "      <th>long</th>\n",
       "      <th>id_y</th>\n",
       "      <th>sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>bbb</td>\n",
       "      <td>b</td>\n",
       "      <td>bb</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id_x long id_y sort\n",
       "0    b  bbb    b   bb"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df_x, df_y, how='inner', left_on='id_x', right_on='id_y')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7.2 LEFT JOIN\n",
    "\n",
    "\n",
    "* ***LEFT JOIN*** mantiene ***todas las filas del DataFrame de la izquierda*** (left). ***Las filas del DataFrame de la derecha (right) se mostrarán si hay una coincidencia con los de la izquierda***. \n",
    "\n",
    "\n",
    "* Si existen valores en el DataFrame de la izquierda pero no en el de la derecha, éste mostrará NaN (***N***ot ***a*** ***N***umber).\n",
    "\n",
    "\n",
    "<img src=\"./imgs/04_04_left.png\" style=\"width: 700px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>id_x</th>\n",
       "      <th>long</th>\n",
       "      <th>id_y</th>\n",
       "      <th>sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>aaaa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>bbb</td>\n",
       "      <td>b</td>\n",
       "      <td>bb</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id_x  long id_y sort\n",
       "0    a  aaaa  NaN  NaN\n",
       "1    b   bbb    b   bb"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df_x, df_y, how='left', left_on='id_x', right_on='id_y')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7.3 RIGHT JOIN\n",
    "\n",
    "\n",
    "* ***RIGHT JOIN*** mantiene ***todas las filas del DataFrame de la derecha*** (right). ***Las filas del DataFrame de la Izquierda (left) se mostrarán si hay una coincidencia con las de la derecha***. \n",
    "\n",
    "\n",
    "* Si existen valores en el DataFrame de la derecha pero no en el de la izquierda, éste mostrará NaN (***N***ot ***a*** ***N***umber).\n",
    "\n",
    "\n",
    "<img src=\"./imgs/04_05_right.png\" style=\"width: 700px;\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>id_x</th>\n",
       "      <th>long</th>\n",
       "      <th>id_y</th>\n",
       "      <th>sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>bbb</td>\n",
       "      <td>b</td>\n",
       "      <td>bb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c</td>\n",
       "      <td>cc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id_x long id_y sort\n",
       "0    b  bbb    b   bb\n",
       "1  NaN  NaN    c   cc"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df_x, df_y, how='right', left_on='id_x', right_on='id_y')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7.4 OUTER JOIN\n",
    "\n",
    "\n",
    "* ***OUTER JOIN*** o (FULL OUTER JOIN) devuelve todas las filas del DataFrame de la izquierda (left) y del de la derecha (right). ***Combina el resultado de los joins LEFT y RIGHT***. \n",
    "\n",
    "\n",
    "* Aparecerá NaN (***N***ot ***a*** ***N***umber) en cada uno de los DataFrames alternativamente cuando no haya una coincidencia.\n",
    "\n",
    "\n",
    "<img src=\"./imgs/04_06_outer.png\" style=\"width: 700px;\"/>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>id_x</th>\n",
       "      <th>long</th>\n",
       "      <th>id_y</th>\n",
       "      <th>sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>aaaa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>bbb</td>\n",
       "      <td>b</td>\n",
       "      <td>bb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c</td>\n",
       "      <td>cc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id_x  long id_y sort\n",
       "0    a  aaaa  NaN  NaN\n",
       "1    b   bbb    b   bb\n",
       "2  NaN   NaN    c   cc"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df_x, df_y, how='outer', left_on='id_x', right_on='id_y')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7.5 Ejemplo \"real\" utilizando la operación JOIN\n",
    "\n",
    "\n",
    "* Dados los dos DataFrames *'df_vehicles'* y *'df_users'* que tienen como clave común el campo ***'user_id'*** vamos a:\n",
    "\n",
    "    1. Identificar por el nombre a quien pertenece cada vehículo (inner join)\n",
    "    2. Mostrar para todos los usuarios registrados en el DataFrame df_users, cuales son sus coches si los tienen (left join).\n",
    "    \n",
    "    \n",
    "<img src=\"./imgs/04_07_ejemplo_join.png\" style=\"width: 400px;\"/>\n",
    "\n",
    "\n",
    "#### 7.5.1. Identificar por el nombre a quien pertenece cada vehículo (inner join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>user_id</th>\n",
       "      <th>brand</th>\n",
       "      <th>model</th>\n",
       "      <th>kilometers</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>10</td>\n",
       "      <td>Don Pepito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>2000</td>\n",
       "      <td>Don Pepito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Megane</td>\n",
       "      <td>23000</td>\n",
       "      <td>Don Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Leon</td>\n",
       "      <td>20</td>\n",
       "      <td>Don Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Cordoba</td>\n",
       "      <td>99999</td>\n",
       "      <td>Don Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>9000</td>\n",
       "      <td>Fofo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>88888</td>\n",
       "      <td>Fofo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>Opel</td>\n",
       "      <td>Corsa</td>\n",
       "      <td>999</td>\n",
       "      <td>Fofito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>34000</td>\n",
       "      <td>Miliki</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id    brand    model  kilometers        name\n",
       "0        1  Renault     Clio          10  Don Pepito\n",
       "1        1     Seat    Ibiza        2000  Don Pepito\n",
       "2        2  Renault   Megane       23000    Don Jose\n",
       "3        2     Seat     Leon          20    Don Jose\n",
       "4        2     Seat  Cordoba       99999    Don Jose\n",
       "5        3     Seat    Ibiza        9000        Fofo\n",
       "6        3  Renault     Clio       88888        Fofo\n",
       "7        5     Opel    Corsa         999      Fofito\n",
       "8        4  Renault     Clio       34000      Miliki"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df_vehicles, df_users, how='inner', on=['user_id'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 7.5.2 Mostrar para todos los usuarios registrados en el DataFrame df_users, cuales son sus coches si los tienen (left join)\n",
    "\n",
    "\n",
    "* Se puede observar que el usuario con identifcador '6' no tiene ningún coche, por eso el valor de sus columnas (del DataFrame df_vehicles) tienen valor nulo (NaN)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>user_id</th>\n",
       "      <th>name</th>\n",
       "      <th>brand</th>\n",
       "      <th>model</th>\n",
       "      <th>kilometers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Don Pepito</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Don Pepito</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>2000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Don Jose</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Megane</td>\n",
       "      <td>23000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Don Jose</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Leon</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Don Jose</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Cordoba</td>\n",
       "      <td>99999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>Fofo</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>9000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>Fofo</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>88888.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>Miliki</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>34000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>Fofito</td>\n",
       "      <td>Opel</td>\n",
       "      <td>Corsa</td>\n",
       "      <td>999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6</td>\n",
       "      <td>Gaby</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id        name    brand    model  kilometers\n",
       "0        1  Don Pepito  Renault     Clio        10.0\n",
       "1        1  Don Pepito     Seat    Ibiza      2000.0\n",
       "2        2    Don Jose  Renault   Megane     23000.0\n",
       "3        2    Don Jose     Seat     Leon        20.0\n",
       "4        2    Don Jose     Seat  Cordoba     99999.0\n",
       "5        3        Fofo     Seat    Ibiza      9000.0\n",
       "6        3        Fofo  Renault     Clio     88888.0\n",
       "7        4      Miliki  Renault     Clio     34000.0\n",
       "8        5      Fofito     Opel    Corsa       999.0\n",
       "9        6        Gaby      NaN      NaN         NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df_users, df_vehicles, how='left', on=['user_id'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "\n",
    "## 8. Uniones (Concat)\n",
    "\n",
    "\n",
    "* Esta operación ***Union*** (o Concat en Pandas) permite ***unir dos o más DataFrames (de forma vertical)***.\n",
    "\n",
    "\n",
    "* La ***sintaxis*** para realizar las uniones seria:\n",
    "\n",
    "\n",
    "```python\n",
    "pd.concat([df_1, df_2,...,df_n], sort=Boolean)\n",
    "```\n",
    "\n",
    "\n",
    "* Se reciben dos parámetros que son:\n",
    "    1. Una lista de DataFrames a unir\n",
    "    2. sort: Puede tener valores True o False y nos permite indicar si ordena las columnas por el orden alfabetico de sus nombres.\n",
    "\n",
    "\n",
    "* Veamos a continuación un ejemplo de como unir dos DataFrames *'df_vehicles'* y un segundo DataFrame *'df_coches'* que definimos a continuación:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>user_id</th>\n",
       "      <th>brand</th>\n",
       "      <th>model</th>\n",
       "      <th>kilometers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>Ferrari</td>\n",
       "      <td>Testarossa</td>\n",
       "      <td>2376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Aston Martin</td>\n",
       "      <td>db9</td>\n",
       "      <td>6584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id         brand       model  kilometers\n",
       "0        6       Ferrari  Testarossa        2376\n",
       "1        6  Aston Martin         db9        6584"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_coches = pd.DataFrame({'user_id': [6, 6], \n",
    "                         'brand': ['Ferrari', 'Aston Martin'],\n",
    "                         'model': ['Testarossa', 'db9'],\n",
    "                         'kilometers': [2376, 6584]})\n",
    "df_coches"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* La unión de estos dos DataFrames seria:\n",
    "\n",
    "\n",
    "<img src=\"./imgs/04_08_union.png\" style=\"width: 600px;\"/>\n",
    "\n",
    "\n",
    "* A continuación vemos como unir los dos DataFrames:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>user_id</th>\n",
       "      <th>brand</th>\n",
       "      <th>model</th>\n",
       "      <th>kilometers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Megane</td>\n",
       "      <td>23000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Leon</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Opel</td>\n",
       "      <td>Corsa</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>34000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Ibiza</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>Seat</td>\n",
       "      <td>Cordoba</td>\n",
       "      <td>99999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>Renault</td>\n",
       "      <td>Clio</td>\n",
       "      <td>88888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>Ferrari</td>\n",
       "      <td>Testarossa</td>\n",
       "      <td>2376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Aston Martin</td>\n",
       "      <td>db9</td>\n",
       "      <td>6584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id         brand       model  kilometers\n",
       "0        1       Renault        Clio          10\n",
       "1        2       Renault      Megane       23000\n",
       "2        3          Seat       Ibiza        9000\n",
       "3        2          Seat        Leon          20\n",
       "4        5          Opel       Corsa         999\n",
       "5        4       Renault        Clio       34000\n",
       "6        1          Seat       Ibiza        2000\n",
       "7        2          Seat     Cordoba       99999\n",
       "8        3       Renault        Clio       88888\n",
       "0        6       Ferrari  Testarossa        2376\n",
       "1        6  Aston Martin         db9        6584"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df_vehicles, df_coches], sort=False)"
   ]
  },
  {
   "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
}