Module3class.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UBC\n",
    "## Programming in Python for DS\n",
    "### Week 3\n",
    "Instructor: Socorro Dominguez-Vidana"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [] Explain what **tidy data** is.\n",
    "- [] Use `.melt()` and `.pivot()` / `.pivot_table()`\n",
    "- [] Learn how to reset a data frame's index.\n",
    "- [] Combine data frames using `.merge()` and `.concat()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tidy Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The concept stems from a [paper](https://vita.had.co.nz/papers/tidy-data.pdf) written by Hadley Wickham in 2014.\n",
    "- We tidy our data to create a standard across multiple analysis tools. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rules for Tidy Data:\n",
    "\n",
    "* each row is a single observation,\n",
    "* each column is a single variable, and\n",
    "* each value is a single cell (i.e., its entry in the data frame is not shared with another value).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://datasciencebook.ca/_main_files/figure-html/02-obs-1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What to do when data is not tidy..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "Data sometimes comes in tables that are easy to read/make sense for humans.\n",
    "\n",
    "- However, they are not necessarily in the best format for computations to happen.\n",
    "\n",
    "Most common scenarios:\n",
    "- We need to convert from Wide to Long tables\n",
    "- We need to convert from Long to Wide tables\n",
    "\n",
    "## Wide to Long"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Goal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://datasciencebook.ca/img/wrangling/pivot_functions.001.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A common task:\n",
    "- Get data into a tidy format by combining values that are stored in separate columns, but are really part of the same variable, into one.\n",
    "- Data is often stored this way because this format is sometimes more intuitive for human readability.\n",
    "\n",
    "- The format above on the left would be difficult to use when performing data visualization or statistical analysis. To find the latest year, we would need to work with the column names; it would be more challenging if you wanted to find the value for the population for a given region for the latest year. In the right format, this becomes very straight forward.\n",
    "\n",
    "- We also do not know what the values on the left really represent: land? population? We solve it with the format on the right."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## From Long to Wide"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://datasciencebook.ca/img/wrangling/pivot_functions.003.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- In the previous figure, the table on the left is in an untidy, long format because:\n",
    "- The **values** of the variable **region** are stored as column names and not easily accessible.\n",
    "- The **mother tongue variable values** are spread across multiple columns, which will prevent us from doing any desired visualization or statistical tasks.\n",
    "- For instance, suppose we want to know the languages with the highest number of Canadians reporting it as their mother tongue among all five regions. A wide format of the table would be hard to use.\n",
    "- **Remember:** one of the criteria for tidy data is that each observation must be in a single row.\n",
    "- We can still get the information out from the \"wrong\" format. However, it would be more challenging. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to Manipulate Long and Wide Tables In Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Melt\n",
    "\n",
    "> wide to long format\n",
    "\n",
    "[Documentation](https://pandas.pydata.org/docs/reference/api/pandas.melt.html)\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_melt.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>name</th>\n",
       "      <th>mfr</th>\n",
       "      <th>calories</th>\n",
       "      <th>protein</th>\n",
       "      <th>fat</th>\n",
       "      <th>sodium</th>\n",
       "      <th>fiber</th>\n",
       "      <th>carbo</th>\n",
       "      <th>sugars</th>\n",
       "      <th>potass</th>\n",
       "      <th>vitamins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>70</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>130</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6</td>\n",
       "      <td>280</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100% Natural Bran</td>\n",
       "      <td>Q</td>\n",
       "      <td>120</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8</td>\n",
       "      <td>135</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>All-Bran</td>\n",
       "      <td>K</td>\n",
       "      <td>70</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>260</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5</td>\n",
       "      <td>320</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>All-Bran with Extra Fiber</td>\n",
       "      <td>K</td>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>140</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0</td>\n",
       "      <td>330</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Almond Delight</td>\n",
       "      <td>R</td>\n",
       "      <td>110</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        name mfr  calories  protein  fat  sodium  fiber   \n",
       "0                  100% Bran   N        70        4    1     130   10.0  \\\n",
       "1          100% Natural Bran   Q       120        3    5      15    2.0   \n",
       "2                   All-Bran   K        70        4    1     260    9.0   \n",
       "3  All-Bran with Extra Fiber   K        50        4    0     140   14.0   \n",
       "4             Almond Delight   R       110        2    2     200    1.0   \n",
       "\n",
       "   carbo  sugars  potass  vitamins  \n",
       "0    5.0       6     280        25  \n",
       "1    8.0       8     135         0  \n",
       "2    7.0       5     320        25  \n",
       "3    8.0       0     330        25  \n",
       "4   14.0       8       1        25  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "cereal = pd.read_csv('https://raw.githubusercontent.com/UBC-MDS/programming-in-python-for-data-science/master/data/cereal.csv')\n",
    "cereal = cereal.drop(labels=['type', 'shelf', 'weight', 'cups', 'rating'], axis=1)\n",
    "cereal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name\n",
       "100% Bran                    1\n",
       "Nutri-Grain Almond-Raisin    1\n",
       "Quaker Oat Squares           1\n",
       "Puffed Wheat                 1\n",
       "Puffed Rice                  1\n",
       "                            ..\n",
       "Frosted Flakes               1\n",
       "Froot Loops                  1\n",
       "Double Chex                  1\n",
       "Crispy Wheat & Raisins       1\n",
       "Wheaties Honey Gold          1\n",
       "Name: count, Length: 77, dtype: int64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cereal['name'].value_counts()"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>mfr</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>calories</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>protein</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>fat</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>sodium</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>fiber</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>carbo</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>sugars</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>potass</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616</th>\n",
       "      <td>100% Bran</td>\n",
       "      <td>N</td>\n",
       "      <td>vitamins</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name mfr  variable  value\n",
       "0    100% Bran   N  calories   70.0\n",
       "77   100% Bran   N   protein    4.0\n",
       "154  100% Bran   N       fat    1.0\n",
       "231  100% Bran   N    sodium  130.0\n",
       "308  100% Bran   N     fiber   10.0\n",
       "385  100% Bran   N     carbo    5.0\n",
       "462  100% Bran   N    sugars    6.0\n",
       "539  100% Bran   N    potass  280.0\n",
       "616  100% Bran   N  vitamins   25.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cereal_long = cereal.melt(id_vars=['name', 'mfr'])\n",
    "cereal_long\n",
    "cereal_long[cereal_long['name'] == '100% Bran']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot\n",
    "\n",
    "> long to wide\n",
    "\n",
    "[Documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot.html)\n",
    "\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_pivot.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>lego_info</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-1</td>\n",
       "      <td>Weetabix Castle</td>\n",
       "      <td>1970</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00-1</td>\n",
       "      <td>Weetabix Castle</td>\n",
       "      <td>1970</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00-2</td>\n",
       "      <td>Weetabix Promotional House 1</td>\n",
       "      <td>1976</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00-2</td>\n",
       "      <td>Weetabix Promotional House 1</td>\n",
       "      <td>1976</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00-3</td>\n",
       "      <td>Weetabix Promotional House 2</td>\n",
       "      <td>1976</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  set_num                          name  year  lego_info  value\n",
       "0    00-1               Weetabix Castle  1970   theme_id    414\n",
       "1    00-1               Weetabix Castle  1970  num_parts    471\n",
       "2    00-2  Weetabix Promotional House 1  1976  num_parts    147\n",
       "3    00-2  Weetabix Promotional House 1  1976   theme_id    413\n",
       "4    00-3  Weetabix Promotional House 2  1976  num_parts    149"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lego = pd.read_csv('https://raw.githubusercontent.com/UBC-MDS/programming-in-python-for-data-science/master/data/lego_untidy.csv')\n",
    "lego.head()"
   ]
  },
  {
   "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>lego_info</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>set_num</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00-1</th>\n",
       "      <td>471</td>\n",
       "      <td>414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-2</th>\n",
       "      <td>147</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-3</th>\n",
       "      <td>149</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-4</th>\n",
       "      <td>126</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-6</th>\n",
       "      <td>3</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tominifigs-1</th>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trucapam-1</th>\n",
       "      <td>71</td>\n",
       "      <td>598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tsuper-1</th>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vwkit-1</th>\n",
       "      <td>22</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wwgp1-1</th>\n",
       "      <td>3</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11673 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "lego_info     num_parts  theme_id\n",
       "set_num                          \n",
       "00-1                471       414\n",
       "00-2                147       413\n",
       "00-3                149       413\n",
       "00-4                126       413\n",
       "00-6                  3        67\n",
       "...                 ...       ...\n",
       "tominifigs-1          2        50\n",
       "trucapam-1           71       598\n",
       "tsuper-1              3        12\n",
       "vwkit-1              22       366\n",
       "wwgp1-1               3       476\n",
       "\n",
       "[11673 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lego_tidy = lego.pivot(index='set_num',\n",
    "                       columns='lego_info',\n",
    "                       values='value')\n",
    "lego_tidy"
   ]
  },
  {
   "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>lego_info</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00-1</th>\n",
       "      <th>Weetabix Castle</th>\n",
       "      <td>471</td>\n",
       "      <td>414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-2</th>\n",
       "      <th>Weetabix Promotional House 1</th>\n",
       "      <td>147</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-3</th>\n",
       "      <th>Weetabix Promotional House 2</th>\n",
       "      <td>149</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-4</th>\n",
       "      <th>Weetabix Promotional Windmill</th>\n",
       "      <td>126</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00-6</th>\n",
       "      <th>Special Offer</th>\n",
       "      <td>3</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "lego_info                              num_parts  theme_id\n",
       "set_num name                                              \n",
       "00-1    Weetabix Castle                      471       414\n",
       "00-2    Weetabix Promotional House 1         147       413\n",
       "00-3    Weetabix Promotional House 2         149       413\n",
       "00-4    Weetabix Promotional Windmill        126       413\n",
       "00-6    Special Offer                          3        67"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lego_tidy = lego.pivot(index=['set_num', 'name'],\n",
    "                       columns='lego_info',\n",
    "                       values='value')\n",
    "lego_tidy.head()"
   ]
  },
  {
   "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>lego_info</th>\n",
       "      <th>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-1</td>\n",
       "      <td>Weetabix Castle</td>\n",
       "      <td>471</td>\n",
       "      <td>414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00-2</td>\n",
       "      <td>Weetabix Promotional House 1</td>\n",
       "      <td>147</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00-3</td>\n",
       "      <td>Weetabix Promotional House 2</td>\n",
       "      <td>149</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00-4</td>\n",
       "      <td>Weetabix Promotional Windmill</td>\n",
       "      <td>126</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00-6</td>\n",
       "      <td>Special Offer</td>\n",
       "      <td>3</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11668</th>\n",
       "      <td>tominifigs-1</td>\n",
       "      <td>Town Minifig Packs 2-Pack</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11669</th>\n",
       "      <td>trucapam-1</td>\n",
       "      <td>Captain America Mosaic</td>\n",
       "      <td>71</td>\n",
       "      <td>598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11670</th>\n",
       "      <td>tsuper-1</td>\n",
       "      <td>Technic Super Set</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11671</th>\n",
       "      <td>vwkit-1</td>\n",
       "      <td>Volkswagen Kit</td>\n",
       "      <td>22</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11672</th>\n",
       "      <td>wwgp1-1</td>\n",
       "      <td>Wild West Limited Edition Gift Pack</td>\n",
       "      <td>3</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11673 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "lego_info       set_num                                 name  num_parts   \n",
       "0                  00-1                      Weetabix Castle        471  \\\n",
       "1                  00-2         Weetabix Promotional House 1        147   \n",
       "2                  00-3         Weetabix Promotional House 2        149   \n",
       "3                  00-4        Weetabix Promotional Windmill        126   \n",
       "4                  00-6                        Special Offer          3   \n",
       "...                 ...                                  ...        ...   \n",
       "11668      tominifigs-1            Town Minifig Packs 2-Pack          2   \n",
       "11669        trucapam-1               Captain America Mosaic         71   \n",
       "11670          tsuper-1                    Technic Super Set          3   \n",
       "11671           vwkit-1                       Volkswagen Kit         22   \n",
       "11672           wwgp1-1  Wild West Limited Edition Gift Pack          3   \n",
       "\n",
       "lego_info  theme_id  \n",
       "0               414  \n",
       "1               413  \n",
       "2               413  \n",
       "3               413  \n",
       "4                67  \n",
       "...             ...  \n",
       "11668            50  \n",
       "11669           598  \n",
       "11670            12  \n",
       "11671           366  \n",
       "11672           476  \n",
       "\n",
       "[11673 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lego_tidy2 = lego_tidy.reset_index()\n",
    "lego_tidy2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Index contains duplicate entries, cannot reshape",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[8], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m# What if I want to use only the name?\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m \u001b[43mlego\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mpivot\u001b[49m\u001b[43m(\u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mname\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m      3\u001b[0m \u001b[43m           \u001b[49m\u001b[43mcolumns\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mlego_info\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m      4\u001b[0m \u001b[43m           \u001b[49m\u001b[43mvalues\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mvalue\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/miniconda3/lib/python3.11/site-packages/pandas/core/frame.py:8419\u001b[0m, in \u001b[0;36mDataFrame.pivot\u001b[0;34m(self, columns, index, values)\u001b[0m\n\u001b[1;32m   8414\u001b[0m \u001b[38;5;129m@Substitution\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m   8415\u001b[0m \u001b[38;5;129m@Appender\u001b[39m(_shared_docs[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpivot\u001b[39m\u001b[38;5;124m\"\u001b[39m])\n\u001b[1;32m   8416\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mpivot\u001b[39m(\u001b[38;5;28mself\u001b[39m, \u001b[38;5;241m*\u001b[39m, columns, index\u001b[38;5;241m=\u001b[39mlib\u001b[38;5;241m.\u001b[39mNoDefault, values\u001b[38;5;241m=\u001b[39mlib\u001b[38;5;241m.\u001b[39mNoDefault) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m DataFrame:\n\u001b[1;32m   8417\u001b[0m     \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mcore\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mreshape\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mpivot\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m pivot\n\u001b[0;32m-> 8419\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpivot\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcolumns\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcolumns\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mvalues\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mvalues\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/miniconda3/lib/python3.11/site-packages/pandas/core/reshape/pivot.py:555\u001b[0m, in \u001b[0;36mpivot\u001b[0;34m(data, columns, index, values)\u001b[0m\n\u001b[1;32m    551\u001b[0m         indexed \u001b[38;5;241m=\u001b[39m data\u001b[38;5;241m.\u001b[39m_constructor_sliced(data[values]\u001b[38;5;241m.\u001b[39m_values, index\u001b[38;5;241m=\u001b[39mmultiindex)\n\u001b[1;32m    552\u001b[0m \u001b[38;5;66;03m# error: Argument 1 to \"unstack\" of \"DataFrame\" has incompatible type \"Union\u001b[39;00m\n\u001b[1;32m    553\u001b[0m \u001b[38;5;66;03m# [List[Any], ExtensionArray, ndarray[Any, Any], Index, Series]\"; expected\u001b[39;00m\n\u001b[1;32m    554\u001b[0m \u001b[38;5;66;03m# \"Hashable\"\u001b[39;00m\n\u001b[0;32m--> 555\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[43mindexed\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43munstack\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcolumns_listlike\u001b[49m\u001b[43m)\u001b[49m  \u001b[38;5;66;03m# type: ignore[arg-type]\u001b[39;00m\n\u001b[1;32m    556\u001b[0m result\u001b[38;5;241m.\u001b[39mindex\u001b[38;5;241m.\u001b[39mnames \u001b[38;5;241m=\u001b[39m [\n\u001b[1;32m    557\u001b[0m     name \u001b[38;5;28;01mif\u001b[39;00m name \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m lib\u001b[38;5;241m.\u001b[39mNoDefault \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;28;01mfor\u001b[39;00m name \u001b[38;5;129;01min\u001b[39;00m result\u001b[38;5;241m.\u001b[39mindex\u001b[38;5;241m.\u001b[39mnames\n\u001b[1;32m    558\u001b[0m ]\n\u001b[1;32m    560\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "File \u001b[0;32m~/miniconda3/lib/python3.11/site-packages/pandas/core/series.py:4314\u001b[0m, in \u001b[0;36mSeries.unstack\u001b[0;34m(self, level, fill_value)\u001b[0m\n\u001b[1;32m   4271\u001b[0m \u001b[38;5;250m\u001b[39m\u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   4272\u001b[0m \u001b[38;5;124;03mUnstack, also known as pivot, Series with MultiIndex to produce DataFrame.\u001b[39;00m\n\u001b[1;32m   4273\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   4310\u001b[0m \u001b[38;5;124;03mb    2    4\u001b[39;00m\n\u001b[1;32m   4311\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   4312\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mcore\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mreshape\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mreshape\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m unstack\n\u001b[0;32m-> 4314\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43munstack\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mlevel\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfill_value\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/miniconda3/lib/python3.11/site-packages/pandas/core/reshape/reshape.py:488\u001b[0m, in \u001b[0;36munstack\u001b[0;34m(obj, level, fill_value)\u001b[0m\n\u001b[1;32m    486\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_1d_only_ea_dtype(obj\u001b[38;5;241m.\u001b[39mdtype):\n\u001b[1;32m    487\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m _unstack_extension_series(obj, level, fill_value)\n\u001b[0;32m--> 488\u001b[0m unstacker \u001b[38;5;241m=\u001b[39m \u001b[43m_Unstacker\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    489\u001b[0m \u001b[43m    \u001b[49m\u001b[43mobj\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mlevel\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mlevel\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mconstructor\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mobj\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_constructor_expanddim\u001b[49m\n\u001b[1;32m    490\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    491\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m unstacker\u001b[38;5;241m.\u001b[39mget_result(\n\u001b[1;32m    492\u001b[0m     obj\u001b[38;5;241m.\u001b[39m_values, value_columns\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m, fill_value\u001b[38;5;241m=\u001b[39mfill_value\n\u001b[1;32m    493\u001b[0m )\n",
      "File \u001b[0;32m~/miniconda3/lib/python3.11/site-packages/pandas/core/reshape/reshape.py:136\u001b[0m, in \u001b[0;36m_Unstacker.__init__\u001b[0;34m(self, index, level, constructor)\u001b[0m\n\u001b[1;32m    128\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m num_cells \u001b[38;5;241m>\u001b[39m np\u001b[38;5;241m.\u001b[39miinfo(np\u001b[38;5;241m.\u001b[39mint32)\u001b[38;5;241m.\u001b[39mmax:\n\u001b[1;32m    129\u001b[0m     warnings\u001b[38;5;241m.\u001b[39mwarn(\n\u001b[1;32m    130\u001b[0m         \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mThe following operation may generate \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mnum_cells\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m cells \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m    131\u001b[0m         \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124min the resulting pandas object.\u001b[39m\u001b[38;5;124m\"\u001b[39m,\n\u001b[1;32m    132\u001b[0m         PerformanceWarning,\n\u001b[1;32m    133\u001b[0m         stacklevel\u001b[38;5;241m=\u001b[39mfind_stack_level(),\n\u001b[1;32m    134\u001b[0m     )\n\u001b[0;32m--> 136\u001b[0m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_make_selectors\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/miniconda3/lib/python3.11/site-packages/pandas/core/reshape/reshape.py:188\u001b[0m, in \u001b[0;36m_Unstacker._make_selectors\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    185\u001b[0m mask\u001b[38;5;241m.\u001b[39mput(selector, \u001b[38;5;28;01mTrue\u001b[39;00m)\n\u001b[1;32m    187\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m mask\u001b[38;5;241m.\u001b[39msum() \u001b[38;5;241m<\u001b[39m \u001b[38;5;28mlen\u001b[39m(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mindex):\n\u001b[0;32m--> 188\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mIndex contains duplicate entries, cannot reshape\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m    190\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mgroup_index \u001b[38;5;241m=\u001b[39m comp_index\n\u001b[1;32m    191\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmask \u001b[38;5;241m=\u001b[39m mask\n",
      "\u001b[0;31mValueError\u001b[0m: Index contains duplicate entries, cannot reshape"
     ]
    }
   ],
   "source": [
    "# What if I want to use only the name?\n",
    "lego.pivot(index='name',\n",
    "           columns='lego_info',\n",
    "           values='value')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What duplicates?\n",
    "\n",
    "### How do I find them?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name\n",
       "Basic Building Set        55\n",
       "Universal Building Set    32\n",
       "Helicopter                23\n",
       "Basic Set                 23\n",
       "Fire Station              14\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lego_tidy.reset_index().value_counts('name').head()"
   ]
  },
  {
   "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>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>lego_info</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>typing.Literal[&lt;no_default&gt;]</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>010-3</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>010-3</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>011-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>011-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>022-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15727</th>\n",
       "      <td>730-2</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1985</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15842</th>\n",
       "      <td>735-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1990</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15843</th>\n",
       "      <td>735-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1990</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15846</th>\n",
       "      <td>740-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1985</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15847</th>\n",
       "      <td>740-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1985</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>467</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             set_num                name  year  lego_info   \n",
       "typing.Literal[<no_default>]                                                \n",
       "30                             010-3  Basic Building Set  1968  num_parts  \\\n",
       "31                             010-3  Basic Building Set  1968   theme_id   \n",
       "32                             011-1  Basic Building Set  1968  num_parts   \n",
       "33                             011-1  Basic Building Set  1968   theme_id   \n",
       "34                             022-1  Basic Building Set  1968  num_parts   \n",
       "...                              ...                 ...   ...        ...   \n",
       "15727                          730-2  Basic Building Set  1985  num_parts   \n",
       "15842                          735-1  Basic Building Set  1990  num_parts   \n",
       "15843                          735-1  Basic Building Set  1990   theme_id   \n",
       "15846                          740-1  Basic Building Set  1985  num_parts   \n",
       "15847                          740-1  Basic Building Set  1985   theme_id   \n",
       "\n",
       "                              value  \n",
       "typing.Literal[<no_default>]         \n",
       "30                               77  \n",
       "31                              366  \n",
       "32                              145  \n",
       "33                              366  \n",
       "34                              110  \n",
       "...                             ...  \n",
       "15727                           432  \n",
       "15842                           538  \n",
       "15843                           467  \n",
       "15846                           530  \n",
       "15847                           467  \n",
       "\n",
       "[110 rows x 5 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset = lego[lego['name'] == 'Basic Building Set']\n",
    "bbset"
   ]
  },
  {
   "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>lego_info</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>010-3</th>\n",
       "      <th>Basic Building Set</th>\n",
       "      <td>77</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>011-1</th>\n",
       "      <th>Basic Building Set</th>\n",
       "      <td>145</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>022-1</th>\n",
       "      <th>Basic Building Set</th>\n",
       "      <td>110</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>033-2</th>\n",
       "      <th>Basic Building Set</th>\n",
       "      <td>177</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>044-1</th>\n",
       "      <th>Basic Building Set</th>\n",
       "      <td>225</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "lego_info                   num_parts  theme_id\n",
       "set_num name                                   \n",
       "010-3   Basic Building Set         77       366\n",
       "011-1   Basic Building Set        145       366\n",
       "022-1   Basic Building Set        110       366\n",
       "033-2   Basic Building Set        177       366\n",
       "044-1   Basic Building Set        225       366"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show with lego too\n",
    "bbset.pivot(index=['set_num','name'],\n",
    "                       columns='lego_info',\n",
    "                       values='value').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if I do not care about the `set_num`, just about the `name`?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Alternative: Use `pivot_table`"
   ]
  },
  {
   "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>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>lego_info</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>typing.Literal[&lt;no_default&gt;]</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>010-3</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>010-3</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>011-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>011-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>022-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             set_num                name  year  lego_info   \n",
       "typing.Literal[<no_default>]                                                \n",
       "30                             010-3  Basic Building Set  1968  num_parts  \\\n",
       "31                             010-3  Basic Building Set  1968   theme_id   \n",
       "32                             011-1  Basic Building Set  1968  num_parts   \n",
       "33                             011-1  Basic Building Set  1968   theme_id   \n",
       "34                             022-1  Basic Building Set  1968  num_parts   \n",
       "\n",
       "                              value  \n",
       "typing.Literal[<no_default>]         \n",
       "30                               77  \n",
       "31                              366  \n",
       "32                              145  \n",
       "33                              366  \n",
       "34                              110  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset.head()"
   ]
  },
  {
   "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>lego_info</th>\n",
       "      <th>name</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 stud Blue Storage Brick</td>\n",
       "      <td>0.0</td>\n",
       "      <td>501.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Scenery and Dagger Trap polybag</td>\n",
       "      <td>25.0</td>\n",
       "      <td>435.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Spectre</td>\n",
       "      <td>7.0</td>\n",
       "      <td>552.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>White Spaceman Key Chain</td>\n",
       "      <td>0.0</td>\n",
       "      <td>503.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>'Where Are My Pants?' Guy</td>\n",
       "      <td>6.0</td>\n",
       "      <td>549.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10365</th>\n",
       "      <td>{Red Race Car Number 3}</td>\n",
       "      <td>39.0</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10366</th>\n",
       "      <td>{Roadplates and Scenery}</td>\n",
       "      <td>85.0</td>\n",
       "      <td>533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10367</th>\n",
       "      <td>{Rock Saw Vehicle}</td>\n",
       "      <td>22.0</td>\n",
       "      <td>442.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10368</th>\n",
       "      <td>{Town Vehicles}</td>\n",
       "      <td>158.0</td>\n",
       "      <td>533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10369</th>\n",
       "      <td>{Yellow Cab}</td>\n",
       "      <td>44.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10370 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "lego_info                              name  num_parts  theme_id\n",
       "0                 1 stud Blue Storage Brick        0.0     501.0\n",
       "1           Scenery and Dagger Trap polybag       25.0     435.0\n",
       "2                                   Spectre        7.0     552.0\n",
       "3                  White Spaceman Key Chain        0.0     503.0\n",
       "4                 'Where Are My Pants?' Guy        6.0     549.0\n",
       "...                                     ...        ...       ...\n",
       "10365               {Red Race Car Number 3}       39.0      82.0\n",
       "10366              {Roadplates and Scenery}       85.0     533.0\n",
       "10367                    {Rock Saw Vehicle}       22.0     442.0\n",
       "10368                       {Town Vehicles}      158.0     533.0\n",
       "10369                          {Yellow Cab}       44.0      22.0\n",
       "\n",
       "[10370 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset2 = lego.pivot_table(index='name',\n",
    "           columns='lego_info',\n",
    "           values='value', aggfunc='mean').reset_index()\n",
    "bbset2"
   ]
  },
  {
   "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>lego_info</th>\n",
       "      <th>name</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1663</th>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>216.672727</td>\n",
       "      <td>454.363636</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "lego_info                name   num_parts    theme_id\n",
       "1663       Basic Building Set  216.672727  454.363636"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset2[bbset2['name']==\"Basic Building Set\"]"
   ]
  },
  {
   "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>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>lego_info</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>typing.Literal[&lt;no_default&gt;]</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>010-3</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>010-3</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>011-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>011-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>022-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1968</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15727</th>\n",
       "      <td>730-2</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1985</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15842</th>\n",
       "      <td>735-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1990</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15843</th>\n",
       "      <td>735-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1990</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15846</th>\n",
       "      <td>740-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1985</td>\n",
       "      <td>num_parts</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15847</th>\n",
       "      <td>740-1</td>\n",
       "      <td>Basic Building Set</td>\n",
       "      <td>1985</td>\n",
       "      <td>theme_id</td>\n",
       "      <td>467</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             set_num                name  year  lego_info   \n",
       "typing.Literal[<no_default>]                                                \n",
       "30                             010-3  Basic Building Set  1968  num_parts  \\\n",
       "31                             010-3  Basic Building Set  1968   theme_id   \n",
       "32                             011-1  Basic Building Set  1968  num_parts   \n",
       "33                             011-1  Basic Building Set  1968   theme_id   \n",
       "34                             022-1  Basic Building Set  1968  num_parts   \n",
       "...                              ...                 ...   ...        ...   \n",
       "15727                          730-2  Basic Building Set  1985  num_parts   \n",
       "15842                          735-1  Basic Building Set  1990  num_parts   \n",
       "15843                          735-1  Basic Building Set  1990   theme_id   \n",
       "15846                          740-1  Basic Building Set  1985  num_parts   \n",
       "15847                          740-1  Basic Building Set  1985   theme_id   \n",
       "\n",
       "                              value  \n",
       "typing.Literal[<no_default>]         \n",
       "30                               77  \n",
       "31                              366  \n",
       "32                              145  \n",
       "33                              366  \n",
       "34                              110  \n",
       "...                             ...  \n",
       "15727                           432  \n",
       "15842                           538  \n",
       "15843                           467  \n",
       "15846                           530  \n",
       "15847                           467  \n",
       "\n",
       "[110 rows x 5 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(110, 5)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "216.6727272727273"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset.pivot(index=['set_num', 'name'],\n",
    "                       columns='lego_info',\n",
    "                       values='value').reset_index()['num_parts'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "454.3636363636364"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbset.pivot(index=['set_num', 'name'],\n",
    "                       columns='lego_info',\n",
    "                       values='value').reset_index()['theme_id'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging Data Frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'animal': ['Falcon', 'Falcon',\n",
    "                              'Parrot', 'Parrot'],\n",
    "                   'id' : [1, 2, 1, 2],\n",
    "                   'speed': [390., 370., 24., 26.]})\n",
    "\n",
    "df_food = pd.DataFrame({'animal': ['Falcon', 'Falcon',\n",
    "                                   'Parrot', 'Parrot'],\n",
    "                        'id' : [1, 2, 1, 2],\n",
    "                        'food': ['kibble', 'meat', 'seeds', 'fruits']})"
   ]
  },
  {
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>390.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id  speed\n",
       "0  Falcon   1  390.0\n",
       "1  Falcon   2  370.0\n",
       "2  Parrot   1   24.0\n",
       "3  Parrot   2   26.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id    food\n",
       "0  Falcon   1  kibble\n",
       "1  Falcon   2    meat\n",
       "2  Parrot   1   seeds\n",
       "3  Parrot   2  fruits"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_food"
   ]
  },
  {
   "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>animal</th>\n",
       "      <th>id_x</th>\n",
       "      <th>speed</th>\n",
       "      <th>id_y</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>390.0</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>390.0</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370.0</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370.0</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id_x  speed  id_y    food\n",
       "0  Falcon     1  390.0     1  kibble\n",
       "1  Falcon     1  390.0     2    meat\n",
       "2  Falcon     2  370.0     1  kibble\n",
       "3  Falcon     2  370.0     2    meat\n",
       "4  Parrot     1   24.0     1   seeds\n",
       "5  Parrot     1   24.0     2  fruits\n",
       "6  Parrot     2   26.0     1   seeds\n",
       "7  Parrot     2   26.0     2  fruits"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.merge(df_food, on = 'animal')"
   ]
  },
  {
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>speed</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>390.0</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370.0</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24.0</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id  speed    food\n",
       "0  Falcon   1  390.0  kibble\n",
       "1  Falcon   2  370.0    meat\n",
       "2  Parrot   1   24.0   seeds\n",
       "3  Parrot   2   26.0  fruits"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.merge(df_food, on = ['animal', 'id'])"
   ]
  },
  {
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>speed</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>390.0</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370.0</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24.0</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id  speed    food\n",
       "0  Falcon   1  390.0  kibble\n",
       "1  Falcon   2  370.0    meat\n",
       "2  Parrot   1   24.0   seeds\n",
       "3  Parrot   2   26.0  fruits"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.merge(df_food, on = ['animal', 'id'], how='right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'Animal': ['Falcon', 'Falcon',\n",
    "                              'Parrot', 'Parrot'],\n",
    "                   'ID' : [1, 2, 1, 2],\n",
    "                   'Speed': [380, 370, 24, 26]})\n",
    "df_food = pd.DataFrame({'animal': ['Falcon', 'Falcon',\n",
    "                                   'Parrot', 'Parrot',\n",
    "                                   'Tiger'],\n",
    "                        'id' : [1, 2, 1, 2, 1],\n",
    "                        'food': ['kibble', 'meat', 'seeds', 'fruits', 'meat']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Animal</th>\n",
       "      <th>ID</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Animal  ID  Speed\n",
       "0  Falcon   1    380\n",
       "1  Falcon   2    370\n",
       "2  Parrot   1     24\n",
       "3  Parrot   2     26"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tiger</td>\n",
       "      <td>1</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id    food\n",
       "0  Falcon   1  kibble\n",
       "1  Falcon   2    meat\n",
       "2  Parrot   1   seeds\n",
       "3  Parrot   2  fruits\n",
       "4   Tiger   1    meat"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_food"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Animal</th>\n",
       "      <th>ID</th>\n",
       "      <th>Speed</th>\n",
       "      <th>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1.0</td>\n",
       "      <td>380.0</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>1</td>\n",
       "      <td>meat</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Animal   ID  Speed  animal  id    food      _merge\n",
       "0  Falcon  1.0  380.0  Falcon   1  kibble        both\n",
       "1  Falcon  2.0  370.0  Falcon   2    meat        both\n",
       "2  Parrot  1.0   24.0  Parrot   1   seeds        both\n",
       "3  Parrot  2.0   26.0  Parrot   2  fruits        both\n",
       "4     NaN  NaN    NaN   Tiger   1    meat  right_only"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df.merge(df_food, left_on = ['Animal', 'ID'], right_on = ['animal', 'id'], how='outer', indicator=True)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Animal</th>\n",
       "      <th>ID</th>\n",
       "      <th>Speed</th>\n",
       "      <th>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>1</td>\n",
       "      <td>meat</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Animal  ID  Speed animal  id  food      _merge\n",
       "4    NaN NaN    NaN  Tiger   1  meat  right_only"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[df2['_merge']=='right_only']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Difference with Concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Animal</th>\n",
       "      <th>ID</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Animal  ID  Speed\n",
       "0  Falcon   1    380\n",
       "1  Falcon   2    370\n",
       "2  Parrot   1     24\n",
       "3  Parrot   2     26"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tiger</td>\n",
       "      <td>1</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id    food\n",
       "0  Falcon   1  kibble\n",
       "1  Falcon   2    meat\n",
       "2  Parrot   1   seeds\n",
       "3  Parrot   2  fruits\n",
       "4   Tiger   1    meat"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_food"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Animal</th>\n",
       "      <th>ID</th>\n",
       "      <th>Speed</th>\n",
       "      <th>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1.0</td>\n",
       "      <td>380.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>1.0</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>2.0</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>1.0</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>2.0</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>1.0</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Animal   ID  Speed  animal   id    food\n",
       "0  Falcon  1.0  380.0     NaN  NaN     NaN\n",
       "1  Falcon  2.0  370.0     NaN  NaN     NaN\n",
       "2  Parrot  1.0   24.0     NaN  NaN     NaN\n",
       "3  Parrot  2.0   26.0     NaN  NaN     NaN\n",
       "0     NaN  NaN    NaN  Falcon  1.0  kibble\n",
       "1     NaN  NaN    NaN  Falcon  2.0    meat\n",
       "2     NaN  NaN    NaN  Parrot  1.0   seeds\n",
       "3     NaN  NaN    NaN  Parrot  2.0  fruits\n",
       "4     NaN  NaN    NaN   Tiger  1.0    meat"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.concat([df, df_food])\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    NaN\n",
       "0    1.0\n",
       "Name: id, dtype: float64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[0, 'id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tiger</td>\n",
       "      <td>1</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id    food\n",
       "0  Falcon   1  kibble\n",
       "1  Falcon   2    meat\n",
       "2  Parrot   1   seeds\n",
       "3  Parrot   2  fruits\n",
       "4   Tiger   1    meat"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_food"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>Animal</th>\n",
       "      <th>ID</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Animal  ID  Speed\n",
       "0  Falcon   1    380\n",
       "1  Falcon   2    370\n",
       "2  Parrot   1     24\n",
       "3  Parrot   2     26"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df_food.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tiger</td>\n",
       "      <td>1</td>\n",
       "      <td>meat</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id    food\n",
       "0  Falcon   1     380\n",
       "1  Falcon   2     370\n",
       "2  Parrot   1      24\n",
       "3  Parrot   2      26\n",
       "0  Falcon   1  kibble\n",
       "1  Falcon   2    meat\n",
       "2  Parrot   1   seeds\n",
       "3  Parrot   2  fruits\n",
       "4   Tiger   1    meat"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df, df_food])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenating sideways (\"merging\" by position?)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "      <th>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>kibble</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>1.0</td>\n",
       "      <td>380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>meat</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>2.0</td>\n",
       "      <td>370.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>seeds</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>1.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>fruits</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>2.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tiger</td>\n",
       "      <td>1</td>\n",
       "      <td>meat</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  id    food  animal   id   food\n",
       "0  Falcon   1  kibble  Falcon  1.0  380.0\n",
       "1  Falcon   2    meat  Falcon  2.0  370.0\n",
       "2  Parrot   1   seeds  Parrot  1.0   24.0\n",
       "3  Parrot   2  fruits  Parrot  2.0   26.0\n",
       "4   Tiger   1    meat     NaN  NaN    NaN"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.concat([df_food, df], axis = 1)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>animal</th>\n",
       "      <th>animal</th>\n",
       "      <th>id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Falcon</td>\n",
       "      <td>Falcon</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Parrot</td>\n",
       "      <td>Parrot</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tiger</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   animal  animal  id   id\n",
       "0  Falcon  Falcon   1  1.0\n",
       "1  Falcon  Falcon   2  2.0\n",
       "2  Parrot  Parrot   1  1.0\n",
       "3  Parrot  Parrot   2  2.0\n",
       "4   Tiger     NaN   1  NaN"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = df3[['animal', 'id']]\n",
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Disadvantages of Concat\n",
    "\n",
    "You may end up with duplicated column names - this will prevent you from some operations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4[df4['animal'] == 'Parrot']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}