Code-Example-005-Pandas-JOIN-Operations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "f3f339e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip3 install -qU pandas \n",
    "!pip3 install -qU numpy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "fb026320",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25bb24cf",
   "metadata": {},
   "source": [
    "# Inner Join: \n",
    "\n",
    "This is the most common type of join, and it returns all rows from the left DataFrame that have matching rows in the right DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "2b8db5d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    B\n",
       "ID   \n",
       "1   4\n",
       "2   5\n",
       "3   6"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'ID': [1, 2, 3], 'B': [4, 5, 6]}).set_index('ID')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "089d9412",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     D\n",
       "ID    \n",
       "2   10\n",
       "3   11\n",
       "4   12"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'ID': [2, 3, 4], 'D': [10, 11, 12]}).set_index('ID')\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52f91314",
   "metadata": {},
   "source": [
    "Look at Pandas Main Dataframe Join Operation\n",
    "\n",
    "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.join.html\n",
    "\n",
    "Venn Diagram \n",
    "https://en.wikipedia.org/wiki/Venn_diagram\n",
    "\n",
    "\n",
    "SQL Join \n",
    "https://en.wikipedia.org/wiki/Join_(SQL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "44f32cb7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    B\n",
      "ID   \n",
      "1   4\n",
      "2   5\n",
      "3   6\n",
      "     D\n",
      "ID    \n",
      "2   10\n",
      "3   11\n",
      "4   12\n",
      "    B     D\n",
      "ID         \n",
      "1   4   NaN\n",
      "2   5  10.0\n",
      "3   6  11.0\n"
     ]
    }
   ],
   "source": [
    "print(df1)\n",
    "print(df2)\n",
    "\n",
    "\n",
    "df = df1.join(df2)\n",
    "\n",
    "# \n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11b31762",
   "metadata": {},
   "source": [
    "# Left Join:\n",
    "\n",
    "This type of join returns all rows from the left DataFrame, even if there are no matching rows in the right DataFrame. Any rows in the right DataFrame that do not have a matching row in the left DataFrame will be filled with NaN values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "85bbdcc7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    B     D\n",
      "ID         \n",
      "1   4   NaN\n",
      "2   5  10.0\n",
      "3   6  11.0\n"
     ]
    }
   ],
   "source": [
    "# Left join on the 'ID' column\n",
    "df = df1.join(df2, on='ID', how='left')\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1126f71d",
   "metadata": {},
   "source": [
    "# Right Join:\n",
    "  \n",
    "This type of join returns all rows from the right DataFrame, even if there are no matching rows in the left DataFrame. Any rows in the left DataFrame that do not have a matching row in the right DataFrame will be filled with NaN values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "03b94928",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     ID    B   D\n",
      "2.0   2  5.0  10\n",
      "3.0   3  6.0  11\n",
      "NaN   4  NaN  12\n"
     ]
    }
   ],
   "source": [
    "# Right join on the 'ID' column\n",
    "df = df1.join(df2, on='ID', how='right')\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b83ee73d",
   "metadata": {},
   "source": [
    "# Full Join: \n",
    "    \n",
    "This type of join returns all rows from both DataFrames, even if there are no matching rows between them. Any rows that do not have a matching row in the other DataFrame will be filled with NaN values.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "952b979b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     ID    B     D\n",
      "1.0   1  4.0   NaN\n",
      "2.0   2  5.0  10.0\n",
      "3.0   3  6.0  11.0\n",
      "NaN   4  NaN  12.0\n"
     ]
    }
   ],
   "source": [
    "# Full join on the 'ID' column\n",
    "df = df1.join(df2, on='ID', how='outer')\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a99e83e",
   "metadata": {},
   "source": [
    "# How \n",
    "\n",
    "how{‘left’, ‘right’, ‘outer’, ‘inner’, ‘cross’}, default ‘left’"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "b039cd10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    B   D\n",
      "ID       \n",
      "2   5  10\n",
      "3   6  11\n"
     ]
    }
   ],
   "source": [
    "# Inner join on the 'ID' column\n",
    "df = df1.join(df2, on='ID', how='inner')\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e516732",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d0ed88e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}