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

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f3f339e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip3 install -qU pandas \n",
    "# !pip3 install -qU numpy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fb026320",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25bb24cf",
   "metadata": {},
   "source": [
    "# Merge Operation: \n",
    "Let us create two example Dataframes in Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>ID</th>\n",
       "      <th>V</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID    V\n",
       "0  10   41\n",
       "1  12  101\n",
       "2  13    6"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_a = pd.DataFrame({'ID': [10, 12, 13], 'V': [41, 101, 6]})\n",
    "df_a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>ID</th>\n",
       "      <th>W</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID    W\n",
       "0  12  201\n",
       "1  13  101\n",
       "2  14   12"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_b = pd.DataFrame({'ID': [12, 13, 14], 'W': [201,101, 12]})\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a02a8aec-c082-4a30-9f4f-4305cc5b3a1c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID |   V |\n",
      "|---:|-----:|----:|\n",
      "|  0 |   10 |  41 |\n",
      "|  1 |   12 | 101 |\n",
      "|  2 |   13 |   6 |\n",
      "\n",
      "###################\n",
      "\n",
      "|    |   ID |   W |\n",
      "|---:|-----:|----:|\n",
      "|  0 |   12 | 201 |\n",
      "|  1 |   13 | 101 |\n",
      "|  2 |   14 |  12 |\n"
     ]
    }
   ],
   "source": [
    "print(df_a.to_markdown())\n",
    "\n",
    "\n",
    "print(\"\\n###################\\n\")\n",
    "print(df_b.to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b6c53008-da0a-4cad-877b-64220cf9d432",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID |   V |   W |\n",
      "|---:|-----:|----:|----:|\n",
      "|  0 |   12 | 101 | 201 |\n",
      "|  1 |   13 |   6 | 101 |\n"
     ]
    }
   ],
   "source": [
    "# Merge the two dataframes\n",
    "df_merged1 = df_a.merge(df_b)\n",
    "\n",
    "print(df_merged1.to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c7430fb6-b784-45b0-8db4-7af3039d2da2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID |   W |   V |\n",
      "|---:|-----:|----:|----:|\n",
      "|  0 |   12 | 201 | 101 |\n",
      "|  1 |   13 | 101 |   6 |\n"
     ]
    }
   ],
   "source": [
    "# Merge the two dataframes\n",
    "df_merged2 = df_b.merge(df_a)\n",
    "\n",
    "# \n",
    "print(df_merged2.to_markdown())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52f91314",
   "metadata": {},
   "source": [
    "# Look at Pandas Main Dataframe Merge  Operation\n",
    "\n",
    "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html\n",
    "\n",
    "## Compare it later to \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": 8,
   "id": "aa44961d-fd67-46f1-b096-e4f9190b6977",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID |   V |   W |\n",
      "|---:|-----:|----:|----:|\n",
      "|  0 |   10 |  41 | nan |\n",
      "|  1 |   12 | 101 | 201 |\n",
      "|  2 |   13 |   6 | 101 |\n"
     ]
    }
   ],
   "source": [
    "# how{‘left’, ‘right’, ‘outer’, ‘inner’, ‘cross’}, default ‘inner’\n",
    "\n",
    "# Merge the two dataframes\n",
    "df_merged = df_a.merge(df_b, how=\"left\")\n",
    "print(df_merged.to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9e516732",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID |   V |   W |\n",
      "|---:|-----:|----:|----:|\n",
      "|  0 |   12 | 101 | 201 |\n",
      "|  1 |   13 |   6 | 101 |\n",
      "|  2 |   14 | nan |  12 |\n"
     ]
    }
   ],
   "source": [
    "# Merge the two dataframes\n",
    "df_merged = df_a.merge(df_b, how=\"right\")\n",
    "print(df_merged.to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2d0ed88e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID |   V |   W |\n",
      "|---:|-----:|----:|----:|\n",
      "|  0 |   10 |  41 | nan |\n",
      "|  1 |   12 | 101 | 201 |\n",
      "|  2 |   13 |   6 | 101 |\n",
      "|  3 |   14 | nan |  12 |\n"
     ]
    }
   ],
   "source": [
    "# Merge the two dataframes\n",
    "df_merged = df_a.merge(df_b, how=\"outer\")\n",
    "print(df_merged.to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4f9f20ab-bd22-4073-82ce-9ce4b80344f4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID |   V |   W |\n",
      "|---:|-----:|----:|----:|\n",
      "|  0 |   12 | 101 | 201 |\n",
      "|  1 |   13 |   6 | 101 |\n"
     ]
    }
   ],
   "source": [
    "df_merged = df_a.merge(df_b, how=\"inner\")\n",
    "print(df_merged.to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b96510d9-e6f0-4531-8334-dd3a5572296b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID_x |   V |   ID_y |   W |\n",
      "|---:|-------:|----:|-------:|----:|\n",
      "|  0 |     10 |  41 |     12 | 201 |\n",
      "|  1 |     10 |  41 |     13 | 101 |\n",
      "|  2 |     10 |  41 |     14 |  12 |\n",
      "|  3 |     12 | 101 |     12 | 201 |\n",
      "|  4 |     12 | 101 |     13 | 101 |\n",
      "|  5 |     12 | 101 |     14 |  12 |\n",
      "|  6 |     13 |   6 |     12 | 201 |\n",
      "|  7 |     13 |   6 |     13 | 101 |\n",
      "|  8 |     13 |   6 |     14 |  12 |\n"
     ]
    }
   ],
   "source": [
    "df_merged = df_a.merge(df_b, how=\"cross\")\n",
    "print(df_merged.to_markdown())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2361c647-4ca5-4bd8-b9b6-0fcce4fb81d4",
   "metadata": {},
   "source": [
    "# Keys \n",
    "\n",
    "## onlabel or list\n",
    "Column or index level names to join on. These must be found in both DataFrames. If on is None and not merging on indexes then this defaults to the intersection of the columns in both DataFrames.\n",
    "\n",
    "## left_onlabel or list, or array-like\n",
    "Column or index level names to join on in the left DataFrame. Can also be an array or list of arrays of the length of the left DataFrame. These arrays are treated as if they are columns.\n",
    "\n",
    "## right_onlabel or list, or array-like\n",
    "Column or index level names to join on in the right DataFrame. Can also be an array or list of arrays of the length of the right DataFrame. These arrays are treated as if they are columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5f81d688-ce28-4d01-b7dc-8e36bfcc978c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    |   ID_x |   V |   ID_y |   W |\n",
      "|---:|-------:|----:|-------:|----:|\n",
      "|  0 |     10 |  41 |    nan | nan |\n",
      "|  1 |     12 | 101 |     13 | 101 |\n",
      "|  2 |     13 |   6 |    nan | nan |\n"
     ]
    }
   ],
   "source": [
    "# Merge the two dataframes\n",
    "df_merged = df_a.merge(df_b, how=\"left\", left_on=\"V\", right_on=\"W\")\n",
    "print(df_merged.to_markdown())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbcbc4e0-0ac3-432e-bde6-593bc9785547",
   "metadata": {},
   "source": [
    "# Study the Pandas' Documentation for Merge Operation. \n",
    "\n",
    "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ee2856c-2168-4d8f-8ad0-9197661b1401",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}