Pandas 5 Tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "8MoyNBbAME22"
   },
   "source": [
    "# Concatenating, Merging and Joining DataFrames in Pandas\n",
    "\n",
    "1. Concat\n",
    "2. Append\n",
    "3. Merge\n",
    "4. Join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "B3XL3a0lQCOW"
   },
   "source": [
    "### Let's undestand concatenating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 191
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1089,
     "status": "ok",
     "timestamp": 1573518281609,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "FZ27093cMFyy",
    "outputId": "ef25a154-72b9-4812-f2da-e68dd2a475b4"
   },
   "outputs": [
    {
     "ename": "",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31mRunning cells with 'Python 3.10.2 64-bit' requires ipykernel package.\n",
      "Run the following command to install 'ipykernel' into the Python environment. \n",
      "Command: '\"C:/Users/stream 11/AppData/Local/Programs/Python/Python310/python.exe\" -m pip install ipykernel -U --user --force-reinstall'"
     ]
    }
   ],
   "source": [
    "# Let's create two dummy data frames to understand\n",
    "import pandas as pd\n",
    "\n",
    "df1 = pd.DataFrame({'item': ['A', 'B', 'C', 'D'],\n",
    "                     'value': [1, 2, 3, 5]})\n",
    "\n",
    "df2 = pd.DataFrame({'item': ['E', 'F', 'G', 'H'],\n",
    "                    'value': [5, 6, 7, 8]})\n",
    "\n",
    "print(df1)\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 171
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 723,
     "status": "ok",
     "timestamp": 1573518284127,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "hQHEhcgQ_4aH",
    "outputId": "b9e421b8-7420-4171-cf2d-d13ca2a0faba"
   },
   "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>item</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  item  value\n",
       "0    A      1\n",
       "1    B      2\n",
       "2    C      3\n",
       "3    D      5"
      ]
     },
     "execution_count": 2,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 294
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 999,
     "status": "ok",
     "timestamp": 1573518319007,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "jmO2uM0zMF4B",
    "outputId": "e4bab94d-fc53-4729-d540-4897f1b33986"
   },
   "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>item</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>G</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>H</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  item  value\n",
       "0    A      1\n",
       "1    B      2\n",
       "2    C      3\n",
       "3    D      5\n",
       "0    E      5\n",
       "1    F      6\n",
       "2    G      7\n",
       "3    H      8"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's stack them vertically by using pd.concat\n",
    "\n",
    "pd.concat([df1, df2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 453
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 595,
     "status": "ok",
     "timestamp": 1573518349989,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "y11Ps271Qawn",
    "outputId": "09e65252-ab58-4b4b-a524-4254ff4c48b9"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:8: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  \n"
     ]
    },
    {
     "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>item</th>\n",
       "      <th>quanity</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>G</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>H</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  item  quanity  value\n",
       "0    A      NaN    1.0\n",
       "1    B      NaN    2.0\n",
       "2    C      NaN    3.0\n",
       "3    D      NaN    5.0\n",
       "0    E      2.0    NaN\n",
       "1    F      2.0    NaN\n",
       "2    G      1.0    NaN\n",
       "3    H      5.0    NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What if they had different column names\n",
    "\n",
    "df1 = pd.DataFrame({'item': ['A', 'B', 'C', 'D'],\n",
    "                     'value': [1, 2, 3, 5]})\n",
    "\n",
    "df2 = pd.DataFrame({'item': ['E', 'F', 'G', 'H'],\n",
    "                    'quanity': [2, 2, 1, 5]})\n",
    "\n",
    "pd.concat([df1, df2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 453
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 775,
     "status": "ok",
     "timestamp": 1573518423930,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "LKInq1ULQoxZ",
    "outputId": "58cf3909-9e4a-43e6-a360-29cc065f66fe"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:8: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  \n"
     ]
    },
    {
     "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>index</th>\n",
       "      <th>item</th>\n",
       "      <th>quanity</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>G</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>H</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index item  quanity  value\n",
       "0      0    A      NaN    1.0\n",
       "1      1    B      NaN    2.0\n",
       "2      2    C      NaN    3.0\n",
       "3      3    D      NaN    5.0\n",
       "4      0    D      2.0    NaN\n",
       "5      1    F      2.0    NaN\n",
       "6      2    G      1.0    NaN\n",
       "7      3    H      5.0    NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What if they had duplicates in items\n",
    "\n",
    "df1 = pd.DataFrame({'item': ['A', 'B', 'C', 'D'],\n",
    "                     'value': [1, 2, 3, 5]})\n",
    "\n",
    "df2 = pd.DataFrame({'item': ['D', 'F', 'G', 'H'],\n",
    "                    'quanity': [2, 2, 1, 5]})\n",
    "\n",
    "pd.concat([df1, df2]).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 171
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 644,
     "status": "ok",
     "timestamp": 1573518465935,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "vlYBAwCWQqCT",
    "outputId": "3c38dfcc-b776-4153-ce7f-4d4199074dc3"
   },
   "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>item</th>\n",
       "      <th>value</th>\n",
       "      <th>item</th>\n",
       "      <th>quanity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>F</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "      <td>G</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>5</td>\n",
       "      <td>H</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  item  value item  quanity\n",
       "0    A      1    D        2\n",
       "1    B      2    F        2\n",
       "2    C      3    G        1\n",
       "3    D      5    H        5"
      ]
     },
     "execution_count": 8,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can use axis = 1 to stack horizontally \n",
    "\n",
    "pd.concat([df1, df2], axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "nxy_1TYfRps8"
   },
   "source": [
    "#### Vertical Concat\n",
    "\n",
    "![alt text](https://pandas.pydata.org/pandas-docs/stable/_images/merging_concat_basic.png)\n",
    "\n",
    "#### Horizontal Conat \n",
    "\n",
    "![alt text](https://pandas.pydata.org/pandas-docs/stable/_images/merging_concat_axis1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "bkRDeum_aw0m"
   },
   "source": [
    "# Similarly we can use Append to add multiple dataFrames onto another\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 575
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 814,
     "status": "ok",
     "timestamp": 1573518570907,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "BACNNgApazZZ",
    "outputId": "07669d82-6f68-48a9-e6f7-d076f3cecd4f"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/pandas/core/frame.py:7138: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  sort=sort,\n"
     ]
    },
    {
     "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>item</th>\n",
       "      <th>quanity</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>G</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>H</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>J</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  item  quanity  value\n",
       "0    A      NaN    1.0\n",
       "1    B      NaN    2.0\n",
       "2    C      NaN    3.0\n",
       "3    D      NaN    5.0\n",
       "0    D      2.0    NaN\n",
       "1    F      2.0    NaN\n",
       "2    G      1.0    NaN\n",
       "3    H      5.0    NaN\n",
       "0    I      3.0    NaN\n",
       "1    J      4.0    NaN\n",
       "2    K      7.0    NaN\n",
       "3    L     25.0    NaN"
      ]
     },
     "execution_count": 11,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'item': ['A', 'B', 'C', 'D'],\n",
    "                     'value': [1, 2, 3, 5]})\n",
    "\n",
    "df2 = pd.DataFrame({'item': ['D', 'F', 'G', 'H'],\n",
    "                    'quanity': [2, 2, 1, 5]})\n",
    "\n",
    "df3 = pd.DataFrame({'item': ['I', 'J', 'K', 'L'],\n",
    "                    'quanity': [3, 4, 7, 25]})\n",
    "\n",
    "df1.append([df2, df3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "P35S8pwqcJ-q"
   },
   "source": [
    "# Pandas merge\n",
    "\n",
    "Pandas has full-featured, high performance in-memory join operations idiomatically very similar to relational databases like SQL:\n",
    "\n",
    "- **one-to-one joins:** for example when joining two DataFrame objects on their indexes (which must contain unique values).\n",
    "- **many-to-one joins:** for example when joining an index (unique) to one or more columns in a different DataFrame.\n",
    "- **many-to-many** joins: joining columns on columns.\n",
    "\n",
    "![alt text](https://i.stack.imgur.com/hMKKt.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 171
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 708,
     "status": "ok",
     "timestamp": 1573518624497,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "3ECIRtg1RR1u",
    "outputId": "e0880106-bc4d-46ff-bee1-3ba73c13fb5b"
   },
   "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>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   A   B\n",
       "0  K0  A0  B0\n",
       "1  K1  A1  B1\n",
       "2  K2  A2  B2\n",
       "3  K3  A3  B3"
      ]
     },
     "execution_count": 12,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfA = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],\n",
    "                     'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "\n",
    "dfB = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],\n",
    "                       'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                       'D': ['D0', 'D1', 'D2', 'D3']})\n",
    "dfA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 171
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 684,
     "status": "ok",
     "timestamp": 1573518627634,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "Jm5t8wPGfLmi",
    "outputId": "11b3cace-f717-4303-b802-f259058e505b"
   },
   "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>key</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   C   D\n",
       "0  K0  C0  D0\n",
       "1  K1  C1  D1\n",
       "2  K2  C2  D2\n",
       "3  K3  C3  D3"
      ]
     },
     "execution_count": 13,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 171
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 621,
     "status": "ok",
     "timestamp": 1573518674910,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "sv0QXG7AfPcR",
    "outputId": "02c57e3a-b7d7-4a8b-c0af-2ec0957563c1"
   },
   "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>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   A   B   C   D\n",
       "0  K0  A0  B0  C0  D0\n",
       "1  K1  A1  B1  C1  D1\n",
       "2  K2  A2  B2  C2  D2\n",
       "3  K3  A3  B3  C3  D3"
      ]
     },
     "execution_count": 14,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merging on unique keys \n",
    "# by default we're doing an inner join\n",
    "\n",
    "pd.merge(dfA, dfB, on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 171
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 599,
     "status": "ok",
     "timestamp": 1573519064697,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "SnqAnnyFfU4O",
    "outputId": "2a78b883-0fb4-4a4a-871f-189641eb5d09"
   },
   "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>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   A   B    C    D\n",
       "0  K0  A0  B0  NaN  NaN\n",
       "1  K1  A1  B1   C1   D1\n",
       "2  K2  A2  B2   C2   D2\n",
       "3  K3  A3  B3   C3   D3"
      ]
     },
     "execution_count": 16,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    " # Understand the difference between left and right joins\n",
    " \n",
    "dfA = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],\n",
    "                     'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "\n",
    "dfB = pd.DataFrame({'key': ['K4', 'K1', 'K2', 'K3'],\n",
    "                       'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                       'D': ['D0', 'D1', 'D2', 'D3']})\n",
    " \n",
    " pd.merge(dfA, dfB, how='left', on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 141
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 431,
     "status": "ok",
     "timestamp": 1569970360220,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": -60
    },
    "id": "SYzQtxdQgZhs",
    "outputId": "5ad31d8e-5693-42e1-eaf1-cd6c76a36685"
   },
   "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>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   A   B   C   D\n",
       "0  K1  A1  B1  C1  D1\n",
       "1  K2  A2  B2  C2  D2\n",
       "2  K3  A3  B3  C3  D3"
      ]
     },
     "execution_count": 33,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Inner Joins\n",
    "\n",
    "pd.merge(dfA, dfB, how='inner', on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 202
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 975,
     "status": "ok",
     "timestamp": 1573519104883,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "t3TuY83Lho1e",
    "outputId": "1305b051-2f37-4b9b-d278-cb449cb031b8"
   },
   "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>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key    A    B    C    D\n",
       "0  K0   A0   B0  NaN  NaN\n",
       "1  K1   A1   B1   C1   D1\n",
       "2  K2   A2   B2   C2   D2\n",
       "3  K3   A3   B3   C3   D3\n",
       "4  K4  NaN  NaN   C0   D0"
      ]
     },
     "execution_count": 17,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Outer Joins\n",
    "\n",
    "pd.merge(dfA, dfB, how='outer', on='key')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "n7WH6CFklvri"
   },
   "source": [
    "# Joining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 141
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 845,
     "status": "ok",
     "timestamp": 1573519155860,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "UPTynXUllw-H",
    "outputId": "7826cb37-3437-47bb-e5f1-24db84ae03a4"
   },
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B\n",
       "K0  A0  B0\n",
       "K1  A1  B1\n",
       "K2  A2  B2"
      ]
     },
     "execution_count": 19,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n",
    "                      'B': ['B0', 'B1', 'B2']},\n",
    "                     index=['K0', 'K1', 'K2'])\n",
    "\n",
    "left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 141
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 616,
     "status": "ok",
     "timestamp": 1573519158278,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "9T1AY_32nqu1",
    "outputId": "476f16fb-6b47-4264-8457-2bbac12c011b"
   },
   "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>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K3</th>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     C   D\n",
       "K0  C0  D0\n",
       "K2  C2  D2\n",
       "K3  C3  D3"
      ]
     },
     "execution_count": 20,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],\n",
    "                       'D': ['D0', 'D2', 'D3']},\n",
    "                      index=['K0', 'K2', 'K3'])\n",
    "\n",
    "right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 141
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1188,
     "status": "ok",
     "timestamp": 1573519164430,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "cXPLF3L1nlQi",
    "outputId": "1645ee45-9b70-4428-9ada-36d0d8cd4351"
   },
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B    C    D\n",
       "K0  A0  B0   C0   D0\n",
       "K1  A1  B1  NaN  NaN\n",
       "K2  A2  B2   C2   D2"
      ]
     },
     "execution_count": 21,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.join(right)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 171
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 620,
     "status": "ok",
     "timestamp": 1573519275287,
     "user": {
      "displayName": "Rajeev Ratan",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCGo6aIm0tOcd5EhqWlYb0rime9sBvHS9YMpx0D2w=s64",
      "userId": "08597265227091462140"
     },
     "user_tz": 0
    },
    "id": "jsP2kgV2nkmW",
    "outputId": "27c3d7e6-5a1b-42bd-f32f-5149468a7e9f"
   },
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A    B    C    D\n",
       "K0   A0   B0   C0   D0\n",
       "K1   A1   B1  NaN  NaN\n",
       "K2   A2   B2   C2   D2\n",
       "K3  NaN  NaN   C3   D3"
      ]
     },
     "execution_count": 22,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.join(right, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "nxh1OreHDrGh"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "4.5 Pandas 6 - Concatenating, Merging and Joining .ipynb",
   "provenance": []
  },
  "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}