23_pandas_operations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas-Operations\n",
    "## Learning Objectives\n",
    "    1. Loading a comma-seprated-value(CSV) dataset\n",
    "    2. Grouping data by value\n",
    "    3. Creating pivot tables\n",
    "    4. Relational Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>sex</th>\n",
       "      <th>smoker</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.99</td>\n",
       "      <td>1.01</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.34</td>\n",
       "      <td>1.66</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.01</td>\n",
       "      <td>3.50</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23.68</td>\n",
       "      <td>3.31</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24.59</td>\n",
       "      <td>3.61</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   total_bill   tip     sex smoker  day    time  size\n",
       "0       16.99  1.01  Female     No  Sun  Dinner     2\n",
       "1       10.34  1.66    Male     No  Sun  Dinner     3\n",
       "2       21.01  3.50    Male     No  Sun  Dinner     3\n",
       "3       23.68  3.31    Male     No  Sun  Dinner     2\n",
       "4       24.59  3.61  Female     No  Sun  Dinner     4"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read the file\n",
    "tips_df = pd.read_csv('files/tips.csv')\n",
    "tips_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>gender</th>\n",
       "      <th>smoker</th>\n",
       "      <th>day</th>\n",
       "      <th>meal</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.99</td>\n",
       "      <td>1.01</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.34</td>\n",
       "      <td>1.66</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.01</td>\n",
       "      <td>3.50</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23.68</td>\n",
       "      <td>3.31</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24.59</td>\n",
       "      <td>3.61</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   total_bill   tip  gender smoker  day    meal  size\n",
       "0       16.99  1.01  Female     No  Sun  Dinner     2\n",
       "1       10.34  1.66    Male     No  Sun  Dinner     3\n",
       "2       21.01  3.50    Male     No  Sun  Dinner     3\n",
       "3       23.68  3.31    Male     No  Sun  Dinner     2\n",
       "4       24.59  3.61  Female     No  Sun  Dinner     4"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename columns\n",
    "rename_dict = dict(time=\"meal\", sex=\"gender\")\n",
    "tips_df = tips_df.rename(columns=rename_dict)\n",
    "tips_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Group by"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on method groupby in module pandas.core.frame:\n",
      "\n",
      "groupby(by=None, axis: 'Axis' = 0, level: 'Level | None' = None, as_index: 'bool' = True, sort: 'bool' = True, group_keys: 'bool' = True, squeeze: 'bool | lib.NoDefault' = <no_default>, observed: 'bool' = False, dropna: 'bool' = True) -> 'DataFrameGroupBy' method of pandas.core.frame.DataFrame instance\n",
      "    Group DataFrame using a mapper or by a Series of columns.\n",
      "    \n",
      "    A groupby operation involves some combination of splitting the\n",
      "    object, applying a function, and combining the results. This can be\n",
      "    used to group large amounts of data and compute operations on these\n",
      "    groups.\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    by : mapping, function, label, or list of labels\n",
      "        Used to determine the groups for the groupby.\n",
      "        If ``by`` is a function, it's called on each value of the object's\n",
      "        index. If a dict or Series is passed, the Series or dict VALUES\n",
      "        will be used to determine the groups (the Series' values are first\n",
      "        aligned; see ``.align()`` method). If a list or ndarray of length\n",
      "        equal to the selected axis is passed (see the `groupby user guide\n",
      "        <https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html#splitting-an-object-into-groups>`_),\n",
      "        the values are used as-is to determine the groups. A label or list\n",
      "        of labels may be passed to group by the columns in ``self``.\n",
      "        Notice that a tuple is interpreted as a (single) key.\n",
      "    axis : {0 or 'index', 1 or 'columns'}, default 0\n",
      "        Split along rows (0) or columns (1).\n",
      "    level : int, level name, or sequence of such, default None\n",
      "        If the axis is a MultiIndex (hierarchical), group by a particular\n",
      "        level or levels.\n",
      "    as_index : bool, default True\n",
      "        For aggregated output, return object with group labels as the\n",
      "        index. Only relevant for DataFrame input. as_index=False is\n",
      "        effectively \"SQL-style\" grouped output.\n",
      "    sort : bool, default True\n",
      "        Sort group keys. Get better performance by turning this off.\n",
      "        Note this does not influence the order of observations within each\n",
      "        group. Groupby preserves the order of rows within each group.\n",
      "    group_keys : bool, default True\n",
      "        When calling apply, add group keys to index to identify pieces.\n",
      "    squeeze : bool, default False\n",
      "        Reduce the dimensionality of the return type if possible,\n",
      "        otherwise return a consistent type.\n",
      "    \n",
      "        .. deprecated:: 1.1.0\n",
      "    \n",
      "    observed : bool, default False\n",
      "        This only applies if any of the groupers are Categoricals.\n",
      "        If True: only show observed values for categorical groupers.\n",
      "        If False: show all values for categorical groupers.\n",
      "    dropna : bool, default True\n",
      "        If True, and if group keys contain NA values, NA values together\n",
      "        with row/column will be dropped.\n",
      "        If False, NA values will also be treated as the key in groups.\n",
      "    \n",
      "        .. versionadded:: 1.1.0\n",
      "    \n",
      "    Returns\n",
      "    -------\n",
      "    DataFrameGroupBy\n",
      "        Returns a groupby object that contains information about the groups.\n",
      "    \n",
      "    See Also\n",
      "    --------\n",
      "    resample : Convenience method for frequency conversion and resampling\n",
      "        of time series.\n",
      "    \n",
      "    Notes\n",
      "    -----\n",
      "    See the `user guide\n",
      "    <https://pandas.pydata.org/pandas-docs/stable/groupby.html>`__ for more\n",
      "    detailed usage and examples, including splitting an object into groups,\n",
      "    iterating through groups, selecting a group, aggregation, and more.\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = pd.DataFrame({'Animal': ['Falcon', 'Falcon',\n",
      "    ...                               'Parrot', 'Parrot'],\n",
      "    ...                    'Max Speed': [380., 370., 24., 26.]})\n",
      "    >>> df\n",
      "       Animal  Max Speed\n",
      "    0  Falcon      380.0\n",
      "    1  Falcon      370.0\n",
      "    2  Parrot       24.0\n",
      "    3  Parrot       26.0\n",
      "    >>> df.groupby(['Animal']).mean()\n",
      "            Max Speed\n",
      "    Animal\n",
      "    Falcon      375.0\n",
      "    Parrot       25.0\n",
      "    \n",
      "    **Hierarchical Indexes**\n",
      "    \n",
      "    We can groupby different levels of a hierarchical index\n",
      "    using the `level` parameter:\n",
      "    \n",
      "    >>> arrays = [['Falcon', 'Falcon', 'Parrot', 'Parrot'],\n",
      "    ...           ['Captive', 'Wild', 'Captive', 'Wild']]\n",
      "    >>> index = pd.MultiIndex.from_arrays(arrays, names=('Animal', 'Type'))\n",
      "    >>> df = pd.DataFrame({'Max Speed': [390., 350., 30., 20.]},\n",
      "    ...                   index=index)\n",
      "    >>> df\n",
      "                    Max Speed\n",
      "    Animal Type\n",
      "    Falcon Captive      390.0\n",
      "           Wild         350.0\n",
      "    Parrot Captive       30.0\n",
      "           Wild          20.0\n",
      "    >>> df.groupby(level=0).mean()\n",
      "            Max Speed\n",
      "    Animal\n",
      "    Falcon      370.0\n",
      "    Parrot       25.0\n",
      "    >>> df.groupby(level=\"Type\").mean()\n",
      "             Max Speed\n",
      "    Type\n",
      "    Captive      210.0\n",
      "    Wild         185.0\n",
      "    \n",
      "    We can also choose to include NA in group keys or not by setting\n",
      "    `dropna` parameter, the default setting is `True`.\n",
      "    \n",
      "    >>> l = [[1, 2, 3], [1, None, 4], [2, 1, 3], [1, 2, 2]]\n",
      "    >>> df = pd.DataFrame(l, columns=[\"a\", \"b\", \"c\"])\n",
      "    \n",
      "    >>> df.groupby(by=[\"b\"]).sum()\n",
      "        a   c\n",
      "    b\n",
      "    1.0 2   3\n",
      "    2.0 2   5\n",
      "    \n",
      "    >>> df.groupby(by=[\"b\"], dropna=False).sum()\n",
      "        a   c\n",
      "    b\n",
      "    1.0 2   3\n",
      "    2.0 2   5\n",
      "    NaN 1   4\n",
      "    \n",
      "    >>> l = [[\"a\", 12, 12], [None, 12.3, 33.], [\"b\", 12.3, 123], [\"a\", 1, 1]]\n",
      "    >>> df = pd.DataFrame(l, columns=[\"a\", \"b\", \"c\"])\n",
      "    \n",
      "    >>> df.groupby(by=\"a\").sum()\n",
      "        b     c\n",
      "    a\n",
      "    a   13.0   13.0\n",
      "    b   12.3  123.0\n",
      "    \n",
      "    >>> df.groupby(by=\"a\", dropna=False).sum()\n",
      "        b     c\n",
      "    a\n",
      "    a   13.0   13.0\n",
      "    b   12.3  123.0\n",
      "    NaN 12.3   33.0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(tips_df.groupby)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>18.056897</td>\n",
       "      <td>2.833448</td>\n",
       "      <td>2.459770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>20.744076</td>\n",
       "      <td>3.089618</td>\n",
       "      <td>2.630573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        total_bill       tip      size\n",
       "gender                                \n",
       "Female   18.056897  2.833448  2.459770\n",
       "Male     20.744076  3.089618  2.630573"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouping based on gender and applying mean\n",
    "# mean is only applied to numeric values, therefor, Time is not included here.\n",
    "mean = tips_df.groupby(['gender']).mean()\n",
    "mean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    Group and then filter before grouping\n",
    "    the follwing sample groups based on gender and only shows those that their tip is higher than 2.9 ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>gender</th>\n",
       "      <th>smoker</th>\n",
       "      <th>day</th>\n",
       "      <th>meal</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>1570.95</td>\n",
       "      <td>246.51</td>\n",
       "      <td>FemaleFemaleFemaleFemaleFemaleFemaleFemaleFema...</td>\n",
       "      <td>NoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoYesNoYesYesNoN...</td>\n",
       "      <td>SunSunSunSunSunSunSatSatSatSatSatSatSunSunSatS...</td>\n",
       "      <td>DinnerDinnerDinnerDinnerDinnerDinnerDinnerDinn...</td>\n",
       "      <td>214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>3256.82</td>\n",
       "      <td>485.07</td>\n",
       "      <td>MaleMaleMaleMaleMaleMaleMaleMaleMaleMaleMaleMa...</td>\n",
       "      <td>NoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNo...</td>\n",
       "      <td>SunSunSunSunSunSunSunSunSunSunSunSunSunSatSatS...</td>\n",
       "      <td>DinnerDinnerDinnerDinnerDinnerDinnerDinnerDinn...</td>\n",
       "      <td>413</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        total_bill     tip                                             gender  \\\n",
       "gender                                                                          \n",
       "Female     1570.95  246.51  FemaleFemaleFemaleFemaleFemaleFemaleFemaleFema...   \n",
       "Male       3256.82  485.07  MaleMaleMaleMaleMaleMaleMaleMaleMaleMaleMaleMa...   \n",
       "\n",
       "                                                   smoker  \\\n",
       "gender                                                      \n",
       "Female  NoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoYesNoYesYesNoN...   \n",
       "Male    NoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNo...   \n",
       "\n",
       "                                                      day  \\\n",
       "gender                                                      \n",
       "Female  SunSunSunSunSunSunSatSatSatSatSatSatSunSunSatS...   \n",
       "Male    SunSunSunSunSunSunSunSunSunSunSunSunSunSatSatS...   \n",
       "\n",
       "                                                     meal  size  \n",
       "gender                                                           \n",
       "Female  DinnerDinnerDinnerDinnerDinnerDinnerDinnerDinn...   214  \n",
       "Male    DinnerDinnerDinnerDinnerDinnerDinnerDinnerDinn...   413  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using cutom function\n",
    "import numpy as np\n",
    "grouped_df = tips_df.groupby(['gender']).apply(lambda x: np.sum(x))\n",
    "grouped_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\pooya\\AppData\\Local\\Temp\\ipykernel_4732\\691178229.py:2: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n",
      "  tips_df.groupby('gender').filter(lambda k : k['tip'].mean() > 2.9).mean()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "total_bill    20.744076\n",
       "tip            3.089618\n",
       "size           2.630573\n",
       "dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the women are removed from the output!!\n",
    "tips_df.groupby('gender').filter(lambda k : k['tip'].mean() > 2.9).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>20.744076</td>\n",
       "      <td>3.089618</td>\n",
       "      <td>2.630573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        total_bill       tip      size\n",
       "gender                                \n",
       "Male     20.744076  3.089618  2.630573"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# nested group-by\n",
    "tips_df.groupby('gender').filter(lambda k : k['tip'].mean() > 2.9).groupby('gender').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th>smoker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Female</th>\n",
       "      <th>No</th>\n",
       "      <td>18.105185</td>\n",
       "      <td>2.773519</td>\n",
       "      <td>2.592593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>17.977879</td>\n",
       "      <td>2.931515</td>\n",
       "      <td>2.242424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Male</th>\n",
       "      <th>No</th>\n",
       "      <td>19.791237</td>\n",
       "      <td>3.113402</td>\n",
       "      <td>2.711340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>22.284500</td>\n",
       "      <td>3.051167</td>\n",
       "      <td>2.500000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               total_bill       tip      size\n",
       "gender smoker                                \n",
       "Female No       18.105185  2.773519  2.592593\n",
       "       Yes      17.977879  2.931515  2.242424\n",
       "Male   No       19.791237  3.113402  2.711340\n",
       "       Yes      22.284500  3.051167  2.500000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# multi dimensional group-by\n",
    "tips_df.groupby(['gender', 'smoker']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot table\n",
    "    this is a pivot table for tips dataframe showing the total_bill and\n",
    "    is grouped by gender and smoker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>smoker</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>18.105185</td>\n",
       "      <td>17.977879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>19.791237</td>\n",
       "      <td>22.284500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "smoker         No        Yes\n",
       "gender                      \n",
       "Female  18.105185  17.977879\n",
       "Male    19.791237  22.284500"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(tips_df, 'total_bill', 'gender', 'smoker')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    this is a pivot table for tips dataframe showing the tip which is\n",
    "    grouped by gender and smoker in one dimension and by day and time in the other"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>day</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Fri</th>\n",
       "      <th>Sat</th>\n",
       "      <th>Sun</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Thur</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>meal</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Lunch</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Lunch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th>smoker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Female</th>\n",
       "      <th>No</th>\n",
       "      <td>3.250</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2.724615</td>\n",
       "      <td>3.329286</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.437083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>2.700</td>\n",
       "      <td>2.66</td>\n",
       "      <td>2.868667</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Male</th>\n",
       "      <th>No</th>\n",
       "      <td>2.500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.256563</td>\n",
       "      <td>3.115349</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.941500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>3.246</td>\n",
       "      <td>1.90</td>\n",
       "      <td>2.879259</td>\n",
       "      <td>3.521333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.058000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "day              Fri             Sat       Sun   Thur          \n",
       "meal          Dinner Lunch    Dinner    Dinner Dinner     Lunch\n",
       "gender smoker                                                  \n",
       "Female No      3.250  3.00  2.724615  3.329286    3.0  2.437083\n",
       "       Yes     2.700  2.66  2.868667  3.500000    NaN  2.990000\n",
       "Male   No      2.500   NaN  3.256563  3.115349    NaN  2.941500\n",
       "       Yes     3.246  1.90  2.879259  3.521333    NaN  3.058000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(tips_df, 'tip', ['gender', 'smoker'], ['day', 'meal'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Relational Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Feature_1</th>\n",
       "      <th>Feature_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>G</td>\n",
       "      <td>H</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>I</td>\n",
       "      <td>J</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1 Feature_2\n",
       "0  1         A         B\n",
       "1  2         C         D\n",
       "2  3         E         F\n",
       "3  4         G         H\n",
       "4  5         I         J"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dummy_data_1 = {\n",
    "        'id':        ['1', '2', '3', '4', '5'],\n",
    "        'Feature_1': ['A', 'C', 'E', 'G', 'I'],\n",
    "        'Feature_2': ['B', 'D', 'F', 'H', 'J'],\n",
    "}\n",
    "df1 = pd.DataFrame(dummy_data_1)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Feature_1</th>\n",
       "      <th>Feature_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>O</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>Q</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>S</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1 Feature_2\n",
       "0  1         K         L\n",
       "1  2         M         N\n",
       "2  6         O         P\n",
       "3  7         Q         R\n",
       "4  8         S         T"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dummy_data_2 = {\n",
    "        'id':        ['1', '2', '6', '7', '8'],\n",
    "        'Feature_1': ['K', 'M', 'O', 'Q', 'S'],\n",
    "        'Feature_2': ['L', 'N', 'P', 'R', 'T']}\n",
    "df2 = pd.DataFrame(dummy_data_2)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Feature_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  Feature_3\n",
       "0   1         12\n",
       "1   2         13\n",
       "2   3         14\n",
       "3   4         15\n",
       "4   5         16\n",
       "5   7         17\n",
       "6   8         15\n",
       "7   9         12\n",
       "8  10         13\n",
       "9  11         23"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dummy_data_3 = {\n",
    "        'id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],\n",
    "        'Feature_3': [12, 13, 14, 15, 16, 17, 15, 12, 13, 23]}\n",
    "df3 = pd.DataFrame(dummy_data_3, columns = ['id', 'Feature_3'])\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Feature_1_x</th>\n",
       "      <th>Feature_2_x</th>\n",
       "      <th>Feature_1_y</th>\n",
       "      <th>Feature_2_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1_x Feature_2_x Feature_1_y Feature_2_y\n",
       "0  1           A           B           K           L\n",
       "1  2           C           D           M           N"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge_col = pd.merge(df1, df2, on='id')\n",
    "\n",
    "df_merge_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Feature_1</th>\n",
       "      <th>Feature_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>G</td>\n",
       "      <td>H</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>I</td>\n",
       "      <td>J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>O</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>Q</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8</td>\n",
       "      <td>S</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1 Feature_2\n",
       "0  1         A         B\n",
       "1  2         C         D\n",
       "2  3         E         F\n",
       "3  4         G         H\n",
       "4  5         I         J\n",
       "5  1         K         L\n",
       "6  2         M         N\n",
       "7  6         O         P\n",
       "8  7         Q         R\n",
       "9  8         S         T"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concatenate\n",
    "df_row = pd.concat([df1, df2], ignore_index=True)\n",
    "df_row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "specifying keys for each dataframe separately!\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>id</th>\n",
       "      <th>Feature_1</th>\n",
       "      <th>Feature_2</th>\n",
       "      <th>Feature_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>G</td>\n",
       "      <td>H</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>I</td>\n",
       "      <td>J</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Q</td>\n",
       "      <td>R</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>S</td>\n",
       "      <td>T</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1 Feature_2  Feature_3\n",
       "0  1         A         B         12\n",
       "1  1         K         L         12\n",
       "2  2         C         D         13\n",
       "3  2         M         N         13\n",
       "4  3         E         F         14\n",
       "5  4         G         H         15\n",
       "6  5         I         J         16\n",
       "7  7         Q         R         17\n",
       "8  8         S         T         15"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"specifying keys for each dataframe separately!\")\n",
    "df_merge_difkey = pd.merge(df_row, df3, left_on='id', right_on='id')\n",
    "\n",
    "df_merge_difkey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Feature_1_x</th>\n",
       "      <th>Feature_2_x</th>\n",
       "      <th>Feature_1_y</th>\n",
       "      <th>Feature_2_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>G</td>\n",
       "      <td>H</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>I</td>\n",
       "      <td>J</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1_x Feature_2_x Feature_1_y Feature_2_y\n",
       "0  1           A           B           K           L\n",
       "1  2           C           D           M           N\n",
       "2  3           E           F         NaN         NaN\n",
       "3  4           G           H         NaN         NaN\n",
       "4  5           I           J         NaN         NaN\n",
       "5  6         NaN         NaN           O           P\n",
       "6  7         NaN         NaN           Q           R\n",
       "7  8         NaN         NaN           S           T"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_outer = pd.merge(df1, df2, on='id', how='outer')\n",
    "\n",
    "df_outer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Feature_1_x</th>\n",
       "      <th>Feature_2_x</th>\n",
       "      <th>Feature_1_y</th>\n",
       "      <th>Feature_2_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1_x Feature_2_x Feature_1_y Feature_2_y\n",
       "0  1           A           B           K           L\n",
       "1  2           C           D           M           N"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner = pd.merge(df1, df2, on='id', how='inner')\n",
    "\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Feature_1_x</th>\n",
       "      <th>Feature_2_x</th>\n",
       "      <th>Feature_1_y</th>\n",
       "      <th>Feature_2_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1_x Feature_2_x Feature_1_y Feature_2_y\n",
       "0  1           A           B           K           L\n",
       "1  2           C           D           M           N\n",
       "2  6         NaN         NaN           O           P\n",
       "3  7         NaN         NaN           Q           R\n",
       "4  8         NaN         NaN           S           T"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_right = pd.merge(df1, df2, on='id', how='right')\n",
    "\n",
    "df_right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Feature_1_x</th>\n",
       "      <th>Feature_2_x</th>\n",
       "      <th>Feature_1_y</th>\n",
       "      <th>Feature_2_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>K</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>G</td>\n",
       "      <td>H</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>I</td>\n",
       "      <td>J</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id Feature_1_x Feature_2_x Feature_1_y Feature_2_y\n",
       "0  1           A           B           K           L\n",
       "1  2           C           D           M           N\n",
       "2  3           E           F         NaN         NaN\n",
       "3  4           G           H         NaN         NaN\n",
       "4  5           I           J         NaN         NaN"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_left = pd.merge(df1, df2, on='id', how='left')\n",
    "\n",
    "df_left"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}