pandas_part8.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging dataframes \n",
    "-------\n",
    "    merge() is a method in pandas in which you can merge two dataframes withou repeating columns as we did in\n",
    "    concat() method.\n",
    "    There are two types of merging -\n",
    "       1) Inner join\n",
    "       2) outer join\n",
    " ### 1) Inner join\n",
    "    In this method you will get the intersetion of two dataframes with merged column.It is the default merge method.\n",
    "    In merge() method you have to pass the dataframes as arguments and list of columns on which you want to merge\n",
    "    and dataframes as arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-02-12</td>\n",
       "      <td>rainy</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  event  temp\n",
       "0  01-02-12  sunny    14\n",
       "1  03-02-12   cold    16\n",
       "2  04-02-12   cold    15\n",
       "3  05-02-12  rainy    10"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1={\n",
    "      \"date\":['01-02-12','03-02-12','04-02-12','05-02-12'],\n",
    "      \"event\":['sunny','cold','cold','rainy'],\n",
    "      \"temp\":[14,16,15,10]\n",
    "}\n",
    "temp=pd.DataFrame(temp1)\n",
    "temp"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-02-12</td>\n",
       "      <td>rainy</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  event  wind-speed\n",
       "0  01-02-12  sunny          12\n",
       "1  03-02-12   cold          10\n",
       "2  04-02-12   cold           9\n",
       "3  05-02-12  rainy          14"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws={\n",
    "      \"date\":['01-02-12','03-02-12','04-02-12','05-02-12'],\n",
    "      \"event\":['sunny','cold','cold','rainy'],\n",
    "      \"wind-speed\":[12,10,9,14],\n",
    "}\n",
    "wind_speed=pd.DataFrame(ws)\n",
    "wind_speed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>temp</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-02-12</td>\n",
       "      <td>rainy</td>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  event  temp  wind-speed\n",
       "0  01-02-12  sunny    14          12\n",
       "1  03-02-12   cold    16          10\n",
       "2  04-02-12   cold    15           9\n",
       "3  05-02-12  rainy    10          14"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.merge(temp,wind_speed,on=['date','event'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    If you have different data in the common columns then you can not do inner merge in this case.\n",
    "    Assume the following case:\n",
    "    \n",
    "      temp                                \n",
    "      ___________________\n",
    "     | event  |   temp   |\n",
    "     |--------|----------|\n",
    "     | sunny  |   40     |\n",
    "     | hot    |   30     |\n",
    "     | rainy  |   25     |\n",
    "     |________|__________|\n",
    "     \n",
    "     wind-speed\n",
    "      ___________________\n",
    "     | event  |wind-speed|\n",
    "     |--------|----------|\n",
    "     | sunny  |   12     |\n",
    "     | hot    |   14     |\n",
    "     | hot    |   11     |\n",
    "     |________|__________|\n",
    "     \n",
    "     Merged Dataframe by inner method\n",
    "     \n",
    "      ________________________________\n",
    "     | event  |   temp   | wind-speed |\n",
    "     |--------|----------|------------|\n",
    "     | sunny  |   40     |     12     | \n",
    "     | hot    |   30     |     14     |\n",
    "     |________|__________|____________|\n",
    "     \n",
    "     You can see that last row is not merged here because there is no common element in the common columns.\n",
    "     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Outer join\n",
    "This is just like union of two dataframe.The value which dont exist will contain NaN.                               \n",
    "     \n",
    "\n",
    "     temp                                \n",
    "      ___________________\n",
    "     | event  |   temp   |\n",
    "     |--------|----------|\n",
    "     | sunny  |   40     |\n",
    "     | hot    |   30     |\n",
    "     | rainy  |   25     |\n",
    "     |________|__________|\n",
    "     \n",
    "     wind-speed\n",
    "      ___________________\n",
    "     | event  |wind-speed|\n",
    "     |--------|----------|\n",
    "     | sunny  |   12     |\n",
    "     | hot    |   14     |\n",
    "     | hot    |   11     |\n",
    "     |________|__________|\n",
    "     \n",
    "     Merged Dataframe by inner method\n",
    "     \n",
    "      ________________________________\n",
    "     | event  |   temp   | wind-speed |\n",
    "     |--------|----------|------------|\n",
    "     | sunny  |   40     |     12     | \n",
    "     | hot    |   30     |     14     |\n",
    "     | rainy  |   25     |     NaN    |\n",
    "     | hot    |   NaN    |     11     |\n",
    "     |________|__________|____________|\n",
    "     \n",
    "     You can see in the final dataframe two rows increased."
   ]
  },
  {
   "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-02-12</td>\n",
       "      <td>hot</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  event  temp\n",
       "0  01-02-12  sunny    14\n",
       "1  03-02-12   cold    16\n",
       "2  04-02-12    hot    15\n",
       "3  05-02-12  sunny    10"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1={\n",
    "      \"date\":['01-02-12','03-02-12','04-02-12','05-02-12'],\n",
    "      \"event\":['sunny','cold','hot','sunny'],\n",
    "      \"temp\":[14,16,15,10]\n",
    "}\n",
    "temp=pd.DataFrame(temp1)\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-02-12</td>\n",
       "      <td>rainy</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  event  wind-speed\n",
       "0  01-02-12  sunny          12\n",
       "1  03-02-12   cold          10\n",
       "2  04-02-12   cold           9\n",
       "3  05-02-12  rainy          14"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws={\n",
    "      \"date\":['01-02-12','03-02-12','04-02-12','05-02-12'],\n",
    "      \"event\":['sunny','cold','cold','rainy'],\n",
    "      \"wind-speed\":[12,10,9,14],\n",
    "}\n",
    "wind_speed=pd.DataFrame(ws)\n",
    "wind_speed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### One extra argument you need to pass in outer join i.e, how=\"outer\".By defaut it is inner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>temp</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>14.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-02-12</td>\n",
       "      <td>hot</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-02-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>04-02-12</td>\n",
       "      <td>cold</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>05-02-12</td>\n",
       "      <td>rainy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  event  temp  wind-speed\n",
       "0  01-02-12  sunny  14.0        12.0\n",
       "1  03-02-12   cold  16.0        10.0\n",
       "2  04-02-12    hot  15.0         NaN\n",
       "3  05-02-12  sunny  10.0         NaN\n",
       "4  04-02-12   cold   NaN         9.0\n",
       "5  05-02-12  rainy   NaN        14.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.merge(temp,wind_speed,on=['date','event'],how='outer')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}