clean_2014_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#BRFSS cleaning example\n",
    "\n",
    "The BRFSS is a fairly large and messy dataset. To put it into a useable format, you have to do a bit of cleaning and massaging. This Notebook demonstrates cleaning the data bit by bit for 2014. Scripts for every year are in the `cleaning_code` folder. I had to do a separate script for each year because the codebooks change slightly nearly every year, and it's just enough to make it a real headache.\n",
    "\n",
    "Pandas is a terrific tool for data cleaning and manipulation, and that's what I use here.\n",
    "\n",
    "##Getting started\n",
    "Let's start by importing our libraries. We'll be using Pandas and Numpy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we'll set our year variable (2014), which made it easy to change when I had to copy scripts for each year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "year = '2014'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Reading in the data\n",
    "\n",
    "The original BRFSS data is available from the CDC in a couple of arcane formats (.XPT and .ASCII). I had to use R to get it into a nice CSV format. You can use my `sas2csv.R` script to make the translation if you like. I've done it for you and have stored the data on my Amazon server [link to raw csv data](https://www.amazon.com/clouddrive/share/HAfuNnNSbFqKmdyuodrVAQMpgcyqoFACuBoKWIqoWeG?ref_=cd_ph_share_link_copy).  This isn't available on GitHub because the file is too big. Also, \n",
    "\n",
    "Let's use Pandas to read in the `.csv` of the raw data for 2014. Since the file is so big, it might take some time to read, and you might even get a warning. We didn't specify UTF-8 encoding in the R script for creating the `.csv`'s, so we need to specify the encoding, which somehow ends up being the interesting choice of \"cp1252.\" That little tidbit right there was a couple hours worth of Googling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/wlarson/anaconda/lib/python3.4/site-packages/pandas/io/parsers.py:1130: DtypeWarning: Columns (121) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  data = self._reader.read(nrows)\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('data/brfss' + year + '.csv', encoding='cp1252')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a quick look at what we have here. As you can see, this is a rather large data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>x.state</th>\n",
       "      <th>fmonth</th>\n",
       "      <th>idate</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>iyear</th>\n",
       "      <th>dispcode</th>\n",
       "      <th>seqno</th>\n",
       "      <th>x.psu</th>\n",
       "      <th>...</th>\n",
       "      <th>x.fobtfs</th>\n",
       "      <th>x.crcrec</th>\n",
       "      <th>x.aidtst3</th>\n",
       "      <th>x.impeduc</th>\n",
       "      <th>x.impmrtl</th>\n",
       "      <th>x.imphome</th>\n",
       "      <th>rcsbrac1</th>\n",
       "      <th>rcsrace1</th>\n",
       "      <th>rchisla1</th>\n",
       "      <th>rcsbirth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0     </th>\n",
       "      <td>      1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1172014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 17</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000001</td>\n",
       "      <td> 2014000001</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1     </th>\n",
       "      <td>      2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1072014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000002</td>\n",
       "      <td> 2014000002</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2     </th>\n",
       "      <td>      3</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1092014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  9</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000003</td>\n",
       "      <td> 2014000003</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3     </th>\n",
       "      <td>      4</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1072014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000004</td>\n",
       "      <td> 2014000004</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 3</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4     </th>\n",
       "      <td>      5</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1162014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 16</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000005</td>\n",
       "      <td> 2014000005</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5     </th>\n",
       "      <td>      6</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1022014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000006</td>\n",
       "      <td> 2014000006</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6     </th>\n",
       "      <td>      7</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1062014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  6</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000007</td>\n",
       "      <td> 2014000007</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7     </th>\n",
       "      <td>      8</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1112014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 11</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000008</td>\n",
       "      <td> 2014000008</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td> 4</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8     </th>\n",
       "      <td>      9</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1022014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000009</td>\n",
       "      <td> 2014000009</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 3</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9     </th>\n",
       "      <td>     10</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1082014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  8</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000010</td>\n",
       "      <td> 2014000010</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10    </th>\n",
       "      <td>     11</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1072014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000011</td>\n",
       "      <td> 2014000011</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11    </th>\n",
       "      <td>     12</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1022014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000012</td>\n",
       "      <td> 2014000012</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12    </th>\n",
       "      <td>     13</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1132014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 13</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000013</td>\n",
       "      <td> 2014000013</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13    </th>\n",
       "      <td>     14</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1042014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  4</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000014</td>\n",
       "      <td> 2014000014</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 2</td>\n",
       "      <td> 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14    </th>\n",
       "      <td>     15</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1022014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000015</td>\n",
       "      <td> 2014000015</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 3</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15    </th>\n",
       "      <td>     16</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1062014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  6</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000016</td>\n",
       "      <td> 2014000016</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 3</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16    </th>\n",
       "      <td>     17</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1072014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000017</td>\n",
       "      <td> 2014000017</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17    </th>\n",
       "      <td>     18</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1072014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000018</td>\n",
       "      <td> 2014000018</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 3</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18    </th>\n",
       "      <td>     19</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1042014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  4</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000019</td>\n",
       "      <td> 2014000019</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19    </th>\n",
       "      <td>     20</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1082014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  8</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000020</td>\n",
       "      <td> 2014000020</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 3</td>\n",
       "      <td> 4</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20    </th>\n",
       "      <td>     21</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1092014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  9</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1200</td>\n",
       "      <td> 2014000021</td>\n",
       "      <td> 2014000021</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td> 2</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21    </th>\n",
       "      <td>     22</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1032014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  3</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000022</td>\n",
       "      <td> 2014000022</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22    </th>\n",
       "      <td>     23</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1132014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 13</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000023</td>\n",
       "      <td> 2014000023</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 5</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23    </th>\n",
       "      <td>     24</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1152014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000024</td>\n",
       "      <td> 2014000024</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24    </th>\n",
       "      <td>     25</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1072014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000025</td>\n",
       "      <td> 2014000025</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 5</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25    </th>\n",
       "      <td>     26</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1142014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 14</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000026</td>\n",
       "      <td> 2014000026</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26    </th>\n",
       "      <td>     27</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1112014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 11</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000027</td>\n",
       "      <td> 2014000027</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27    </th>\n",
       "      <td>     28</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1082014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  8</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000028</td>\n",
       "      <td> 2014000028</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28    </th>\n",
       "      <td>     29</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1072014</td>\n",
       "      <td>  1</td>\n",
       "      <td>  7</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000029</td>\n",
       "      <td> 2014000029</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 4</td>\n",
       "      <td> 3</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29    </th>\n",
       "      <td>     30</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1142014</td>\n",
       "      <td>  1</td>\n",
       "      <td> 14</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014000030</td>\n",
       "      <td> 2014000030</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  9</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <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>464634</th>\n",
       "      <td> 464635</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005959</td>\n",
       "      <td> 2014005959</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td> 6</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464635</th>\n",
       "      <td> 464636</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12132014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 13</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005960</td>\n",
       "      <td> 2014005960</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 5</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464636</th>\n",
       "      <td> 464637</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12112014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 11</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005961</td>\n",
       "      <td> 2014005961</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464637</th>\n",
       "      <td> 464638</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12142014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 14</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005962</td>\n",
       "      <td> 2014005962</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464638</th>\n",
       "      <td> 464639</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005963</td>\n",
       "      <td> 2014005963</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464639</th>\n",
       "      <td> 464640</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12122014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 12</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005964</td>\n",
       "      <td> 2014005964</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464640</th>\n",
       "      <td> 464641</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12122014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 12</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005965</td>\n",
       "      <td> 2014005965</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464641</th>\n",
       "      <td> 464642</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005966</td>\n",
       "      <td> 2014005966</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464642</th>\n",
       "      <td> 464643</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12142014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 14</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005967</td>\n",
       "      <td> 2014005967</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464643</th>\n",
       "      <td> 464644</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12152014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1200</td>\n",
       "      <td> 2014005968</td>\n",
       "      <td> 2014005968</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464644</th>\n",
       "      <td> 464645</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12132014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 13</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005969</td>\n",
       "      <td> 2014005969</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464645</th>\n",
       "      <td> 464646</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005970</td>\n",
       "      <td> 2014005970</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 4</td>\n",
       "      <td> 2</td>\n",
       "      <td> 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464646</th>\n",
       "      <td> 464647</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12122014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 12</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005971</td>\n",
       "      <td> 2014005971</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 5</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464647</th>\n",
       "      <td> 464648</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12152014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005972</td>\n",
       "      <td> 2014005972</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 2</td>\n",
       "      <td> 3</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464648</th>\n",
       "      <td> 464649</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12152014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1200</td>\n",
       "      <td> 2014005973</td>\n",
       "      <td> 2014005973</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td> 5</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464649</th>\n",
       "      <td> 464650</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12152014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005974</td>\n",
       "      <td> 2014005974</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464650</th>\n",
       "      <td> 464651</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005975</td>\n",
       "      <td> 2014005975</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464651</th>\n",
       "      <td> 464652</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12152014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005976</td>\n",
       "      <td> 2014005976</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464652</th>\n",
       "      <td> 464653</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12112014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 11</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005977</td>\n",
       "      <td> 2014005977</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 5</td>\n",
       "      <td> 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464653</th>\n",
       "      <td> 464654</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12162014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 16</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005978</td>\n",
       "      <td> 2014005978</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  1</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464654</th>\n",
       "      <td> 464655</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12152014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005979</td>\n",
       "      <td> 2014005979</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 3</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464655</th>\n",
       "      <td> 464656</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12122014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 12</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005980</td>\n",
       "      <td> 2014005980</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464656</th>\n",
       "      <td> 464657</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12162014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 16</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005981</td>\n",
       "      <td> 2014005981</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464657</th>\n",
       "      <td> 464658</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005982</td>\n",
       "      <td> 2014005982</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 4</td>\n",
       "      <td> 2</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464658</th>\n",
       "      <td> 464659</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005983</td>\n",
       "      <td> 2014005983</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td>  2</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464659</th>\n",
       "      <td> 464660</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12112014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 11</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005984</td>\n",
       "      <td> 2014005984</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464660</th>\n",
       "      <td> 464661</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005985</td>\n",
       "      <td> 2014005985</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  2</td>\n",
       "      <td> 6</td>\n",
       "      <td> 2</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464661</th>\n",
       "      <td> 464662</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12152014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 15</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005986</td>\n",
       "      <td> 2014005986</td>\n",
       "      <td>...</td>\n",
       "      <td>  2</td>\n",
       "      <td>  2</td>\n",
       "      <td>  1</td>\n",
       "      <td> 5</td>\n",
       "      <td> 1</td>\n",
       "      <td> 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464662</th>\n",
       "      <td> 464663</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12132014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 13</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005987</td>\n",
       "      <td> 2014005987</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 4</td>\n",
       "      <td> 1</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464663</th>\n",
       "      <td> 464664</td>\n",
       "      <td> 72</td>\n",
       "      <td> 11</td>\n",
       "      <td> 12102014</td>\n",
       "      <td> 12</td>\n",
       "      <td> 10</td>\n",
       "      <td> 2014</td>\n",
       "      <td> 1100</td>\n",
       "      <td> 2014005988</td>\n",
       "      <td> 2014005988</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>  1</td>\n",
       "      <td> 6</td>\n",
       "      <td> 6</td>\n",
       "      <td> 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>464664 rows × 280 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0  x.state  fmonth     idate  imonth  iday  iyear  dispcode  \\\n",
       "0                1        1       1   1172014       1    17   2014      1100   \n",
       "1                2        1       1   1072014       1     7   2014      1100   \n",
       "2                3        1       1   1092014       1     9   2014      1100   \n",
       "3                4        1       1   1072014       1     7   2014      1100   \n",
       "4                5        1       1   1162014       1    16   2014      1100   \n",
       "5                6        1       1   1022014       1     2   2014      1100   \n",
       "6                7        1       1   1062014       1     6   2014      1100   \n",
       "7                8        1       1   1112014       1    11   2014      1100   \n",
       "8                9        1       1   1022014       1     2   2014      1100   \n",
       "9               10        1       1   1082014       1     8   2014      1100   \n",
       "10              11        1       1   1072014       1     7   2014      1100   \n",
       "11              12        1       1   1022014       1     2   2014      1100   \n",
       "12              13        1       1   1132014       1    13   2014      1100   \n",
       "13              14        1       1   1042014       1     4   2014      1100   \n",
       "14              15        1       1   1022014       1     2   2014      1100   \n",
       "15              16        1       1   1062014       1     6   2014      1100   \n",
       "16              17        1       1   1072014       1     7   2014      1100   \n",
       "17              18        1       1   1072014       1     7   2014      1100   \n",
       "18              19        1       1   1042014       1     4   2014      1100   \n",
       "19              20        1       1   1082014       1     8   2014      1100   \n",
       "20              21        1       1   1092014       1     9   2014      1200   \n",
       "21              22        1       1   1032014       1     3   2014      1100   \n",
       "22              23        1       1   1132014       1    13   2014      1100   \n",
       "23              24        1       1   1152014       1    15   2014      1100   \n",
       "24              25        1       1   1072014       1     7   2014      1100   \n",
       "25              26        1       1   1142014       1    14   2014      1100   \n",
       "26              27        1       1   1112014       1    11   2014      1100   \n",
       "27              28        1       1   1082014       1     8   2014      1100   \n",
       "28              29        1       1   1072014       1     7   2014      1100   \n",
       "29              30        1       1   1142014       1    14   2014      1100   \n",
       "...            ...      ...     ...       ...     ...   ...    ...       ...   \n",
       "464634      464635       72      11  12102014      12    10   2014      1100   \n",
       "464635      464636       72      11  12132014      12    13   2014      1100   \n",
       "464636      464637       72      11  12112014      12    11   2014      1100   \n",
       "464637      464638       72      11  12142014      12    14   2014      1100   \n",
       "464638      464639       72      11  12102014      12    10   2014      1100   \n",
       "464639      464640       72      11  12122014      12    12   2014      1100   \n",
       "464640      464641       72      11  12122014      12    12   2014      1100   \n",
       "464641      464642       72      11  12102014      12    10   2014      1100   \n",
       "464642      464643       72      11  12142014      12    14   2014      1100   \n",
       "464643      464644       72      11  12152014      12    15   2014      1200   \n",
       "464644      464645       72      11  12132014      12    13   2014      1100   \n",
       "464645      464646       72      11  12102014      12    10   2014      1100   \n",
       "464646      464647       72      11  12122014      12    12   2014      1100   \n",
       "464647      464648       72      11  12152014      12    15   2014      1100   \n",
       "464648      464649       72      11  12152014      12    15   2014      1200   \n",
       "464649      464650       72      11  12152014      12    15   2014      1100   \n",
       "464650      464651       72      11  12102014      12    10   2014      1100   \n",
       "464651      464652       72      11  12152014      12    15   2014      1100   \n",
       "464652      464653       72      11  12112014      12    11   2014      1100   \n",
       "464653      464654       72      11  12162014      12    16   2014      1100   \n",
       "464654      464655       72      11  12152014      12    15   2014      1100   \n",
       "464655      464656       72      11  12122014      12    12   2014      1100   \n",
       "464656      464657       72      11  12162014      12    16   2014      1100   \n",
       "464657      464658       72      11  12102014      12    10   2014      1100   \n",
       "464658      464659       72      11  12102014      12    10   2014      1100   \n",
       "464659      464660       72      11  12112014      12    11   2014      1100   \n",
       "464660      464661       72      11  12102014      12    10   2014      1100   \n",
       "464661      464662       72      11  12152014      12    15   2014      1100   \n",
       "464662      464663       72      11  12132014      12    13   2014      1100   \n",
       "464663      464664       72      11  12102014      12    10   2014      1100   \n",
       "\n",
       "             seqno       x.psu     ...      x.fobtfs  x.crcrec  x.aidtst3  \\\n",
       "0       2014000001  2014000001     ...             2         1          2   \n",
       "1       2014000002  2014000002     ...             2         2          2   \n",
       "2       2014000003  2014000003     ...             2         2          2   \n",
       "3       2014000004  2014000004     ...             2         1          2   \n",
       "4       2014000005  2014000005     ...             2         1          2   \n",
       "5       2014000006  2014000006     ...           NaN       NaN          2   \n",
       "6       2014000007  2014000007     ...             2         1          1   \n",
       "7       2014000008  2014000008     ...             2         2          1   \n",
       "8       2014000009  2014000009     ...             2         2          2   \n",
       "9       2014000010  2014000010     ...           NaN       NaN          2   \n",
       "10      2014000011  2014000011     ...           NaN       NaN          2   \n",
       "11      2014000012  2014000012     ...             2         1          2   \n",
       "12      2014000013  2014000013     ...           NaN       NaN          2   \n",
       "13      2014000014  2014000014     ...             2         1          2   \n",
       "14      2014000015  2014000015     ...             2         1          2   \n",
       "15      2014000016  2014000016     ...           NaN       NaN          2   \n",
       "16      2014000017  2014000017     ...           NaN         1          2   \n",
       "17      2014000018  2014000018     ...             2       NaN          2   \n",
       "18      2014000019  2014000019     ...             2         2          2   \n",
       "19      2014000020  2014000020     ...           NaN         1          2   \n",
       "20      2014000021  2014000021     ...           NaN       NaN        NaN   \n",
       "21      2014000022  2014000022     ...             2         2          2   \n",
       "22      2014000023  2014000023     ...           NaN       NaN          1   \n",
       "23      2014000024  2014000024     ...             2         1          1   \n",
       "24      2014000025  2014000025     ...           NaN       NaN          1   \n",
       "25      2014000026  2014000026     ...           NaN       NaN          2   \n",
       "26      2014000027  2014000027     ...             2         1          2   \n",
       "27      2014000028  2014000028     ...             2         1          2   \n",
       "28      2014000029  2014000029     ...           NaN       NaN          2   \n",
       "29      2014000030  2014000030     ...             2         1          9   \n",
       "...            ...         ...     ...           ...       ...        ...   \n",
       "464634  2014005959  2014005959     ...             2         2          1   \n",
       "464635  2014005960  2014005960     ...           NaN       NaN          1   \n",
       "464636  2014005961  2014005961     ...             2         2          2   \n",
       "464637  2014005962  2014005962     ...           NaN       NaN          1   \n",
       "464638  2014005963  2014005963     ...             2         1          2   \n",
       "464639  2014005964  2014005964     ...           NaN       NaN          1   \n",
       "464640  2014005965  2014005965     ...           NaN       NaN          2   \n",
       "464641  2014005966  2014005966     ...             2         1          1   \n",
       "464642  2014005967  2014005967     ...           NaN       NaN          2   \n",
       "464643  2014005968  2014005968     ...           NaN       NaN        NaN   \n",
       "464644  2014005969  2014005969     ...             2         2          1   \n",
       "464645  2014005970  2014005970     ...           NaN       NaN          2   \n",
       "464646  2014005971  2014005971     ...           NaN       NaN          2   \n",
       "464647  2014005972  2014005972     ...           NaN       NaN          2   \n",
       "464648  2014005973  2014005973     ...           NaN       NaN        NaN   \n",
       "464649  2014005974  2014005974     ...           NaN       NaN          1   \n",
       "464650  2014005975  2014005975     ...             2         2          2   \n",
       "464651  2014005976  2014005976     ...           NaN       NaN          1   \n",
       "464652  2014005977  2014005977     ...           NaN       NaN          2   \n",
       "464653  2014005978  2014005978     ...             2         1          1   \n",
       "464654  2014005979  2014005979     ...             2         2          2   \n",
       "464655  2014005980  2014005980     ...             2         2          2   \n",
       "464656  2014005981  2014005981     ...             2         1          2   \n",
       "464657  2014005982  2014005982     ...           NaN       NaN          2   \n",
       "464658  2014005983  2014005983     ...             2         1          2   \n",
       "464659  2014005984  2014005984     ...           NaN       NaN          2   \n",
       "464660  2014005985  2014005985     ...           NaN       NaN          2   \n",
       "464661  2014005986  2014005986     ...             2         2          1   \n",
       "464662  2014005987  2014005987     ...           NaN       NaN          1   \n",
       "464663  2014005988  2014005988     ...           NaN       NaN          1   \n",
       "\n",
       "        x.impeduc  x.impmrtl  x.imphome  rcsbrac1  rcsrace1  rchisla1  \\\n",
       "0               5          1          1       NaN       NaN       NaN   \n",
       "1               4          1          1       NaN       NaN       NaN   \n",
       "2               6          1          1       NaN       NaN       NaN   \n",
       "3               6          3          1       NaN       NaN       NaN   \n",
       "4               5          1          1       NaN       NaN       NaN   \n",
       "5               6          1          1       NaN       NaN       NaN   \n",
       "6               6          1          1       NaN       NaN       NaN   \n",
       "7               4          2          1       NaN       NaN       NaN   \n",
       "8               3          1          1       NaN       NaN       NaN   \n",
       "9               5          1          1       NaN       NaN       NaN   \n",
       "10              4          1          1       NaN       NaN       NaN   \n",
       "11              5          1          1       NaN       NaN       NaN   \n",
       "12              6          1          1       NaN       NaN       NaN   \n",
       "13              6          2          3       NaN       NaN       NaN   \n",
       "14              5          3          1       NaN       NaN       NaN   \n",
       "15              5          3          1       NaN       NaN       NaN   \n",
       "16              5          1          1       NaN       NaN       NaN   \n",
       "17              5          3          2       NaN       NaN       NaN   \n",
       "18              5          1          1       NaN       NaN       NaN   \n",
       "19              3          4          2       NaN       NaN       NaN   \n",
       "20              2          2          1       NaN       NaN       NaN   \n",
       "21              6          4          1       NaN       NaN       NaN   \n",
       "22              5          2          1       NaN       NaN       NaN   \n",
       "23              4          1          1       NaN       NaN       NaN   \n",
       "24              5          2          1       NaN       NaN       NaN   \n",
       "25              2          1          1       NaN       NaN       NaN   \n",
       "26              5          1          1       NaN       NaN       NaN   \n",
       "27              5          1          1       NaN       NaN       NaN   \n",
       "28              4          3          2       NaN       NaN       NaN   \n",
       "29              6          1          1       NaN       NaN       NaN   \n",
       "...           ...        ...        ...       ...       ...       ...   \n",
       "464634          6          2          1       NaN       NaN       NaN   \n",
       "464635          5          5          1       NaN       NaN       NaN   \n",
       "464636          2          1          1       NaN       NaN       NaN   \n",
       "464637          5          1          2       NaN       NaN       NaN   \n",
       "464638          6          2          1       NaN       NaN       NaN   \n",
       "464639          5          1          1       NaN       NaN       NaN   \n",
       "464640          5          5          1       NaN       NaN       NaN   \n",
       "464641          4          1          1       NaN       NaN       NaN   \n",
       "464642          2          1          1       NaN       NaN       NaN   \n",
       "464643          4          1          2       NaN       NaN       NaN   \n",
       "464644          4          1          1       NaN       NaN       NaN   \n",
       "464645          4          2          3       NaN       NaN       NaN   \n",
       "464646          5          5          2       NaN       NaN       NaN   \n",
       "464647          2          3          2       NaN       NaN       NaN   \n",
       "464648          5          5          1       NaN       NaN       NaN   \n",
       "464649          6          1          1       NaN       NaN       NaN   \n",
       "464650          5          1          1       NaN       NaN       NaN   \n",
       "464651          5          1          1       NaN       NaN       NaN   \n",
       "464652          5          5          3       NaN       NaN       NaN   \n",
       "464653          6          1          1       NaN       NaN       NaN   \n",
       "464654          5          3          1       NaN       NaN       NaN   \n",
       "464655          4          1          1       NaN       NaN       NaN   \n",
       "464656          6          1          1       NaN       NaN       NaN   \n",
       "464657          4          2          2       NaN       NaN       NaN   \n",
       "464658          5          1          1       NaN       NaN       NaN   \n",
       "464659          4          1          1       NaN       NaN       NaN   \n",
       "464660          6          2          1       NaN       NaN       NaN   \n",
       "464661          5          1          2       NaN       NaN       NaN   \n",
       "464662          4          1          1       NaN       NaN       NaN   \n",
       "464663          6          6          1       NaN       NaN       NaN   \n",
       "\n",
       "        rcsbirth  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "5            NaN  \n",
       "6            NaN  \n",
       "7            NaN  \n",
       "8            NaN  \n",
       "9            NaN  \n",
       "10           NaN  \n",
       "11           NaN  \n",
       "12           NaN  \n",
       "13           NaN  \n",
       "14           NaN  \n",
       "15           NaN  \n",
       "16           NaN  \n",
       "17           NaN  \n",
       "18           NaN  \n",
       "19           NaN  \n",
       "20           NaN  \n",
       "21           NaN  \n",
       "22           NaN  \n",
       "23           NaN  \n",
       "24           NaN  \n",
       "25           NaN  \n",
       "26           NaN  \n",
       "27           NaN  \n",
       "28           NaN  \n",
       "29           NaN  \n",
       "...          ...  \n",
       "464634       NaN  \n",
       "464635       NaN  \n",
       "464636       NaN  \n",
       "464637       NaN  \n",
       "464638       NaN  \n",
       "464639       NaN  \n",
       "464640       NaN  \n",
       "464641       NaN  \n",
       "464642       NaN  \n",
       "464643       NaN  \n",
       "464644       NaN  \n",
       "464645       NaN  \n",
       "464646       NaN  \n",
       "464647       NaN  \n",
       "464648       NaN  \n",
       "464649       NaN  \n",
       "464650       NaN  \n",
       "464651       NaN  \n",
       "464652       NaN  \n",
       "464653       NaN  \n",
       "464654       NaN  \n",
       "464655       NaN  \n",
       "464656       NaN  \n",
       "464657       NaN  \n",
       "464658       NaN  \n",
       "464659       NaN  \n",
       "464660       NaN  \n",
       "464661       NaN  \n",
       "464662       NaN  \n",
       "464663       NaN  \n",
       "\n",
       "[464664 rows x 280 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 464664\n",
      "Number of columns (variables in the survey): 280\n",
      "Index(['Unnamed: 0', 'x.state', 'fmonth', 'idate', 'imonth', 'iday', 'iyear', 'dispcode', 'seqno', 'x.psu', 'ctelenum', 'pvtresd1', 'colghous', 'stateres', 'ladult', 'numadult', 'nummen', 'numwomen', 'genhlth', 'physhlth', 'menthlth', 'poorhlth', 'hlthpln1', 'persdoc2', 'medcost', 'checkup1', 'exerany2', 'sleptim1', 'cvdinfr4', 'cvdcrhd4', 'cvdstrk3', 'asthma3', 'asthnow', 'chcscncr', 'chcocncr', 'chccopd1', 'havarth3', 'addepev2', 'chckidny', 'diabete3', 'diabage2', 'lastden3', 'rmvteth3', 'veteran3', 'marital', 'children', 'educa', 'employ1', 'income2', 'weight2', 'height3', 'numhhol2', 'numphon2', 'cpdemo1', 'internet', 'renthom1', 'sex', 'pregnant', 'qlactlm2', 'useequip', 'blind', 'decide', 'diffwalk', 'diffdres', 'diffalon', 'smoke100', 'smokday2', 'stopsmk2', 'lastsmk2', 'usenow3', 'alcday5', 'avedrnk2', 'drnk3ge5', 'maxdrnks', 'flushot6', 'flshtmy2', 'pneuvac3', 'shingle2', 'fall12mn', 'fallinj2', 'seatbelt', 'drnkdri2', 'hadmam', 'howlong', 'profexam', 'lengexam', 'hadpap2', 'lastpap2', 'hadhyst2', 'pcpsaad2', 'pcpsadi1', 'pcpsare1', 'psatest1', 'psatime', 'pcpsars1', 'bldstool', 'lstblds3', 'hadsigm3', 'hadsgco1', 'lastsig3', ...], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of rows:\", len(df))\n",
    "print(\"Number of columns (variables in the survey):\", len(df.columns))\n",
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Collecting our variables of interest\n",
    "\n",
    "Great, so we have a lot of rows and 280 columns. We're just trying to find some basic demographic information. If we look through the codebook, we can find those variable names. Note that an underscore in the codebook is the same as `x.` in the data, and that variables are all caps in the codebook but lowercase in the data.\n",
    "\n",
    "- **Income:** income2\n",
    "- **Race:** x.race\n",
    "- **State:** x.state\n",
    "- **Age:** x.ageg5yr\n",
    "- **Sex:** sex\n",
    "- **Height:** height3\n",
    "- **Weight:** weight2\n",
    "\n",
    "We'll use height and weight to calculate BMI. In later years (like 2014), the good people at the CDC calculate that and include it as a separate variable, but in earlier years, they weren't so advanced, so we'll always just grab height and weight and calculate it ourselves.\n",
    "\n",
    "We can use Pandas to easily grab the columns we want. We're going to grab those columns as Pandas Series, clean them up, and then piece everything back together as a master DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "income = df['income2']\n",
    "race = df['x.race']\n",
    "state = df['x.state']\n",
    "age = df['x.ageg5yr']\n",
    "sex = df['sex']\n",
    "height = df['height3']\n",
    "weight = df['weight2']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we look at one of these variables, we can get a peek at the way the survey data is coded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      7\n",
       "1      4\n",
       "2      7\n",
       "3      5\n",
       "4      4\n",
       "5      6\n",
       "6      8\n",
       "7      1\n",
       "8     77\n",
       "9      7\n",
       "10    77\n",
       "11     6\n",
       "12     8\n",
       "13     5\n",
       "14     5\n",
       "...\n",
       "464649     8\n",
       "464650     3\n",
       "464651     1\n",
       "464652     6\n",
       "464653     5\n",
       "464654     1\n",
       "464655     2\n",
       "464656    99\n",
       "464657     2\n",
       "464658     5\n",
       "464659     6\n",
       "464660     7\n",
       "464661    99\n",
       "464662     2\n",
       "464663     4\n",
       "Name: income2, Length: 464664, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Cleaning up a column: Income\n",
    "\n",
    "We'll start with cleaning up the income data. Looking at the codebook, the codes are:\n",
    "\n",
    "* 1: <\\$10k\n",
    "* 2: \\$10k - \\$15k\n",
    "* 3: \\$15k - \\$20k\n",
    "* 4: \\$20k - \\$25k\n",
    "* 5: \\$25k - \\$35k\n",
    "* 6: \\$35k - \\$50k\n",
    "* 7: \\$50k - \\$75k\n",
    "* 8: \\>\\$75k\n",
    "* 77: Unknown\n",
    "* 99: Refused\n",
    "\n",
    "We're going to group 77 and 99 together as `np.nan`, and we're going to group 7 and 8 together as simply \\>\\$50k (in earlier BRFSS years, it stops at />\\$50K, rather than including the \\$50k-\\$75k and \\>\\$75k groups)\n",
    "\n",
    "Pandas makes it easy to use a dictionary for replacement, so we'll put these codes into a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "income_replace = {1:'<10k', 2:'10k-15k', 3:'15k-20k', 4:'20k-25k', 5:'25k-35k', 6:'35k-50k', 7:'>50k', 8:'>50k', 77:np.nan, 99:np.nan}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Cleaning the other columns\n",
    "\n",
    "Next, we'll clean the race column. If you are working with historical data, this is the trickiest column. The name of the variable, the meanings of the codes, and the groups represented change frequently over the years, and sometimes in subtle ways. Being able to use historical data is why my dictionary ends up grouping some things together that the 2014 dataset breaks out. See the codebook for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "race_replace = {1:'white', 2:'black', 3:'native american', 4:'asian/pacific islander', 5:'asian/pacific islander', 6:'other/multiracial', 7:'other/multiracial', 8:'hispanic', 9:'refused/unknown'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll skip the state column for now. It's represented as a FIPS statecode, and you can find more info on Wikipedia: [FIPS codes](https://en.wikipedia.org/wiki/Federal_Information_Processing_Standard_state_code).\n",
    "\n",
    "Let's get the age and sex columns mapped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "age_replace = {1:'18-24', 2:'25-29', 3:'30-34', 4:'35-39', 5:'40-44', 6:'45-49', 7:'50-54', 8:'55-59', 9:'60-64', 10:'65-69', 11:'70-74', 12:'75-79', 13:'80+', 14:np.nan}\n",
    "sex_replace = {1:'male', 2:'female'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The height and weight columns are a little trickier to deal with, so we'll handle those in a minute.\n",
    "\n",
    "##Using Pandas to do the replacement\n",
    "Since we've got our dictionaries of codes mapped to values, we can let Pandas do the heavy lifting of cleaning up our columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "income = income.replace(income_replace)\n",
    "race = race.replace(race_replace)\n",
    "age = age.replace(age_replace)\n",
    "sex = sex.replace(sex_replace)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check out one of our cleaned up columns. Now it is beautiful and easy-to-use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        >50k\n",
       "1     20k-25k\n",
       "2        >50k\n",
       "3     25k-35k\n",
       "4     20k-25k\n",
       "5     35k-50k\n",
       "6        >50k\n",
       "7        <10k\n",
       "8         NaN\n",
       "9        >50k\n",
       "10        NaN\n",
       "11    35k-50k\n",
       "12       >50k\n",
       "13    25k-35k\n",
       "14    25k-35k\n",
       "...\n",
       "464649       >50k\n",
       "464650    15k-20k\n",
       "464651       <10k\n",
       "464652    35k-50k\n",
       "464653    25k-35k\n",
       "464654       <10k\n",
       "464655    10k-15k\n",
       "464656        NaN\n",
       "464657    10k-15k\n",
       "464658    25k-35k\n",
       "464659    35k-50k\n",
       "464660       >50k\n",
       "464661        NaN\n",
       "464662    10k-15k\n",
       "464663    20k-25k\n",
       "Name: income2, Length: 464664, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "income"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Cleaning the height and weight columns\n",
    "\n",
    "Okay, now let's work with the height and weight data. We'll start by noting that 7777 and 9999 are 'unknown' or 'replaced' codes, so we'll NaN those right away. Also, in later years, the survey gave the option of taking height and weight in metric and imperial units. Earlier on, it's all imperial, so we'll make and option for using the metric system (which is denoted differently in the codes). Finally, our max weight changes over the years also, so we'll set that now to 999 lbs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "hw_replace = {7777:np.nan, 9999:np.nan}\n",
    "metric = True\n",
    "max_weight = 999\n",
    "height = height.replace(hw_replace)\n",
    "weight = weight.replace(hw_replace)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###Dealing with the height codes\n",
    "\n",
    "Alright, now we can tackle processing the height column. In the codebook, we see that things are a little funny.\n",
    "\n",
    "* 200-711: Height in ft/in (\\_|\\_ \\_) - i.e. the first digit is feet, the second and third digits are inches. So 509 is 5' 9''\n",
    "* 9000-9998: Height in m/cm (9\\_|\\_ \\_)\n",
    "\n",
    "To handle this, we are going to run through the height column, grab the value, and turn it into a string. We'll pop the first token and save it as the feet, save the other two tokens as the inches, and then we will cast everything as floats and convert the result to inches and then to meters.\n",
    "\n",
    "If the number is greather than 9000 and our metric toggle is `True`, then we know that it is a metric value, and we'll do a similar process.\n",
    "\n",
    "_**Note:** I noticed that if I do all of this in Pandas, it is very slow. It's much faster to put the Series into a python list, do the operations and append the result to a new list, and then save the result back to a Series._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "height = height.tolist()\n",
    "new_height = []\n",
    "for row in height:\n",
    "    h = str(row)\n",
    "\n",
    "    if row < 1: # If the height is 0, it isn't real\n",
    "        meters = np.nan\n",
    "\n",
    "    elif row < 712:\n",
    "        feet = float(h[0])\n",
    "        inches = float(h[1:])\n",
    "        if inches > 12: # Catches an error by the surveyor: no one is 5' 13\" tall\n",
    "            meters = np.nan\n",
    "        else:\n",
    "            inches = inches + feet*12\n",
    "            meters = inches * 0.0254\n",
    "\n",
    "    elif row < 9999 and row >= 9000 and metric:\n",
    "        meters = float(h[1])+float(h[2:])*0.01\n",
    "        if meters == 0: # If the height is 0, it isn't real\n",
    "            meters = np.nan\n",
    "\n",
    "    else: # If you've gotten this far, give up. You're NaN\n",
    "        meters = np.nan\n",
    "\n",
    "    new_height.append(meters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's do something similar for the weight. Except this time, we don't have to play fun games with strings for the imperial units (we still do with the metric units)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weight = weight.tolist()\n",
    "new_weight = []\n",
    "for row in weight:\n",
    "    \n",
    "    if row < 10: # No person ages 18-99 should weigh 10 lbs.\n",
    "        kg = np.nan\n",
    "\n",
    "    elif row < max_weight:\n",
    "        kg = row * 0.453592\n",
    "\n",
    "    elif row < 9999 and metric:\n",
    "        w = str(row)\n",
    "        kg = float(w[1:])\n",
    "        if kg < 10: # Again, no person 18-99 should weigh 10 kgs. And yes, I know that 10 lbs != 10 kgs, but whatever\n",
    "            kg = np.nan\n",
    "\n",
    "    else:\n",
    "        kg = np.nan\n",
    "\n",
    "    new_weight.append(kg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###Calculating BMI\n",
    "\n",
    "We've got height and weight in metric units. Let's compute the BMI, which is \n",
    "\n",
    "$bmi=\\frac{w}{h^2}$\n",
    "\n",
    "BMIs that are less than 10 or greater than 200 are possible, but highly unlikely. At the moment, I'm not terribly interested in these outliers (which are honestly more likely the result of error than true BMIs anyway). We will exclude them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bmi = []\n",
    "for h, w in zip(new_height, new_weight):\n",
    "    b = w/(h*h)\n",
    "    if b < 10 or b > 200:\n",
    "        b = np.nan\n",
    "    bmi.append(b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll quickly save the height, weight, and BMI back to Pandas Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "height = pd.Series(new_height)\n",
    "weight = pd.Series(new_weight)\n",
    "bmi = pd.Series(bmi)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Saving our clean data\n",
    "\n",
    "We're almost there! Now, let's take all of our cleaned up columns and put them back together as a single DataFrame and name the columns something easy to understand. We'll save that out to a `.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "brfss_out = pd.concat([income, race, state, age, sex, height, weight, bmi], axis=1)\n",
    "brfss_out.columns = ['income', 'race', 'state', 'age', 'sex', 'height', 'weight', 'bmi']\n",
    "brfss_out.to_csv('brfss' + year + 'clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now, at long last, we are finished. We have a cleaned up set of demographic data in `brfss2014clean.csv`. It is in the same format as what we can spit out for all of the other years of the BRFSS 1987-2014, so it will be easy to run analyses across all the years."
   ]
  }
 ],
 "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.4.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}