Python-Data Combining.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Two most common encodings- UTF-8 and Latin-1\n",
    "\n",
    "trans1=pd.read_csv(\"Trans1.csv\", encoding=\"Latin-1\")\n",
    "trans2=pd.read_csv(\"Trans2.csv\", encoding=\"Latin-1\")\n",
    "Info=pd.read_csv(\"Info.csv\", encoding=\"Latin-1\")\n"
   ]
  },
  {
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/3/2019</td>\n",
       "      <td>44.00</td>\n",
       "      <td>600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>22.34</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>335.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>2/10/2019</td>\n",
       "      <td>33.50</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Id  Name  Tran_Date  Tran_Amt  Tran_Code       Tran_Desc  SIC_Code\n",
       "0   101   Tim  1/29/2019    145.00        600        Airlines       233\n",
       "1   101   Tim  1/30/2019    164.00        600      Restaurant       434\n",
       "2   101   Tim  1/31/2019    489.00        600  Transportation       244\n",
       "3   101   Tim   2/1/2019    467.00        600          Movies       354\n",
       "4   101   Tim   2/2/2019    445.00        600   Entertainment       445\n",
       "5   101   Tim   2/3/2019     44.00        600             NaN       456\n",
       "6   101   Tim   2/4/2019     45.00        600         Tickets       453\n",
       "7   101   Tim   2/5/2019     12.00        600         Holiday       354\n",
       "8   101   Tim   2/4/2019     45.00        400         Tickets       453\n",
       "9   101   Tim   2/5/2019     12.00        400         Holiday       354\n",
       "10  102  Dana   2/4/2019     22.34        600        Airlines       233\n",
       "11  102  Dana   2/5/2019    335.00        600      Restaurant       434\n",
       "12  103  Mike  2/10/2019     33.50        600      Restaurant       434"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trans1.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Name</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>34.5</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.6</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>566.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name Tran_Date  Tran_Amt  Tran_Code   Tran_Desc  SIC_Code\n",
       "0  104  Pete  2/5/2019      34.5        600     Holiday       354\n",
       "1  104  Pete  2/4/2019      45.6        600    Airlines       233\n",
       "2  105  Paul  2/5/2019     566.0        600  Restaurant       434"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trans2.head(20)"
   ]
  },
  {
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>106</td>\n",
       "      <td>Jon</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>107</td>\n",
       "      <td>Payal</td>\n",
       "      <td>AP</td>\n",
       "      <td>India</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id   Name State Country Gender  Income\n",
       "0  101    Tim    PA     USA      M    60.0\n",
       "1  102   Dana    DE     USA      M    87.0\n",
       "2  103   Mike    AL     USA      M    80.0\n",
       "3  104   Pete    CA     USA      M    45.0\n",
       "4  105   Paul    CA     USA      M     NaN\n",
       "5  106    Jon    DE     USA      M     NaN\n",
       "6  107  Payal    AP   India      F     NaN"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Info.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Name</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/3/2019</td>\n",
       "      <td>44.00</td>\n",
       "      <td>600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>22.34</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>335.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>2/10/2019</td>\n",
       "      <td>33.50</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>34.50</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.60</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>566.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Id  Name  Tran_Date  Tran_Amt  Tran_Code       Tran_Desc  SIC_Code\n",
       "0   101   Tim  1/29/2019    145.00        600        Airlines       233\n",
       "1   101   Tim  1/30/2019    164.00        600      Restaurant       434\n",
       "2   101   Tim  1/31/2019    489.00        600  Transportation       244\n",
       "3   101   Tim   2/1/2019    467.00        600          Movies       354\n",
       "4   101   Tim   2/2/2019    445.00        600   Entertainment       445\n",
       "5   101   Tim   2/3/2019     44.00        600             NaN       456\n",
       "6   101   Tim   2/4/2019     45.00        600         Tickets       453\n",
       "7   101   Tim   2/5/2019     12.00        600         Holiday       354\n",
       "8   101   Tim   2/4/2019     45.00        400         Tickets       453\n",
       "9   101   Tim   2/5/2019     12.00        400         Holiday       354\n",
       "10  102  Dana   2/4/2019     22.34        600        Airlines       233\n",
       "11  102  Dana   2/5/2019    335.00        600      Restaurant       434\n",
       "12  103  Mike  2/10/2019     33.50        600      Restaurant       434\n",
       "0   104  Pete   2/5/2019     34.50        600         Holiday       354\n",
       "1   104  Pete   2/4/2019     45.60        600        Airlines       233\n",
       "2   105  Paul   2/5/2019    566.00        600      Restaurant       434"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Topic 1: Data Combine\n",
    "\n",
    "#1.1 Combining dataframes using concat function- concat_axis=0\n",
    "# Without specifying the axis, the default axis=0 (which bascially combines the data vertically like SQL UNION)\n",
    "Trans=pd.concat([trans1, trans2])\n",
    "\n",
    "Trans.head(20)"
   ]
  },
  {
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/3/2019</td>\n",
       "      <td>44.00</td>\n",
       "      <td>600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>22.34</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>335.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>2/10/2019</td>\n",
       "      <td>33.50</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>34.50</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.60</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>566.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Id  Name  Tran_Date  Tran_Amt  Tran_Code       Tran_Desc  SIC_Code\n",
       "0   101   Tim  1/29/2019    145.00        600        Airlines       233\n",
       "1   101   Tim  1/30/2019    164.00        600      Restaurant       434\n",
       "2   101   Tim  1/31/2019    489.00        600  Transportation       244\n",
       "3   101   Tim   2/1/2019    467.00        600          Movies       354\n",
       "4   101   Tim   2/2/2019    445.00        600   Entertainment       445\n",
       "5   101   Tim   2/3/2019     44.00        600             NaN       456\n",
       "6   101   Tim   2/4/2019     45.00        600         Tickets       453\n",
       "7   101   Tim   2/5/2019     12.00        600         Holiday       354\n",
       "8   101   Tim   2/4/2019     45.00        400         Tickets       453\n",
       "9   101   Tim   2/5/2019     12.00        400         Holiday       354\n",
       "10  102  Dana   2/4/2019     22.34        600        Airlines       233\n",
       "11  102  Dana   2/5/2019    335.00        600      Restaurant       434\n",
       "12  103  Mike  2/10/2019     33.50        600      Restaurant       434\n",
       "13  104  Pete   2/5/2019     34.50        600         Holiday       354\n",
       "14  104  Pete   2/4/2019     45.60        600        Airlines       233\n",
       "15  105  Paul   2/5/2019    566.00        600      Restaurant       434"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1.2 Using the same above with Ignore Index option\n",
    "#ignore_index can be used to clear the existing index in the result\n",
    "#When combining the datasets using above, the indexes in both will remain same. \n",
    "#first dataset- index no starts with 0-12 and second starts with 0-2\n",
    "#On using the ignore_index='True' index numbers continues from 0-15 in the order when data combines vertically. \n",
    "\n",
    "Trans=pd.concat([trans1, trans2],ignore_index='True')\n",
    "\n",
    "Trans.head(20)"
   ]
  },
  {
   "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></th>\n",
       "      <th>Id</th>\n",
       "      <th>Name_x</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Name_y</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/3/2019</td>\n",
       "      <td>44.00</td>\n",
       "      <td>600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>400</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>22.34</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>335.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Mike</td>\n",
       "      <td>2/10/2019</td>\n",
       "      <td>33.50</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>34.50</td>\n",
       "      <td>600</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.60</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Paul</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>566.00</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Id Name_x State Country Gender  Income Name_y  Tran_Date  Tran_Amt  \\\n",
       "0   101    Tim    PA     USA      M    60.0    Tim  1/29/2019    145.00   \n",
       "1   101    Tim    PA     USA      M    60.0    Tim  1/30/2019    164.00   \n",
       "2   101    Tim    PA     USA      M    60.0    Tim  1/31/2019    489.00   \n",
       "3   101    Tim    PA     USA      M    60.0    Tim   2/1/2019    467.00   \n",
       "4   101    Tim    PA     USA      M    60.0    Tim   2/2/2019    445.00   \n",
       "5   101    Tim    PA     USA      M    60.0    Tim   2/3/2019     44.00   \n",
       "6   101    Tim    PA     USA      M    60.0    Tim   2/4/2019     45.00   \n",
       "7   101    Tim    PA     USA      M    60.0    Tim   2/5/2019     12.00   \n",
       "8   101    Tim    PA     USA      M    60.0    Tim   2/4/2019     45.00   \n",
       "9   101    Tim    PA     USA      M    60.0    Tim   2/5/2019     12.00   \n",
       "10  102   Dana    DE     USA      M    87.0   Dana   2/4/2019     22.34   \n",
       "11  102   Dana    DE     USA      M    87.0   Dana   2/5/2019    335.00   \n",
       "12  103   Mike    AL     USA      M    80.0   Mike  2/10/2019     33.50   \n",
       "13  104   Pete    CA     USA      M    45.0   Pete   2/5/2019     34.50   \n",
       "14  104   Pete    CA     USA      M    45.0   Pete   2/4/2019     45.60   \n",
       "15  105   Paul    CA     USA      M     NaN   Paul   2/5/2019    566.00   \n",
       "\n",
       "    Tran_Code       Tran_Desc  SIC_Code  \n",
       "0         600        Airlines       233  \n",
       "1         600      Restaurant       434  \n",
       "2         600  Transportation       244  \n",
       "3         600          Movies       354  \n",
       "4         600   Entertainment       445  \n",
       "5         600             NaN       456  \n",
       "6         600         Tickets       453  \n",
       "7         600         Holiday       354  \n",
       "8         400         Tickets       453  \n",
       "9         400         Holiday       354  \n",
       "10        600        Airlines       233  \n",
       "11        600      Restaurant       434  \n",
       "12        600      Restaurant       434  \n",
       "13        600         Holiday       354  \n",
       "14        600        Airlines       233  \n",
       "15        600      Restaurant       434  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1.3 Combining dataframes using Merge function (Inner Join)\n",
    "# This bascially is similar to inner join where the two customers who exist in Info didn't appear because they dont have transactions\n",
    "# As we have name variable in both datasets, on combining python creates automatically name_x and name_y in the final dataset\n",
    "# If we don't submit how, its inner join by default\n",
    "\n",
    "merge1=pd.merge(left=Info, right=Trans, on='Id')\n",
    "merge1.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Name_x</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Name_y</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id Name_x State Country Gender  Income Name_y  Tran_Date  Tran_Amt  \\\n",
       "0  101    Tim    PA     USA      M    60.0    Tim  1/29/2019     145.0   \n",
       "1  101    Tim    PA     USA      M    60.0    Tim  1/30/2019     164.0   \n",
       "2  101    Tim    PA     USA      M    60.0    Tim  1/31/2019     489.0   \n",
       "3  101    Tim    PA     USA      M    60.0    Tim   2/1/2019     467.0   \n",
       "4  101    Tim    PA     USA      M    60.0    Tim   2/2/2019     445.0   \n",
       "\n",
       "   Tran_Code       Tran_Desc  SIC_Code  \n",
       "0      600.0        Airlines     233.0  \n",
       "1      600.0      Restaurant     434.0  \n",
       "2      600.0  Transportation     244.0  \n",
       "3      600.0          Movies     354.0  \n",
       "4      600.0   Entertainment     445.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1.4 Combining dataframes using Merge function (left Join)\n",
    "# Now you can see 106 and 107 customers who doesn't have transactions\n",
    "merge_left=pd.merge(left=Info, right=Trans, how='left',on='Id')\n",
    "\n",
    "merge_left.head()"
   ]
  },
  {
   "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 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>Name_x</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Name_y</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id Name_x State Country Gender  Income Name_y  Tran_Date  Tran_Amt  \\\n",
       "0  101    Tim    PA     USA      M    60.0    Tim  1/29/2019     145.0   \n",
       "1  101    Tim    PA     USA      M    60.0    Tim  1/30/2019     164.0   \n",
       "2  101    Tim    PA     USA      M    60.0    Tim  1/31/2019     489.0   \n",
       "3  101    Tim    PA     USA      M    60.0    Tim   2/1/2019     467.0   \n",
       "4  101    Tim    PA     USA      M    60.0    Tim   2/2/2019     445.0   \n",
       "\n",
       "   Tran_Code       Tran_Desc  SIC_Code  \n",
       "0        600        Airlines       233  \n",
       "1        600      Restaurant       434  \n",
       "2        600  Transportation       244  \n",
       "3        600          Movies       354  \n",
       "4        600   Entertainment       445  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1.5 Combining dataframes using Merge function (right Join)\n",
    "\n",
    "merge_right=pd.merge(left=Info, right=Trans, how='right',on='Id')\n",
    "merge_right.head()"
   ]
  },
  {
   "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>Name_x</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Name_y</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/3/2019</td>\n",
       "      <td>44.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>456.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.00</td>\n",
       "      <td>400.0</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>12.00</td>\n",
       "      <td>400.0</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>22.34</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>Dana</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>335.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Mike</td>\n",
       "      <td>2/10/2019</td>\n",
       "      <td>33.50</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>34.50</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Pete</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.60</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Paul</td>\n",
       "      <td>2/5/2019</td>\n",
       "      <td>566.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>106</td>\n",
       "      <td>Jon</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>107</td>\n",
       "      <td>Payal</td>\n",
       "      <td>AP</td>\n",
       "      <td>India</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Id Name_x State Country Gender  Income Name_y  Tran_Date  Tran_Amt  \\\n",
       "0   101    Tim    PA     USA      M    60.0    Tim  1/29/2019    145.00   \n",
       "1   101    Tim    PA     USA      M    60.0    Tim  1/30/2019    164.00   \n",
       "2   101    Tim    PA     USA      M    60.0    Tim  1/31/2019    489.00   \n",
       "3   101    Tim    PA     USA      M    60.0    Tim   2/1/2019    467.00   \n",
       "4   101    Tim    PA     USA      M    60.0    Tim   2/2/2019    445.00   \n",
       "5   101    Tim    PA     USA      M    60.0    Tim   2/3/2019     44.00   \n",
       "6   101    Tim    PA     USA      M    60.0    Tim   2/4/2019     45.00   \n",
       "7   101    Tim    PA     USA      M    60.0    Tim   2/5/2019     12.00   \n",
       "8   101    Tim    PA     USA      M    60.0    Tim   2/4/2019     45.00   \n",
       "9   101    Tim    PA     USA      M    60.0    Tim   2/5/2019     12.00   \n",
       "10  102   Dana    DE     USA      M    87.0   Dana   2/4/2019     22.34   \n",
       "11  102   Dana    DE     USA      M    87.0   Dana   2/5/2019    335.00   \n",
       "12  103   Mike    AL     USA      M    80.0   Mike  2/10/2019     33.50   \n",
       "13  104   Pete    CA     USA      M    45.0   Pete   2/5/2019     34.50   \n",
       "14  104   Pete    CA     USA      M    45.0   Pete   2/4/2019     45.60   \n",
       "15  105   Paul    CA     USA      M     NaN   Paul   2/5/2019    566.00   \n",
       "16  106    Jon    DE     USA      M     NaN    NaN        NaN       NaN   \n",
       "17  107  Payal    AP   India      F     NaN    NaN        NaN       NaN   \n",
       "\n",
       "    Tran_Code       Tran_Desc  SIC_Code  \n",
       "0       600.0        Airlines     233.0  \n",
       "1       600.0      Restaurant     434.0  \n",
       "2       600.0  Transportation     244.0  \n",
       "3       600.0          Movies     354.0  \n",
       "4       600.0   Entertainment     445.0  \n",
       "5       600.0             NaN     456.0  \n",
       "6       600.0         Tickets     453.0  \n",
       "7       600.0         Holiday     354.0  \n",
       "8       400.0         Tickets     453.0  \n",
       "9       400.0         Holiday     354.0  \n",
       "10      600.0        Airlines     233.0  \n",
       "11      600.0      Restaurant     434.0  \n",
       "12      600.0      Restaurant     434.0  \n",
       "13      600.0         Holiday     354.0  \n",
       "14      600.0        Airlines     233.0  \n",
       "15      600.0      Restaurant     434.0  \n",
       "16        NaN             NaN       NaN  \n",
       "17        NaN             NaN       NaN  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1.6 Combining dataframes using Merge function (outer Join)\n",
    "\n",
    "merge_outer=pd.merge(left=Info, right=Trans, how='outer',on='Id')\n",
    "\n",
    "merge_outer.head(20)"
   ]
  },
  {
   "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>Name_d1</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Name_d2</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id Name_d1 State Country Gender  Income Name_d2  Tran_Date  Tran_Amt  \\\n",
       "0  101     Tim    PA     USA      M    60.0     Tim  1/29/2019     145.0   \n",
       "1  101     Tim    PA     USA      M    60.0     Tim  1/30/2019     164.0   \n",
       "2  101     Tim    PA     USA      M    60.0     Tim  1/31/2019     489.0   \n",
       "3  101     Tim    PA     USA      M    60.0     Tim   2/1/2019     467.0   \n",
       "4  101     Tim    PA     USA      M    60.0     Tim   2/2/2019     445.0   \n",
       "\n",
       "   Tran_Code       Tran_Desc  SIC_Code  \n",
       "0        600        Airlines       233  \n",
       "1        600      Restaurant       434  \n",
       "2        600  Transportation       244  \n",
       "3        600          Movies       354  \n",
       "4        600   Entertainment       445  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1.7 Use of suffixes\n",
    "# If the variable exists in both datasets during merging, python creates them as var_x and var_y by default\n",
    "#However, if we want to specify the suffix explicitly- we can use the suffix option like below\n",
    "\n",
    "\n",
    "merge_suffix=pd.merge(left=Info, right=Trans, on='Id', suffixes=('_d1','_d2'))\n",
    "\n",
    "merge_suffix.head()"
   ]
  },
  {
   "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_d1</th>\n",
       "      <th>Name_d1</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Id_d2</th>\n",
       "      <th>Name_d2</th>\n",
       "      <th>Tran_Date</th>\n",
       "      <th>Tran_Amt</th>\n",
       "      <th>Tran_Code</th>\n",
       "      <th>Tran_Desc</th>\n",
       "      <th>SIC_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/29/2019</td>\n",
       "      <td>145.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Airlines</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/30/2019</td>\n",
       "      <td>164.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1/31/2019</td>\n",
       "      <td>489.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/1/2019</td>\n",
       "      <td>467.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Movies</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/2/2019</td>\n",
       "      <td>445.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>106</td>\n",
       "      <td>Jon</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/3/2019</td>\n",
       "      <td>44.0</td>\n",
       "      <td>600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>107</td>\n",
       "      <td>Payal</td>\n",
       "      <td>AP</td>\n",
       "      <td>India</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>2/4/2019</td>\n",
       "      <td>45.0</td>\n",
       "      <td>600</td>\n",
       "      <td>Tickets</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id_d1 Name_d1 State Country Gender  Income  Id_d2 Name_d2  Tran_Date  \\\n",
       "0    101     Tim    PA     USA      M    60.0    101     Tim  1/29/2019   \n",
       "1    102    Dana    DE     USA      M    87.0    101     Tim  1/30/2019   \n",
       "2    103    Mike    AL     USA      M    80.0    101     Tim  1/31/2019   \n",
       "3    104    Pete    CA     USA      M    45.0    101     Tim   2/1/2019   \n",
       "4    105    Paul    CA     USA      M     NaN    101     Tim   2/2/2019   \n",
       "5    106     Jon    DE     USA      M     NaN    101     Tim   2/3/2019   \n",
       "6    107   Payal    AP   India      F     NaN    101     Tim   2/4/2019   \n",
       "\n",
       "   Tran_Amt  Tran_Code       Tran_Desc  SIC_Code  \n",
       "0     145.0        600        Airlines       233  \n",
       "1     164.0        600      Restaurant       434  \n",
       "2     489.0        600  Transportation       244  \n",
       "3     467.0        600          Movies       354  \n",
       "4     445.0        600   Entertainment       445  \n",
       "5      44.0        600             NaN       456  \n",
       "6      45.0        600         Tickets       453  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Please Note: We can also join on indexes if needed. However, we won't need that as indexes concept is not useful in the pandas\n",
    "## Below is the syntax if we want to join the dataset using indexes rather than on any columns\n",
    "# Only 0-6 rows exits in Info, as we used left join only 0-6 rows from Trans is pulled based on index join\n",
    "\n",
    "merge_index_suffix=pd.merge(left=Info, right=Trans, how='inner',left_index=True, right_index=True,suffixes=('_d1','_d2'))\n",
    "\n",
    "merge_index_suffix.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Gender_Calc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income  Gender_Calc\n",
       "0  101   Tim    PA     USA      M    60.0            1\n",
       "1  102  Dana    DE     USA      M    87.0            1\n",
       "2  103  Mike    AL     USA      M    80.0            1\n",
       "3  104  Pete    CA     USA      M    45.0            1\n",
       "4  105  Paul    CA     USA      M     NaN            1"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Topic 2: Transforming Data with Pandas\n",
    "# Using map(), apply(), applymap(), apply(), melt()\n",
    "\n",
    "\n",
    "#2.1 Creation of new column based on cases (this is more like CASE statement in SAS)\n",
    "# Below we will use the Gender column to create a new calculated column in the Info dataset \n",
    "# which defines M=1 and F=0\n",
    "\n",
    "def label(element):\n",
    "    if element=='M':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "Info['Gender_Calc']=Info['Gender'].map(label)\n",
    "\n",
    "# Even the below function works same as map and it gives you the same result \n",
    "#Info['Gender_Calc']=Info['Gender'].apply(label)\n",
    "\n",
    "Info.head()"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Income&gt;50</th>\n",
       "      <th>Income&gt;40</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income Income>50 Income>40\n",
       "0  101   Tim    PA     USA      M    60.0      High      High\n",
       "1  102  Dana    DE     USA      M    87.0      High      High\n",
       "2  103  Mike    AL     USA      M    80.0      High      High\n",
       "3  104  Pete    CA     USA      M    45.0       Low      High\n",
       "4  105  Paul    CA     USA      M     NaN       Low       Low"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2.2 Difference between apply() and map()\n",
    "# Map- works with only pre-definied values. We cannot pass any value in arguments for case statements consideration\n",
    "# Apply- works with values as well where we can pass values in arguments for case statements consideration\n",
    "# Below, we use Info data (Income column) and if the income is >50 we will assign High else low\n",
    "\n",
    "Info_appmap=pd.read_csv(\"Info.csv\", encoding=\"Latin-1\")\n",
    "\n",
    "\n",
    "def label(element,x):\n",
    "    if element > x:\n",
    "        return 'High'\n",
    "    else:\n",
    "        return 'Low'\n",
    "\n",
    "# Here, we use the same case statement definition above with 2 values- 40, 50 (by creating 2 columns) with single case definition\n",
    "# Using map we cannot do this because map doesn't accept values.\n",
    "\n",
    "Info_appmap['Income>50']=Info['Income'].apply(label, x=50)\n",
    "\n",
    "Info_appmap['Income>40']=Info['Income'].apply(label, x=40)\n",
    "\n",
    "\n",
    "Info_appmap.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Income&gt;50</th>\n",
       "      <th>Income&gt;40</th>\n",
       "      <th>Gender_Calc</th>\n",
       "      <th>State_Calc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income Income>50 Income>40  Gender_Calc  \\\n",
       "0  101   Tim    PA     USA      M    60.0      High      High            1   \n",
       "1  102  Dana    DE     USA      M    87.0      High      High            1   \n",
       "2  103  Mike    AL     USA      M    80.0      High      High            1   \n",
       "3  104  Pete    CA     USA      M    45.0       Low      High            1   \n",
       "4  105  Paul    CA     USA      M     NaN       Low       Low            1   \n",
       "\n",
       "   State_Calc  \n",
       "0           0  \n",
       "1           0  \n",
       "2           0  \n",
       "3           0  \n",
       "4           0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2.3 Use of applymap()\n",
    "# Applymap() cannot be applied on a single series. It should be applied to Dataframe only which involves more than 1 column\n",
    "# This is useful when you want to apply the same case definition calculation to more than 1 column\n",
    "# For example: lets convert both id and income column in info dataset (>102- High, low)\n",
    "\n",
    "def label(element):\n",
    "    if element=='M':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "        \n",
    "factors=['State', 'Gender']\n",
    "\n",
    "\n",
    "#Here we are calculating case statetment definitions for 2 columns. Hence, we cannot give Info_app['One var'] allocation\n",
    "# as python doesn't know which variable assignment/name is among them\n",
    "#hence, we need to first define one variable val (which basically stores calculated columns as dataframe)\n",
    "# later, they can be assigned back to original dataset by pulling columns from intermediate dataframe that was created.\n",
    "val=Info_appmap[factors].applymap(label)\n",
    "\n",
    "Info_appmap['Gender_Calc']= val['Gender']\n",
    "Info_appmap['State_Calc']= val['State']\n",
    "\n",
    "Info_appmap.head()\n",
    "## Please note that you can also use apply() function to the dataframe as well which is not covered here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Id   Name State Country Gender  Income     variable value\n",
      "0   101    Tim    PA     USA      M    60.0    Income>50  High\n",
      "1   102   Dana    DE     USA      M    87.0    Income>50  High\n",
      "2   103   Mike    AL     USA      M    80.0    Income>50  High\n",
      "3   104   Pete    CA     USA      M    45.0    Income>50   Low\n",
      "4   105   Paul    CA     USA      M     NaN    Income>50   Low\n",
      "5   106    Jon    DE     USA      M     NaN    Income>50   Low\n",
      "6   107  Payal    AP   India      F     NaN    Income>50   Low\n",
      "7   101    Tim    PA     USA      M    60.0    Income>40  High\n",
      "8   102   Dana    DE     USA      M    87.0    Income>40  High\n",
      "9   103   Mike    AL     USA      M    80.0    Income>40  High\n",
      "10  104   Pete    CA     USA      M    45.0    Income>40  High\n",
      "11  105   Paul    CA     USA      M     NaN    Income>40   Low\n",
      "12  106    Jon    DE     USA      M     NaN    Income>40   Low\n",
      "13  107  Payal    AP   India      F     NaN    Income>40   Low\n",
      "14  101    Tim    PA     USA      M    60.0  Gender_Calc     1\n",
      "15  102   Dana    DE     USA      M    87.0  Gender_Calc     1\n",
      "16  103   Mike    AL     USA      M    80.0  Gender_Calc     1\n",
      "17  104   Pete    CA     USA      M    45.0  Gender_Calc     1\n",
      "18  105   Paul    CA     USA      M     NaN  Gender_Calc     1\n",
      "19  106    Jon    DE     USA      M     NaN  Gender_Calc     1\n",
      "20  107  Payal    AP   India      F     NaN  Gender_Calc     0\n",
      "21  101    Tim    PA     USA      M    60.0   State_Calc     0\n",
      "22  102   Dana    DE     USA      M    87.0   State_Calc     0\n",
      "23  103   Mike    AL     USA      M    80.0   State_Calc     0\n",
      "24  104   Pete    CA     USA      M    45.0   State_Calc     0\n",
      "25  105   Paul    CA     USA      M     NaN   State_Calc     0\n",
      "26  106    Jon    DE     USA      M     NaN   State_Calc     0\n",
      "27  107  Payal    AP   India      F     NaN   State_Calc     0\n"
     ]
    }
   ],
   "source": [
    "#2.4 Using pd.melt(): unpivots a Dataframe from wide format to long format\n",
    "# Info_appmap has 4 calculated varaibles (Income>50, Income>40, Gender_Calc, and State_Calc)\n",
    "# Syntax: pd.melt(df,id_vars=[col1,col2],value_vars=[col1,col2])\n",
    "# id_vars= are the columns that should remain same in the final dataset\n",
    "#value_vars= are the names of the columns that should be changed to rows in the result\n",
    "# Melt function is used to reshape our data so that we could use vectorized operation on single column if needed.\n",
    "\n",
    "\n",
    "id_variables=['Id','Name','State','Country','Gender','Income']\n",
    "value_variables=['Income>50','Income>40','Gender_Calc','State_Calc']\n",
    "\n",
    "melt_df=pd.melt(Info_appmap, id_vars=id_variables, value_vars=value_variables)\n",
    "\n",
    "print(melt_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Income&gt;50</th>\n",
       "      <th>Income&gt;40</th>\n",
       "      <th>Gender_Calc_Rename</th>\n",
       "      <th>State_Calc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>106</td>\n",
       "      <td>Jon</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>107</td>\n",
       "      <td>Payal</td>\n",
       "      <td>AP</td>\n",
       "      <td>India</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id   Name State Country Gender  Income Income>50 Income>40  \\\n",
       "0  101    Tim    PA     USA      M    60.0      High      High   \n",
       "1  102   Dana    DE     USA      M    87.0      High      High   \n",
       "2  103   Mike    AL     USA      M    80.0      High      High   \n",
       "3  104   Pete    CA     USA      M    45.0       Low      High   \n",
       "4  105   Paul    CA     USA      M     NaN       Low       Low   \n",
       "5  106    Jon    DE     USA      M     NaN       Low       Low   \n",
       "6  107  Payal    AP   India      F     NaN       Low       Low   \n",
       "\n",
       "   Gender_Calc_Rename  State_Calc  \n",
       "0                   1           0  \n",
       "1                   1           0  \n",
       "2                   1           0  \n",
       "3                   1           0  \n",
       "4                   1           0  \n",
       "5                   1           0  \n",
       "6                   0           0  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Topic 3: Working with Strings in Pandas\n",
    "#3.1 Renaming one of the column\n",
    "new=\"Gender_Calc_Rename\"\n",
    "Info_appmap.rename(columns={'Gender_Calc':new})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#3.2 Commonly used String functions\n",
    "\n",
    "#Series.str.spllit()- Splits each element in the Series\n",
    "#Series.str.strip()- Strips white spaces from each string in series\n",
    "#Series.str.lower()- Converts strings in series to lower case letters\n",
    "#Series.str.upper()- Converts strings in series to upper case letters\n",
    "#Series.str.get()-  Retreievs the ith element of each element in the Series\n",
    "#Series.str.replace()- Replaces regex of string in the series with another string\n",
    "#Series.str.cat()- Concatenates strings in a Series\n",
    "#Series.str.extract()- Extracts substrings from Series matching the regex pattern"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Income&gt;50</th>\n",
       "      <th>Income&gt;40</th>\n",
       "      <th>Gender_Calc</th>\n",
       "      <th>State_Calc</th>\n",
       "      <th>Country_Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income Income>50 Income>40  Gender_Calc  \\\n",
       "0  101   Tim    PA     USA      M    60.0      High      High            1   \n",
       "1  102  Dana    DE     USA      M    87.0      High      High            1   \n",
       "2  103  Mike    AL     USA      M    80.0      High      High            1   \n",
       "3  104  Pete    CA     USA      M    45.0       Low      High            1   \n",
       "4  105  Paul    CA     USA      M     NaN       Low       Low            1   \n",
       "\n",
       "   State_Calc  Country_Length  \n",
       "0           0               3  \n",
       "1           0               3  \n",
       "2           0               3  \n",
       "3           0               3  \n",
       "4           0               3  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#3.3 Calculating the lenth of a string for one column and store it in a different column\n",
    " # When calculating the length of any string column, we need to make sure that we take care of NaN values\n",
    " # because NaN values will be coming as length as 3 eventhough we knew they are \n",
    "\n",
    "def compute_lengths(element):\n",
    "     if pd.isnull(element):\n",
    "         pass\n",
    "     else:\n",
    "         return len(str(element))     \n",
    "    \n",
    "Info_appmap['Country_Length']= Info_appmap['Country'].apply(compute_lengths)\n",
    "\n",
    "\n",
    "Info_appmap.head()\n",
    "\n",
    "#You can see that the Country length is calculated which is the last column in the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Income&gt;50</th>\n",
       "      <th>Income&gt;40</th>\n",
       "      <th>Gender_Calc</th>\n",
       "      <th>State_Calc</th>\n",
       "      <th>Country_Length</th>\n",
       "      <th>New_Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>106</td>\n",
       "      <td>Jon</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>107</td>\n",
       "      <td>Payal</td>\n",
       "      <td>AP</td>\n",
       "      <td>India</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>INDIA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id   Name State Country Gender  Income Income>50 Income>40  Gender_Calc  \\\n",
       "2  103   Mike    AL     USA      M    80.0      High      High            1   \n",
       "3  104   Pete    CA     USA      M    45.0       Low      High            1   \n",
       "4  105   Paul    CA     USA      M     NaN       Low       Low            1   \n",
       "5  106    Jon    DE     USA      M     NaN       Low       Low            1   \n",
       "6  107  Payal    AP   India      F     NaN       Low       Low            0   \n",
       "\n",
       "   State_Calc  Country_Length New_Country  \n",
       "2           0               3         USA  \n",
       "3           0               3         USA  \n",
       "4           0               3         USA  \n",
       "5           0               3         USA  \n",
       "6           0               5       INDIA  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#3.4 Creating a new calculated column by converting the string stored in one column to upper case letters\n",
    "\n",
    "Info_appmap['New_Country']= Info_appmap['Country'].str.upper()\n",
    "Info_appmap.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Income&gt;50</th>\n",
       "      <th>Income&gt;40</th>\n",
       "      <th>Gender_Calc</th>\n",
       "      <th>State_Calc</th>\n",
       "      <th>Country_Length</th>\n",
       "      <th>New_Country</th>\n",
       "      <th>Pa_name_pattern</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income Income>50 Income>40  Gender_Calc  \\\n",
       "0  101   Tim    PA     USA      M    60.0      High      High            1   \n",
       "1  102  Dana    DE     USA      M    87.0      High      High            1   \n",
       "2  103  Mike    AL     USA      M    80.0      High      High            1   \n",
       "3  104  Pete    CA     USA      M    45.0       Low      High            1   \n",
       "4  105  Paul    CA     USA      M     NaN       Low       Low            1   \n",
       "\n",
       "   State_Calc  Country_Length New_Country  Pa_name_pattern  \n",
       "0           0               3         USA            False  \n",
       "1           0               3         USA            False  \n",
       "2           0               3         USA            False  \n",
       "3           0               3         USA            False  \n",
       "4           0               3         USA             True  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#3.5 Pattern Searching- Using contains\n",
    "# For example: lets search for pattern in Name in Info_appmap- we are looking for pattern in names that starts with Pa\n",
    "#Pa_name_pattern column created at the end with True or false based on existing pattern available for Name column\n",
    "\n",
    "pattern=r\"[Pp]a\"\n",
    "\n",
    "Info_appmap['Pa_name_pattern']=Info_appmap['Name'].str.contains(pattern)\n",
    "Info_appmap.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Income&gt;50</th>\n",
       "      <th>Income&gt;40</th>\n",
       "      <th>Gender_Calc</th>\n",
       "      <th>State_Calc</th>\n",
       "      <th>Country_Length</th>\n",
       "      <th>New_Country</th>\n",
       "      <th>Pa_name_pattern</th>\n",
       "      <th>Pattern_Match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "      <td>Pattern_Mismatch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "      <td>Pattern_Mismatch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "      <td>Pattern_Mismatch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>False</td>\n",
       "      <td>Pattern_Mismatch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Low</td>\n",
       "      <td>Low</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>True</td>\n",
       "      <td>Pattern_Match</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income Income>50 Income>40  Gender_Calc  \\\n",
       "0  101   Tim    PA     USA      M    60.0      High      High            1   \n",
       "1  102  Dana    DE     USA      M    87.0      High      High            1   \n",
       "2  103  Mike    AL     USA      M    80.0      High      High            1   \n",
       "3  104  Pete    CA     USA      M    45.0       Low      High            1   \n",
       "4  105  Paul    CA     USA      M     NaN       Low       Low            1   \n",
       "\n",
       "   State_Calc  Country_Length New_Country  Pa_name_pattern     Pattern_Match  \n",
       "0           0               3         USA            False  Pattern_Mismatch  \n",
       "1           0               3         USA            False  Pattern_Mismatch  \n",
       "2           0               3         USA            False  Pattern_Mismatch  \n",
       "3           0               3         USA            False  Pattern_Mismatch  \n",
       "4           0               3         USA             True     Pattern_Match  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The above column yields us results like True, False (bool_values)\n",
    "#Although, they are created as True/False, their internal values is still 1 and 0.\n",
    "# Using True/False in below case definition won't work. We need to use 1 and 0\n",
    "# Column created with \"String values\" instead of True/False Boolean values\n",
    "\n",
    "def pattern_match(element):\n",
    "    if element==1:\n",
    "        return \"Pattern_Match\"\n",
    "    else:\n",
    "        return \"Pattern_Mismatch\"\n",
    "\n",
    "Info_appmap['Pattern_Match']=Info_appmap['Pa_name_pattern'].apply(pattern_match)\n",
    "Info_appmap.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#3.6 extract() and extractall()\n",
    "# For example: lets write pattern searh for valid year: for valid year- lets assume year is valid only if it starts with 1 or 2\n",
    "# and the remaining 3 should be any number from 0-9\n",
    "# Pattern string for this can be written as follows\n",
    "pattern= r\"[1-2][0=9]{3}]\" # This is pattern for every letter. if there is repetition of letter we can give it as {n}\n",
    "# where n is the number of repetitions\n",
    "\n",
    "pattern=r\"[A-Z]\" # Pattern for upper case letters\n",
    "pattern=r\"[a-z]\" # Pattern for lower case letters\n",
    "\n",
    "# expand=True (You don't have to create a new varible with in dataset with bool values created as result of extract function)\n",
    "# Instead, you can just create using - expand=True option with in extract() function\n",
    "\n",
    "                                                                         \n",
    "#extractall(pattern): extract looks for only first pattern in the given string and stops. However, extractall looks for all\n",
    "# n numnber of matches with in the string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Id             0\n",
      "Name           0\n",
      "State          0\n",
      "Country        0\n",
      "Gender         0\n",
      "Income         3\n",
      "Gender_Calc    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Topic 4: Working with Missing and Duplicate Data\n",
    "\n",
    "#4.1 Identifying missing values\n",
    "# Let's identify missing values in the dataset Info\n",
    "# The below code prints the column names of the dataset and the number of missing values each column contain\n",
    "\n",
    "print(Info.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    False\n",
      "1     True\n",
      "2     True\n",
      "3     True\n",
      "4     True\n",
      "5     True\n",
      "6    False\n",
      "dtype: bool\n"
     ]
    }
   ],
   "source": [
    "#4.2 Indentifying the duplicate values\n",
    "# First match in the column will be taken as False(not dup) and later if we have same then it returns True which means dup\n",
    "print(Info.duplicated(['Country']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    False\n",
      "1    False\n",
      "2    False\n",
      "3    False\n",
      "4    False\n",
      "5    False\n",
      "6    False\n",
      "dtype: bool\n"
     ]
    }
   ],
   "source": [
    "#Name is unique in our dataset. Hence, everything should return False as we used name and country combination for dup check\n",
    "print(Info.duplicated(['Name','Country']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Gender_Calc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income  Gender_Calc\n",
       "0  101   Tim    PA     USA      M    60.0            1\n",
       "1  102  Dana    DE     USA      M    87.0            1\n",
       "2  103  Mike    AL     USA      M    80.0            1\n",
       "3  104  Pete    CA     USA      M    45.0            1\n",
       "4  105  Paul    CA     USA      M     NaN            1"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#4.3 Dropping the duplicates\n",
    "#Info and Info_drop1 will be same after the below statement because there are no duplicates identified to drop\n",
    "\n",
    "Info_drop1=Info.drop_duplicates(['Name','Country'])\n",
    "Info_drop1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Gender_Calc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>107</td>\n",
       "      <td>Payal</td>\n",
       "      <td>AP</td>\n",
       "      <td>India</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id   Name State Country Gender  Income  Gender_Calc\n",
       "0  101    Tim    PA     USA      M    60.0            1\n",
       "6  107  Payal    AP   India      F     NaN            0"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Let's try with only country where we identified the duplicates\n",
    "# only 2 rows should be returned because 5 rows are identified as duplicates\n",
    "Info_drop2=Info.drop_duplicates(['Country'])\n",
    "Info_drop2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "68.0\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>Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Income</th>\n",
       "      <th>Gender_Calc</th>\n",
       "      <th>Imputed_Income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>Tim</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>Dana</td>\n",
       "      <td>DE</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>87.0</td>\n",
       "      <td>1</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>Mike</td>\n",
       "      <td>AL</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>1</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>Pete</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>45.0</td>\n",
       "      <td>1</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>Paul</td>\n",
       "      <td>CA</td>\n",
       "      <td>USA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  Name State Country Gender  Income  Gender_Calc  Imputed_Income\n",
       "0  101   Tim    PA     USA      M    60.0            1            60.0\n",
       "1  102  Dana    DE     USA      M    87.0            1            87.0\n",
       "2  103  Mike    AL     USA      M    80.0            1            80.0\n",
       "3  104  Pete    CA     USA      M    45.0            1            45.0\n",
       "4  105  Paul    CA     USA      M     NaN            1            68.0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#4.4 Imputation of missing values with mean or any fixed value- Using fillno()\n",
    "\n",
    "mean_income= Info['Income'].mean()\n",
    "print(mean_income)\n",
    "Info['Imputed_Income']=Info['Income'].fillna(mean_income)\n",
    "\n",
    "Info.head()\n",
    "#You can see the Inputed column at the end of the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#4.5 Dropping rows/columns which contains missing values\n",
    "\n",
    "# df.dropna()- Drop rows with missing values\n",
    "# df.drop(columns_to_drop, axis=1)\n",
    "# df.dropna(thresh=min_nonnull, axis=1)- Drop columns with less than a certain number of non null values \n",
    "# The last option is widely used in data preparation for predictive modeling algorithms"
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}