{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Manupaltion using Python Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The following operations in Pandas are done to achieve the same results shown in vignette of data.table package in R."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Basics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>cancelled</th>\n",
       "      <th>carrier</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>914</td>\n",
       "      <td>14</td>\n",
       "      <td>1238</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N338AA</td>\n",
       "      <td>1</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>359</td>\n",
       "      <td>2475</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1157</td>\n",
       "      <td>-3</td>\n",
       "      <td>1523</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N335AA</td>\n",
       "      <td>3</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>363</td>\n",
       "      <td>2475</td>\n",
       "      <td>11</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1902</td>\n",
       "      <td>2</td>\n",
       "      <td>2224</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N327AA</td>\n",
       "      <td>21</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>351</td>\n",
       "      <td>2475</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>722</td>\n",
       "      <td>-8</td>\n",
       "      <td>1014</td>\n",
       "      <td>-26</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N3EHAA</td>\n",
       "      <td>29</td>\n",
       "      <td>LGA</td>\n",
       "      <td>PBI</td>\n",
       "      <td>157</td>\n",
       "      <td>1035</td>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1347</td>\n",
       "      <td>2</td>\n",
       "      <td>1706</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N319AA</td>\n",
       "      <td>117</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>350</td>\n",
       "      <td>2475</td>\n",
       "      <td>13</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day  dep_time  dep_delay  arr_time  arr_delay  cancelled  \\\n",
       "0  2014      1    1       914         14      1238         13          0   \n",
       "1  2014      1    1      1157         -3      1523         13          0   \n",
       "2  2014      1    1      1902          2      2224          9          0   \n",
       "3  2014      1    1       722         -8      1014        -26          0   \n",
       "4  2014      1    1      1347          2      1706          1          0   \n",
       "\n",
       "  carrier tailnum  flight origin dest  air_time  distance  hour  min  \n",
       "0      AA  N338AA       1    JFK  LAX       359      2475     9   14  \n",
       "1      AA  N335AA       3    JFK  LAX       363      2475    11   57  \n",
       "2      AA  N327AA      21    JFK  LAX       351      2475    19    2  \n",
       "3      AA  N3EHAA      29    LGA  PBI       157      1035     7   22  \n",
       "4      AA  N319AA     117    JFK  LAX       350      2475    13   47  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv file in pandas:\n",
    "\n",
    "flights = pd.read_csv(\"D:\\PythonPandas\\FlightsNew.csv\")\n",
    "flights.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p> In this notebook, we will :</p>\n",
    "<p>start with basics  - how to subset rows, select and compute on columns</p>\n",
    "<p>and then we will look at performing data aggregations by group.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get all the flights with “JFK” as the origin airport in the month of March."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>cancelled</th>\n",
       "      <th>carrier</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43609</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>849</td>\n",
       "      <td>-11</td>\n",
       "      <td>1306</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N784AA</td>\n",
       "      <td>1</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>375</td>\n",
       "      <td>2475</td>\n",
       "      <td>8</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>43610</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1157</td>\n",
       "      <td>-3</td>\n",
       "      <td>1529</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N787AA</td>\n",
       "      <td>3</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>368</td>\n",
       "      <td>2475</td>\n",
       "      <td>11</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>43611</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>652</td>\n",
       "      <td>-3</td>\n",
       "      <td>1019</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N785AA</td>\n",
       "      <td>9</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>366</td>\n",
       "      <td>2475</td>\n",
       "      <td>6</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>43612</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1117</td>\n",
       "      <td>47</td>\n",
       "      <td>1502</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N788AA</td>\n",
       "      <td>19</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>379</td>\n",
       "      <td>2475</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>43613</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1950</td>\n",
       "      <td>50</td>\n",
       "      <td>2321</td>\n",
       "      <td>66</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N336AA</td>\n",
       "      <td>21</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>350</td>\n",
       "      <td>2475</td>\n",
       "      <td>19</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  year  month  day  dep_time  dep_delay  arr_time  arr_delay  \\\n",
       "0  43609  2014      3    1       849        -11      1306         36   \n",
       "1  43610  2014      3    1      1157         -3      1529         14   \n",
       "2  43611  2014      3    1       652         -3      1019          4   \n",
       "3  43612  2014      3    1      1117         47      1502         77   \n",
       "4  43613  2014      3    1      1950         50      2321         66   \n",
       "\n",
       "   cancelled carrier tailnum  flight origin dest  air_time  distance  hour  \\\n",
       "0          0      AA  N784AA       1    JFK  LAX       375      2475     8   \n",
       "1          0      AA  N787AA       3    JFK  LAX       368      2475    11   \n",
       "2          0      AA  N785AA       9    JFK  LAX       366      2475     6   \n",
       "3          0      AA  N788AA      19    JFK  LAX       379      2475    11   \n",
       "4          0      AA  N336AA      21    JFK  LAX       350      2475    19   \n",
       "\n",
       "   min  \n",
       "0   49  \n",
       "1   57  \n",
       "2   52  \n",
       "3   17  \n",
       "4   50  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ans=flights.loc[(flights[\"origin\"].isin([\"JFK\"]))&(flights[\"month\"]==3)].reset_index()\n",
    "ans.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get the first two rows from flights. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>cancelled</th>\n",
       "      <th>carrier</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>914</td>\n",
       "      <td>14</td>\n",
       "      <td>1238</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N338AA</td>\n",
       "      <td>1</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>359</td>\n",
       "      <td>2475</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1157</td>\n",
       "      <td>-3</td>\n",
       "      <td>1523</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>AA</td>\n",
       "      <td>N335AA</td>\n",
       "      <td>3</td>\n",
       "      <td>JFK</td>\n",
       "      <td>LAX</td>\n",
       "      <td>363</td>\n",
       "      <td>2475</td>\n",
       "      <td>11</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day  dep_time  dep_delay  arr_time  arr_delay  cancelled  \\\n",
       "0  2014      1    1       914         14      1238         13          0   \n",
       "1  2014      1    1      1157         -3      1523         13          0   \n",
       "\n",
       "  carrier tailnum  flight origin dest  air_time  distance  hour  min  \n",
       "0      AA  N338AA       1    JFK  LAX       359      2475     9   14  \n",
       "1      AA  N335AA       3    JFK  LAX       363      2475    11   57  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_tworows=flights.loc[0:1]\n",
    "flights_tworows.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sort flights first by column origin in ascending order, and then by dest in descending order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>dep_time</th>\n",
       "      <th>dep_delay</th>\n",
       "      <th>arr_time</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>cancelled</th>\n",
       "      <th>carrier</th>\n",
       "      <th>tailnum</th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>air_time</th>\n",
       "      <th>distance</th>\n",
       "      <th>hour</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2790</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>836</td>\n",
       "      <td>6</td>\n",
       "      <td>1151</td>\n",
       "      <td>49</td>\n",
       "      <td>0</td>\n",
       "      <td>EV</td>\n",
       "      <td>N12175</td>\n",
       "      <td>4419</td>\n",
       "      <td>EWR</td>\n",
       "      <td>XNA</td>\n",
       "      <td>195</td>\n",
       "      <td>1131</td>\n",
       "      <td>8</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3351</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>833</td>\n",
       "      <td>7</td>\n",
       "      <td>1111</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>EV</td>\n",
       "      <td>N24128</td>\n",
       "      <td>4419</td>\n",
       "      <td>EWR</td>\n",
       "      <td>XNA</td>\n",
       "      <td>190</td>\n",
       "      <td>1131</td>\n",
       "      <td>8</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4066</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>811</td>\n",
       "      <td>-6</td>\n",
       "      <td>1035</td>\n",
       "      <td>-13</td>\n",
       "      <td>0</td>\n",
       "      <td>EV</td>\n",
       "      <td>N12142</td>\n",
       "      <td>4419</td>\n",
       "      <td>EWR</td>\n",
       "      <td>XNA</td>\n",
       "      <td>179</td>\n",
       "      <td>1131</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4825</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>810</td>\n",
       "      <td>-7</td>\n",
       "      <td>1036</td>\n",
       "      <td>-12</td>\n",
       "      <td>0</td>\n",
       "      <td>EV</td>\n",
       "      <td>N11193</td>\n",
       "      <td>4419</td>\n",
       "      <td>EWR</td>\n",
       "      <td>XNA</td>\n",
       "      <td>184</td>\n",
       "      <td>1131</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5658</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>833</td>\n",
       "      <td>16</td>\n",
       "      <td>1055</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>EV</td>\n",
       "      <td>N14198</td>\n",
       "      <td>4419</td>\n",
       "      <td>EWR</td>\n",
       "      <td>XNA</td>\n",
       "      <td>181</td>\n",
       "      <td>1131</td>\n",
       "      <td>8</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  year  month  day  dep_time  dep_delay  arr_time  arr_delay  \\\n",
       "0   2790  2014      1    5       836          6      1151         49   \n",
       "1   3351  2014      1    6       833          7      1111         13   \n",
       "2   4066  2014      1    7       811         -6      1035        -13   \n",
       "3   4825  2014      1    8       810         -7      1036        -12   \n",
       "4   5658  2014      1    9       833         16      1055          7   \n",
       "\n",
       "   cancelled carrier tailnum  flight origin dest  air_time  distance  hour  \\\n",
       "0          0      EV  N12175    4419    EWR  XNA       195      1131     8   \n",
       "1          0      EV  N24128    4419    EWR  XNA       190      1131     8   \n",
       "2          0      EV  N12142    4419    EWR  XNA       179      1131     8   \n",
       "3          0      EV  N11193    4419    EWR  XNA       184      1131     8   \n",
       "4          0      EV  N14198    4419    EWR  XNA       181      1131     8   \n",
       "\n",
       "   min  \n",
       "0   36  \n",
       "1   33  \n",
       "2   11  \n",
       "3   10  \n",
       "4   33  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_sort=flights.sort_values([\"origin\",\"dest\"],ascending=[1,0]).reset_index()\n",
    "flights_sort.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select both arr_delay and dep_delay columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>arr_delay</th>\n",
       "      <th>dep_delay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-26</td>\n",
       "      <td>-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   arr_delay  dep_delay\n",
       "0         13         14\n",
       "1         13         -3\n",
       "2          9          2\n",
       "3        -26         -8\n",
       "4          1          2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arr_dep_delay=flights[[\"arr_delay\",\"dep_delay\"]].copy()\n",
    "arr_dep_delay.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select both arr_delay and dep_delay columns and rename them to delay_arr and delay_dep."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>delay_arr</th>\n",
       "      <th>delay_dep</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-26</td>\n",
       "      <td>-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   delay_arr  delay_dep\n",
       "0         13         14\n",
       "1         13         -3\n",
       "2          9          2\n",
       "3        -26         -8\n",
       "4          1          2"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delay_arr_dep=flights[[\"arr_delay\",\"dep_delay\"]].rename(columns={\"arr_delay\":\"delay_arr\",\"dep_delay\":\"delay_dep\"})\n",
    "delay_arr_dep.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How many trips have had total delay < 0?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "141814"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Step1: Create a column called \"delay\" in \"flights\" dataframe and assign 0 to it.\n",
    "flights[\"delay\"]=0\n",
    "#Step2: Check for cases where arr_delay + dep_delay is less than 0\n",
    "flights.loc[(flights[\"arr_delay\"]+flights[\"dep_delay\"])<0,\"delay\"]=1\n",
    "#Step3: Count the number of 1 in the delay column\n",
    "flights.loc[flights[\"delay\"]==1][\"delay\"].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calculate the average arrival and departure delay for all flights with “JFK” as the origin airport in the month of June."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "arr_delay     7.731465\n",
       "dep_delay    11.446167\n",
       "dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.loc[flights[\"origin\"].isin([\"JFK\"])][[\"arr_delay\",\"dep_delay\"]].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How many trips have been made in 2014 from “JFK” airport in the month of June?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8422"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(flights.loc[(flights[\"origin\"].isin([\"JFK\"]))&(flights[\"month\"]==6)][\"origin\"].tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Aggregations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How can we get the number of trips corresponding to each origin airport?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "aggregations={\"origin\":\"count\"}\n",
    "             "
   ]
  },
  {
   "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>origin</th>\n",
       "      <th>number_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EWR</td>\n",
       "      <td>87400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JFK</td>\n",
       "      <td>81483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LGA</td>\n",
       "      <td>84433</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  origin  number_trips\n",
       "0    EWR         87400\n",
       "1    JFK         81483\n",
       "2    LGA         84433"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.groupby([\"origin\"]).agg({\"origin\":\"count\"}).rename(columns={\"origin\":\"number_trips\"}).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How can we calculate the number of trips for each origin airport for carrier code “AA”?"
   ]
  },
  {
   "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>origin</th>\n",
       "      <th>number_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EWR</td>\n",
       "      <td>2649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JFK</td>\n",
       "      <td>11923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LGA</td>\n",
       "      <td>11730</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  origin  number_trips\n",
       "0    EWR          2649\n",
       "1    JFK         11923\n",
       "2    LGA         11730"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.loc[flights[\"carrier\"].isin([\"AA\"])].groupby([\"origin\"]).count().reset_index()[['origin','flight']].rename(columns={\"flight\":\"number_trips\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How can we get the total number of trips for each origin, dest pair for carrier code “AA”?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>number_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EWR</td>\n",
       "      <td>DFW</td>\n",
       "      <td>1618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EWR</td>\n",
       "      <td>LAX</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EWR</td>\n",
       "      <td>MIA</td>\n",
       "      <td>848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EWR</td>\n",
       "      <td>PHX</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>JFK</td>\n",
       "      <td>AUS</td>\n",
       "      <td>297</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  origin dest  number_trips\n",
       "0    EWR  DFW          1618\n",
       "1    EWR  LAX            62\n",
       "2    EWR  MIA           848\n",
       "3    EWR  PHX           121\n",
       "4    JFK  AUS           297"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.loc[flights[\"carrier\"].isin([\"AA\"])].groupby([\"origin\",\"dest\"]).count().reset_index()[[\"origin\",\"dest\",\"flight\"]].rename(columns={\"flight\":\"number_trips\"}).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How can we get the average arrival and departure delay for each orig,dest pair for each month for carrier code “AA”? "
   ]
  },
  {
   "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>origin</th>\n",
       "      <th>dest</th>\n",
       "      <th>month</th>\n",
       "      <th>arr_delay</th>\n",
       "      <th>dep_delay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EWR</td>\n",
       "      <td>DFW</td>\n",
       "      <td>1</td>\n",
       "      <td>6.427673</td>\n",
       "      <td>10.012579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EWR</td>\n",
       "      <td>DFW</td>\n",
       "      <td>2</td>\n",
       "      <td>10.536765</td>\n",
       "      <td>11.345588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EWR</td>\n",
       "      <td>DFW</td>\n",
       "      <td>3</td>\n",
       "      <td>12.865031</td>\n",
       "      <td>8.079755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EWR</td>\n",
       "      <td>DFW</td>\n",
       "      <td>4</td>\n",
       "      <td>17.792683</td>\n",
       "      <td>12.920732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EWR</td>\n",
       "      <td>DFW</td>\n",
       "      <td>5</td>\n",
       "      <td>18.487805</td>\n",
       "      <td>18.682927</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  origin dest  month  arr_delay  dep_delay\n",
       "0    EWR  DFW      1   6.427673  10.012579\n",
       "1    EWR  DFW      2  10.536765  11.345588\n",
       "2    EWR  DFW      3  12.865031   8.079755\n",
       "3    EWR  DFW      4  17.792683  12.920732\n",
       "4    EWR  DFW      5  18.487805  18.682927"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights.loc[flights[\"carrier\"].isin([\"AA\"])].groupby([\"origin\",\"dest\",\"month\"])[[\"arr_delay\",\"dep_delay\"]].mean().reset_index().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Melting Operation\n",
    "#### Melting operation is converting from wide form to long form"
   ]
  },
  {
   "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>family_id</th>\n",
       "      <th>age_mother</th>\n",
       "      <th>dob_child1</th>\n",
       "      <th>dob_child2</th>\n",
       "      <th>dob_child3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1998-11-26</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>1996-06-22</td>\n",
       "      <td>2004-04-05</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>2002-07-11</td>\n",
       "      <td>2004-04-05</td>\n",
       "      <td>2007-09-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>2004-10-10</td>\n",
       "      <td>2009-08-27</td>\n",
       "      <td>2012-07-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>2000-12-05</td>\n",
       "      <td>2005-02-28</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   family_id  age_mother  dob_child1  dob_child2  dob_child3\n",
       "0          1          30  1998-11-26          NA          NA\n",
       "1          2          27  1996-06-22  2004-04-05          NA\n",
       "2          3          26  2002-07-11  2004-04-05  2007-09-02\n",
       "3          4          32  2004-10-10  2009-08-27  2012-07-21\n",
       "4          5          29  2000-12-05  2005-02-28            "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "family_data=pd.DataFrame({'family_id':[1,2,3,4,5], \n",
    "                          'age_mother':[30,27,26,32,29],\n",
    "                          'dob_child1':['1998-11-26','1996-06-22','2002-07-11','2004-10-10','2000-12-05'],\n",
    "                          'dob_child2':['NA','2004-04-05','2004-04-05','2009-08-27','2005-02-28'],\n",
    "                          'dob_child3':['NA','NA','2007-09-02','2012-07-21', \"\"]\n",
    "                         \n",
    "                         })[['family_id','age_mother','dob_child1','dob_child2','dob_child3']]\n",
    "family_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We need to melt the above dataframe"
   ]
  },
  {
   "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>family_id</th>\n",
       "      <th>age_mother</th>\n",
       "      <th>dob_child</th>\n",
       "      <th>dob</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>dob_child1</td>\n",
       "      <td>1998-11-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>dob_child1</td>\n",
       "      <td>1996-06-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>dob_child1</td>\n",
       "      <td>2002-07-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>dob_child1</td>\n",
       "      <td>2004-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>dob_child1</td>\n",
       "      <td>2000-12-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>dob_child2</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>dob_child2</td>\n",
       "      <td>2004-04-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>dob_child2</td>\n",
       "      <td>2004-04-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>dob_child2</td>\n",
       "      <td>2009-08-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>dob_child2</td>\n",
       "      <td>2005-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>dob_child3</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>dob_child3</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>dob_child3</td>\n",
       "      <td>2007-09-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>dob_child3</td>\n",
       "      <td>2012-07-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>dob_child3</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    family_id  age_mother   dob_child         dob\n",
       "0           1          30  dob_child1  1998-11-26\n",
       "1           2          27  dob_child1  1996-06-22\n",
       "2           3          26  dob_child1  2002-07-11\n",
       "3           4          32  dob_child1  2004-10-10\n",
       "4           5          29  dob_child1  2000-12-05\n",
       "5           1          30  dob_child2          NA\n",
       "6           2          27  dob_child2  2004-04-05\n",
       "7           3          26  dob_child2  2004-04-05\n",
       "8           4          32  dob_child2  2009-08-27\n",
       "9           5          29  dob_child2  2005-02-28\n",
       "10          1          30  dob_child3          NA\n",
       "11          2          27  dob_child3          NA\n",
       "12          3          26  dob_child3  2007-09-02\n",
       "13          4          32  dob_child3  2012-07-21\n",
       "14          5          29  dob_child3            "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "family_melt=pd.melt(family_data,\n",
    "                   id_vars=['family_id','age_mother'],\n",
    "                   value_vars=['dob_child1','dob_child2','dob_child3'],\n",
    "                   var_name=['dob_child'],\n",
    "                   value_name='dob')\n",
    "family_melt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Dcast operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#family_melt.set_index(['dob','family_id']).unstack('dob')\n",
    "\n",
    "family_dcast=family_melt.pivot_table(index=['family_id','age_mother'],columns=\"dob_child\",values=\"dob\",aggfunc='first')"
   ]
  },
  {
   "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>dob_child</th>\n",
       "      <th>dob_child1</th>\n",
       "      <th>dob_child2</th>\n",
       "      <th>dob_child3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>family_id</th>\n",
       "      <th>age_mother</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>30</th>\n",
       "      <td>1998-11-26</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>27</th>\n",
       "      <td>1996-06-22</td>\n",
       "      <td>2004-04-05</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>26</th>\n",
       "      <td>2002-07-11</td>\n",
       "      <td>2004-04-05</td>\n",
       "      <td>2007-09-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <th>32</th>\n",
       "      <td>2004-10-10</td>\n",
       "      <td>2009-08-27</td>\n",
       "      <td>2012-07-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>29</th>\n",
       "      <td>2000-12-05</td>\n",
       "      <td>2005-02-28</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "dob_child             dob_child1  dob_child2  dob_child3\n",
       "family_id age_mother                                    \n",
       "1         30          1998-11-26          NA          NA\n",
       "2         27          1996-06-22  2004-04-05          NA\n",
       "3         26          2002-07-11  2004-04-05  2007-09-02\n",
       "4         32          2004-10-10  2009-08-27  2012-07-21\n",
       "5         29          2000-12-05  2005-02-28            "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "family_dcast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['dob_child1', 'dob_child2', 'dob_child3'], dtype='object', name='dob_child')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "family_dcast.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>family_id</th>\n",
       "      <th>age_mother</th>\n",
       "      <th>dob_child1</th>\n",
       "      <th>dob_child2</th>\n",
       "      <th>dob_child3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1998-11-26</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>1996-06-22</td>\n",
       "      <td>2004-04-05</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>2002-07-11</td>\n",
       "      <td>2004-04-05</td>\n",
       "      <td>2007-09-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>2004-10-10</td>\n",
       "      <td>2009-08-27</td>\n",
       "      <td>2012-07-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>29</td>\n",
       "      <td>2000-12-05</td>\n",
       "      <td>2005-02-28</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   family_id  age_mother  dob_child1  dob_child2  dob_child3\n",
       "0          1          30  1998-11-26          NA          NA\n",
       "1          2          27  1996-06-22  2004-04-05          NA\n",
       "2          3          26  2002-07-11  2004-04-05  2007-09-02\n",
       "3          4          32  2004-10-10  2009-08-27  2012-07-21\n",
       "4          5          29  2000-12-05  2005-02-28            "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "family_dcast.reset_index().rename_axis(\"\",axis=\"columns\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}