Python Pandas CSV.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Read CSV Tutorial\n",
    "Here's the read_csv examples for the YouTube Video (link [here](https://youtu.be/piCU_gxSF7I))."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Unnamed: 0</th>\n",
       "      <th>idNum</th>\n",
       "      <th>date</th>\n",
       "      <th>problem</th>\n",
       "      <th>MDC</th>\n",
       "      <th>citationIssued</th>\n",
       "      <th>personSearch</th>\n",
       "      <th>vehicleSearch</th>\n",
       "      <th>preRace</th>\n",
       "      <th>race</th>\n",
       "      <th>gender</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>policePrecinct</th>\n",
       "      <th>neighborhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6823</td>\n",
       "      <td>17-000003</td>\n",
       "      <td>2017-01-01 00:00:42</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>44.966617</td>\n",
       "      <td>-93.246458</td>\n",
       "      <td>1</td>\n",
       "      <td>Cedar Riverside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6824</td>\n",
       "      <td>17-000007</td>\n",
       "      <td>2017-01-01 00:03:07</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Male</td>\n",
       "      <td>44.980450</td>\n",
       "      <td>-93.271340</td>\n",
       "      <td>1</td>\n",
       "      <td>Downtown West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6825</td>\n",
       "      <td>17-000073</td>\n",
       "      <td>2017-01-01 00:23:15</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>44.948350</td>\n",
       "      <td>-93.275380</td>\n",
       "      <td>5</td>\n",
       "      <td>Whittier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6826</td>\n",
       "      <td>17-000092</td>\n",
       "      <td>2017-01-01 00:33:48</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>East African</td>\n",
       "      <td>Male</td>\n",
       "      <td>44.948360</td>\n",
       "      <td>-93.281350</td>\n",
       "      <td>5</td>\n",
       "      <td>Whittier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6827</td>\n",
       "      <td>17-000098</td>\n",
       "      <td>2017-01-01 00:37:58</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>44.979078</td>\n",
       "      <td>-93.262076</td>\n",
       "      <td>1</td>\n",
       "      <td>Downtown West</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0      idNum                 date     problem  MDC citationIssued  \\\n",
       "0        6823  17-000003  2017-01-01 00:00:42  suspicious  MDC            NaN   \n",
       "1        6824  17-000007  2017-01-01 00:03:07  suspicious  MDC            NaN   \n",
       "2        6825  17-000073  2017-01-01 00:23:15     traffic  MDC            NaN   \n",
       "3        6826  17-000092  2017-01-01 00:33:48  suspicious  MDC            NaN   \n",
       "4        6827  17-000098  2017-01-01 00:37:58     traffic  MDC            NaN   \n",
       "\n",
       "  personSearch vehicleSearch  preRace          race   gender        lat  \\\n",
       "0           NO            NO  Unknown       Unknown  Unknown  44.966617   \n",
       "1           NO            NO  Unknown       Unknown     Male  44.980450   \n",
       "2           NO            NO  Unknown         White   Female  44.948350   \n",
       "3           NO            NO  Unknown  East African     Male  44.948360   \n",
       "4           NO            NO  Unknown         White   Female  44.979078   \n",
       "\n",
       "        long  policePrecinct     neighborhood  \n",
       "0 -93.246458               1  Cedar Riverside  \n",
       "1 -93.271340               1    Downtown West  \n",
       "2 -93.275380               5         Whittier  \n",
       "3 -93.281350               5         Whittier  \n",
       "4 -93.262076               1    Downtown West  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url_csv = 'http://vincentarelbundock.github.io/Rdatasets/csv/carData/MplsStops.csv'\n",
    "\n",
    "df = pd.read_csv(url_csv)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Read CSV Setting Index Column\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idNum</th>\n",
       "      <th>date</th>\n",
       "      <th>problem</th>\n",
       "      <th>MDC</th>\n",
       "      <th>citationIssued</th>\n",
       "      <th>personSearch</th>\n",
       "      <th>vehicleSearch</th>\n",
       "      <th>preRace</th>\n",
       "      <th>race</th>\n",
       "      <th>gender</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>policePrecinct</th>\n",
       "      <th>neighborhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6823</th>\n",
       "      <td>17-000003</td>\n",
       "      <td>2017-01-01 00:00:42</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>44.966617</td>\n",
       "      <td>-93.246458</td>\n",
       "      <td>1</td>\n",
       "      <td>Cedar Riverside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6824</th>\n",
       "      <td>17-000007</td>\n",
       "      <td>2017-01-01 00:03:07</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Male</td>\n",
       "      <td>44.980450</td>\n",
       "      <td>-93.271340</td>\n",
       "      <td>1</td>\n",
       "      <td>Downtown West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6825</th>\n",
       "      <td>17-000073</td>\n",
       "      <td>2017-01-01 00:23:15</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>44.948350</td>\n",
       "      <td>-93.275380</td>\n",
       "      <td>5</td>\n",
       "      <td>Whittier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6826</th>\n",
       "      <td>17-000092</td>\n",
       "      <td>2017-01-01 00:33:48</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>East African</td>\n",
       "      <td>Male</td>\n",
       "      <td>44.948360</td>\n",
       "      <td>-93.281350</td>\n",
       "      <td>5</td>\n",
       "      <td>Whittier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6827</th>\n",
       "      <td>17-000098</td>\n",
       "      <td>2017-01-01 00:37:58</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>44.979078</td>\n",
       "      <td>-93.262076</td>\n",
       "      <td>1</td>\n",
       "      <td>Downtown West</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          idNum                 date     problem  MDC citationIssued  \\\n",
       "6823  17-000003  2017-01-01 00:00:42  suspicious  MDC            NaN   \n",
       "6824  17-000007  2017-01-01 00:03:07  suspicious  MDC            NaN   \n",
       "6825  17-000073  2017-01-01 00:23:15     traffic  MDC            NaN   \n",
       "6826  17-000092  2017-01-01 00:33:48  suspicious  MDC            NaN   \n",
       "6827  17-000098  2017-01-01 00:37:58     traffic  MDC            NaN   \n",
       "\n",
       "     personSearch vehicleSearch  preRace          race   gender        lat  \\\n",
       "6823           NO            NO  Unknown       Unknown  Unknown  44.966617   \n",
       "6824           NO            NO  Unknown       Unknown     Male  44.980450   \n",
       "6825           NO            NO  Unknown         White   Female  44.948350   \n",
       "6826           NO            NO  Unknown  East African     Male  44.948360   \n",
       "6827           NO            NO  Unknown         White   Female  44.979078   \n",
       "\n",
       "           long  policePrecinct     neighborhood  \n",
       "6823 -93.246458               1  Cedar Riverside  \n",
       "6824 -93.271340               1    Downtown West  \n",
       "6825 -93.275380               5         Whittier  \n",
       "6826 -93.281350               5         Whittier  \n",
       "6827 -93.262076               1    Downtown West  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url_csv = 'http://vincentarelbundock.github.io/Rdatasets/csv/carData/MplsStops.csv'\n",
    "\n",
    "df = pd.read_csv(url_csv, index_col=0)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Reading Certain Columns\n",
    "First we use Pandas usecols with a list of integers"
   ]
  },
  {
   "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>idNum</th>\n",
       "      <th>date</th>\n",
       "      <th>problem</th>\n",
       "      <th>MDC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6823</th>\n",
       "      <td>17-000003</td>\n",
       "      <td>2017-01-01 00:00:42</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6824</th>\n",
       "      <td>17-000007</td>\n",
       "      <td>2017-01-01 00:03:07</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6825</th>\n",
       "      <td>17-000073</td>\n",
       "      <td>2017-01-01 00:23:15</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6826</th>\n",
       "      <td>17-000092</td>\n",
       "      <td>2017-01-01 00:33:48</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6827</th>\n",
       "      <td>17-000098</td>\n",
       "      <td>2017-01-01 00:37:58</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          idNum                 date     problem  MDC\n",
       "6823  17-000003  2017-01-01 00:00:42  suspicious  MDC\n",
       "6824  17-000007  2017-01-01 00:03:07  suspicious  MDC\n",
       "6825  17-000073  2017-01-01 00:23:15     traffic  MDC\n",
       "6826  17-000092  2017-01-01 00:33:48  suspicious  MDC\n",
       "6827  17-000098  2017-01-01 00:37:58     traffic  MDC"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url_csv = 'http://vincentarelbundock.github.io/Rdatasets/csv/carData/MplsStops.csv'\n",
    "\n",
    "cols = [0, 1, 2, 3, 4]\n",
    "df = pd.read_csv(url_csv, index_col=0, usecols=cols)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we use Pandas usecols with a list of strings"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>problem</th>\n",
       "      <th>MDC</th>\n",
       "      <th>personSearch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idNum</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17-000003</th>\n",
       "      <td>2017-01-01 00:00:42</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17-000007</th>\n",
       "      <td>2017-01-01 00:03:07</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17-000073</th>\n",
       "      <td>2017-01-01 00:23:15</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17-000092</th>\n",
       "      <td>2017-01-01 00:33:48</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17-000098</th>\n",
       "      <td>2017-01-01 00:37:58</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          date     problem  MDC personSearch\n",
       "idNum                                                       \n",
       "17-000003  2017-01-01 00:00:42  suspicious  MDC           NO\n",
       "17-000007  2017-01-01 00:03:07  suspicious  MDC           NO\n",
       "17-000073  2017-01-01 00:23:15     traffic  MDC           NO\n",
       "17-000092  2017-01-01 00:33:48  suspicious  MDC           NO\n",
       "17-000098  2017-01-01 00:37:58     traffic  MDC           NO"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url_csv = 'http://vincentarelbundock.github.io/Rdatasets/csv/carData/MplsStops.csv'\n",
    "\n",
    "cols = ['idNum', 'date', 'problem', 'MDC', 'personSearch']\n",
    "\n",
    "df = pd.read_csv(url_csv, index_col='idNum', usecols=cols)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 51920 entries, 17-000003 to 17-491482\n",
      "Data columns (total 4 columns):\n",
      "date            51920 non-null object\n",
      "problem         51920 non-null object\n",
      "MDC             51920 non-null object\n",
      "personSearch    43699 non-null object\n",
      "dtypes: object(4)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove Unnamed Columns from Pandas Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'idNum', 'date', 'problem', 'MDC', 'citationIssued',\n",
       "       'personSearch', 'vehicleSearch', 'preRace', 'race', 'gender', 'lat',\n",
       "       'long', 'policePrecinct', 'neighborhood'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csv_url = 'http://vincentarelbundock.github.io/Rdatasets/csv/carData/MplsStops.csv'\n",
    "cols = pd.read_csv(csv_url, nrows=1).columns # Read first rows using nrows and take the columns\n",
    "cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idNum</th>\n",
       "      <th>date</th>\n",
       "      <th>problem</th>\n",
       "      <th>MDC</th>\n",
       "      <th>citationIssued</th>\n",
       "      <th>personSearch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17-000003</td>\n",
       "      <td>2017-01-01 00:00:42</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17-000007</td>\n",
       "      <td>2017-01-01 00:03:07</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17-000073</td>\n",
       "      <td>2017-01-01 00:23:15</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17-000092</td>\n",
       "      <td>2017-01-01 00:33:48</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17-000098</td>\n",
       "      <td>2017-01-01 00:37:58</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       idNum                 date     problem  MDC citationIssued personSearch\n",
       "0  17-000003  2017-01-01 00:00:42  suspicious  MDC            NaN           NO\n",
       "1  17-000007  2017-01-01 00:03:07  suspicious  MDC            NaN           NO\n",
       "2  17-000073  2017-01-01 00:23:15     traffic  MDC            NaN           NO\n",
       "3  17-000092  2017-01-01 00:33:48  suspicious  MDC            NaN           NO\n",
       "4  17-000098  2017-01-01 00:37:58     traffic  MDC            NaN           NO"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(csv_url, usecols=cols[1:]) # Take col 2 and forward\n",
    "df.iloc[:, 0:6].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idNum</th>\n",
       "      <th>date</th>\n",
       "      <th>problem</th>\n",
       "      <th>MDC</th>\n",
       "      <th>citationIssued</th>\n",
       "      <th>personSearch</th>\n",
       "      <th>vehicleSearch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17-000003</td>\n",
       "      <td>2017-01-01 00:00:42</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17-000007</td>\n",
       "      <td>2017-01-01 00:03:07</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17-000073</td>\n",
       "      <td>2017-01-01 00:23:15</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17-000092</td>\n",
       "      <td>2017-01-01 00:33:48</td>\n",
       "      <td>suspicious</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17-000098</td>\n",
       "      <td>2017-01-01 00:37:58</td>\n",
       "      <td>traffic</td>\n",
       "      <td>MDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       idNum                 date     problem  MDC citationIssued  \\\n",
       "0  17-000003  2017-01-01 00:00:42  suspicious  MDC            NaN   \n",
       "1  17-000007  2017-01-01 00:03:07  suspicious  MDC            NaN   \n",
       "2  17-000073  2017-01-01 00:23:15     traffic  MDC            NaN   \n",
       "3  17-000092  2017-01-01 00:33:48  suspicious  MDC            NaN   \n",
       "4  17-000098  2017-01-01 00:37:58     traffic  MDC            NaN   \n",
       "\n",
       "  personSearch vehicleSearch  \n",
       "0           NO            NO  \n",
       "1           NO            NO  \n",
       "2           NO            NO  \n",
       "3           NO            NO  \n",
       "4           NO            NO  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop(df.columns[df.columns.str.contains('unnamed', case=False)],\n",
    "          axis=1, inplace=True)\n",
    "\n",
    "# The following line will give us the same result as the line above\n",
    "# df = df.loc[:, ~df.columns.str.contains('unnamed', case=False)]\n",
    "\n",
    "df.iloc[:, 0:7].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Skipping rows using Pandas read_csv skiprows and header\n",
    "Now we will skip rows using the parameter skiprows. When we have done that we will use the parameter header to tell the parser where the header row is. In this case this is the same as skipping the rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Day</th>\n",
       "      <th>Age</th>\n",
       "      <th>Response</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>23</td>\n",
       "      <td>0.5627187141917855</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Billie</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>22</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Robert</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>20</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Don</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>27</td>\n",
       "      <td>0.5229607643652396</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>21</td>\n",
       "      <td>Not Available</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID    Name    Day  Age            Response  Gender\n",
       "0   1    John  Sixth   23  0.5627187141917855       0\n",
       "1   2  Billie  Sixth   22       Not Available       0\n",
       "2   3  Robert  Sixth   20       Not Available       0\n",
       "3   4     Don  Sixth   27  0.5229607643652396       0\n",
       "4   5  Joseph  Sixth   21       Not Available       0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "csv_file = 'Simdata/skiprow.csv'\n",
    "\n",
    "df = pd.read_csv(csv_file, index_col=0, skiprows=3)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "csv_file = 'Simdata/skiprow.csv'\n",
    "\n",
    "df = pd.read_csv(csv_file, index_col=0, header=3)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missing Values Coded Differently than 'Usual'\n",
    "Now we are going to handle missing values that are coded in a way that the parser cannot understand. For instance, if it was coded \"Not Available\" we have to tell Pandas this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Day</th>\n",
       "      <th>Age</th>\n",
       "      <th>Response</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>23</td>\n",
       "      <td>0.562719</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Billie</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Robert</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Don</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>27</td>\n",
       "      <td>0.522961</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID    Name    Day  Age  Response  Gender\n",
       "0   1    John  Sixth   23  0.562719       0\n",
       "1   2  Billie  Sixth   22       NaN       0\n",
       "2   3  Robert  Sixth   20       NaN       0\n",
       "3   4     Don  Sixth   27  0.522961       0\n",
       "4   5  Joseph  Sixth   21       NaN       0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "csv_file = 'Simdata/skiprow.csv'\n",
    "\n",
    "df = pd.read_csv(csv_file, index_col=0, header=3, na_values='Not Available')\n",
    "\n",
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}