Part 8: pandas_merge.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "anaconda-cloud": {},
    "kernelspec": {
      "display_name": "Python [default]",
      "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.5.2"
    },
    "colab": {
      "name": "Partpandas_merge.ipynb",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/gulabpatel/Python_Tutorials/blob/master/Part%208%3A%20pandas_merge.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P-UFOv6rrYXa"
      },
      "source": [
        "# <font color=\"purple\"><h3 align=\"center\">Pandas Merge Tutorial</h3></font>",
        "https://stackoverflow.com/questions/53645882/pandas-merging-101"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XWOL4aWFrYXb"
      },
      "source": [
        "## <font color='blue'>Basic Merge Using a Dataframe Column</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OdilCd-urYXb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "824c5a8a-bd6f-4cb7-9155-c32b2267bad9"
      },
      "source": [
        "import pandas as pd\n",
        "df1 = pd.DataFrame({\n",
        "    \"city\": [\"new york\",\"chicago\",\"orlando\"],\n",
        "    \"temperature\": [21,14,35],\n",
        "})\n",
        "df1"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       city  temperature\n",
              "0  new york           21\n",
              "1   chicago           14\n",
              "2   orlando           35"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 1
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "CVYy2gcLrYXd",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "7b560bcc-ea14-4c9d-f86d-ef5e9788f2cd"
      },
      "source": [
        "df2 = pd.DataFrame({\n",
        "    \"city\": [\"chicago\",\"new york\",\"orlando\"],\n",
        "    \"humidity\": [65,68,75],\n",
        "})\n",
        "df2"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>chicago</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>new york</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>75</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       city  humidity\n",
              "0   chicago        65\n",
              "1  new york        68\n",
              "2   orlando        75"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": false,
        "id": "YXPZR-HcrYXd",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "3b2a257b-c8a3-4d10-d1ed-3364d094521a"
      },
      "source": [
        "df3 = pd.merge(df1, df2, on=\"city\")\n",
        "df3"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35</td>\n",
              "      <td>75</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       city  temperature  humidity\n",
              "0  new york           21        68\n",
              "1   chicago           14        65\n",
              "2   orlando           35        75"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DSQO0bmMrYXd"
      },
      "source": [
        "## <font color='blue'>Type Of DataBase Joins</font>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i9Y0B5UQrYXd"
      },
      "source": [
        "<img src=\"db_joins.jpg\" height=\"800\", width=\"800\">"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "mwCCCQQyrYXd",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "4975d5aa-3757-4183-c834-7e18be7d86cb"
      },
      "source": [
        "df1 = pd.DataFrame({\n",
        "    \"city\": [\"new york\",\"chicago\",\"orlando\", \"baltimore\"],\n",
        "    \"temperature\": [21,14,35, 38],\n",
        "})\n",
        "df1"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>baltimore</td>\n",
              "      <td>38</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature\n",
              "0   new york           21\n",
              "1    chicago           14\n",
              "2    orlando           35\n",
              "3  baltimore           38"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "59ujZwC0rYXe",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "8451e0f5-1012-4db6-d5bd-77391edf2269"
      },
      "source": [
        "df2 = pd.DataFrame({\n",
        "    \"city\": [\"chicago\",\"new york\",\"san diego\"],\n",
        "    \"humidity\": [65,68,71],\n",
        "})\n",
        "df2"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>chicago</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>new york</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>san diego</td>\n",
              "      <td>71</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  humidity\n",
              "0    chicago        65\n",
              "1   new york        68\n",
              "2  san diego        71"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "H9N5t4LOrYXe",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 111
        },
        "outputId": "38037a1a-d04a-4071-fe64-c20b623c9fd9"
      },
      "source": [
        "df3=pd.merge(df1,df2,on=\"city\",how=\"inner\")\n",
        "df3"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       city  temperature  humidity\n",
              "0  new york           21        68\n",
              "1   chicago           14        65"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "npsdbMT2rYXe",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "9a629864-962a-41a3-b315-728440265d0e"
      },
      "source": [
        "df3=pd.merge(df1,df2,on=\"city\",how=\"outer\")\n",
        "df3"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21.0</td>\n",
              "      <td>68.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14.0</td>\n",
              "      <td>65.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>baltimore</td>\n",
              "      <td>38.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>san diego</td>\n",
              "      <td>NaN</td>\n",
              "      <td>71.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature  humidity\n",
              "0   new york         21.0      68.0\n",
              "1    chicago         14.0      65.0\n",
              "2    orlando         35.0       NaN\n",
              "3  baltimore         38.0       NaN\n",
              "4  san diego          NaN      71.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "4UckBJcXrYXf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "79dc286d-e380-4913-bf83-5ca06153df2f"
      },
      "source": [
        "df3=pd.merge(df1,df2,on=\"city\",how=\"left\")\n",
        "df3"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21</td>\n",
              "      <td>68.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14</td>\n",
              "      <td>65.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>baltimore</td>\n",
              "      <td>38</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature  humidity\n",
              "0   new york           21      68.0\n",
              "1    chicago           14      65.0\n",
              "2    orlando           35       NaN\n",
              "3  baltimore           38       NaN"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "9yBBwlnHrYXf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "7b745010-9201-4137-d81a-297eee3fff35"
      },
      "source": [
        "df3=pd.merge(df1,df2,on=\"city\",how=\"right\")\n",
        "df3"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14.0</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>new york</td>\n",
              "      <td>21.0</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>san diego</td>\n",
              "      <td>NaN</td>\n",
              "      <td>71</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature  humidity\n",
              "0    chicago         14.0        65\n",
              "1   new york         21.0        68\n",
              "2  san diego          NaN        71"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VhlX8tHDrYXf"
      },
      "source": [
        "## <font color='blue'>indicator flag</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "bYAE4LJvrYXf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "40d7fc5b-25e3-4932-8095-9b5f0b3d72bc"
      },
      "source": [
        "df3=pd.merge(df1,df2,on=\"city\",how=\"outer\",indicator=True)\n",
        "df3"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "      <th>_merge</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21.0</td>\n",
              "      <td>68.0</td>\n",
              "      <td>both</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14.0</td>\n",
              "      <td>65.0</td>\n",
              "      <td>both</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>left_only</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>baltimore</td>\n",
              "      <td>38.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>left_only</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>san diego</td>\n",
              "      <td>NaN</td>\n",
              "      <td>71.0</td>\n",
              "      <td>right_only</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature  humidity      _merge\n",
              "0   new york         21.0      68.0        both\n",
              "1    chicago         14.0      65.0        both\n",
              "2    orlando         35.0       NaN   left_only\n",
              "3  baltimore         38.0       NaN   left_only\n",
              "4  san diego          NaN      71.0  right_only"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xxf6W6IFrYXf"
      },
      "source": [
        "## <font color='blue'>suffixes</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B675y3H0rYXf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "c403af21-33df-414c-8faf-c5b41904c806"
      },
      "source": [
        "df1 = pd.DataFrame({\n",
        "    \"city\": [\"new york\",\"chicago\",\"orlando\", \"baltimore\"],\n",
        "    \"temperature\": [21,14,35,38],\n",
        "    \"humidity\": [65,68,71, 75]\n",
        "})\n",
        "df1"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35</td>\n",
              "      <td>71</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>baltimore</td>\n",
              "      <td>38</td>\n",
              "      <td>75</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature  humidity\n",
              "0   new york           21        65\n",
              "1    chicago           14        68\n",
              "2    orlando           35        71\n",
              "3  baltimore           38        75"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "281G0YxVrYXg",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "9373ed72-19c1-468f-b0d1-2a9821276796"
      },
      "source": [
        "df2 = pd.DataFrame({\n",
        "    \"city\": [\"chicago\",\"new york\",\"san diego\"],\n",
        "    \"temperature\": [21,14,35],\n",
        "    \"humidity\": [65,68,71]\n",
        "})\n",
        "df2"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>chicago</td>\n",
              "      <td>21</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>new york</td>\n",
              "      <td>14</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>san diego</td>\n",
              "      <td>35</td>\n",
              "      <td>71</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature  humidity\n",
              "0    chicago           21        65\n",
              "1   new york           14        68\n",
              "2  san diego           35        71"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": false,
        "id": "jgqqaL8crYXg",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "e35f0cab-a0a9-403a-c0d6-01b0e9fb18cd"
      },
      "source": [
        "df3= pd.merge(df1,df2,on=\"city\",how=\"outer\", suffixes=('_first','_second'))\n",
        "df3"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>city</th>\n",
              "      <th>temperature_first</th>\n",
              "      <th>humidity_first</th>\n",
              "      <th>temperature_second</th>\n",
              "      <th>humidity_second</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>new york</td>\n",
              "      <td>21.0</td>\n",
              "      <td>65.0</td>\n",
              "      <td>14.0</td>\n",
              "      <td>68.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>chicago</td>\n",
              "      <td>14.0</td>\n",
              "      <td>68.0</td>\n",
              "      <td>21.0</td>\n",
              "      <td>65.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>orlando</td>\n",
              "      <td>35.0</td>\n",
              "      <td>71.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>baltimore</td>\n",
              "      <td>38.0</td>\n",
              "      <td>75.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>san diego</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>35.0</td>\n",
              "      <td>71.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        city  temperature_first  ...  temperature_second  humidity_second\n",
              "0   new york               21.0  ...                14.0             68.0\n",
              "1    chicago               14.0  ...                21.0             65.0\n",
              "2    orlando               35.0  ...                 NaN              NaN\n",
              "3  baltimore               38.0  ...                 NaN              NaN\n",
              "4  san diego                NaN  ...                35.0             71.0\n",
              "\n",
              "[5 rows x 5 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "94gRxdkkrYXg"
      },
      "source": [
        "## <font color='blue'>join</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qWTw3v2GrYXg",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "ab9c5db7-bf8c-4c17-9b81-7a2279def062"
      },
      "source": [
        "df1 = pd.DataFrame({\n",
        "    \"city\": [\"new york\",\"chicago\",\"orlando\"],\n",
        "    \"temperature\": [21,14,35],\n",
        "})\n",
        "df1.set_index('city',inplace=True)\n",
        "df1"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>temperature</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>city</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>new york</th>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>chicago</th>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>orlando</th>\n",
              "      <td>35</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          temperature\n",
              "city                 \n",
              "new york           21\n",
              "chicago            14\n",
              "orlando            35"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "WQ9JEVc4rYXg",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "cb68c864-fec2-45ec-abd7-74b79a8c0ce7"
      },
      "source": [
        "df2 = pd.DataFrame({\n",
        "    \"city\": [\"chicago\",\"new york\",\"orlando\"],\n",
        "    \"humidity\": [65,68,75],\n",
        "})\n",
        "df2.set_index('city',inplace=True)\n",
        "df2"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>humidity</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>city</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>chicago</th>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>new york</th>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>orlando</th>\n",
              "      <td>75</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          humidity\n",
              "city              \n",
              "chicago         65\n",
              "new york        68\n",
              "orlando         75"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zn53Ge_ErYXg",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "da3d7077-3f20-4944-9311-85a8db89664c"
      },
      "source": [
        "df1.join(df2,lsuffix='_l', rsuffix='_r')"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>temperature</th>\n",
              "      <th>humidity</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>city</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>new york</th>\n",
              "      <td>21</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>chicago</th>\n",
              "      <td>14</td>\n",
              "      <td>65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>orlando</th>\n",
              "      <td>35</td>\n",
              "      <td>75</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          temperature  humidity\n",
              "city                           \n",
              "new york           21        68\n",
              "chicago            14        65\n",
              "orlando            35        75"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jqRmeevVsgcO"
      },
      "source": [
        "###Help is available on the video https://www.youtube.com/watch?v=h4hOPGo4UVU&t=143s"
      ]
    }
  ]
}