10-DA_Pandas_combine.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 10. Combining information in Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often information is comming from different sources and it is necessary to combine it into one object. We are going to see the different ways in which information contained within separate Dataframes can be combined in a meaningful way."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.1 Concatenation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The simplest way we can combine two Dataframes is simply to \"paste\" them together:"
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              birth  death              city\n",
       "composer                                    \n",
       "Mahler         1860   1911           Kaliste\n",
       "Beethoven      1770   1827              Bonn\n",
       "Puccini        1858   1924           Lucques\n",
       "Shostakovich   1906   1975  Saint-Petersburg"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet1')\n",
    "composers1"
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Verdi</th>\n",
       "      <td>1813</td>\n",
       "      <td>1901</td>\n",
       "      <td>Roncole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dvorak</th>\n",
       "      <td>1841</td>\n",
       "      <td>1904</td>\n",
       "      <td>Nelahozeves</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schumann</th>\n",
       "      <td>1810</td>\n",
       "      <td>1856</td>\n",
       "      <td>Zwickau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stravinsky</th>\n",
       "      <td>1882</td>\n",
       "      <td>1971</td>\n",
       "      <td>Oranienbaum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            birth  death         city\n",
       "composer                             \n",
       "Verdi        1813   1901      Roncole\n",
       "Dvorak       1841   1904  Nelahozeves\n",
       "Schumann     1810   1856      Zwickau\n",
       "Stravinsky   1882   1971  Oranienbaum\n",
       "Mahler       1860   1911      Kaliste"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers2 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet3')\n",
    "composers2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To be concatenated, Dataframes need to be provided as a list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_composers = pd.concat([composers1,composers2])"
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Verdi</th>\n",
       "      <td>1813</td>\n",
       "      <td>1901</td>\n",
       "      <td>Roncole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dvorak</th>\n",
       "      <td>1841</td>\n",
       "      <td>1904</td>\n",
       "      <td>Nelahozeves</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schumann</th>\n",
       "      <td>1810</td>\n",
       "      <td>1856</td>\n",
       "      <td>Zwickau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stravinsky</th>\n",
       "      <td>1882</td>\n",
       "      <td>1971</td>\n",
       "      <td>Oranienbaum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              birth  death              city\n",
       "composer                                    \n",
       "Mahler         1860   1911           Kaliste\n",
       "Beethoven      1770   1827              Bonn\n",
       "Puccini        1858   1924           Lucques\n",
       "Shostakovich   1906   1975  Saint-Petersburg\n",
       "Verdi          1813   1901           Roncole\n",
       "Dvorak         1841   1904       Nelahozeves\n",
       "Schumann       1810   1856           Zwickau\n",
       "Stravinsky     1882   1971       Oranienbaum\n",
       "Mahler         1860   1911           Kaliste"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_composers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One potential problem is that two tables contain duplicated information:"
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          birth  death     city\n",
       "composer                       \n",
       "Mahler     1860   1911  Kaliste\n",
       "Mahler     1860   1911  Kaliste"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_composers.loc['Mahler']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is very easy to get rid of it using.```duplicated()``` gives us a boolean series of duplications and we can just selected non-duplicated rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "composer\n",
       "Mahler          False\n",
       "Beethoven       False\n",
       "Puccini         False\n",
       "Shostakovich    False\n",
       "Verdi           False\n",
       "Dvorak          False\n",
       "Schumann        False\n",
       "Stravinsky      False\n",
       "Mahler           True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_composers.duplicated()"
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Verdi</th>\n",
       "      <td>1813</td>\n",
       "      <td>1901</td>\n",
       "      <td>Roncole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dvorak</th>\n",
       "      <td>1841</td>\n",
       "      <td>1904</td>\n",
       "      <td>Nelahozeves</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schumann</th>\n",
       "      <td>1810</td>\n",
       "      <td>1856</td>\n",
       "      <td>Zwickau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stravinsky</th>\n",
       "      <td>1882</td>\n",
       "      <td>1971</td>\n",
       "      <td>Oranienbaum</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              birth  death              city\n",
       "composer                                    \n",
       "Mahler         1860   1911           Kaliste\n",
       "Beethoven      1770   1827              Bonn\n",
       "Puccini        1858   1924           Lucques\n",
       "Shostakovich   1906   1975  Saint-Petersburg\n",
       "Verdi          1813   1901           Roncole\n",
       "Dvorak         1841   1904       Nelahozeves\n",
       "Schumann       1810   1856           Zwickau\n",
       "Stravinsky     1882   1971       Oranienbaum"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_composers[~all_composers.duplicated()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.2 Joining two tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An other classical case is that of two list with similar index but containing different information, e.g. "
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              birth  death              city\n",
       "composer                                    \n",
       "Mahler         1860   1911           Kaliste\n",
       "Beethoven      1770   1827              Bonn\n",
       "Puccini        1858   1924           Lucques\n",
       "Shostakovich   1906   1975  Saint-Petersburg"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet1')\n",
    "composers1"
   ]
  },
  {
   "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>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brahms</th>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           first name\n",
       "composer             \n",
       "Mahler         Gustav\n",
       "Beethoven  Ludwig van\n",
       "Puccini       Giacomo\n",
       "Brahms       Johannes"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers2 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet4')\n",
    "composers2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we we use again simple concatenation, this doesn't help us much. We just end up with a large matrix with lots of NaN's:"
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860.0</td>\n",
       "      <td>1911.0</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770.0</td>\n",
       "      <td>1827.0</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858.0</td>\n",
       "      <td>1924.0</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906.0</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brahms</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               birth   death              city  first name\n",
       "composer                                                  \n",
       "Mahler        1860.0  1911.0           Kaliste         NaN\n",
       "Beethoven     1770.0  1827.0              Bonn         NaN\n",
       "Puccini       1858.0  1924.0           Lucques         NaN\n",
       "Shostakovich  1906.0  1975.0  Saint-Petersburg         NaN\n",
       "Mahler           NaN     NaN               NaN      Gustav\n",
       "Beethoven        NaN     NaN               NaN  Ludwig van\n",
       "Puccini          NaN     NaN               NaN     Giacomo\n",
       "Brahms           NaN     NaN               NaN    Johannes"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([composers1, composers2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The better way of doing this is to **join** the tables. This is a classical database concept available in Pandas.\n",
    "\n",
    "```join()``` operates on two tables: the first one is the \"left\" table which uses ```join()``` as a method. The other table is the \"right\" one.\n",
    "\n",
    "Let's try the default join settings:"
   ]
  },
  {
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              birth  death              city\n",
       "composer                                    \n",
       "Mahler         1860   1911           Kaliste\n",
       "Beethoven      1770   1827              Bonn\n",
       "Puccini        1858   1924           Lucques\n",
       "Shostakovich   1906   1975  Saint-Petersburg"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brahms</th>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           first name\n",
       "composer             \n",
       "Mahler         Gustav\n",
       "Beethoven  Ludwig van\n",
       "Puccini       Giacomo\n",
       "Brahms       Johannes"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              birth  death              city  first name\n",
       "composer                                                \n",
       "Mahler         1860   1911           Kaliste      Gustav\n",
       "Beethoven      1770   1827              Bonn  Ludwig van\n",
       "Puccini        1858   1924           Lucques     Giacomo\n",
       "Shostakovich   1906   1975  Saint-Petersburg         NaN"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1.join(composers2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that Pandas was smart enough to notice that the two tables had a index name and used it to combine the tables. We also see that one element from the second table (Brahms) is missing. The reason for this is the way indices not present in both tables are handled. There are four ways of doing this with two tables called here the \"left\" and \"right\" table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.2.1. Join left"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here \"left\" and \"right\" just represent two Dataframes that should be merged. They have a common index, but not necessarily the same items. For example here Shostakovich is missing in the second table, while Brahms is missing in the first one. When using the \"right\" join, we use the first Dataframe as basis and only use the indices that appear there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              birth  death              city  first name\n",
       "composer                                                \n",
       "Mahler         1860   1911           Kaliste      Gustav\n",
       "Beethoven      1770   1827              Bonn  Ludwig van\n",
       "Puccini        1858   1924           Lucques     Giacomo\n",
       "Shostakovich   1906   1975  Saint-Petersburg         NaN"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1.join(composers2, how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hence Brahms is left out."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.2.2. Join right"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do the the opposite and use the indices of the second Dataframe as basis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860.0</td>\n",
       "      <td>1911.0</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770.0</td>\n",
       "      <td>1827.0</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858.0</td>\n",
       "      <td>1924.0</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brahms</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            birth   death     city  first name\n",
       "composer                                      \n",
       "Mahler     1860.0  1911.0  Kaliste      Gustav\n",
       "Beethoven  1770.0  1827.0     Bonn  Ludwig van\n",
       "Puccini    1858.0  1924.0  Lucques     Giacomo\n",
       "Brahms        NaN     NaN      NaN    Johannes"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1.join(composers2, how = 'right')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we have Brahms but not Shostakovich."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.2.3. Inner, outer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we can just say that we want to recover eihter only the items that appaer in both Dataframes (inner, like in a Venn diagram) or all the items (outer)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           birth  death     city  first name\n",
       "composer                                    \n",
       "Mahler      1860   1911  Kaliste      Gustav\n",
       "Beethoven   1770   1827     Bonn  Ludwig van\n",
       "Puccini     1858   1924  Lucques     Giacomo"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1.join(composers2, how = 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>composer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Beethoven</th>\n",
       "      <td>1770.0</td>\n",
       "      <td>1827.0</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brahms</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mahler</th>\n",
       "      <td>1860.0</td>\n",
       "      <td>1911.0</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puccini</th>\n",
       "      <td>1858.0</td>\n",
       "      <td>1924.0</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shostakovich</th>\n",
       "      <td>1906.0</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               birth   death              city  first name\n",
       "composer                                                  \n",
       "Beethoven     1770.0  1827.0              Bonn  Ludwig van\n",
       "Brahms           NaN     NaN               NaN    Johannes\n",
       "Mahler        1860.0  1911.0           Kaliste      Gustav\n",
       "Puccini       1858.0  1924.0           Lucques     Giacomo\n",
       "Shostakovich  1906.0  1975.0  Saint-Petersburg         NaN"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1.join(composers2, how = 'outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.3.4 Joining on columns : merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above we have used ```join``` to join based on indices. However sometimes tables don't have the same indices but similar contents that we want to merge. For example let's imagine whe have the two Dataframes below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "composers1 = pd.read_excel('Data/composers.xlsx', sheet_name='Sheet1')\n",
    "composers2 = pd.read_excel('Data/composers.xlsx', sheet_name='Sheet6')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>composer</th>\n",
       "      <th>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mahler</td>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beethoven</td>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Puccini</td>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shostakovich</td>\n",
       "      <td>1906</td>\n",
       "      <td>1975</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       composer  birth  death              city\n",
       "0        Mahler   1860   1911           Kaliste\n",
       "1     Beethoven   1770   1827              Bonn\n",
       "2       Puccini   1858   1924           Lucques\n",
       "3  Shostakovich   1906   1975  Saint-Petersburg"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>last name</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Puccini</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beethoven</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brahms</td>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mahler</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   last name  first name\n",
       "0    Puccini     Giacomo\n",
       "1  Beethoven  Ludwig van\n",
       "2     Brahms    Johannes\n",
       "3     Mahler      Gustav"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composers2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The indices don't match and are not the composer name. In addition the columns containing the composer names have different labels. Here we can use ```merge()```and specify which columns we want to use for merging, and what type of merging we need (inner, left etc.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>composer</th>\n",
       "      <th>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>last name</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mahler</td>\n",
       "      <td>1860</td>\n",
       "      <td>1911</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Mahler</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beethoven</td>\n",
       "      <td>1770</td>\n",
       "      <td>1827</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Beethoven</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Puccini</td>\n",
       "      <td>1858</td>\n",
       "      <td>1924</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Puccini</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    composer  birth  death     city  last name  first name\n",
       "0     Mahler   1860   1911  Kaliste     Mahler      Gustav\n",
       "1  Beethoven   1770   1827     Bonn  Beethoven  Ludwig van\n",
       "2    Puccini   1858   1924  Lucques    Puccini     Giacomo"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(composers1, composers2, left_on='composer', right_on='last name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again we can use another variety of join than the default inner:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>composer</th>\n",
       "      <th>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>last name</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mahler</td>\n",
       "      <td>1860.0</td>\n",
       "      <td>1911.0</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Mahler</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beethoven</td>\n",
       "      <td>1770.0</td>\n",
       "      <td>1827.0</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Beethoven</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Puccini</td>\n",
       "      <td>1858.0</td>\n",
       "      <td>1924.0</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Puccini</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shostakovich</td>\n",
       "      <td>1906.0</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brahms</td>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       composer   birth   death              city  last name  first name\n",
       "0        Mahler  1860.0  1911.0           Kaliste     Mahler      Gustav\n",
       "1     Beethoven  1770.0  1827.0              Bonn  Beethoven  Ludwig van\n",
       "2       Puccini  1858.0  1924.0           Lucques    Puccini     Giacomo\n",
       "3  Shostakovich  1906.0  1975.0  Saint-Petersburg        NaN         NaN\n",
       "4           NaN     NaN     NaN               NaN     Brahms    Johannes"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(composers1, composers2, left_on='composer', right_on='last name',how = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>composer</th>\n",
       "      <th>birth</th>\n",
       "      <th>death</th>\n",
       "      <th>city</th>\n",
       "      <th>last name</th>\n",
       "      <th>first name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mahler</td>\n",
       "      <td>1860.0</td>\n",
       "      <td>1911.0</td>\n",
       "      <td>Kaliste</td>\n",
       "      <td>Mahler</td>\n",
       "      <td>Gustav</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beethoven</td>\n",
       "      <td>1770.0</td>\n",
       "      <td>1827.0</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Beethoven</td>\n",
       "      <td>Ludwig van</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Puccini</td>\n",
       "      <td>1858.0</td>\n",
       "      <td>1924.0</td>\n",
       "      <td>Lucques</td>\n",
       "      <td>Puccini</td>\n",
       "      <td>Giacomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brahms</td>\n",
       "      <td>Johannes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    composer   birth   death     city  last name  first name\n",
       "0     Mahler  1860.0  1911.0  Kaliste     Mahler      Gustav\n",
       "1  Beethoven  1770.0  1827.0     Bonn  Beethoven  Ludwig van\n",
       "2    Puccini  1858.0  1924.0  Lucques    Puccini     Giacomo\n",
       "3        NaN     NaN     NaN      NaN     Brahms    Johannes"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(composers1, composers2, left_on='composer', right_on='last name',how = 'right')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}