data_fixing_workspace.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data-Fixing with Zillow Dataset\n",
    "Useful Links:\n",
    "- [Dataframe merging](https://pandas.pydata.org/pandas-docs/stable/merging.html)\n",
    "- [Pipe-separated values to df](https://stackoverflow.com/questions/20949955/changing-pipe-separated-data-to-dataframe-in-python-pandas)\n",
    "- [Storing and reading pickles for storage of \"good\" Dataframes](https://stackoverflow.com/questions/17098654/how-to-store-a-dataframe-using-pandas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import operator\n",
    "import sys\n",
    "import os\n",
    "from utils import zillow_helpers\n",
    "sys.path.insert(0,'../')\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set some data folders here. Be sure to \"ignore\" your actual data folders when pushing to Github."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "datadir1 = 'data/46/ZAsmt/'\n",
    "datadir2 = 'data/46/ZTrans/'\n",
    "datadir3 = 'data/50/ZAsmt/'\n",
    "datadir4 = 'data/50/ZTrans/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List the text files in the folder. This could be helpful for making iterables later!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['AdditionalPropertyAddress.txt',\n",
       " 'BKManagedSpecific.txt',\n",
       " 'Building.txt',\n",
       " 'BuildingAreas.txt',\n",
       " 'CareOfName.txt',\n",
       " 'ExteriorWall.txt',\n",
       " 'ExtraFeature.txt',\n",
       " 'Garage.txt',\n",
       " 'InteriorFlooring.txt',\n",
       " 'InteriorWall.txt',\n",
       " 'LotSiteAppeal.txt',\n",
       " 'MailAddress.txt',\n",
       " 'Main.txt',\n",
       " 'Name.txt',\n",
       " 'Oby.txt',\n",
       " 'Pool.txt',\n",
       " 'SaleData.txt',\n",
       " 'TaxDistrict.txt',\n",
       " 'TaxExemption.txt',\n",
       " 'TypeConstruction.txt',\n",
       " 'Value.txt',\n",
       " 'VestingCodes.txt']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir(datadir1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Known syntax for opening and \"reading\" lines from text files contained below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "f = open(datadir1+'SaleData.txt', 'r')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A30D272B-3F28-E611-80C4-3863BB43AC67|1|||2011-06-10|||90|398|WD|WRDE|| |46107|801464\n",
      "\n",
      "A90D272B-3F28-E611-80C4-3863BB43AC67|1|||1998-08-24|||||||| |46107|801464\n",
      "\n",
      "AD0D272B-3F28-E611-80C4-3863BB43AC67|1|||2010-07-14|||90|14|WD|WRDE|58000.0000|AF|46107|801464\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Only read a few lines at a time with these files!!!\n",
    "for lines in range(3):\n",
    "    line = f.readline()\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's good to note that creation of a dataframe from these massive file-sets does not seem to require a lot of processing power. Attempting to read and print the whole dataset probably will crash your kernel! Be sure to read a few lines of the head each time to check!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A30D272B-3F28-E611-80C4-3863BB43AC67</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011-06-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>398</td>\n",
       "      <td>WD</td>\n",
       "      <td>WRDE</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>46107</td>\n",
       "      <td>801464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A90D272B-3F28-E611-80C4-3863BB43AC67</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1998-08-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>46107</td>\n",
       "      <td>801464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AD0D272B-3F28-E611-80C4-3863BB43AC67</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-07-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>14</td>\n",
       "      <td>WD</td>\n",
       "      <td>WRDE</td>\n",
       "      <td>58000.0</td>\n",
       "      <td>AF</td>\n",
       "      <td>46107</td>\n",
       "      <td>801464</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     0   1   2   3           4   5    6    7   \\\n",
       "0  A30D272B-3F28-E611-80C4-3863BB43AC67   1 NaN NaN  2011-06-10 NaN  NaN   90   \n",
       "1  A90D272B-3F28-E611-80C4-3863BB43AC67   1 NaN NaN  1998-08-24 NaN  NaN  NaN   \n",
       "2  AD0D272B-3F28-E611-80C4-3863BB43AC67   1 NaN NaN  2010-07-14 NaN  NaN   90   \n",
       "\n",
       "    8    9     10       11  12     13      14  \n",
       "0  398   WD  WRDE      NaN      46107  801464  \n",
       "1  NaN  NaN   NaN      NaN      46107  801464  \n",
       "2   14   WD  WRDE  58000.0  AF  46107  801464  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(datadir1+'SaleData.txt', sep=\"|\", index_col=False, header=None, low_memory=False)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>85</th>\n",
       "      <th>86</th>\n",
       "      <th>87</th>\n",
       "      <th>88</th>\n",
       "      <th>89</th>\n",
       "      <th>90</th>\n",
       "      <th>91</th>\n",
       "      <th>92</th>\n",
       "      <th>93</th>\n",
       "      <th>94</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01D1B108-F8A6-E611-80C9-3863BB43AC67</td>\n",
       "      <td>119614254</td>\n",
       "      <td>46137</td>\n",
       "      <td>SD</td>\n",
       "      <td>ZIEBACH</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>72016</td>\n",
       "      <td>4</td>\n",
       "      <td>BKF</td>\n",
       "      <td>002643</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1564496</td>\n",
       "      <td>2624</td>\n",
       "      <td>562086187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02D1B108-F8A6-E611-80C9-3863BB43AC67</td>\n",
       "      <td>119613826</td>\n",
       "      <td>46137</td>\n",
       "      <td>SD</td>\n",
       "      <td>ZIEBACH</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>72016</td>\n",
       "      <td>4</td>\n",
       "      <td>BKF</td>\n",
       "      <td>002213</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1564496</td>\n",
       "      <td>2196</td>\n",
       "      <td>-1621235809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03D1B108-F8A6-E611-80C9-3863BB43AC67</td>\n",
       "      <td>119615712</td>\n",
       "      <td>46137</td>\n",
       "      <td>SD</td>\n",
       "      <td>ZIEBACH</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>72016</td>\n",
       "      <td>4</td>\n",
       "      <td>BKF</td>\n",
       "      <td>004104</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1564496</td>\n",
       "      <td>4081</td>\n",
       "      <td>1892456234</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 95 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     0          1      2   3        4   \\\n",
       "0  01D1B108-F8A6-E611-80C9-3863BB43AC67  119614254  46137  SD  ZIEBACH   \n",
       "1  02D1B108-F8A6-E611-80C9-3863BB43AC67  119613826  46137  SD  ZIEBACH   \n",
       "2  03D1B108-F8A6-E611-80C9-3863BB43AC67  119615712  46137  SD  ZIEBACH   \n",
       "\n",
       "           5      6   7    8       9      ...      85  86  87  88  89  90  91  \\\n",
       "0  2016-11-01  72016   4  BKF  002643     ...     NaN NaN NaN NaN NaN NaN   0   \n",
       "1  2016-11-01  72016   4  BKF  002213     ...     NaN NaN NaN NaN NaN NaN   0   \n",
       "2  2016-11-01  72016   4  BKF  004104     ...     NaN NaN NaN NaN NaN NaN   0   \n",
       "\n",
       "        92    93          94  \n",
       "0  1564496  2624   562086187  \n",
       "1  1564496  2196 -1621235809  \n",
       "2  1564496  4081  1892456234  \n",
       "\n",
       "[3 rows x 95 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = zillow_helpers.txt_to_df(datadir1+'Main.txt')\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If your data frame is good, then pickle it! It will save your dataframe and prevent the need to re-create it later.\n",
    "![Pickle Rick](http://pm1.narvii.com/6511/c7ba0df4a630d1c05fad94fec2cac061bc28d69a_128.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.to_pickle(\"df.pickle\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df3 = pd.read_pickle(\"df.pickle\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A30D272B-3F28-E611-80C4-3863BB43AC67</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011-06-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>398</td>\n",
       "      <td>WD</td>\n",
       "      <td>WRDE</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>46107</td>\n",
       "      <td>801464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A90D272B-3F28-E611-80C4-3863BB43AC67</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1998-08-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>46107</td>\n",
       "      <td>801464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AD0D272B-3F28-E611-80C4-3863BB43AC67</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-07-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>14</td>\n",
       "      <td>WD</td>\n",
       "      <td>WRDE</td>\n",
       "      <td>58000.0</td>\n",
       "      <td>AF</td>\n",
       "      <td>46107</td>\n",
       "      <td>801464</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     0   1   2   3           4   5    6    7   \\\n",
       "0  A30D272B-3F28-E611-80C4-3863BB43AC67   1 NaN NaN  2011-06-10 NaN  NaN   90   \n",
       "1  A90D272B-3F28-E611-80C4-3863BB43AC67   1 NaN NaN  1998-08-24 NaN  NaN  NaN   \n",
       "2  AD0D272B-3F28-E611-80C4-3863BB43AC67   1 NaN NaN  2010-07-14 NaN  NaN   90   \n",
       "\n",
       "    8    9     10       11  12     13      14  \n",
       "0  398   WD  WRDE      NaN      46107  801464  \n",
       "1  NaN  NaN   NaN      NaN      46107  801464  \n",
       "2   14   WD  WRDE  58000.0  AF  46107  801464  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Organizing via Syntax\n",
    "Time to start looking at organizing/merging files! It seems that the best way to organize is by separating dataframes into ZAsmt and ZTrans first."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "First, lets use our column helper files in order to create arrays to be used to name columns later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "chelpMain = 'column_helper/main.csv'\n",
    "chelpPI = 'column_helper/propertyinfo.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chelpMainOpen = open(chelpMain, 'r')\n",
    "chelpPIOpen = open(chelpPI, 'r')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TransId\n",
      "\n",
      "FIPS\n",
      "\n",
      "State\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for lines in range(3):\n",
    "    line = chelpMainOpen.readline()\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TransId\n",
      "\n",
      "AssessorParcelNumber\n",
      "\n",
      "APNIndicatorStndCode\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for lines in range(3):\n",
    "    line = chelpPIOpen.readline()\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df4 = zillow_helpers.txt_to_df(datadir2+'Main.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>121</th>\n",
       "      <th>122</th>\n",
       "      <th>123</th>\n",
       "      <th>124</th>\n",
       "      <th>125</th>\n",
       "      <th>126</th>\n",
       "      <th>127</th>\n",
       "      <th>128</th>\n",
       "      <th>129</th>\n",
       "      <th>130</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>315852373</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0001</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791749</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>1</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.651814e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>315852374</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0002</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791750</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>2</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.531757e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>315852375</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0003</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791751</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>3</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-6.570774e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 131 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         0      1   2          3   4   5           6    7   8     9    \\\n",
       "0  315852373  46009  SD  BON HOMME   D   P  2009-01-06  NaN  96  0001   \n",
       "1  315852374  46009  SD  BON HOMME   D   P  2009-01-07  NaN  96  0002   \n",
       "2  315852375  46009  SD  BON HOMME   D   P  2009-01-09  NaN  96  0003   \n",
       "\n",
       "       ...      121  122  123         124  125  126      127 128  129  \\\n",
       "0      ...       NM  NaN       2411791749  NaN       1702496   1  BKF   \n",
       "1      ...       NM  NaN       2411791750  NaN       1702496   2  BKF   \n",
       "2      ...       NM  NaN       2411791751  NaN       1702496   3  BKF   \n",
       "\n",
       "            130  \n",
       "0  1.651814e+09  \n",
       "1  1.531757e+08  \n",
       "2 -6.570774e+08  \n",
       "\n",
       "[3 rows x 131 columns]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chelpMain = 'column_helper/main.csv'\n",
    "chelpPI = 'column_helper/propertyinfo.csv'\n",
    "\n",
    "chelpMainOpen = open(chelpMain, 'r')\n",
    "chelpPIOpen = open(chelpPI, 'r')\n",
    "\n",
    "chelpMainArray = []\n",
    "for line in chelpMainOpen:\n",
    "    chelpMainArray.append(line.rstrip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['TransId', 'FIPS', 'State', 'County', 'DataClassStndCode', 'RecordTypeStndCode', 'RecordingDate', 'RecordingDocumentNumber', 'RecordingBookNumber', 'RecordingPageNumber', 'ReRecordedCorrectionStndCode', 'PriorRecordingDate', 'PriorDocumentDate', 'PriorDocumentNumber', 'PriorBookNumber', 'PriorPageNumber', 'DocumentTypeStndCode', 'DocumentDate', 'SignatureDate', 'EffectiveDate', 'BuyerVestingStndCode', 'BuyerMultiVestingFlag', 'PartialInterestTransferStndCode', 'PartialInterestTransferPercent', 'SalesPriceAmount', 'SalesPriceAmountStndCode', 'CityTransferTax', 'CountyTransferTax', 'StateTransferTax', 'TotalTransferTax', 'IntraFamilyTransferFlag', 'TransferTaxExemptFlag', 'PropertyUseStndCode', 'AssessmentLandUseStndCode', 'OccupancyStatusStndCode', 'LegalStndCode', 'BorrowerVestingStndCode', 'LenderName', 'LenderTypeStndCode', 'LenderIDStndCode', 'LenderDBAName', 'DBALenderTypeStndCode', 'DBALenderIDStndCode', 'LenderMailCareOfName', 'LenderMailHouseNumber', 'LenderMailHouseNumberExt', 'LenderMailStreetPreDirectional', 'LenderMailStreetName', 'LenderMailStreetSuffix', 'LenderMailStreetPostDirectional', 'LenderMailFullStreetAddress', 'LenderMailBuildingName', 'LenderMailBuildingNumber', 'LenderMailUnitDesignator', 'LenderMailUnit', 'LenderMailCity', 'LenderMailState', 'LenderMailZip', 'LenderMailZip4', 'LoanAmount', 'LoanAmountStndCode', 'MaximumLoanAmount', 'LoanTypeStndCode', 'LoanTypeClosedOpenEndStndCode', 'LoanTypeFutureAdvanceFlag', 'LoanTypeProgramStndCode', 'LoanRateTypeStndCode', 'LoanDueDate', 'LoanTermMonths', 'LoanTermYears', 'InitialInterestRate', 'ARMFirstAdjustmentDate', 'ARMFirstAdjustmentMaxRate', 'ARMFirstAdjustmentMinRate', 'ARMIndexStndCode', 'ARMAdjustmentFrequencyStndCode', 'ARMMargin', 'ARMInitialCap', 'ARMPeriodicCap', 'ARMLifetimeCap', 'ARMMaxInterestRate', 'ARMMinInterestRate', 'InterestOnlyFlag', 'InterestOnlyTerm', 'PrepaymentPenaltyFlag', 'PrepaymentPenaltyTerm', 'BiWeeklyPaymentFlag', 'AssumabilityRiderFlag', 'BalloonRiderFlag', 'CondominiumRiderFlag', 'PlannedUnitDevelopmentRiderFlag', 'SecondHomeRiderFlag', 'OneToFourFamilyRiderFlag', 'ConcurrentMtgeDocOrBkPg', 'LoanNumber', 'MERSMINNumber', 'CaseNumber', 'MERSFlag', 'TitleCompanyName', 'TitleCompanyIDStndCode', 'AccommodationRecordingFlag', 'UnpaidBalance', 'InstallmentAmount', 'InstallmentDueDate', 'TotalDelinquentAmount', 'DelinquentAsOfDate', 'CurrentLender', 'CurrentLenderTypeStndCode', 'CurrentLenderIDStndCode', 'TrusteeSaleNumber', 'AttorneyFileNumber', 'AuctionDate', 'AuctionTime', 'AuctionFullStreetAddress', 'AuctionCityName', 'StartingBid', 'KeyedDate', 'KeyerID', 'SubVendorStndCode', 'ImageFileName', 'BuilderFlag', 'MatchStndCode', 'REOStndCode', 'UpdateOwnershipFlag', 'LoadID', 'StatusInd', 'TransactionTypeStndCode', 'BatchID', 'BKFSPID', 'ZVendorStndCode', 'SourceChkSum']\n"
     ]
    }
   ],
   "source": [
    "print(chelpMainArray)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df4.columns=chelpMainArray"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransId</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>DataClassStndCode</th>\n",
       "      <th>RecordTypeStndCode</th>\n",
       "      <th>RecordingDate</th>\n",
       "      <th>RecordingDocumentNumber</th>\n",
       "      <th>RecordingBookNumber</th>\n",
       "      <th>RecordingPageNumber</th>\n",
       "      <th>...</th>\n",
       "      <th>MatchStndCode</th>\n",
       "      <th>REOStndCode</th>\n",
       "      <th>UpdateOwnershipFlag</th>\n",
       "      <th>LoadID</th>\n",
       "      <th>StatusInd</th>\n",
       "      <th>TransactionTypeStndCode</th>\n",
       "      <th>BatchID</th>\n",
       "      <th>BKFSPID</th>\n",
       "      <th>ZVendorStndCode</th>\n",
       "      <th>SourceChkSum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>315852373</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0001</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791749</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>1</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.651814e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>315852374</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0002</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791750</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>2</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.531757e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>315852375</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0003</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791751</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>3</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-6.570774e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>315852376</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0004</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791752</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>4</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-8.579398e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315852377</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0005</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791753</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>5</td>\n",
       "      <td>BKF</td>\n",
       "      <td>3.610861e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>315852378</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0006</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791754</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>6</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.132851e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>315852379</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0008</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791755</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>7</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.282545e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>315852380</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0010</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791756</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>8</td>\n",
       "      <td>BKF</td>\n",
       "      <td>2.096268e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>315852381</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>011</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791757</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>9</td>\n",
       "      <td>BKF</td>\n",
       "      <td>2.133314e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>315852382</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0013</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791758</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>10</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-6.505621e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>315852383</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0014</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791759</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>11</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.409499e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>315852384</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0015</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791760</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>12</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-5.732568e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>315852385</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0016</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791761</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>13</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-2.019233e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>315852386</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0018</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791762</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>14</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.764427e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>315970772</td>\n",
       "      <td>46045</td>\n",
       "      <td>SD</td>\n",
       "      <td>EDMUNDS</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2007-03-05</td>\n",
       "      <td>005657</td>\n",
       "      <td>121</td>\n",
       "      <td>45</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2837193155</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2240820</td>\n",
       "      <td>837</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.299543e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>315970773</td>\n",
       "      <td>46045</td>\n",
       "      <td>SD</td>\n",
       "      <td>EDMUNDS</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2007-03-05</td>\n",
       "      <td>005659</td>\n",
       "      <td>121</td>\n",
       "      <td>46</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2837193156</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2240820</td>\n",
       "      <td>838</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.349484e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>315970774</td>\n",
       "      <td>46045</td>\n",
       "      <td>SD</td>\n",
       "      <td>EDMUNDS</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2007-03-05</td>\n",
       "      <td>005660</td>\n",
       "      <td>121</td>\n",
       "      <td>47</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2837193941</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2240820</td>\n",
       "      <td>357</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-8.435763e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>315970775</td>\n",
       "      <td>46045</td>\n",
       "      <td>SD</td>\n",
       "      <td>EDMUNDS</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2007-03-06</td>\n",
       "      <td>005664</td>\n",
       "      <td>121</td>\n",
       "      <td>48</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2837193942</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2240820</td>\n",
       "      <td>358</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.511650e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>315970776</td>\n",
       "      <td>46045</td>\n",
       "      <td>SD</td>\n",
       "      <td>EDMUNDS</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2007-03-06</td>\n",
       "      <td>005667</td>\n",
       "      <td>121</td>\n",
       "      <td>49</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2837193157</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2240820</td>\n",
       "      <td>839</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-7.015568e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>315970777</td>\n",
       "      <td>46045</td>\n",
       "      <td>SD</td>\n",
       "      <td>EDMUNDS</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2007-03-06</td>\n",
       "      <td>005668</td>\n",
       "      <td>121</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2837193158</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2240820</td>\n",
       "      <td>840</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.419252e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>315970778</td>\n",
       "      <td>46045</td>\n",
       "      <td>SD</td>\n",
       "      <td>EDMUNDS</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2007-03-07</td>\n",
       "      <td>005678</td>\n",
       "      <td>121</td>\n",
       "      <td>51</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2837193159</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2240820</td>\n",
       "      <td>841</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.278553e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>315975260</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-25</td>\n",
       "      <td>041770</td>\n",
       "      <td>133</td>\n",
       "      <td>327</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642117</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>1</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.826860e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>315975261</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-25</td>\n",
       "      <td>041780</td>\n",
       "      <td>133</td>\n",
       "      <td>329</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642118</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>2</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-5.297475e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>315975262</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-25</td>\n",
       "      <td>041783</td>\n",
       "      <td>133</td>\n",
       "      <td>332</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642119</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1062517</td>\n",
       "      <td>3</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.285240e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>315975263</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-27</td>\n",
       "      <td>041789</td>\n",
       "      <td>133</td>\n",
       "      <td>337</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642120</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>4</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-5.618417e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>315975264</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-28</td>\n",
       "      <td>041794</td>\n",
       "      <td>133</td>\n",
       "      <td>341</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642121</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>5</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-9.101336e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>315975265</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-28</td>\n",
       "      <td>041797</td>\n",
       "      <td>133</td>\n",
       "      <td>343</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642122</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>6</td>\n",
       "      <td>BKF</td>\n",
       "      <td>5.707815e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>315975266</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-28</td>\n",
       "      <td>041799</td>\n",
       "      <td>133</td>\n",
       "      <td>344</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642123</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>7</td>\n",
       "      <td>BKF</td>\n",
       "      <td>5.342536e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>315975267</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-08-29</td>\n",
       "      <td>041800</td>\n",
       "      <td>133</td>\n",
       "      <td>345</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642124</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>8</td>\n",
       "      <td>BKF</td>\n",
       "      <td>8.652645e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>315975268</td>\n",
       "      <td>46047</td>\n",
       "      <td>SD</td>\n",
       "      <td>FALL RIVER</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2008-09-02</td>\n",
       "      <td>041802</td>\n",
       "      <td>133</td>\n",
       "      <td>346</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>322642125</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>6704</td>\n",
       "      <td>9</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.137384e+09</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>434192</th>\n",
       "      <td>411233641</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>213</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326246</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25523</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-4.062573e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434193</th>\n",
       "      <td>411233642</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>215</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005386</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25524</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.131106e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434194</th>\n",
       "      <td>411233643</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>217</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326322</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25525</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.710760e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434195</th>\n",
       "      <td>411233644</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>218</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005388</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25526</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.354516e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434196</th>\n",
       "      <td>411233645</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>221</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005389</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25527</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.732588e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434197</th>\n",
       "      <td>411233646</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>222</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005390</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25528</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.095525e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434198</th>\n",
       "      <td>411233647</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>223</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326345</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25529</td>\n",
       "      <td>BKF</td>\n",
       "      <td>8.309003e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434199</th>\n",
       "      <td>411233648</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>224</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326139</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25530</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-3.064645e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434200</th>\n",
       "      <td>411233649</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>225</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005393</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25531</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.904139e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434201</th>\n",
       "      <td>411233650</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>226</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326136</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25532</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.619932e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434202</th>\n",
       "      <td>411233651</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>227</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326334</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25533</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.400223e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434203</th>\n",
       "      <td>411233652</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>228</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326275</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25534</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-7.713479e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434204</th>\n",
       "      <td>411233653</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>231</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326286</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25535</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.473133e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434205</th>\n",
       "      <td>411233654</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>232</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326223</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25536</td>\n",
       "      <td>BKF</td>\n",
       "      <td>2.092558e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434206</th>\n",
       "      <td>411233655</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>233</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005399</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25537</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.097496e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434207</th>\n",
       "      <td>411233656</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>235</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326177</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25538</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-6.867208e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434208</th>\n",
       "      <td>411233657</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>237</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326332</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25539</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-6.489207e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434209</th>\n",
       "      <td>411233658</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>241</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326144</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25540</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.901485e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434210</th>\n",
       "      <td>411233659</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>242</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326242</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25541</td>\n",
       "      <td>BKF</td>\n",
       "      <td>7.517638e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434211</th>\n",
       "      <td>411233660</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>243</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326184</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25542</td>\n",
       "      <td>BKF</td>\n",
       "      <td>9.468836e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434212</th>\n",
       "      <td>411233661</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>245</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326352</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25543</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-9.727775e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434213</th>\n",
       "      <td>411233662</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>246</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005406</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25544</td>\n",
       "      <td>BKF</td>\n",
       "      <td>3.757779e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434214</th>\n",
       "      <td>411233663</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>249</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326255</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25545</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.460535e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434215</th>\n",
       "      <td>411233664</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>250</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326361</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25546</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.850310e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434216</th>\n",
       "      <td>411233665</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>251</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326368</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25547</td>\n",
       "      <td>BKF</td>\n",
       "      <td>3.817695e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434217</th>\n",
       "      <td>411233666</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>252</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005410</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25548</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-9.039898e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434218</th>\n",
       "      <td>411233667</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>254</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326133</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25549</td>\n",
       "      <td>BKF</td>\n",
       "      <td>2.129362e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434219</th>\n",
       "      <td>411233668</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>255</td>\n",
       "      <td>...</td>\n",
       "      <td>MA</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2861326204</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2283726</td>\n",
       "      <td>25550</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-9.487834e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434220</th>\n",
       "      <td>411233669</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>258</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005413</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25551</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1.026002e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434221</th>\n",
       "      <td>411233670</td>\n",
       "      <td>46099</td>\n",
       "      <td>SD</td>\n",
       "      <td>MINNEHAHA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2013-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>549</td>\n",
       "      <td>259</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2795005414</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2186724</td>\n",
       "      <td>25552</td>\n",
       "      <td>BKF</td>\n",
       "      <td>6.311876e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>434222 rows × 131 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          TransId   FIPS State      County DataClassStndCode  \\\n",
       "0       315852373  46009    SD   BON HOMME                 D   \n",
       "1       315852374  46009    SD   BON HOMME                 D   \n",
       "2       315852375  46009    SD   BON HOMME                 D   \n",
       "3       315852376  46009    SD   BON HOMME                 D   \n",
       "4       315852377  46009    SD   BON HOMME                 D   \n",
       "5       315852378  46009    SD   BON HOMME                 D   \n",
       "6       315852379  46009    SD   BON HOMME                 D   \n",
       "7       315852380  46009    SD   BON HOMME                 D   \n",
       "8       315852381  46009    SD   BON HOMME                 D   \n",
       "9       315852382  46009    SD   BON HOMME                 D   \n",
       "10      315852383  46009    SD   BON HOMME                 D   \n",
       "11      315852384  46009    SD   BON HOMME                 D   \n",
       "12      315852385  46009    SD   BON HOMME                 D   \n",
       "13      315852386  46009    SD   BON HOMME                 D   \n",
       "14      315970772  46045    SD     EDMUNDS                 D   \n",
       "15      315970773  46045    SD     EDMUNDS                 D   \n",
       "16      315970774  46045    SD     EDMUNDS                 D   \n",
       "17      315970775  46045    SD     EDMUNDS                 D   \n",
       "18      315970776  46045    SD     EDMUNDS                 D   \n",
       "19      315970777  46045    SD     EDMUNDS                 D   \n",
       "20      315970778  46045    SD     EDMUNDS                 D   \n",
       "21      315975260  46047    SD  FALL RIVER                 D   \n",
       "22      315975261  46047    SD  FALL RIVER                 D   \n",
       "23      315975262  46047    SD  FALL RIVER                 D   \n",
       "24      315975263  46047    SD  FALL RIVER                 D   \n",
       "25      315975264  46047    SD  FALL RIVER                 D   \n",
       "26      315975265  46047    SD  FALL RIVER                 D   \n",
       "27      315975266  46047    SD  FALL RIVER                 D   \n",
       "28      315975267  46047    SD  FALL RIVER                 D   \n",
       "29      315975268  46047    SD  FALL RIVER                 D   \n",
       "...           ...    ...   ...         ...               ...   \n",
       "434192  411233641  46099    SD   MINNEHAHA                 D   \n",
       "434193  411233642  46099    SD   MINNEHAHA                 D   \n",
       "434194  411233643  46099    SD   MINNEHAHA                 D   \n",
       "434195  411233644  46099    SD   MINNEHAHA                 D   \n",
       "434196  411233645  46099    SD   MINNEHAHA                 D   \n",
       "434197  411233646  46099    SD   MINNEHAHA                 D   \n",
       "434198  411233647  46099    SD   MINNEHAHA                 D   \n",
       "434199  411233648  46099    SD   MINNEHAHA                 D   \n",
       "434200  411233649  46099    SD   MINNEHAHA                 D   \n",
       "434201  411233650  46099    SD   MINNEHAHA                 D   \n",
       "434202  411233651  46099    SD   MINNEHAHA                 D   \n",
       "434203  411233652  46099    SD   MINNEHAHA                 D   \n",
       "434204  411233653  46099    SD   MINNEHAHA                 D   \n",
       "434205  411233654  46099    SD   MINNEHAHA                 D   \n",
       "434206  411233655  46099    SD   MINNEHAHA                 D   \n",
       "434207  411233656  46099    SD   MINNEHAHA                 D   \n",
       "434208  411233657  46099    SD   MINNEHAHA                 D   \n",
       "434209  411233658  46099    SD   MINNEHAHA                 D   \n",
       "434210  411233659  46099    SD   MINNEHAHA                 D   \n",
       "434211  411233660  46099    SD   MINNEHAHA                 D   \n",
       "434212  411233661  46099    SD   MINNEHAHA                 D   \n",
       "434213  411233662  46099    SD   MINNEHAHA                 D   \n",
       "434214  411233663  46099    SD   MINNEHAHA                 D   \n",
       "434215  411233664  46099    SD   MINNEHAHA                 D   \n",
       "434216  411233665  46099    SD   MINNEHAHA                 D   \n",
       "434217  411233666  46099    SD   MINNEHAHA                 D   \n",
       "434218  411233667  46099    SD   MINNEHAHA                 D   \n",
       "434219  411233668  46099    SD   MINNEHAHA                 D   \n",
       "434220  411233669  46099    SD   MINNEHAHA                 D   \n",
       "434221  411233670  46099    SD   MINNEHAHA                 D   \n",
       "\n",
       "       RecordTypeStndCode RecordingDate RecordingDocumentNumber  \\\n",
       "0                       P    2009-01-06                     NaN   \n",
       "1                       P    2009-01-07                     NaN   \n",
       "2                       P    2009-01-09                     NaN   \n",
       "3                       P    2009-01-09                     NaN   \n",
       "4                       P    2009-01-13                     NaN   \n",
       "5                       P    2009-01-14                     NaN   \n",
       "6                       P    2009-01-15                     NaN   \n",
       "7                       P    2009-01-16                     NaN   \n",
       "8                       P    2009-01-16                     NaN   \n",
       "9                       P    2009-01-20                     NaN   \n",
       "10                      P    2009-01-20                     NaN   \n",
       "11                      P    2009-01-20                     NaN   \n",
       "12                      P    2009-01-20                     NaN   \n",
       "13                      P    2009-01-23                     NaN   \n",
       "14                      P    2007-03-05                  005657   \n",
       "15                      P    2007-03-05                  005659   \n",
       "16                      P    2007-03-05                  005660   \n",
       "17                      P    2007-03-06                  005664   \n",
       "18                      P    2007-03-06                  005667   \n",
       "19                      P    2007-03-06                  005668   \n",
       "20                      P    2007-03-07                  005678   \n",
       "21                      P    2008-08-25                  041770   \n",
       "22                      P    2008-08-25                  041780   \n",
       "23                      P    2008-08-25                  041783   \n",
       "24                      P    2008-08-27                  041789   \n",
       "25                      P    2008-08-28                  041794   \n",
       "26                      P    2008-08-28                  041797   \n",
       "27                      P    2008-08-28                  041799   \n",
       "28                      P    2008-08-29                  041800   \n",
       "29                      P    2008-09-02                  041802   \n",
       "...                   ...           ...                     ...   \n",
       "434192                  P    2013-05-08                     NaN   \n",
       "434193                  P    2013-05-08                     NaN   \n",
       "434194                  P    2013-05-08                     NaN   \n",
       "434195                  P    2013-05-08                     NaN   \n",
       "434196                  P    2013-05-08                     NaN   \n",
       "434197                  P    2013-05-09                     NaN   \n",
       "434198                  P    2013-05-09                     NaN   \n",
       "434199                  P    2013-05-09                     NaN   \n",
       "434200                  P    2013-05-09                     NaN   \n",
       "434201                  P    2013-05-09                     NaN   \n",
       "434202                  P    2013-05-09                     NaN   \n",
       "434203                  P    2013-05-09                     NaN   \n",
       "434204                  P    2013-05-09                     NaN   \n",
       "434205                  P    2013-05-09                     NaN   \n",
       "434206                  P    2013-05-09                     NaN   \n",
       "434207                  P    2013-05-09                     NaN   \n",
       "434208                  P    2013-05-09                     NaN   \n",
       "434209                  P    2013-05-09                     NaN   \n",
       "434210                  P    2013-05-09                     NaN   \n",
       "434211                  P    2013-05-09                     NaN   \n",
       "434212                  P    2013-05-10                     NaN   \n",
       "434213                  P    2013-05-10                     NaN   \n",
       "434214                  P    2013-05-10                     NaN   \n",
       "434215                  P    2013-05-10                     NaN   \n",
       "434216                  P    2013-05-10                     NaN   \n",
       "434217                  P    2013-05-10                     NaN   \n",
       "434218                  P    2013-05-10                     NaN   \n",
       "434219                  P    2013-05-10                     NaN   \n",
       "434220                  P    2013-05-10                     NaN   \n",
       "434221                  P    2013-05-10                     NaN   \n",
       "\n",
       "       RecordingBookNumber RecordingPageNumber      ...      MatchStndCode  \\\n",
       "0                       96                0001      ...                 NM   \n",
       "1                       96                0002      ...                 NM   \n",
       "2                       96                0003      ...                 NM   \n",
       "3                       96                0004      ...                 NM   \n",
       "4                       96                0005      ...                 NM   \n",
       "5                       96                0006      ...                 NM   \n",
       "6                       96                0008      ...                 NM   \n",
       "7                       96                0010      ...                 NM   \n",
       "8                       96                 011      ...                 NM   \n",
       "9                       96                0013      ...                 NM   \n",
       "10                      96                0014      ...                 NM   \n",
       "11                      96                0015      ...                 NM   \n",
       "12                      96                0016      ...                 NM   \n",
       "13                      96                0018      ...                 NM   \n",
       "14                     121                  45      ...                 NM   \n",
       "15                     121                  46      ...                 NM   \n",
       "16                     121                  47      ...                 NM   \n",
       "17                     121                  48      ...                 NM   \n",
       "18                     121                  49      ...                 NM   \n",
       "19                     121                  50      ...                 NM   \n",
       "20                     121                  51      ...                 NM   \n",
       "21                     133                 327      ...                      \n",
       "22                     133                 329      ...                      \n",
       "23                     133                 332      ...                      \n",
       "24                     133                 337      ...                      \n",
       "25                     133                 341      ...                      \n",
       "26                     133                 343      ...                      \n",
       "27                     133                 344      ...                      \n",
       "28                     133                 345      ...                      \n",
       "29                     133                 346      ...                      \n",
       "...                    ...                 ...      ...                ...   \n",
       "434192                 549                 213      ...                 MA   \n",
       "434193                 549                 215      ...                 NM   \n",
       "434194                 549                 217      ...                 MA   \n",
       "434195                 549                 218      ...                 NM   \n",
       "434196                 549                 221      ...                 NM   \n",
       "434197                 549                 222      ...                 NM   \n",
       "434198                 549                 223      ...                 MA   \n",
       "434199                 549                 224      ...                 MA   \n",
       "434200                 549                 225      ...                 NM   \n",
       "434201                 549                 226      ...                 MA   \n",
       "434202                 549                 227      ...                 MA   \n",
       "434203                 549                 228      ...                 MA   \n",
       "434204                 549                 231      ...                 MA   \n",
       "434205                 549                 232      ...                 MA   \n",
       "434206                 549                 233      ...                 NM   \n",
       "434207                 549                 235      ...                 MA   \n",
       "434208                 549                 237      ...                 MA   \n",
       "434209                 549                 241      ...                 MA   \n",
       "434210                 549                 242      ...                 MA   \n",
       "434211                 549                 243      ...                 MA   \n",
       "434212                 549                 245      ...                 MA   \n",
       "434213                 549                 246      ...                 NM   \n",
       "434214                 549                 249      ...                 MA   \n",
       "434215                 549                 250      ...                 MA   \n",
       "434216                 549                 251      ...                 MA   \n",
       "434217                 549                 252      ...                 NM   \n",
       "434218                 549                 254      ...                 MA   \n",
       "434219                 549                 255      ...                 MA   \n",
       "434220                 549                 258      ...                 NM   \n",
       "434221                 549                 259      ...                 NM   \n",
       "\n",
       "        REOStndCode  UpdateOwnershipFlag      LoadID  StatusInd  \\\n",
       "0               NaN                       2411791749        NaN   \n",
       "1               NaN                       2411791750        NaN   \n",
       "2               NaN                       2411791751        NaN   \n",
       "3               NaN                       2411791752        NaN   \n",
       "4               NaN                       2411791753        NaN   \n",
       "5               NaN                       2411791754        NaN   \n",
       "6               NaN                       2411791755        NaN   \n",
       "7               NaN                       2411791756        NaN   \n",
       "8               NaN                       2411791757        NaN   \n",
       "9               NaN                       2411791758        NaN   \n",
       "10              NaN                       2411791759        NaN   \n",
       "11              NaN                       2411791760        NaN   \n",
       "12              NaN                       2411791761        NaN   \n",
       "13              NaN                       2411791762        NaN   \n",
       "14              NaN                       2837193155        NaN   \n",
       "15              NaN                       2837193156        NaN   \n",
       "16              NaN                       2837193941        NaN   \n",
       "17              NaN                       2837193942        NaN   \n",
       "18              NaN                       2837193157        NaN   \n",
       "19              NaN                       2837193158        NaN   \n",
       "20              NaN                       2837193159        NaN   \n",
       "21              NaN                        322642117        NaN   \n",
       "22              NaN                        322642118        NaN   \n",
       "23              NaN                        322642119        NaN   \n",
       "24              NaN                        322642120        NaN   \n",
       "25              NaN                        322642121        NaN   \n",
       "26              NaN                        322642122        NaN   \n",
       "27              NaN                        322642123        NaN   \n",
       "28              NaN                        322642124        NaN   \n",
       "29              NaN                        322642125        NaN   \n",
       "...             ...                  ...         ...        ...   \n",
       "434192          NaN                       2861326246        NaN   \n",
       "434193          NaN                       2795005386        NaN   \n",
       "434194          NaN                       2861326322        NaN   \n",
       "434195          NaN                       2795005388        NaN   \n",
       "434196          NaN                       2795005389        NaN   \n",
       "434197          NaN                       2795005390        NaN   \n",
       "434198          NaN                       2861326345        NaN   \n",
       "434199          NaN                       2861326139        NaN   \n",
       "434200          NaN                       2795005393        NaN   \n",
       "434201          NaN                       2861326136        NaN   \n",
       "434202          NaN                       2861326334        NaN   \n",
       "434203          NaN                       2861326275        NaN   \n",
       "434204          NaN                       2861326286        NaN   \n",
       "434205          NaN                       2861326223        NaN   \n",
       "434206          NaN                       2795005399        NaN   \n",
       "434207          NaN                       2861326177        NaN   \n",
       "434208          NaN                       2861326332        NaN   \n",
       "434209          NaN                       2861326144        NaN   \n",
       "434210          NaN                       2861326242        NaN   \n",
       "434211          NaN                       2861326184        NaN   \n",
       "434212          NaN                       2861326352        NaN   \n",
       "434213          NaN                       2795005406        NaN   \n",
       "434214          NaN                       2861326255        NaN   \n",
       "434215          NaN                       2861326361        NaN   \n",
       "434216          NaN                       2861326368        NaN   \n",
       "434217          NaN                       2795005410        NaN   \n",
       "434218          NaN                       2861326133        NaN   \n",
       "434219          NaN                       2861326204        NaN   \n",
       "434220          NaN                       2795005413        NaN   \n",
       "434221          NaN                       2795005414        NaN   \n",
       "\n",
       "        TransactionTypeStndCode  BatchID BKFSPID  ZVendorStndCode  \\\n",
       "0                                1702496       1              BKF   \n",
       "1                                1702496       2              BKF   \n",
       "2                                1702496       3              BKF   \n",
       "3                                1702496       4              BKF   \n",
       "4                                1702496       5              BKF   \n",
       "5                                1702496       6              BKF   \n",
       "6                                1702496       7              BKF   \n",
       "7                                1702496       8              BKF   \n",
       "8                                1702496       9              BKF   \n",
       "9                                1702496      10              BKF   \n",
       "10                               1702496      11              BKF   \n",
       "11                               1702496      12              BKF   \n",
       "12                               1702496      13              BKF   \n",
       "13                               1702496      14              BKF   \n",
       "14                               2240820     837              BKF   \n",
       "15                               2240820     838              BKF   \n",
       "16                               2240820     357              BKF   \n",
       "17                               2240820     358              BKF   \n",
       "18                               2240820     839              BKF   \n",
       "19                               2240820     840              BKF   \n",
       "20                               2240820     841              BKF   \n",
       "21                                  6704       1              BKF   \n",
       "22                                  6704       2              BKF   \n",
       "23                               1062517       3              BKF   \n",
       "24                                  6704       4              BKF   \n",
       "25                                  6704       5              BKF   \n",
       "26                                  6704       6              BKF   \n",
       "27                                  6704       7              BKF   \n",
       "28                                  6704       8              BKF   \n",
       "29                                  6704       9              BKF   \n",
       "...                         ...      ...     ...              ...   \n",
       "434192                           2283726   25523              BKF   \n",
       "434193                           2186724   25524              BKF   \n",
       "434194                           2283726   25525              BKF   \n",
       "434195                           2186724   25526              BKF   \n",
       "434196                           2186724   25527              BKF   \n",
       "434197                           2186724   25528              BKF   \n",
       "434198                           2283726   25529              BKF   \n",
       "434199                           2283726   25530              BKF   \n",
       "434200                           2186724   25531              BKF   \n",
       "434201                           2283726   25532              BKF   \n",
       "434202                           2283726   25533              BKF   \n",
       "434203                           2283726   25534              BKF   \n",
       "434204                           2283726   25535              BKF   \n",
       "434205                           2283726   25536              BKF   \n",
       "434206                           2186724   25537              BKF   \n",
       "434207                           2283726   25538              BKF   \n",
       "434208                           2283726   25539              BKF   \n",
       "434209                           2283726   25540              BKF   \n",
       "434210                           2283726   25541              BKF   \n",
       "434211                           2283726   25542              BKF   \n",
       "434212                           2283726   25543              BKF   \n",
       "434213                           2186724   25544              BKF   \n",
       "434214                           2283726   25545              BKF   \n",
       "434215                           2283726   25546              BKF   \n",
       "434216                           2283726   25547              BKF   \n",
       "434217                           2186724   25548              BKF   \n",
       "434218                           2283726   25549              BKF   \n",
       "434219                           2283726   25550              BKF   \n",
       "434220                           2186724   25551              BKF   \n",
       "434221                           2186724   25552              BKF   \n",
       "\n",
       "        SourceChkSum  \n",
       "0       1.651814e+09  \n",
       "1       1.531757e+08  \n",
       "2      -6.570774e+08  \n",
       "3      -8.579398e+08  \n",
       "4       3.610861e+08  \n",
       "5      -1.132851e+09  \n",
       "6       1.282545e+09  \n",
       "7       2.096268e+09  \n",
       "8       2.133314e+09  \n",
       "9      -6.505621e+08  \n",
       "10     -1.409499e+09  \n",
       "11     -5.732568e+08  \n",
       "12     -2.019233e+09  \n",
       "13      1.764427e+09  \n",
       "14      1.299543e+09  \n",
       "15      1.349484e+09  \n",
       "16     -8.435763e+08  \n",
       "17     -1.511650e+09  \n",
       "18     -7.015568e+08  \n",
       "19     -1.419252e+09  \n",
       "20      1.278553e+09  \n",
       "21     -1.826860e+09  \n",
       "22     -5.297475e+08  \n",
       "23     -1.285240e+09  \n",
       "24     -5.618417e+07  \n",
       "25     -9.101336e+08  \n",
       "26      5.707815e+08  \n",
       "27      5.342536e+07  \n",
       "28      8.652645e+08  \n",
       "29      1.137384e+09  \n",
       "...              ...  \n",
       "434192 -4.062573e+08  \n",
       "434193  1.131106e+09  \n",
       "434194  1.710760e+09  \n",
       "434195 -1.354516e+09  \n",
       "434196 -1.732588e+09  \n",
       "434197 -1.095525e+09  \n",
       "434198  8.309003e+08  \n",
       "434199 -3.064645e+08  \n",
       "434200  1.904139e+09  \n",
       "434201 -1.619932e+09  \n",
       "434202 -1.400223e+09  \n",
       "434203 -7.713479e+08  \n",
       "434204 -1.473133e+09  \n",
       "434205  2.092558e+09  \n",
       "434206 -1.097496e+09  \n",
       "434207 -6.867208e+08  \n",
       "434208 -6.489207e+08  \n",
       "434209  1.901485e+09  \n",
       "434210  7.517638e+08  \n",
       "434211  9.468836e+08  \n",
       "434212 -9.727775e+08  \n",
       "434213  3.757779e+08  \n",
       "434214  1.460535e+09  \n",
       "434215 -1.850310e+09  \n",
       "434216  3.817695e+08  \n",
       "434217 -9.039898e+08  \n",
       "434218  2.129362e+08  \n",
       "434219 -9.487834e+08  \n",
       "434220 -1.026002e+09  \n",
       "434221  6.311876e+08  \n",
       "\n",
       "[434222 rows x 131 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransId</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>DataClassStndCode</th>\n",
       "      <th>RecordTypeStndCode</th>\n",
       "      <th>RecordingDate</th>\n",
       "      <th>RecordingDocumentNumber</th>\n",
       "      <th>RecordingBookNumber</th>\n",
       "      <th>RecordingPageNumber</th>\n",
       "      <th>...</th>\n",
       "      <th>MatchStndCode</th>\n",
       "      <th>REOStndCode</th>\n",
       "      <th>UpdateOwnershipFlag</th>\n",
       "      <th>LoadID</th>\n",
       "      <th>StatusInd</th>\n",
       "      <th>TransactionTypeStndCode</th>\n",
       "      <th>BatchID</th>\n",
       "      <th>BKFSPID</th>\n",
       "      <th>ZVendorStndCode</th>\n",
       "      <th>SourceChkSum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>315852373</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0001</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791749</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>1</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.651814e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>315852374</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0002</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791750</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>2</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.531757e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>315852375</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0003</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791751</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>3</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-6.570774e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>315852376</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0004</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791752</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>4</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-8.579398e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315852377</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0005</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791753</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>5</td>\n",
       "      <td>BKF</td>\n",
       "      <td>3.610861e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 131 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     TransId   FIPS State     County DataClassStndCode RecordTypeStndCode  \\\n",
       "0  315852373  46009    SD  BON HOMME                 D                  P   \n",
       "1  315852374  46009    SD  BON HOMME                 D                  P   \n",
       "2  315852375  46009    SD  BON HOMME                 D                  P   \n",
       "3  315852376  46009    SD  BON HOMME                 D                  P   \n",
       "4  315852377  46009    SD  BON HOMME                 D                  P   \n",
       "\n",
       "  RecordingDate RecordingDocumentNumber RecordingBookNumber  \\\n",
       "0    2009-01-06                     NaN                  96   \n",
       "1    2009-01-07                     NaN                  96   \n",
       "2    2009-01-09                     NaN                  96   \n",
       "3    2009-01-09                     NaN                  96   \n",
       "4    2009-01-13                     NaN                  96   \n",
       "\n",
       "  RecordingPageNumber      ...      MatchStndCode  REOStndCode  \\\n",
       "0                0001      ...                 NM          NaN   \n",
       "1                0002      ...                 NM          NaN   \n",
       "2                0003      ...                 NM          NaN   \n",
       "3                0004      ...                 NM          NaN   \n",
       "4                0005      ...                 NM          NaN   \n",
       "\n",
       "   UpdateOwnershipFlag      LoadID  StatusInd  TransactionTypeStndCode  \\\n",
       "0                       2411791749        NaN                            \n",
       "1                       2411791750        NaN                            \n",
       "2                       2411791751        NaN                            \n",
       "3                       2411791752        NaN                            \n",
       "4                       2411791753        NaN                            \n",
       "\n",
       "   BatchID BKFSPID  ZVendorStndCode  SourceChkSum  \n",
       "0  1702496       1              BKF  1.651814e+09  \n",
       "1  1702496       2              BKF  1.531757e+08  \n",
       "2  1702496       3              BKF -6.570774e+08  \n",
       "3  1702496       4              BKF -8.579398e+08  \n",
       "4  1702496       5              BKF  3.610861e+08  \n",
       "\n",
       "[5 rows x 131 columns]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5 = zillow_helpers.txt_to_column_df('data/46/ZTrans/Main.txt', 'column_helper/main.csv')\n",
    "df5.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransId</th>\n",
       "      <th>AssessorParcelNumber</th>\n",
       "      <th>APNIndicatorStndCode</th>\n",
       "      <th>TaxIDNumber</th>\n",
       "      <th>TaxIDIndicatorStndCode</th>\n",
       "      <th>UnformattedAssessorParcelNumber</th>\n",
       "      <th>AlternateParcelNumber</th>\n",
       "      <th>HawaiiCondoCPRCode</th>\n",
       "      <th>PropertyHouseNumber</th>\n",
       "      <th>PropertyHouseNumberExt</th>\n",
       "      <th>...</th>\n",
       "      <th>PropertyAddressMatchType</th>\n",
       "      <th>PropertyAddressDPV</th>\n",
       "      <th>PropertyGeocodeQualityCode</th>\n",
       "      <th>PropertyAddressQualityCode</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>LoadID</th>\n",
       "      <th>ImportParcelID</th>\n",
       "      <th>BKFSPID</th>\n",
       "      <th>AssessmentRecordMatchFlag</th>\n",
       "      <th>BatchID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>315997382</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46061</td>\n",
       "      <td>2565142377</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1872223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>315997383</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46061</td>\n",
       "      <td>2565142378</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1872223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>315997384</td>\n",
       "      <td>103-57-022-004-000-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1035702200400002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46061</td>\n",
       "      <td>2565142119</td>\n",
       "      <td>119206404.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1872223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>315997385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46061</td>\n",
       "      <td>2565142379</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1000198961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315997386</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46061</td>\n",
       "      <td>2565142380</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1000198961</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 68 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     TransId   AssessorParcelNumber APNIndicatorStndCode  TaxIDNumber  \\\n",
       "0  315997382                    NaN                  NaN          NaN   \n",
       "1  315997383                    NaN                  NaN          NaN   \n",
       "2  315997384  103-57-022-004-000-02                  NaN          NaN   \n",
       "3  315997385                    NaN                  NaN          NaN   \n",
       "4  315997386                    NaN                  NaN          NaN   \n",
       "\n",
       "  TaxIDIndicatorStndCode UnformattedAssessorParcelNumber  \\\n",
       "0                    NaN                             NaN   \n",
       "1                    NaN                             NaN   \n",
       "2                    NaN                1035702200400002   \n",
       "3                    NaN                             NaN   \n",
       "4                    NaN                             NaN   \n",
       "\n",
       "   AlternateParcelNumber HawaiiCondoCPRCode PropertyHouseNumber  \\\n",
       "0                    NaN                NaN                 NaN   \n",
       "1                    NaN                NaN                 NaN   \n",
       "2                    NaN                NaN                 NaN   \n",
       "3                    NaN                NaN                 NaN   \n",
       "4                    NaN                NaN                 NaN   \n",
       "\n",
       "   PropertyHouseNumberExt     ...     PropertyAddressMatchType  \\\n",
       "0                     NaN     ...                          NaN   \n",
       "1                     NaN     ...                          NaN   \n",
       "2                     NaN     ...                          NaN   \n",
       "3                     NaN     ...                          NaN   \n",
       "4                     NaN     ...                          NaN   \n",
       "\n",
       "  PropertyAddressDPV PropertyGeocodeQualityCode PropertyAddressQualityCode  \\\n",
       "0                NaN                        NaN                        NaN   \n",
       "1                NaN                        NaN                        NaN   \n",
       "2                NaN                        NaN                        NaN   \n",
       "3                NaN                        NaN                        NaN   \n",
       "4                NaN                        NaN                        NaN   \n",
       "\n",
       "    FIPS      LoadID ImportParcelID BKFSPID  AssessmentRecordMatchFlag  \\\n",
       "0  46061  2565142377            NaN       2                          0   \n",
       "1  46061  2565142378            NaN       3                          0   \n",
       "2  46061  2565142119    119206404.0       1                          1   \n",
       "3  46061  2565142379            NaN       4                          0   \n",
       "4  46061  2565142380            NaN       5                          0   \n",
       "\n",
       "      BatchID  \n",
       "0     1872223  \n",
       "1     1872223  \n",
       "2     1872223  \n",
       "3  1000198961  \n",
       "4  1000198961  \n",
       "\n",
       "[5 rows x 68 columns]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6 = zillow_helpers.txt_to_column_df('data/46/ZTrans/PropertyInfo.txt', 'column_helper/propertyinfo.csv')\n",
    "df6.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransId</th>\n",
       "      <th>FIPS_x</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>DataClassStndCode</th>\n",
       "      <th>RecordTypeStndCode</th>\n",
       "      <th>RecordingDate</th>\n",
       "      <th>RecordingDocumentNumber</th>\n",
       "      <th>RecordingBookNumber</th>\n",
       "      <th>RecordingPageNumber</th>\n",
       "      <th>...</th>\n",
       "      <th>PropertyAddressMatchType</th>\n",
       "      <th>PropertyAddressDPV</th>\n",
       "      <th>PropertyGeocodeQualityCode</th>\n",
       "      <th>PropertyAddressQualityCode</th>\n",
       "      <th>FIPS_y</th>\n",
       "      <th>LoadID_y</th>\n",
       "      <th>ImportParcelID</th>\n",
       "      <th>BKFSPID_y</th>\n",
       "      <th>AssessmentRecordMatchFlag</th>\n",
       "      <th>BatchID_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>315852373</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0001</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46009.0</td>\n",
       "      <td>2.411792e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1702496.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>315852374</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0002</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46009.0</td>\n",
       "      <td>2.411792e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1702496.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>315852375</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0003</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46009.0</td>\n",
       "      <td>2.411792e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1702496.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>315852376</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0004</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46009.0</td>\n",
       "      <td>2.411792e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1702496.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315852377</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0005</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46009.0</td>\n",
       "      <td>2.411792e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1702496.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 198 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     TransId  FIPS_x State     County DataClassStndCode RecordTypeStndCode  \\\n",
       "0  315852373   46009    SD  BON HOMME                 D                  P   \n",
       "1  315852374   46009    SD  BON HOMME                 D                  P   \n",
       "2  315852375   46009    SD  BON HOMME                 D                  P   \n",
       "3  315852376   46009    SD  BON HOMME                 D                  P   \n",
       "4  315852377   46009    SD  BON HOMME                 D                  P   \n",
       "\n",
       "  RecordingDate RecordingDocumentNumber RecordingBookNumber  \\\n",
       "0    2009-01-06                     NaN                  96   \n",
       "1    2009-01-07                     NaN                  96   \n",
       "2    2009-01-09                     NaN                  96   \n",
       "3    2009-01-09                     NaN                  96   \n",
       "4    2009-01-13                     NaN                  96   \n",
       "\n",
       "  RecordingPageNumber    ...     PropertyAddressMatchType  PropertyAddressDPV  \\\n",
       "0                0001    ...                          NaN                 NaN   \n",
       "1                0002    ...                          NaN                 NaN   \n",
       "2                0003    ...                          NaN                 NaN   \n",
       "3                0004    ...                          NaN                 NaN   \n",
       "4                0005    ...                          NaN                 NaN   \n",
       "\n",
       "   PropertyGeocodeQualityCode  PropertyAddressQualityCode   FIPS_y  \\\n",
       "0                         NaN                         NaN  46009.0   \n",
       "1                         NaN                         NaN  46009.0   \n",
       "2                         NaN                         NaN  46009.0   \n",
       "3                         NaN                         NaN  46009.0   \n",
       "4                         NaN                         NaN  46009.0   \n",
       "\n",
       "       LoadID_y ImportParcelID BKFSPID_y  AssessmentRecordMatchFlag  BatchID_y  \n",
       "0  2.411792e+09            NaN       1.0                        0.0  1702496.0  \n",
       "1  2.411792e+09            NaN       2.0                        0.0  1702496.0  \n",
       "2  2.411792e+09            NaN       3.0                        0.0  1702496.0  \n",
       "3  2.411792e+09            NaN       4.0                        0.0  1702496.0  \n",
       "4  2.411792e+09            NaN       5.0                        0.0  1702496.0  \n",
       "\n",
       "[5 rows x 198 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7 = df5.merge(df6, left_on='TransId', right_on='TransId', how='outer')\n",
    "df7.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 434222 entries, 0 to 434221\n",
      "Columns: 131 entries, TransId to SourceChkSum\n",
      "dtypes: float64(53), int64(5), object(73)\n",
      "memory usage: 434.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df5.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 439769 entries, 0 to 439768\n",
      "Data columns (total 68 columns):\n",
      "TransId                               439769 non-null int64\n",
      "AssessorParcelNumber                  217117 non-null object\n",
      "APNIndicatorStndCode                  361 non-null object\n",
      "TaxIDNumber                           0 non-null float64\n",
      "TaxIDIndicatorStndCode                914 non-null object\n",
      "UnformattedAssessorParcelNumber       217117 non-null object\n",
      "AlternateParcelNumber                 0 non-null float64\n",
      "HawaiiCondoCPRCode                    222390 non-null object\n",
      "PropertyHouseNumber                   142392 non-null object\n",
      "PropertyHouseNumberExt                0 non-null float64\n",
      "PropertyStreetPreDirectional          266342 non-null object\n",
      "PropertyStreetName                    142420 non-null object\n",
      "PropertyStreetSuffix                  139201 non-null object\n",
      "PropertyStreetPostDirectional         232125 non-null object\n",
      "PropertyBuildingNumber                0 non-null float64\n",
      "PropertyFullStreetAddress             219292 non-null object\n",
      "PropertyCity                          217827 non-null object\n",
      "PropertyState                         220841 non-null object\n",
      "PropertyZip                           223761 non-null float64\n",
      "PropertyZip4                          197977 non-null float64\n",
      "OriginalPropertyFullStreetAddress     3006 non-null object\n",
      "OriginalPropertyAddressLastline       0 non-null float64\n",
      "PropertyAddressStndCode               872 non-null object\n",
      "LegalLot                              297233 non-null object\n",
      "LegalOtherLot                         7 non-null object\n",
      "LegalLotCode                          263757 non-null object\n",
      "LegalBlock                            213365 non-null object\n",
      "LegalSubdivisionName                  341412 non-null object\n",
      "LegalCondoProjectPUDDevName           0 non-null float64\n",
      "LegalBuildingNumber                   0 non-null float64\n",
      "LegalUnit                             3365 non-null object\n",
      "LegalSection                          138 non-null object\n",
      "LegalPhase                            1051 non-null object\n",
      "LegalTract                            28982 non-null object\n",
      "LegalDistrict                         8932 non-null object\n",
      "LegalMunicipality                     0 non-null float64\n",
      "LegalCity                             258950 non-null object\n",
      "LegalTownship                         2638 non-null object\n",
      "LegalSTRSection                       0 non-null float64\n",
      "LegalSTRTownship                      0 non-null float64\n",
      "LegalSTRRange                         0 non-null float64\n",
      "LegalSTRMeridian                      0 non-null float64\n",
      "LegalSecTwnRngMer                     32660 non-null object\n",
      "LegalRecordersMapReference            9108 non-null object\n",
      "LegalDescription                      15180 non-null object\n",
      "LegalLotSize                          50904 non-null object\n",
      "PropertySequenceNumber                439769 non-null int64\n",
      "PropertyAddressMatchcode              217521 non-null object\n",
      "PropertyAddressUnitDesignator         10745 non-null object\n",
      "PropertyAddressUnitNumber             11251 non-null object\n",
      "PropertyAddressCarrierRoute           190553 non-null object\n",
      "PropertyAddressGeoCodeMatchCode       217379 non-null object\n",
      "PropertyAddressLatitude               59856 non-null float64\n",
      "PropertyAddressLongitude              59856 non-null float64\n",
      "PropertyAddressCensusTractAndBlock    59856 non-null float64\n",
      "PropertyAddressConfidenceScore        0 non-null float64\n",
      "PropertyAddressCBSACode               0 non-null float64\n",
      "PropertyAddressCBSADivisionCode       0 non-null float64\n",
      "PropertyAddressMatchType              0 non-null float64\n",
      "PropertyAddressDPV                    0 non-null float64\n",
      "PropertyGeocodeQualityCode            0 non-null float64\n",
      "PropertyAddressQualityCode            0 non-null float64\n",
      "FIPS                                  439769 non-null int64\n",
      "LoadID                                439769 non-null int64\n",
      "ImportParcelID                        209399 non-null float64\n",
      "BKFSPID                               439769 non-null int64\n",
      "AssessmentRecordMatchFlag             439769 non-null int64\n",
      "BatchID                               439769 non-null int64\n",
      "dtypes: float64(25), int64(7), object(36)\n",
      "memory usage: 228.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df6.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 439771 entries, 0 to 439770\n",
      "Columns: 198 entries, TransId to BatchID_y\n",
      "dtypes: float64(84), int64(5), object(109)\n",
      "memory usage: 667.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df7.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from utils import zillow_helpers as zh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "datadir2 = 'data/46/ZTrans/'\n",
    "datadir4 = 'data/50/ZTrans/'\n",
    "chelpMain = 'column_helper/main.csv'\n",
    "chelpPI = 'column_helper/propertyinfo.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df8 = zh.txt_to_column_df(datadir2+'Main.txt', chelpMain)\n",
    "df9 = zh.txt_to_column_df(datadir4+'Main.txt', chelpMain)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 434222 entries, 0 to 434221\n",
      "Columns: 131 entries, TransId to SourceChkSum\n",
      "dtypes: float64(53), int64(5), object(73)\n",
      "memory usage: 434.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df8.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransId</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>DataClassStndCode</th>\n",
       "      <th>RecordTypeStndCode</th>\n",
       "      <th>RecordingDate</th>\n",
       "      <th>RecordingDocumentNumber</th>\n",
       "      <th>RecordingBookNumber</th>\n",
       "      <th>RecordingPageNumber</th>\n",
       "      <th>...</th>\n",
       "      <th>MatchStndCode</th>\n",
       "      <th>REOStndCode</th>\n",
       "      <th>UpdateOwnershipFlag</th>\n",
       "      <th>LoadID</th>\n",
       "      <th>StatusInd</th>\n",
       "      <th>TransactionTypeStndCode</th>\n",
       "      <th>BatchID</th>\n",
       "      <th>BKFSPID</th>\n",
       "      <th>ZVendorStndCode</th>\n",
       "      <th>SourceChkSum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>315852373</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0001</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791749</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>1</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.651814e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>315852374</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0002</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791750</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>2</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1.531757e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>315852375</td>\n",
       "      <td>46009</td>\n",
       "      <td>SD</td>\n",
       "      <td>BON HOMME</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>2009-01-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>0003</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2411791751</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1702496</td>\n",
       "      <td>3</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-6.570774e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 131 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     TransId   FIPS State     County DataClassStndCode RecordTypeStndCode  \\\n",
       "0  315852373  46009    SD  BON HOMME                 D                  P   \n",
       "1  315852374  46009    SD  BON HOMME                 D                  P   \n",
       "2  315852375  46009    SD  BON HOMME                 D                  P   \n",
       "\n",
       "  RecordingDate RecordingDocumentNumber RecordingBookNumber  \\\n",
       "0    2009-01-06                     NaN                  96   \n",
       "1    2009-01-07                     NaN                  96   \n",
       "2    2009-01-09                     NaN                  96   \n",
       "\n",
       "  RecordingPageNumber      ...      MatchStndCode  REOStndCode  \\\n",
       "0                0001      ...                 NM          NaN   \n",
       "1                0002      ...                 NM          NaN   \n",
       "2                0003      ...                 NM          NaN   \n",
       "\n",
       "   UpdateOwnershipFlag      LoadID  StatusInd  TransactionTypeStndCode  \\\n",
       "0                       2411791749        NaN                            \n",
       "1                       2411791750        NaN                            \n",
       "2                       2411791751        NaN                            \n",
       "\n",
       "   BatchID BKFSPID  ZVendorStndCode  SourceChkSum  \n",
       "0  1702496       1              BKF  1.651814e+09  \n",
       "1  1702496       2              BKF  1.531757e+08  \n",
       "2  1702496       3              BKF -6.570774e+08  \n",
       "\n",
       "[3 rows x 131 columns]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df8.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 789800 entries, 0 to 789799\n",
      "Columns: 131 entries, TransId to SourceChkSum\n",
      "dtypes: float64(82), int64(6), object(43)\n",
      "memory usage: 789.4+ MB\n"
     ]
    }
   ],
   "source": [
    "df9.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TransId</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>DataClassStndCode</th>\n",
       "      <th>RecordTypeStndCode</th>\n",
       "      <th>RecordingDate</th>\n",
       "      <th>RecordingDocumentNumber</th>\n",
       "      <th>RecordingBookNumber</th>\n",
       "      <th>RecordingPageNumber</th>\n",
       "      <th>...</th>\n",
       "      <th>MatchStndCode</th>\n",
       "      <th>REOStndCode</th>\n",
       "      <th>UpdateOwnershipFlag</th>\n",
       "      <th>LoadID</th>\n",
       "      <th>StatusInd</th>\n",
       "      <th>TransactionTypeStndCode</th>\n",
       "      <th>BatchID</th>\n",
       "      <th>BKFSPID</th>\n",
       "      <th>ZVendorStndCode</th>\n",
       "      <th>SourceChkSum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>349481687</td>\n",
       "      <td>50005</td>\n",
       "      <td>VT</td>\n",
       "      <td>CALEDONIA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>1963-11-18</td>\n",
       "      <td>845731</td>\n",
       "      <td>56</td>\n",
       "      <td>34</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2768030159</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2137747</td>\n",
       "      <td>37256</td>\n",
       "      <td>BKF</td>\n",
       "      <td>-1350954867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>349481688</td>\n",
       "      <td>50005</td>\n",
       "      <td>VT</td>\n",
       "      <td>CALEDONIA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>1985-12-05</td>\n",
       "      <td>800475</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2768030160</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2137747</td>\n",
       "      <td>37257</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1782568710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>349481691</td>\n",
       "      <td>50005</td>\n",
       "      <td>VT</td>\n",
       "      <td>CALEDONIA</td>\n",
       "      <td>D</td>\n",
       "      <td>P</td>\n",
       "      <td>1987-01-01</td>\n",
       "      <td>501211</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2768030164</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2137747</td>\n",
       "      <td>37261</td>\n",
       "      <td>BKF</td>\n",
       "      <td>1669866767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 131 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     TransId   FIPS State     County DataClassStndCode RecordTypeStndCode  \\\n",
       "0  349481687  50005    VT  CALEDONIA                 D                  P   \n",
       "1  349481688  50005    VT  CALEDONIA                 D                  P   \n",
       "2  349481691  50005    VT  CALEDONIA                 D                  P   \n",
       "\n",
       "  RecordingDate RecordingDocumentNumber RecordingBookNumber  \\\n",
       "0    1963-11-18                  845731                  56   \n",
       "1    1985-12-05                  800475                 NaN   \n",
       "2    1987-01-01                  501211                 NaN   \n",
       "\n",
       "  RecordingPageNumber      ...      MatchStndCode  REOStndCode  \\\n",
       "0                  34      ...                 NM          NaN   \n",
       "1                 NaN      ...                 NM          NaN   \n",
       "2                 NaN      ...                 NM          NaN   \n",
       "\n",
       "   UpdateOwnershipFlag      LoadID  StatusInd  TransactionTypeStndCode  \\\n",
       "0                       2768030159        NaN                            \n",
       "1                       2768030160        NaN                            \n",
       "2                       2768030164        NaN                            \n",
       "\n",
       "   BatchID BKFSPID  ZVendorStndCode  SourceChkSum  \n",
       "0  2137747   37256              BKF   -1350954867  \n",
       "1  2137747   37257              BKF    1782568710  \n",
       "2  2137747   37261              BKF    1669866767  \n",
       "\n",
       "[3 rows x 131 columns]"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df9.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df10 = pd.concat([df8,df9])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1224022 entries, 0 to 789799\n",
      "Columns: 131 entries, TransId to SourceChkSum\n",
      "dtypes: float64(53), int64(5), object(73)\n",
      "memory usage: 1.2+ GB\n"
     ]
    }
   ],
   "source": [
    "df10.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}