2. Pandas for Machine Learning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Essential Pandas for Machine Learning\n",
    "\n",
    "<hr>\n",
    "\n",
    "### Agenda\n",
    "1. Introduction to Pandas\n",
    "2. Understanding Series & DataFrames\n",
    "3. Loading CSV,JSON\n",
    "4. Connecting databases\n",
    "5. Descriptive Statistics\n",
    "6. Accessing subsets of data - Rows, Columns, Filters\n",
    "7. Handling Missing Data\n",
    "8. Dropping rows & columns\n",
    "9. Handling Duplicates\n",
    "10. Function Application - map, apply, groupby, rolling, str\n",
    "11. Merge, Join & Concatenate\n",
    "12. Pivot-tables\n",
    "13. Normalizing JSON\n",
    "\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Introduction to Pandas\n",
    "* High Performance, Easy-to-use open source library for Data Analysis\n",
    "* Creates tabular format of data from different sources like csv, json, database.\n",
    "* Have utilities for descriptive statistics, aggregation, handling missing data\n",
    "* Database utilities like merge, join are available\n",
    "* Fast, Programmable & Easy alternative to spreadsheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Understanding Series & DataFrames\n",
    "* Series represents one column\n",
    "* Combine multiple columns to create a table ( .i.e DataFrame )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "c    3\n",
       "d    4\n",
       "e    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser1 = pd.Series(data=[1,2,3,4,5], index=list('abcde'))\n",
    "ser1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    11\n",
       "b    22\n",
       "c    33\n",
       "d    44\n",
       "e    55\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser2 = pd.Series(data=[11,22,33,44,55], index=list('abcde'))\n",
    "ser2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Creating DataFrame from above two series\n",
    "* Data corresponding to same index belongs to same row"
   ]
  },
  {
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>4</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>5</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A   B\n",
       "a  1  11\n",
       "b  2  22\n",
       "c  3  33\n",
       "d  4  44\n",
       "e  5  55"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'A':ser1, 'B':ser2})\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Creating a random dataframe of 10 X 10"
   ]
  },
  {
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "      <th>f</th>\n",
       "      <th>g</th>\n",
       "      <th>h</th>\n",
       "      <th>i</th>\n",
       "      <th>j</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>J</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d  e  f  g  h  i  j\n",
       "A  4  6  3  7  3  8  7  8  2  6\n",
       "B  4  9  5  3  7  7  8  5  6  2\n",
       "C  3  1  2  3  5  6  4  7  6  3\n",
       "D  4  6  9  6  2  6  6  3  5  4\n",
       "E  9  7  4  7  7  6  2  5  9  5\n",
       "F  9  6  8  1  8  5  4  2  5  4\n",
       "G  3  3  9  4  2  3  1  8  8  7\n",
       "H  9  5  5  5  8  9  9  4  8  2\n",
       "I  8  9  8  3  1  8  6  1  7  8\n",
       "J  9  3  7  5  3  9  6  4  1  5"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(data=np.random.randint(1,10,size=(10,10)), index=list('ABCDEFGHIJ'), columns=list('abcdefghij'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Loading CSV,JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "hr_data = pd.read_csv('https://raw.githubusercontent.com/zekelabs/data-science-complete-tutorial/master/Data/HR_comma_sep.csv.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14999 entries, 0 to 14998\n",
      "Data columns (total 10 columns):\n",
      "satisfaction_level       14999 non-null float64\n",
      "last_evaluation          14999 non-null float64\n",
      "number_project           14999 non-null int64\n",
      "average_montly_hours     14999 non-null int64\n",
      "time_spend_company       14999 non-null int64\n",
      "Work_accident            14999 non-null int64\n",
      "left                     14999 non-null int64\n",
      "promotion_last_5years    14999 non-null int64\n",
      "sales                    14999 non-null object\n",
      "salary                   14999 non-null object\n",
      "dtypes: float64(2), int64(6), object(2)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "hr_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "hr_data_itr = pd.read_csv('https://raw.githubusercontent.com/zekelabs/data-science-complete-tutorial/master/Data/HR_comma_sep.csv.txt', chunksize=5000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 0 to 4999\n",
      "Data columns (total 10 columns):\n",
      "satisfaction_level       5000 non-null float64\n",
      "last_evaluation          5000 non-null float64\n",
      "number_project           5000 non-null int64\n",
      "average_montly_hours     5000 non-null int64\n",
      "time_spend_company       5000 non-null int64\n",
      "Work_accident            5000 non-null int64\n",
      "left                     5000 non-null int64\n",
      "promotion_last_5years    5000 non-null int64\n",
      "sales                    5000 non-null object\n",
      "salary                   5000 non-null object\n",
      "dtypes: float64(2), int64(6), object(2)\n",
      "memory usage: 390.7+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 5000 to 9999\n",
      "Data columns (total 10 columns):\n",
      "satisfaction_level       5000 non-null float64\n",
      "last_evaluation          5000 non-null float64\n",
      "number_project           5000 non-null int64\n",
      "average_montly_hours     5000 non-null int64\n",
      "time_spend_company       5000 non-null int64\n",
      "Work_accident            5000 non-null int64\n",
      "left                     5000 non-null int64\n",
      "promotion_last_5years    5000 non-null int64\n",
      "sales                    5000 non-null object\n",
      "salary                   5000 non-null object\n",
      "dtypes: float64(2), int64(6), object(2)\n",
      "memory usage: 390.7+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4999 entries, 10000 to 14998\n",
      "Data columns (total 10 columns):\n",
      "satisfaction_level       4999 non-null float64\n",
      "last_evaluation          4999 non-null float64\n",
      "number_project           4999 non-null int64\n",
      "average_montly_hours     4999 non-null int64\n",
      "time_spend_company       4999 non-null int64\n",
      "Work_accident            4999 non-null int64\n",
      "left                     4999 non-null int64\n",
      "promotion_last_5years    4999 non-null int64\n",
      "sales                    4999 non-null object\n",
      "salary                   4999 non-null object\n",
      "dtypes: float64(2), int64(6), object(2)\n",
      "memory usage: 390.6+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "for hr_data in hr_data_itr:\n",
    "    print (hr_data.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Goodfellas</th>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Raging Bull</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roman Holiday</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Apartment</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vertigo</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Goodfellas            4.5         4.5              2.0           NaN   \n",
       "Raging Bull           NaN         NaN              1.0           4.5   \n",
       "Roman Holiday         3.0         NaN              4.5           NaN   \n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "The Apartment         1.0         1.0              5.0           1.5   \n",
       "Vertigo               3.5         4.5              3.0           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Goodfellas                  2.5          4.5           3.0            5.0  \n",
       "Raging Bull                 4.0          3.0           NaN            5.0  \n",
       "Roman Holiday               1.5          NaN           4.5            1.0  \n",
       "Scarface                    4.5          4.5           2.5            3.5  \n",
       "The Apartment               1.0          1.0           NaN            1.0  \n",
       "Vertigo                     5.0          4.0           NaN            NaN  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_json('https://raw.githubusercontent.com/zekelabs/data-science-complete-tutorial/master/Data/movie.json.txt')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Connecting Databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pysqlite3\n",
      "  Downloading https://files.pythonhosted.org/packages/96/6b/da6cd7f0706052ffd8e06910983ab47e42cd8312cee54face9af0d2cbd65/pysqlite3-0.2.0.tar.gz\n",
      "Building wheels for collected packages: pysqlite3\n",
      "  Running setup.py bdist_wheel for pysqlite3: started\n",
      "  Running setup.py bdist_wheel for pysqlite3: finished with status 'done'\n",
      "  Running setup.py clean for pysqlite3\n",
      "Failed to build pysqlite3\n",
      "Installing collected packages: pysqlite3\n",
      "  Running setup.py install for pysqlite3: started\n",
      "    Running setup.py install for pysqlite3: finished with status 'done'\n",
      "Successfully installed pysqlite3\n"
     ]
    }
   ],
   "source": [
    "!pip install pysqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect('Data/database.sqlite')"
   ]
  },
  {
   "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>ProductId</th>\n",
       "      <th>UserId</th>\n",
       "      <th>ProfileName</th>\n",
       "      <th>HelpfulnessNumerator</th>\n",
       "      <th>HelpfulnessDenominator</th>\n",
       "      <th>Score</th>\n",
       "      <th>Time</th>\n",
       "      <th>Summary</th>\n",
       "      <th>Text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>B001E4KFG0</td>\n",
       "      <td>A3SGXH7AUHU8GW</td>\n",
       "      <td>delmartian</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1303862400</td>\n",
       "      <td>Good Quality Dog Food</td>\n",
       "      <td>I have bought several of the Vitality canned d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>B00813GRG4</td>\n",
       "      <td>A1D87F6ZCVE5NK</td>\n",
       "      <td>dll pa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1346976000</td>\n",
       "      <td>Not as Advertised</td>\n",
       "      <td>Product arrived labeled as Jumbo Salted Peanut...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>B000LQOCH0</td>\n",
       "      <td>ABXLMWJIXXAIN</td>\n",
       "      <td>Natalia Corres \"Natalia Corres\"</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1219017600</td>\n",
       "      <td>\"Delight\" says it all</td>\n",
       "      <td>This is a confection that has been around a fe...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>B000UA0QIQ</td>\n",
       "      <td>A395BORC6FGVXV</td>\n",
       "      <td>Karl</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1307923200</td>\n",
       "      <td>Cough Medicine</td>\n",
       "      <td>If you are looking for the secret ingredient i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>B006K2ZZ7K</td>\n",
       "      <td>A1UQRSCLF8GW1T</td>\n",
       "      <td>Michael D. Bigham \"M. Wassir\"</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1350777600</td>\n",
       "      <td>Great taffy</td>\n",
       "      <td>Great taffy at a great price.  There was a wid...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id   ProductId          UserId                      ProfileName  \\\n",
       "0   1  B001E4KFG0  A3SGXH7AUHU8GW                       delmartian   \n",
       "1   2  B00813GRG4  A1D87F6ZCVE5NK                           dll pa   \n",
       "2   3  B000LQOCH0   ABXLMWJIXXAIN  Natalia Corres \"Natalia Corres\"   \n",
       "3   4  B000UA0QIQ  A395BORC6FGVXV                             Karl   \n",
       "4   5  B006K2ZZ7K  A1UQRSCLF8GW1T    Michael D. Bigham \"M. Wassir\"   \n",
       "\n",
       "   HelpfulnessNumerator  HelpfulnessDenominator  Score        Time  \\\n",
       "0                     1                       1      5  1303862400   \n",
       "1                     0                       0      1  1346976000   \n",
       "2                     1                       1      4  1219017600   \n",
       "3                     3                       3      2  1307923200   \n",
       "4                     0                       0      5  1350777600   \n",
       "\n",
       "                 Summary                                               Text  \n",
       "0  Good Quality Dog Food  I have bought several of the Vitality canned d...  \n",
       "1      Not as Advertised  Product arrived labeled as Jumbo Salted Peanut...  \n",
       "2  \"Delight\" says it all  This is a confection that has been around a fe...  \n",
       "3         Cough Medicine  If you are looking for the secret ingredient i...  \n",
       "4            Great taffy  Great taffy at a great price.  There was a wid...  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"SELECT * FROM Reviews LIMIT 5\",con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* import MySQLdb\n",
    "* mysql_cn= MySQLdb.connect(host='myhost', \n",
    "                port=3306,user='myusername', passwd='mypassword', \n",
    "                db='information_schema')\n",
    "* df_mysql = pd.read_sql('select * from VIEWS;', con=mysql_cn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Descriptive Statistics\n",
    "* Pandas api's for understanding data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "hr_data = pd.read_csv('https://raw.githubusercontent.com/zekelabs/data-science-complete-tutorial/master/Data/HR_comma_sep.csv.txt')"
   ]
  },
  {
   "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>satisfaction_level</th>\n",
       "      <th>last_evaluation</th>\n",
       "      <th>number_project</th>\n",
       "      <th>average_montly_hours</th>\n",
       "      <th>time_spend_company</th>\n",
       "      <th>Work_accident</th>\n",
       "      <th>left</th>\n",
       "      <th>promotion_last_5years</th>\n",
       "      <th>sales</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.38</td>\n",
       "      <td>0.53</td>\n",
       "      <td>2</td>\n",
       "      <td>157</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.80</td>\n",
       "      <td>0.86</td>\n",
       "      <td>5</td>\n",
       "      <td>262</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.11</td>\n",
       "      <td>0.88</td>\n",
       "      <td>7</td>\n",
       "      <td>272</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.72</td>\n",
       "      <td>0.87</td>\n",
       "      <td>5</td>\n",
       "      <td>223</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.37</td>\n",
       "      <td>0.52</td>\n",
       "      <td>2</td>\n",
       "      <td>159</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   satisfaction_level  last_evaluation  number_project  average_montly_hours  \\\n",
       "0                0.38             0.53               2                   157   \n",
       "1                0.80             0.86               5                   262   \n",
       "2                0.11             0.88               7                   272   \n",
       "3                0.72             0.87               5                   223   \n",
       "4                0.37             0.52               2                   159   \n",
       "\n",
       "   time_spend_company  Work_accident  left  promotion_last_5years  sales  \\\n",
       "0                   3              0     1                      0  sales   \n",
       "1                   6              0     1                      0  sales   \n",
       "2                   4              0     1                      0  sales   \n",
       "3                   5              0     1                      0  sales   \n",
       "4                   3              0     1                      0  sales   \n",
       "\n",
       "   salary  \n",
       "0     low  \n",
       "1  medium  \n",
       "2  medium  \n",
       "3     low  \n",
       "4     low  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>satisfaction_level</th>\n",
       "      <th>last_evaluation</th>\n",
       "      <th>number_project</th>\n",
       "      <th>average_montly_hours</th>\n",
       "      <th>time_spend_company</th>\n",
       "      <th>Work_accident</th>\n",
       "      <th>left</th>\n",
       "      <th>promotion_last_5years</th>\n",
       "      <th>sales</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14994</th>\n",
       "      <td>0.40</td>\n",
       "      <td>0.57</td>\n",
       "      <td>2</td>\n",
       "      <td>151</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>support</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14995</th>\n",
       "      <td>0.37</td>\n",
       "      <td>0.48</td>\n",
       "      <td>2</td>\n",
       "      <td>160</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>support</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14996</th>\n",
       "      <td>0.37</td>\n",
       "      <td>0.53</td>\n",
       "      <td>2</td>\n",
       "      <td>143</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>support</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14997</th>\n",
       "      <td>0.11</td>\n",
       "      <td>0.96</td>\n",
       "      <td>6</td>\n",
       "      <td>280</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>support</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14998</th>\n",
       "      <td>0.37</td>\n",
       "      <td>0.52</td>\n",
       "      <td>2</td>\n",
       "      <td>158</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>support</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       satisfaction_level  last_evaluation  number_project  \\\n",
       "14994                0.40             0.57               2   \n",
       "14995                0.37             0.48               2   \n",
       "14996                0.37             0.53               2   \n",
       "14997                0.11             0.96               6   \n",
       "14998                0.37             0.52               2   \n",
       "\n",
       "       average_montly_hours  time_spend_company  Work_accident  left  \\\n",
       "14994                   151                   3              0     1   \n",
       "14995                   160                   3              0     1   \n",
       "14996                   143                   3              0     1   \n",
       "14997                   280                   4              0     1   \n",
       "14998                   158                   3              0     1   \n",
       "\n",
       "       promotion_last_5years    sales salary  \n",
       "14994                      0  support    low  \n",
       "14995                      0  support    low  \n",
       "14996                      0  support    low  \n",
       "14997                      0  support    low  \n",
       "14998                      0  support    low  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14999 entries, 0 to 14998\n",
      "Data columns (total 10 columns):\n",
      "satisfaction_level       14999 non-null float64\n",
      "last_evaluation          14999 non-null float64\n",
      "number_project           14999 non-null int64\n",
      "average_montly_hours     14999 non-null int64\n",
      "time_spend_company       14999 non-null int64\n",
      "Work_accident            14999 non-null int64\n",
      "left                     14999 non-null int64\n",
      "promotion_last_5years    14999 non-null int64\n",
      "sales                    14999 non-null object\n",
      "salary                   14999 non-null object\n",
      "dtypes: float64(2), int64(6), object(2)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "hr_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>satisfaction_level</th>\n",
       "      <th>last_evaluation</th>\n",
       "      <th>number_project</th>\n",
       "      <th>average_montly_hours</th>\n",
       "      <th>time_spend_company</th>\n",
       "      <th>Work_accident</th>\n",
       "      <th>left</th>\n",
       "      <th>promotion_last_5years</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>14999.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.612834</td>\n",
       "      <td>0.716102</td>\n",
       "      <td>3.803054</td>\n",
       "      <td>201.050337</td>\n",
       "      <td>3.498233</td>\n",
       "      <td>0.144610</td>\n",
       "      <td>0.238083</td>\n",
       "      <td>0.021268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.248631</td>\n",
       "      <td>0.171169</td>\n",
       "      <td>1.232592</td>\n",
       "      <td>49.943099</td>\n",
       "      <td>1.460136</td>\n",
       "      <td>0.351719</td>\n",
       "      <td>0.425924</td>\n",
       "      <td>0.144281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.090000</td>\n",
       "      <td>0.360000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.440000</td>\n",
       "      <td>0.560000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>156.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.640000</td>\n",
       "      <td>0.720000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>200.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.820000</td>\n",
       "      <td>0.870000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>245.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>310.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       satisfaction_level  last_evaluation  number_project  \\\n",
       "count        14999.000000     14999.000000    14999.000000   \n",
       "mean             0.612834         0.716102        3.803054   \n",
       "std              0.248631         0.171169        1.232592   \n",
       "min              0.090000         0.360000        2.000000   \n",
       "25%              0.440000         0.560000        3.000000   \n",
       "50%              0.640000         0.720000        4.000000   \n",
       "75%              0.820000         0.870000        5.000000   \n",
       "max              1.000000         1.000000        7.000000   \n",
       "\n",
       "       average_montly_hours  time_spend_company  Work_accident          left  \\\n",
       "count          14999.000000        14999.000000   14999.000000  14999.000000   \n",
       "mean             201.050337            3.498233       0.144610      0.238083   \n",
       "std               49.943099            1.460136       0.351719      0.425924   \n",
       "min               96.000000            2.000000       0.000000      0.000000   \n",
       "25%              156.000000            3.000000       0.000000      0.000000   \n",
       "50%              200.000000            3.000000       0.000000      0.000000   \n",
       "75%              245.000000            4.000000       0.000000      0.000000   \n",
       "max              310.000000           10.000000       1.000000      1.000000   \n",
       "\n",
       "       promotion_last_5years  \n",
       "count           14999.000000  \n",
       "mean                0.021268  \n",
       "std                 0.144281  \n",
       "min                 0.000000  \n",
       "25%                 0.000000  \n",
       "50%                 0.000000  \n",
       "75%                 0.000000  \n",
       "max                 1.000000  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "low       7316\n",
       "medium    6446\n",
       "high      1237\n",
       "Name: salary, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data.salary.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Accessing subset of data - rows, columns, filters\n",
    "* Get all columns with categorical values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_cols_data = hr_data.select_dtypes('object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>sales</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sales</td>\n",
       "      <td>medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sales</td>\n",
       "      <td>medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sales  salary\n",
       "0  sales     low\n",
       "1  sales  medium\n",
       "2  sales  medium\n",
       "3  sales     low\n",
       "4  sales     low"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_cols_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Rename columns names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "hr_data.rename(columns={'sales':'department'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>satisfaction_level</th>\n",
       "      <th>last_evaluation</th>\n",
       "      <th>number_project</th>\n",
       "      <th>average_montly_hours</th>\n",
       "      <th>time_spend_company</th>\n",
       "      <th>Work_accident</th>\n",
       "      <th>left</th>\n",
       "      <th>promotion_last_5years</th>\n",
       "      <th>department</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.38</td>\n",
       "      <td>0.53</td>\n",
       "      <td>2</td>\n",
       "      <td>157</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.80</td>\n",
       "      <td>0.86</td>\n",
       "      <td>5</td>\n",
       "      <td>262</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.11</td>\n",
       "      <td>0.88</td>\n",
       "      <td>7</td>\n",
       "      <td>272</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.72</td>\n",
       "      <td>0.87</td>\n",
       "      <td>5</td>\n",
       "      <td>223</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.37</td>\n",
       "      <td>0.52</td>\n",
       "      <td>2</td>\n",
       "      <td>159</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>sales</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   satisfaction_level  last_evaluation  number_project  average_montly_hours  \\\n",
       "0                0.38             0.53               2                   157   \n",
       "1                0.80             0.86               5                   262   \n",
       "2                0.11             0.88               7                   272   \n",
       "3                0.72             0.87               5                   223   \n",
       "4                0.37             0.52               2                   159   \n",
       "\n",
       "   time_spend_company  Work_accident  left  promotion_last_5years department  \\\n",
       "0                   3              0     1                      0      sales   \n",
       "1                   6              0     1                      0      sales   \n",
       "2                   4              0     1                      0      sales   \n",
       "3                   5              0     1                      0      sales   \n",
       "4                   3              0     1                      0      sales   \n",
       "\n",
       "   salary  \n",
       "0     low  \n",
       "1  medium  \n",
       "2  medium  \n",
       "3     low  \n",
       "4     low  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Select column by column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['satisfaction_level', 'last_evaluation', 'number_project',\n",
       "       'average_montly_hours', 'time_spend_company', 'Work_accident', 'left',\n",
       "       'promotion_last_5years', 'department', 'salary'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>satisfaction_level</th>\n",
       "      <th>last_evaluation</th>\n",
       "      <th>number_project</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.38</td>\n",
       "      <td>0.53</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.80</td>\n",
       "      <td>0.86</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.11</td>\n",
       "      <td>0.88</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.72</td>\n",
       "      <td>0.87</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.37</td>\n",
       "      <td>0.52</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   satisfaction_level  last_evaluation  number_project\n",
       "0                0.38             0.53               2\n",
       "1                0.80             0.86               5\n",
       "2                0.11             0.88               7\n",
       "3                0.72             0.87               5\n",
       "4                0.37             0.52               2"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data[['satisfaction_level','last_evaluation','number_project']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.38\n",
       "1    0.80\n",
       "2    0.11\n",
       "3    0.72\n",
       "4    0.37\n",
       "Name: satisfaction_level, dtype: float64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data.satisfaction_level[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.38\n",
       "1    0.80\n",
       "2    0.11\n",
       "3    0.72\n",
       "4    0.37\n",
       "Name: satisfaction_level, dtype: float64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hr_data['satisfaction_level'][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "movie_data = pd.read_json('https://raw.githubusercontent.com/zekelabs/data-science-complete-tutorial/master/Data/movie.json.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Goodfellas</th>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Raging Bull</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roman Holiday</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Apartment</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vertigo</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Goodfellas            4.5         4.5              2.0           NaN   \n",
       "Raging Bull           NaN         NaN              1.0           4.5   \n",
       "Roman Holiday         3.0         NaN              4.5           NaN   \n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "The Apartment         1.0         1.0              5.0           1.5   \n",
       "Vertigo               3.5         4.5              3.0           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Goodfellas                  2.5          4.5           3.0            5.0  \n",
       "Raging Bull                 4.0          3.0           NaN            5.0  \n",
       "Roman Holiday               1.5          NaN           4.5            1.0  \n",
       "Scarface                    4.5          4.5           2.5            3.5  \n",
       "The Apartment               1.0          1.0           NaN            1.0  \n",
       "Vertigo                     5.0          4.0           NaN            NaN  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Access data by index values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Adam Cohen          3.0\n",
       "Bill Duffy          5.0\n",
       "Brenda Peterson     1.5\n",
       "Chris Duncan        NaN\n",
       "Clarissa Jackson    4.5\n",
       "David Smith         4.5\n",
       "Julie Hammel        2.5\n",
       "Samuel Miller       3.5\n",
       "Name: Scarface, dtype: float64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data.loc['Scarface']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Apartment</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vertigo</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "The Apartment         1.0         1.0              5.0           1.5   \n",
       "Vertigo               3.5         4.5              3.0           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Scarface                    4.5          4.5           2.5            3.5  \n",
       "The Apartment               1.0          1.0           NaN            1.0  \n",
       "Vertigo                     5.0          4.0           NaN            NaN  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data.loc['Scarface':'Vertigo']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Apartment</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vertigo</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "The Apartment         1.0         1.0              5.0           1.5   \n",
       "Vertigo               3.5         4.5              3.0           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Scarface                    4.5          4.5           2.5            3.5  \n",
       "The Apartment               1.0          1.0           NaN            1.0  \n",
       "Vertigo                     5.0          4.0           NaN            NaN  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data['Scarface':'Vertigo']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Adam Cohen          NaN\n",
       "Bill Duffy          NaN\n",
       "Brenda Peterson     1.0\n",
       "Chris Duncan        4.5\n",
       "Clarissa Jackson    4.0\n",
       "David Smith         3.0\n",
       "Julie Hammel        NaN\n",
       "Samuel Miller       5.0\n",
       "Name: Raging Bull, dtype: float64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data.iloc[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Raging Bull</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roman Holiday</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Raging Bull           NaN         NaN              1.0           4.5   \n",
       "Roman Holiday         3.0         NaN              4.5           NaN   \n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Raging Bull                 4.0          3.0           NaN            5.0  \n",
       "Roman Holiday               1.5          NaN           4.5            1.0  \n",
       "Scarface                    4.5          4.5           2.5            3.5  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data.iloc[1:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Raging Bull</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roman Holiday</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Raging Bull           NaN         NaN              1.0           4.5   \n",
       "Roman Holiday         3.0         NaN              4.5           NaN   \n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Raging Bull                 4.0          3.0           NaN            5.0  \n",
       "Roman Holiday               1.5          NaN           4.5            1.0  \n",
       "Scarface                    4.5          4.5           2.5            3.5  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data[1:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Filtering rows based on conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Goodfellas</th>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vertigo</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Goodfellas         4.5         4.5              2.0           NaN   \n",
       "Vertigo            3.5         4.5              3.0           NaN   \n",
       "\n",
       "            Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Goodfellas               2.5          4.5           3.0            5.0  \n",
       "Vertigo                  5.0          4.0           NaN            NaN  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data[ (movie_data['Adam Cohen'] > 3)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Goodfellas</th>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Goodfellas         4.5         4.5              2.0           NaN   \n",
       "\n",
       "            Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Goodfellas               2.5          4.5           3.0            5.0  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data[ ((movie_data['Adam Cohen'] > 3) & (movie_data['David Smith'] > 4))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Handling missing data\n",
    "* Machine Learning algorithms don't expect data missing\n",
    "* If there is a columns with more than 40% data missing, we may drop the column\n",
    "* Fow rows with, important column values missing. Drop the rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Goodfellas</th>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Raging Bull</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roman Holiday</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Apartment</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vertigo</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Goodfellas            4.5         4.5              2.0           NaN   \n",
       "Raging Bull           NaN         NaN              1.0           4.5   \n",
       "Roman Holiday         3.0         NaN              4.5           NaN   \n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "The Apartment         1.0         1.0              5.0           1.5   \n",
       "Vertigo               3.5         4.5              3.0           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Goodfellas                  2.5          4.5           3.0            5.0  \n",
       "Raging Bull                 4.0          3.0           NaN            5.0  \n",
       "Roman Holiday               1.5          NaN           4.5            1.0  \n",
       "Scarface                    4.5          4.5           2.5            3.5  \n",
       "The Apartment               1.0          1.0           NaN            1.0  \n",
       "Vertigo                     5.0          4.0           NaN            NaN  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Get all the rows for which column 'Bill Duffy' is missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Goodfellas        True\n",
       "Raging Bull      False\n",
       "Roman Holiday    False\n",
       "Scarface          True\n",
       "The Apartment     True\n",
       "Vertigo           True\n",
       "Name: Bill Duffy, dtype: bool"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data['Bill Duffy'].notnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Goodfellas</th>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scarface</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Apartment</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vertigo</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Goodfellas            4.5         4.5              2.0           NaN   \n",
       "Scarface              3.0         5.0              1.5           NaN   \n",
       "The Apartment         1.0         1.0              5.0           1.5   \n",
       "Vertigo               3.5         4.5              3.0           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Goodfellas                  2.5          4.5           3.0            5.0  \n",
       "Scarface                    4.5          4.5           2.5            3.5  \n",
       "The Apartment               1.0          1.0           NaN            1.0  \n",
       "Vertigo                     5.0          4.0           NaN            NaN  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data[movie_data['Bill Duffy'].notnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Get all the rows for which 'Bill Duffy' is null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>Adam Cohen</th>\n",
       "      <th>Bill Duffy</th>\n",
       "      <th>Brenda Peterson</th>\n",
       "      <th>Chris Duncan</th>\n",
       "      <th>Clarissa Jackson</th>\n",
       "      <th>David Smith</th>\n",
       "      <th>Julie Hammel</th>\n",
       "      <th>Samuel Miller</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Raging Bull</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roman Holiday</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Adam Cohen  Bill Duffy  Brenda Peterson  Chris Duncan  \\\n",
       "Raging Bull           NaN         NaN              1.0           4.5   \n",
       "Roman Holiday         3.0         NaN              4.5           NaN   \n",
       "\n",
       "               Clarissa Jackson  David Smith  Julie Hammel  Samuel Miller  \n",
       "Raging Bull                 4.0          3.0           NaN            5.0  \n",
       "Roman Holiday               1.5          NaN           4.5            1.0  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_data[movie_data['Bill Duffy'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. Dropping Rows & Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic_data = pd.read_csv('https://raw.githubusercontent.com/zekelabs/data-science-complete-tutorial/master/Data/titanic-train.csv.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 12 columns):\n",
      "PassengerId    891 non-null int64\n",
      "Survived       891 non-null int64\n",
      "Pclass         891 non-null int64\n",
      "Name           891 non-null object\n",
      "Sex            891 non-null object\n",
      "Age            714 non-null float64\n",
      "SibSp          891 non-null int64\n",
      "Parch          891 non-null int64\n",
      "Ticket         891 non-null object\n",
      "Fare           891 non-null float64\n",
      "Cabin          204 non-null object\n",
      "Embarked       889 non-null object\n",
      "dtypes: float64(2), int64(5), object(5)\n",
      "memory usage: 83.6+ KB\n"
     ]
    }
   ],
   "source": [
    "titanic_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Dropping 'Cabin' column as it has only 204 data present in 891 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic_data.drop(['Cabin'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 11 columns):\n",
      "PassengerId    891 non-null int64\n",
      "Survived       891 non-null int64\n",
      "Pclass         891 non-null int64\n",
      "Name           891 non-null object\n",
      "Sex            891 non-null object\n",
      "Age            714 non-null float64\n",
      "SibSp          891 non-null int64\n",
      "Parch          891 non-null int64\n",
      "Ticket         891 non-null object\n",
      "Fare           891 non-null float64\n",
      "Embarked       889 non-null object\n",
      "dtypes: float64(2), int64(5), object(4)\n",
      "memory usage: 76.6+ KB\n"
     ]
    }
   ],
   "source": [
    "titanic_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Now, drop all rows with missing values\n",
    "* We don't have inplace = True, so doesn't modify the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 712 entries, 0 to 890\n",
      "Data columns (total 11 columns):\n",
      "PassengerId    712 non-null int64\n",
      "Survived       712 non-null int64\n",
      "Pclass         712 non-null int64\n",
      "Name           712 non-null object\n",
      "Sex            712 non-null object\n",
      "Age            712 non-null float64\n",
      "SibSp          712 non-null int64\n",
      "Parch          712 non-null int64\n",
      "Ticket         712 non-null object\n",
      "Fare           712 non-null float64\n",
      "Embarked       712 non-null object\n",
      "dtypes: float64(2), int64(5), object(4)\n",
      "memory usage: 66.8+ KB\n"
     ]
    }
   ],
   "source": [
    "titanic_data.dropna().info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Consider only selected columns to check if they contain NA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 11 columns):\n",
      "PassengerId    891 non-null int64\n",
      "Survived       891 non-null int64\n",
      "Pclass         891 non-null int64\n",
      "Name           891 non-null object\n",
      "Sex            891 non-null object\n",
      "Age            714 non-null float64\n",
      "SibSp          891 non-null int64\n",
      "Parch          891 non-null int64\n",
      "Ticket         891 non-null object\n",
      "Fare           891 non-null float64\n",
      "Embarked       889 non-null object\n",
      "dtypes: float64(2), int64(5), object(4)\n",
      "memory usage: 76.6+ KB\n"
     ]
    }
   ],
   "source": [
    "titanic_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 712 entries, 0 to 890\n",
      "Data columns (total 11 columns):\n",
      "PassengerId    712 non-null int64\n",
      "Survived       712 non-null int64\n",
      "Pclass         712 non-null int64\n",
      "Name           712 non-null object\n",
      "Sex            712 non-null object\n",
      "Age            712 non-null float64\n",
      "SibSp          712 non-null int64\n",
      "Parch          712 non-null int64\n",
      "Ticket         712 non-null object\n",
      "Fare           712 non-null float64\n",
      "Embarked       712 non-null object\n",
      "dtypes: float64(2), int64(5), object(4)\n",
      "memory usage: 66.8+ KB\n"
     ]
    }
   ],
   "source": [
    "titanic_data.dropna(subset=['Embarked','Age']).info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Another approach of handling missing data is filling the missing ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 11 columns):\n",
      "PassengerId    891 non-null int64\n",
      "Survived       891 non-null int64\n",
      "Pclass         891 non-null int64\n",
      "Name           891 non-null object\n",
      "Sex            891 non-null object\n",
      "Age            714 non-null float64\n",
      "SibSp          891 non-null int64\n",
      "Parch          891 non-null int64\n",
      "Ticket         891 non-null object\n",
      "Fare           891 non-null float64\n",
      "Embarked       889 non-null object\n",
      "dtypes: float64(2), int64(5), object(4)\n",
      "memory usage: 76.6+ KB\n"
     ]
    }
   ],
   "source": [
    "titanic_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 11 columns):\n",
      "PassengerId    891 non-null int64\n",
      "Survived       891 non-null int64\n",
      "Pclass         891 non-null int64\n",
      "Name           891 non-null object\n",
      "Sex            891 non-null object\n",
      "Age            891 non-null float64\n",
      "SibSp          891 non-null int64\n",
      "Parch          891 non-null int64\n",
      "Ticket         891 non-null object\n",
      "Fare           891 non-null float64\n",
      "Embarked       891 non-null object\n",
      "dtypes: float64(2), int64(5), object(4)\n",
      "memory usage: 76.6+ KB\n"
     ]
    }
   ],
   "source": [
    "titanic_data.fillna({'Age':0,'Embarked':'Unknown'}).info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    22.0\n",
       "1    38.0\n",
       "2    26.0\n",
       "3    35.0\n",
       "4    35.0\n",
       "Name: Age, dtype: float64"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data.Age.fillna(method='ffill')[:5]\n",
    "#Other options are 'bfill'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Handling Duplicates\n",
    "* Sometimes, it difficult to ensure that data is not duplicated.\n",
    "* This becomes responsibility in Data cleaning step to make sure duplicated data is deleted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'A':[1,1,3,4,5,1], 'B':[1,1,3,7,8,1], 'C':[3,1,1,6,7,1]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "0  1  1  3\n",
       "1  1  1  1\n",
       "2  3  3  1\n",
       "3  4  7  6\n",
       "4  5  8  7\n",
       "5  1  1  1"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4    False\n",
       "5     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "5  1  1  1"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "1  1  1  1\n",
       "5  1  1  1"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated(subset=['A','B'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10. Function Application\n",
    "* map for transforming one column to another\n",
    "* Can be applied only to series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic_data_age = titanic_data[titanic_data.Age.notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic_data['age_category'] = titanic_data.Age.map(lambda age: 'Kid' if age < 18 else 'Adult')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "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>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>age_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113803</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Allen, Mr. William Henry</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>373450</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PassengerId  Survived  Pclass  \\\n",
       "0            1         0       3   \n",
       "1            2         1       1   \n",
       "2            3         1       3   \n",
       "3            4         1       1   \n",
       "4            5         0       3   \n",
       "\n",
       "                                                Name     Sex   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "3       Futrelle, Mrs. Jacques Heath (Lily May Peel)  female  35.0      1   \n",
       "4                           Allen, Mr. William Henry    male  35.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Embarked age_category  \n",
       "0      0         A/5 21171   7.2500        S        Adult  \n",
       "1      0          PC 17599  71.2833        C        Adult  \n",
       "2      0  STON/O2. 3101282   7.9250        S        Adult  \n",
       "3      0            113803  53.1000        S        Adult  \n",
       "4      0            373450   8.0500        S        Adult  "
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* apply function can be done to Series as well as DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21205.17"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data.Age.apply('sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Adult\n",
       "1    Adult\n",
       "2    Adult\n",
       "3    Adult\n",
       "4    Adult\n",
       "5    Adult\n",
       "6    Adult\n",
       "7      Kid\n",
       "8    Adult\n",
       "9      Kid\n",
       "Name: Age, dtype: object"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data.Age.apply(lambda age: 'Kid' if age < 18 else 'Adult')[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* apply on dataframes helps us dealing with multiple columns\n",
    "* func will receive all the rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "#e will be each row\n",
    "def func(e):\n",
    "    if e.Sex == 'male':\n",
    "        return e.Fare * 2\n",
    "    else:\n",
    "        return e.Fare\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    14.5000\n",
       "1    71.2833\n",
       "2     7.9250\n",
       "3    53.1000\n",
       "4    16.1000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data.apply(func,axis=1)[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* groupby - It splits data into groups, a function is applied to each groups separately, combine results into a data structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Sex\n",
       "female    27.915709\n",
       "male      30.726645\n",
       "Name: Age, dtype: float64"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data.groupby(['Sex']).Age.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "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>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>female</th>\n",
       "      <td>27.915709</td>\n",
       "      <td>0.75</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>30.726645</td>\n",
       "      <td>0.42</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             mean   min   max\n",
       "Sex                          \n",
       "female  27.915709  0.75  63.0\n",
       "male    30.726645  0.42  80.0"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data.groupby(['Sex']).Age.agg(['mean','min','max'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Rolling for window based operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'titanic_data' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-2-e3085e252bb8>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mtitanic_data\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mAge\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrolling\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mwindow\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m5\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mmin_periods\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0magg\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'sum'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'min'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'titanic_data' is not defined"
     ]
    }
   ],
   "source": [
    "titanic_data.Age.rolling(window=5,min_periods=1).agg(['sum','min'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* For columns containing string, we have str utilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "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>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>age_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113803</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Allen, Mr. William Henry</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>373450</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Moran, Mr. James</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>330877</td>\n",
       "      <td>8.4583</td>\n",
       "      <td>Q</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>McCarthy, Mr. Timothy J</td>\n",
       "      <td>male</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17463</td>\n",
       "      <td>51.8625</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Johnson, Mrs. Oscar W (Elisabeth Vilhelmina Berg)</td>\n",
       "      <td>female</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>347742</td>\n",
       "      <td>11.1333</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Nasser, Mrs. Nicholas (Adele Achem)</td>\n",
       "      <td>female</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>237736</td>\n",
       "      <td>30.0708</td>\n",
       "      <td>C</td>\n",
       "      <td>Kid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Saundercock, Mr. William Henry</td>\n",
       "      <td>male</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5. 2151</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Andersson, Mr. Anders Johan</td>\n",
       "      <td>male</td>\n",
       "      <td>39.0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>347082</td>\n",
       "      <td>31.2750</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Hewlett, Mrs. (Mary D Kingcome)</td>\n",
       "      <td>female</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>248706</td>\n",
       "      <td>16.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Williams, Mr. Charles Eugene</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>244373</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Vander Planke, Mrs. Julius (Emelia Maria Vande...</td>\n",
       "      <td>female</td>\n",
       "      <td>31.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>345763</td>\n",
       "      <td>18.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Masselmani, Mrs. Fatima</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2649</td>\n",
       "      <td>7.2250</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Fynney, Mr. Joseph J</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>239865</td>\n",
       "      <td>26.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Beesley, Mr. Lawrence</td>\n",
       "      <td>male</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>248698</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Sloper, Mr. William Thompson</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113788</td>\n",
       "      <td>35.5000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Asplund, Mrs. Carl Oscar (Selma Augusta Emilia...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>347077</td>\n",
       "      <td>31.3875</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Emir, Mr. Farred Chehab</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2631</td>\n",
       "      <td>7.2250</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Fortune, Mr. Charles Alexander</td>\n",
       "      <td>male</td>\n",
       "      <td>19.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>19950</td>\n",
       "      <td>263.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Todoroff, Mr. Lalio</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349216</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Spencer, Mrs. William Augustus (Marie Eugenie)</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17569</td>\n",
       "      <td>146.5208</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Wheadon, Mr. Edward H</td>\n",
       "      <td>male</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>C.A. 24579</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Meyer, Mr. Edgar Joseph</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17604</td>\n",
       "      <td>82.1708</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Holverson, Mr. Alexander Oskar</td>\n",
       "      <td>male</td>\n",
       "      <td>42.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113789</td>\n",
       "      <td>52.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>37</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Mamee, Mr. Hanna</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2677</td>\n",
       "      <td>7.2292</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Cann, Mr. Ernest Charles</td>\n",
       "      <td>male</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A./5. 2152</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Ahlin, Mrs. Johan (Johanna Persdotter Larsson)</td>\n",
       "      <td>female</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7546</td>\n",
       "      <td>9.4750</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Turpin, Mrs. William John Robert (Dorothy Ann ...</td>\n",
       "      <td>female</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11668</td>\n",
       "      <td>21.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Kraeff, Mr. Theodor</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349253</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>851</th>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Svensson, Mr. Johan</td>\n",
       "      <td>male</td>\n",
       "      <td>74.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>347060</td>\n",
       "      <td>7.7750</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>854</th>\n",
       "      <td>855</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Carter, Mrs. Ernest Courtenay (Lilian Hughes)</td>\n",
       "      <td>female</td>\n",
       "      <td>44.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>244252</td>\n",
       "      <td>26.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>855</th>\n",
       "      <td>856</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Aks, Mrs. Sam (Leah Rosen)</td>\n",
       "      <td>female</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>392091</td>\n",
       "      <td>9.3500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>856</th>\n",
       "      <td>857</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Wick, Mrs. George Dennick (Mary Hitchcock)</td>\n",
       "      <td>female</td>\n",
       "      <td>45.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>36928</td>\n",
       "      <td>164.8667</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857</th>\n",
       "      <td>858</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Daly, Mr. Peter Denis</td>\n",
       "      <td>male</td>\n",
       "      <td>51.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113055</td>\n",
       "      <td>26.5500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <td>859</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Baclini, Mrs. Solomon (Latifa Qurban)</td>\n",
       "      <td>female</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2666</td>\n",
       "      <td>19.2583</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>859</th>\n",
       "      <td>860</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Razi, Mr. Raihed</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2629</td>\n",
       "      <td>7.2292</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>860</th>\n",
       "      <td>861</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Hansen, Mr. Claus Peter</td>\n",
       "      <td>male</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>350026</td>\n",
       "      <td>14.1083</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>861</th>\n",
       "      <td>862</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Giles, Mr. Frederick Edward</td>\n",
       "      <td>male</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>28134</td>\n",
       "      <td>11.5000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>862</th>\n",
       "      <td>863</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Swift, Mrs. Frederick Joel (Margaret Welles Ba...</td>\n",
       "      <td>female</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17466</td>\n",
       "      <td>25.9292</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>864</th>\n",
       "      <td>865</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Gill, Mr. John William</td>\n",
       "      <td>male</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>233866</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>865</th>\n",
       "      <td>866</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Bystrom, Mrs. (Karolina)</td>\n",
       "      <td>female</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>236852</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867</th>\n",
       "      <td>868</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Roebling, Mr. Washington Augustus II</td>\n",
       "      <td>male</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17590</td>\n",
       "      <td>50.4958</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>868</th>\n",
       "      <td>869</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>van Melkebeke, Mr. Philemon</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>345777</td>\n",
       "      <td>9.5000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>870</th>\n",
       "      <td>871</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Balkic, Mr. Cerin</td>\n",
       "      <td>male</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349248</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>871</th>\n",
       "      <td>872</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Beckwith, Mrs. Richard Leonard (Sallie Monypeny)</td>\n",
       "      <td>female</td>\n",
       "      <td>47.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11751</td>\n",
       "      <td>52.5542</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>872</th>\n",
       "      <td>873</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Carlsson, Mr. Frans Olof</td>\n",
       "      <td>male</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>695</td>\n",
       "      <td>5.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>873</th>\n",
       "      <td>874</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Vander Cruyssen, Mr. Victor</td>\n",
       "      <td>male</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>345765</td>\n",
       "      <td>9.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874</th>\n",
       "      <td>875</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Abelson, Mrs. Samuel (Hannah Wizosky)</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>P/PP 3381</td>\n",
       "      <td>24.0000</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>876</th>\n",
       "      <td>877</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Gustafsson, Mr. Alfred Ossian</td>\n",
       "      <td>male</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7534</td>\n",
       "      <td>9.8458</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>877</th>\n",
       "      <td>878</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Petroff, Mr. Nedelio</td>\n",
       "      <td>male</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349212</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878</th>\n",
       "      <td>879</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Laleff, Mr. Kristo</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349217</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>879</th>\n",
       "      <td>880</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Potter, Mrs. Thomas Jr (Lily Alexenia Wilson)</td>\n",
       "      <td>female</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11767</td>\n",
       "      <td>83.1583</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>880</th>\n",
       "      <td>881</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Shelley, Mrs. William (Imanita Parrish Hall)</td>\n",
       "      <td>female</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>230433</td>\n",
       "      <td>26.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881</th>\n",
       "      <td>882</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Markun, Mr. Johann</td>\n",
       "      <td>male</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349257</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>883</th>\n",
       "      <td>884</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Banfield, Mr. Frederick James</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>C.A./SOTON 34068</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>884</th>\n",
       "      <td>885</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Sutehall, Mr. Henry Jr</td>\n",
       "      <td>male</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>SOTON/OQ 392076</td>\n",
       "      <td>7.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>885</th>\n",
       "      <td>886</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Rice, Mrs. William (Margaret Norton)</td>\n",
       "      <td>female</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>382652</td>\n",
       "      <td>29.1250</td>\n",
       "      <td>Q</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>890</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Behr, Mr. Karl Howell</td>\n",
       "      <td>male</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>111369</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>C</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890</th>\n",
       "      <td>891</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Dooley, Mr. Patrick</td>\n",
       "      <td>male</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>370376</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>Q</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>647 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     PassengerId  Survived  Pclass  \\\n",
       "0              1         0       3   \n",
       "1              2         1       1   \n",
       "3              4         1       1   \n",
       "4              5         0       3   \n",
       "5              6         0       3   \n",
       "6              7         0       1   \n",
       "8              9         1       3   \n",
       "9             10         1       2   \n",
       "12            13         0       3   \n",
       "13            14         0       3   \n",
       "15            16         1       2   \n",
       "17            18         1       2   \n",
       "18            19         0       3   \n",
       "19            20         1       3   \n",
       "20            21         0       2   \n",
       "21            22         1       2   \n",
       "23            24         1       1   \n",
       "25            26         1       3   \n",
       "26            27         0       3   \n",
       "27            28         0       1   \n",
       "29            30         0       3   \n",
       "31            32         1       1   \n",
       "33            34         0       2   \n",
       "34            35         0       1   \n",
       "35            36         0       1   \n",
       "36            37         1       3   \n",
       "37            38         0       3   \n",
       "40            41         0       3   \n",
       "41            42         0       2   \n",
       "42            43         0       3   \n",
       "..           ...       ...     ...   \n",
       "851          852         0       3   \n",
       "854          855         0       2   \n",
       "855          856         1       3   \n",
       "856          857         1       1   \n",
       "857          858         1       1   \n",
       "858          859         1       3   \n",
       "859          860         0       3   \n",
       "860          861         0       3   \n",
       "861          862         0       2   \n",
       "862          863         1       1   \n",
       "864          865         0       2   \n",
       "865          866         1       2   \n",
       "867          868         0       1   \n",
       "868          869         0       3   \n",
       "870          871         0       3   \n",
       "871          872         1       1   \n",
       "872          873         0       1   \n",
       "873          874         0       3   \n",
       "874          875         1       2   \n",
       "876          877         0       3   \n",
       "877          878         0       3   \n",
       "878          879         0       3   \n",
       "879          880         1       1   \n",
       "880          881         1       2   \n",
       "881          882         0       3   \n",
       "883          884         0       2   \n",
       "884          885         0       3   \n",
       "885          886         0       3   \n",
       "889          890         1       1   \n",
       "890          891         0       3   \n",
       "\n",
       "                                                  Name     Sex   Age  SibSp  \\\n",
       "0                              Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1    Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "3         Futrelle, Mrs. Jacques Heath (Lily May Peel)  female  35.0      1   \n",
       "4                             Allen, Mr. William Henry    male  35.0      0   \n",
       "5                                     Moran, Mr. James    male   NaN      0   \n",
       "6                              McCarthy, Mr. Timothy J    male  54.0      0   \n",
       "8    Johnson, Mrs. Oscar W (Elisabeth Vilhelmina Berg)  female  27.0      0   \n",
       "9                  Nasser, Mrs. Nicholas (Adele Achem)  female  14.0      1   \n",
       "12                      Saundercock, Mr. William Henry    male  20.0      0   \n",
       "13                         Andersson, Mr. Anders Johan    male  39.0      1   \n",
       "15                    Hewlett, Mrs. (Mary D Kingcome)   female  55.0      0   \n",
       "17                        Williams, Mr. Charles Eugene    male   NaN      0   \n",
       "18   Vander Planke, Mrs. Julius (Emelia Maria Vande...  female  31.0      1   \n",
       "19                             Masselmani, Mrs. Fatima  female   NaN      0   \n",
       "20                                Fynney, Mr. Joseph J    male  35.0      0   \n",
       "21                               Beesley, Mr. Lawrence    male  34.0      0   \n",
       "23                        Sloper, Mr. William Thompson    male  28.0      0   \n",
       "25   Asplund, Mrs. Carl Oscar (Selma Augusta Emilia...  female  38.0      1   \n",
       "26                             Emir, Mr. Farred Chehab    male   NaN      0   \n",
       "27                      Fortune, Mr. Charles Alexander    male  19.0      3   \n",
       "29                                 Todoroff, Mr. Lalio    male   NaN      0   \n",
       "31      Spencer, Mrs. William Augustus (Marie Eugenie)  female   NaN      1   \n",
       "33                               Wheadon, Mr. Edward H    male  66.0      0   \n",
       "34                             Meyer, Mr. Edgar Joseph    male  28.0      1   \n",
       "35                      Holverson, Mr. Alexander Oskar    male  42.0      1   \n",
       "36                                    Mamee, Mr. Hanna    male   NaN      0   \n",
       "37                            Cann, Mr. Ernest Charles    male  21.0      0   \n",
       "40      Ahlin, Mrs. Johan (Johanna Persdotter Larsson)  female  40.0      1   \n",
       "41   Turpin, Mrs. William John Robert (Dorothy Ann ...  female  27.0      1   \n",
       "42                                 Kraeff, Mr. Theodor    male   NaN      0   \n",
       "..                                                 ...     ...   ...    ...   \n",
       "851                                Svensson, Mr. Johan    male  74.0      0   \n",
       "854      Carter, Mrs. Ernest Courtenay (Lilian Hughes)  female  44.0      1   \n",
       "855                         Aks, Mrs. Sam (Leah Rosen)  female  18.0      0   \n",
       "856         Wick, Mrs. George Dennick (Mary Hitchcock)  female  45.0      1   \n",
       "857                             Daly, Mr. Peter Denis     male  51.0      0   \n",
       "858              Baclini, Mrs. Solomon (Latifa Qurban)  female  24.0      0   \n",
       "859                                   Razi, Mr. Raihed    male   NaN      0   \n",
       "860                            Hansen, Mr. Claus Peter    male  41.0      2   \n",
       "861                        Giles, Mr. Frederick Edward    male  21.0      1   \n",
       "862  Swift, Mrs. Frederick Joel (Margaret Welles Ba...  female  48.0      0   \n",
       "864                             Gill, Mr. John William    male  24.0      0   \n",
       "865                           Bystrom, Mrs. (Karolina)  female  42.0      0   \n",
       "867               Roebling, Mr. Washington Augustus II    male  31.0      0   \n",
       "868                        van Melkebeke, Mr. Philemon    male   NaN      0   \n",
       "870                                  Balkic, Mr. Cerin    male  26.0      0   \n",
       "871   Beckwith, Mrs. Richard Leonard (Sallie Monypeny)  female  47.0      1   \n",
       "872                           Carlsson, Mr. Frans Olof    male  33.0      0   \n",
       "873                        Vander Cruyssen, Mr. Victor    male  47.0      0   \n",
       "874              Abelson, Mrs. Samuel (Hannah Wizosky)  female  28.0      1   \n",
       "876                      Gustafsson, Mr. Alfred Ossian    male  20.0      0   \n",
       "877                               Petroff, Mr. Nedelio    male  19.0      0   \n",
       "878                                 Laleff, Mr. Kristo    male   NaN      0   \n",
       "879      Potter, Mrs. Thomas Jr (Lily Alexenia Wilson)  female  56.0      0   \n",
       "880       Shelley, Mrs. William (Imanita Parrish Hall)  female  25.0      0   \n",
       "881                                 Markun, Mr. Johann    male  33.0      0   \n",
       "883                      Banfield, Mr. Frederick James    male  28.0      0   \n",
       "884                             Sutehall, Mr. Henry Jr    male  25.0      0   \n",
       "885               Rice, Mrs. William (Margaret Norton)  female  39.0      0   \n",
       "889                              Behr, Mr. Karl Howell    male  26.0      0   \n",
       "890                                Dooley, Mr. Patrick    male  32.0      0   \n",
       "\n",
       "     Parch            Ticket      Fare Embarked age_category  \n",
       "0        0         A/5 21171    7.2500        S        Adult  \n",
       "1        0          PC 17599   71.2833        C        Adult  \n",
       "3        0            113803   53.1000        S        Adult  \n",
       "4        0            373450    8.0500        S        Adult  \n",
       "5        0            330877    8.4583        Q        Adult  \n",
       "6        0             17463   51.8625        S        Adult  \n",
       "8        2            347742   11.1333        S        Adult  \n",
       "9        0            237736   30.0708        C          Kid  \n",
       "12       0         A/5. 2151    8.0500        S        Adult  \n",
       "13       5            347082   31.2750        S        Adult  \n",
       "15       0            248706   16.0000        S        Adult  \n",
       "17       0            244373   13.0000        S        Adult  \n",
       "18       0            345763   18.0000        S        Adult  \n",
       "19       0              2649    7.2250        C        Adult  \n",
       "20       0            239865   26.0000        S        Adult  \n",
       "21       0            248698   13.0000        S        Adult  \n",
       "23       0            113788   35.5000        S        Adult  \n",
       "25       5            347077   31.3875        S        Adult  \n",
       "26       0              2631    7.2250        C        Adult  \n",
       "27       2             19950  263.0000        S        Adult  \n",
       "29       0            349216    7.8958        S        Adult  \n",
       "31       0          PC 17569  146.5208        C        Adult  \n",
       "33       0        C.A. 24579   10.5000        S        Adult  \n",
       "34       0          PC 17604   82.1708        C        Adult  \n",
       "35       0            113789   52.0000        S        Adult  \n",
       "36       0              2677    7.2292        C        Adult  \n",
       "37       0        A./5. 2152    8.0500        S        Adult  \n",
       "40       0              7546    9.4750        S        Adult  \n",
       "41       0             11668   21.0000        S        Adult  \n",
       "42       0            349253    7.8958        C        Adult  \n",
       "..     ...               ...       ...      ...          ...  \n",
       "851      0            347060    7.7750        S        Adult  \n",
       "854      0            244252   26.0000        S        Adult  \n",
       "855      1            392091    9.3500        S        Adult  \n",
       "856      1             36928  164.8667        S        Adult  \n",
       "857      0            113055   26.5500        S        Adult  \n",
       "858      3              2666   19.2583        C        Adult  \n",
       "859      0              2629    7.2292        C        Adult  \n",
       "860      0            350026   14.1083        S        Adult  \n",
       "861      0             28134   11.5000        S        Adult  \n",
       "862      0             17466   25.9292        S        Adult  \n",
       "864      0            233866   13.0000        S        Adult  \n",
       "865      0            236852   13.0000        S        Adult  \n",
       "867      0          PC 17590   50.4958        S        Adult  \n",
       "868      0            345777    9.5000        S        Adult  \n",
       "870      0            349248    7.8958        S        Adult  \n",
       "871      1             11751   52.5542        S        Adult  \n",
       "872      0               695    5.0000        S        Adult  \n",
       "873      0            345765    9.0000        S        Adult  \n",
       "874      0         P/PP 3381   24.0000        C        Adult  \n",
       "876      0              7534    9.8458        S        Adult  \n",
       "877      0            349212    7.8958        S        Adult  \n",
       "878      0            349217    7.8958        S        Adult  \n",
       "879      1             11767   83.1583        C        Adult  \n",
       "880      1            230433   26.0000        S        Adult  \n",
       "881      0            349257    7.8958        S        Adult  \n",
       "883      0  C.A./SOTON 34068   10.5000        S        Adult  \n",
       "884      0   SOTON/OQ 392076    7.0500        S        Adult  \n",
       "885      5            382652   29.1250        Q        Adult  \n",
       "889      0            111369   30.0000        C        Adult  \n",
       "890      0            370376    7.7500        Q        Adult  \n",
       "\n",
       "[647 rows x 12 columns]"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titanic_data[titanic_data.Name.str.contains('Mr')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11. Append,Merge, Join & Concatenate\n",
    "* Append for stacking dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(data=np.random.randint(1,10,size=(10,3)), columns=list('ABC'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(data=np.random.randint(1,10,size=(10,3)), columns=list('ABC'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "0  1  4  3\n",
       "1  8  9  1\n",
       "2  1  6  6\n",
       "3  9  9  4\n",
       "4  9  3  6\n",
       "5  6  7  3\n",
       "6  7  4  2\n",
       "7  2  2  9\n",
       "8  6  9  6\n",
       "9  2  6  8"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "0  1  4  4\n",
       "1  8  6  1\n",
       "2  5  3  2\n",
       "3  9  4  8\n",
       "4  4  1  1\n",
       "5  8  9  6\n",
       "6  1  2  2\n",
       "7  8  1  8\n",
       "8  7  1  3\n",
       "9  3  4  7"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A  B  C\n",
       "0   1  4  3\n",
       "1   8  9  1\n",
       "2   1  6  6\n",
       "3   9  9  4\n",
       "4   9  3  6\n",
       "5   6  7  3\n",
       "6   7  4  2\n",
       "7   2  2  9\n",
       "8   6  9  6\n",
       "9   2  6  8\n",
       "10  1  4  4\n",
       "11  8  6  1\n",
       "12  5  3  2\n",
       "13  9  4  8\n",
       "14  4  1  1\n",
       "15  8  9  6\n",
       "16  1  2  2\n",
       "17  8  1  8\n",
       "18  7  1  3\n",
       "19  3  4  7"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.append(df2, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3','K4','K5'],\n",
    "                         'A': ['A0', 'A1', 'A2', 'A3','A4','A5'],\n",
    "                         'B': ['B0', 'B1', 'B2', 'B3','B4','B5']})\n",
    "\n",
    "right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3','K6','K7'],\n",
    "                          'C': ['C0', 'C1', 'C2', 'C3','C6','C7'],\n",
    "                         'D': ['D0', 'D1', 'D2', 'D3','D6','D7']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>key</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>K0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>K1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>K2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>K3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B key   C   D\n",
       "0  A0  B0  K0  C0  D0\n",
       "1  A1  B1  K1  C1  D1\n",
       "2  A2  B2  K2  C2  D2\n",
       "3  A3  B3  K3  C3  D3"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.merge(right, on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>key</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>K0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>K1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>K2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>K3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>B4</td>\n",
       "      <td>K4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>B5</td>\n",
       "      <td>K5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B key    C    D\n",
       "0  A0  B0  K0   C0   D0\n",
       "1  A1  B1  K1   C1   D1\n",
       "2  A2  B2  K2   C2   D2\n",
       "3  A3  B3  K3   C3   D3\n",
       "4  A4  B4  K4  NaN  NaN\n",
       "5  A5  B5  K5  NaN  NaN"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.merge(right, on='key', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* join for combining data based on index values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n",
    "                     'B': ['B0', 'B1', 'B2']},\n",
    "                     index=['K0', 'K1', 'K2'])\n",
    "\n",
    "right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D2', 'D3']},\n",
    "                     index=['K0', 'K2', 'K3'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B    C    D\n",
       "K0  A0  B0   C0   D0\n",
       "K1  A1  B1  NaN  NaN\n",
       "K2  A2  B2   C2   D2"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.join(right)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12. Pivot Tables\n",
    "* An useful way to get important information from data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data = pd.read_excel('https://github.com/zekelabs/data-science-complete-tutorial/blob/master/Data/sales-funnel.xlsx?raw=true')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "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>Account</th>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>737550</td>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>146832</td>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>218895</td>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>40000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>218895</td>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>412290</td>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>740150</td>\n",
       "      <td>Barton LLC</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>141962</td>\n",
       "      <td>Herman LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>163416</td>\n",
       "      <td>Purdy-Kunde</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>239344</td>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>239344</td>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>307599</td>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>3</td>\n",
       "      <td>7000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>688981</td>\n",
       "      <td>Keeling LLC</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>5</td>\n",
       "      <td>100000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>729833</td>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>729833</td>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Monitor</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Account                          Name            Rep        Manager  \\\n",
       "0    714466               Trantow-Barrows   Craig Booker   Debra Henley   \n",
       "1    714466               Trantow-Barrows   Craig Booker   Debra Henley   \n",
       "2    714466               Trantow-Barrows   Craig Booker   Debra Henley   \n",
       "3    737550  Fritsch, Russel and Anderson   Craig Booker   Debra Henley   \n",
       "4    146832                  Kiehn-Spinka  Daniel Hilton   Debra Henley   \n",
       "5    218895                     Kulas Inc  Daniel Hilton   Debra Henley   \n",
       "6    218895                     Kulas Inc  Daniel Hilton   Debra Henley   \n",
       "7    412290                 Jerde-Hilpert     John Smith   Debra Henley   \n",
       "8    740150                    Barton LLC     John Smith   Debra Henley   \n",
       "9    141962                    Herman LLC    Cedric Moss  Fred Anderson   \n",
       "10   163416                   Purdy-Kunde    Cedric Moss  Fred Anderson   \n",
       "11   239344                    Stokes LLC    Cedric Moss  Fred Anderson   \n",
       "12   239344                    Stokes LLC    Cedric Moss  Fred Anderson   \n",
       "13   307599   Kassulke, Ondricka and Metz     Wendy Yule  Fred Anderson   \n",
       "14   688981                   Keeling LLC     Wendy Yule  Fred Anderson   \n",
       "15   729833                     Koepp Ltd     Wendy Yule  Fred Anderson   \n",
       "16   729833                     Koepp Ltd     Wendy Yule  Fred Anderson   \n",
       "\n",
       "        Product  Quantity   Price     Status  \n",
       "0           CPU         1   30000  presented  \n",
       "1      Software         1   10000  presented  \n",
       "2   Maintenance         2    5000    pending  \n",
       "3           CPU         1   35000   declined  \n",
       "4           CPU         2   65000        won  \n",
       "5           CPU         2   40000    pending  \n",
       "6      Software         1   10000  presented  \n",
       "7   Maintenance         2    5000    pending  \n",
       "8           CPU         1   35000   declined  \n",
       "9           CPU         2   65000        won  \n",
       "10          CPU         1   30000  presented  \n",
       "11  Maintenance         1    5000    pending  \n",
       "12     Software         1   10000  presented  \n",
       "13  Maintenance         3    7000        won  \n",
       "14          CPU         5  100000        won  \n",
       "15          CPU         2   65000   declined  \n",
       "16      Monitor         2    5000  presented  "
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>2880948</td>\n",
       "      <td>80000</td>\n",
       "      <td>720237.0</td>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>584622</td>\n",
       "      <td>115000</td>\n",
       "      <td>194874.0</td>\n",
       "      <td>38333.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>1152440</td>\n",
       "      <td>40000</td>\n",
       "      <td>576220.0</td>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>784066</td>\n",
       "      <td>110000</td>\n",
       "      <td>196016.5</td>\n",
       "      <td>27500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>2456246</td>\n",
       "      <td>177000</td>\n",
       "      <td>614061.5</td>\n",
       "      <td>44250.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 sum              mean              \n",
       "                             Account   Price   Account         Price\n",
       "Manager       Rep                                                   \n",
       "Debra Henley  Craig Booker   2880948   80000  720237.0  20000.000000\n",
       "              Daniel Hilton   584622  115000  194874.0  38333.333333\n",
       "              John Smith     1152440   40000  576220.0  20000.000000\n",
       "Fred Anderson Cedric Moss     784066  110000  196016.5  27500.000000\n",
       "              Wendy Yule     2456246  177000  614061.5  44250.000000"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(sales_data, index=['Manager','Rep'], values=['Account','Price'], aggfunc=[np.sum, np.mean])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 13. Normalizing JSON\n",
    "* JSON data will not always be of flat but can be hierchial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [     {'state': 'Florida',\n",
    "              'shortname': 'FL',\n",
    "              'info': {\n",
    "                   'governor': 'Rick Scott'\n",
    "              },\n",
    "              'counties': [{'name': 'Dade', 'population': 12345},\n",
    "                          {'name': 'Broward', 'population': 40000},\n",
    "                          {'name': 'Palm Beach', 'population': 60000}]},\n",
    "             {'state': 'Ohio',\n",
    "              'shortname': 'OH',\n",
    "              'info': {\n",
    "                   'governor': 'John Kasich'\n",
    "              },\n",
    "              'counties': [{'name': 'Summit', 'population': 1234},\n",
    "                           {'name': 'Cuyahoga', 'population': 1337}]}]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "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>counties</th>\n",
       "      <th>info.governor</th>\n",
       "      <th>shortname</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'name': 'Dade', 'population': 12345}, {'name...</td>\n",
       "      <td>Rick Scott</td>\n",
       "      <td>FL</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[{'name': 'Summit', 'population': 1234}, {'nam...</td>\n",
       "      <td>John Kasich</td>\n",
       "      <td>OH</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            counties info.governor shortname  \\\n",
       "0  [{'name': 'Dade', 'population': 12345}, {'name...    Rick Scott        FL   \n",
       "1  [{'name': 'Summit', 'population': 1234}, {'nam...   John Kasich        OH   \n",
       "\n",
       "     state  \n",
       "0  Florida  \n",
       "1     Ohio  "
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_normalize(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "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>name</th>\n",
       "      <th>population</th>\n",
       "      <th>state</th>\n",
       "      <th>info.governor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dade</td>\n",
       "      <td>12345</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Broward</td>\n",
       "      <td>40000</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Palm Beach</td>\n",
       "      <td>60000</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Summit</td>\n",
       "      <td>1234</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>John Kasich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cuyahoga</td>\n",
       "      <td>1337</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>John Kasich</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name  population    state info.governor\n",
       "0        Dade       12345  Florida    Rick Scott\n",
       "1     Broward       40000  Florida    Rick Scott\n",
       "2  Palm Beach       60000  Florida    Rick Scott\n",
       "3      Summit        1234     Ohio   John Kasich\n",
       "4    Cuyahoga        1337     Ohio   John Kasich"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_normalize(data,'counties',['state',['info', 'governor']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}