USCIS scrape and concat.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Playing around with the USCIS immigration forms data gave this URL for a search result, which has PDF and CSVs that go back to 2012."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get('https://www.uscis.gov/tools/reports-studies/immigration-forms-data?topic_id=20709&field_native_doc_issue_date_value%5Bvalue%5D%5Bmonth%5D=&field_native_doc_issue_date_value_1%5Bvalue%5D%5Byear%5D=&combined=&items_per_page=200')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.status_code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like the request was successful, let's get links to the data sources."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BeautifulSoup is a library for HTML parsing, super useful for web scraping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "soup = BeautifulSoup(r.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inspecting one of the links for a CSV, I see that it has these properties that I can query by."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(29,\n",
       " <a href=\"https://www.uscis.gov/sites/default/files/USCIS/Resources/Reports%20and%20Studies/Immigration%20Forms%20Data/Naturalization%20Data/N400_performancedata_fy2019_qtr2.csv\" length=\"20453\" type=\"text/csv\"> Form N-400, Application for Naturalization, by Category of Naturalization, Case Status, and USCIS Field Office Location (Fiscal Year 2019, 2nd Quarter, Jan. 1-Mar. 31, 2019)  (CSV, 20 KB)</a>)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csvs = soup.findAll(\"a\", attrs={\"type\":\"text/csv\"})\n",
    "len(csvs), csvs[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like we should cut off the first 4 lines. We can iterate through all the HTML objects and query on the link it's linking to, saving to files. And here's how we can name them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Fiscal Year 2019, 2nd Quarter, Jan. 1-Mar. 31, 2019'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csvs[0].text.split('(')[1].split(')')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# another quirk of the data is it's encoded in a less-commonly used encoding, discovered what it is by trying a few\n",
    "encoding=\"windows-1252\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = []\n",
    "for obj in csvs:\n",
    "    text = requests.get(obj.get('href')).content.decode(encoding)\n",
    "    csv_fname = obj.text.split('(')[1].split(')')[0]\n",
    "    csv_fname = csv_fname.replace(\"Fiscal Year\", \"FY\")\n",
    "    csv_fname = csv_fname.replace(\"Quarter\", \"Q\")\n",
    "    with open('data/USCIS/' + csv_fname + '.csv', 'w') as f:\n",
    "        f.write(text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenate raw CSVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['FY 2013, 4th Q, July 1- Sept. 30, 2013.csv',\n",
       " 'FY 2014, 1st Q, Oct. 1-Dec. 31, 2013.csv',\n",
       " 'FY 2014, 2nd Q, Jan. 1-March 31, 2014.csv',\n",
       " 'FY 2014, 3rd Q, April 1-June 30, 2014.csv',\n",
       " 'FY 2014, 4th Q, July 1-Sept. 30, 2014.csv',\n",
       " 'FY 2015, 1st Q, Oct. 1-Dec. 31, 2014.csv',\n",
       " 'FY 2015, 2nd Q, Jan. 1-March 31, 2015.csv',\n",
       " 'FY 2015, 3rd Q, April 1-June 30, 2015.csv',\n",
       " 'FY 2015, 4th Q, July 1-Sept. 30, 2015.csv',\n",
       " 'FY 2016, 1st Q, Oct. 1-Dec.31, 2015.csv',\n",
       " 'FY 2016, 2nd Q, Jan. 1-March 31, 2016.csv',\n",
       " 'FY 2016, 3rd Q, April 1-June 30, 2016.csv',\n",
       " 'FY 2016, 4th Q, July 1-Sept. 30, 2016.csv',\n",
       " 'FY 2017, 1st Q, Oct. 1-Dec. 31, 2016.csv',\n",
       " 'FY 2017, 2nd Q, Jan. 1-March 31, 2017.csv',\n",
       " 'FY 2017, 3rd Q, April 1-June 30, 2017.csv',\n",
       " 'FY 2017, 4th Q, July 1-Sept. 30, 2017.csv',\n",
       " 'FY 2018, 1st Q, Oct. 1-Dec. 31, 2017.csv',\n",
       " 'FY 2018, 2nd Q, Jan. 1-March 31, 2018.csv',\n",
       " 'FY 2018, 3rd Q, April 1-June 30, 2018.csv',\n",
       " 'FY 2018, 4th Q, July 1-Sept. 30, 2018.csv',\n",
       " 'FY 2019, 1st Q, Oct. 1-Dec. 31, 2018.csv',\n",
       " 'FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019.csv',\n",
       " 'FYs 2010-2012, Oct. 2009-June 2013.csv',\n",
       " 'FYs 2010-2012, Oct. 2009-Sept. 2012.csv',\n",
       " 'FYs 2010-2013, Oct. 2009-Dec. 2012.csv',\n",
       " 'FYs 2010-2013, Oct. 2009-March 2013.csv',\n",
       " 'FYs 2010-2013, Oct. 2009-Nov. 2012.csv',\n",
       " 'FYs 2010-2013, Oct. 2009-Oct. 2013.csv',\n",
       " 'massaged',\n",
       " 'Naturalizations by field office.xlsx',\n",
       " '~$Naturalizations by field office.xlsx']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.listdir('data/USCIS')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like the format is different for some of them, let's take a peek at the first 10 lines of each file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The field office city is always the 2nd column (for all the FY CSVs post-2013 Q4), so that's good. Let's try loading one into a CSV and dropping all the rows without field office names. Pandas columns are 0-indexed so the second column will be called the 1st."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I spend a bunch of time trying to automatically extract the correct column names before realizing I could save time by just deleting everything above what I wanted to use. That's what the `data/USCIS/massaged` folder is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FY 2013, 4th Q, July 1- Sept. 30, 2013.csv 16 16\n",
      "FY 2014, 1st Q, Oct. 1-Dec. 31, 2013.csv 16 16\n",
      "FY 2014, 2nd Q, Jan. 1-March 31, 2014.csv 16 16\n",
      "FY 2014, 3rd Q, April 1-June 30, 2014.csv 16 16\n",
      "FY 2014, 4th Q, July 1-Sept. 30, 2014.csv 16 16\n",
      "FY 2015, 1st Q, Oct. 1-Dec. 31, 2014.csv 16 16\n",
      "FY 2015, 2nd Q, Jan. 1-March 31, 2015.csv 17 17\n",
      "FY 2015, 3rd Q, April 1-June 30, 2015.csv 17 17\n",
      "FY 2015, 4th Q, July 1-Sept. 30, 2015.csv 17 17\n",
      "FY 2016, 1st Q, Oct. 1-Dec.31, 2015.csv 17 17\n",
      "FY 2016, 2nd Q, Jan. 1-March 31, 2016.csv 17 17\n",
      "FY 2016, 3rd Q, April 1-June 30, 2016.csv 17 17\n",
      "FY 2016, 4th Q, July 1-Sept. 30, 2016.csv 17 17\n",
      "FY 2017, 1st Q, Oct. 1-Dec. 31, 2016.csv 17 17\n",
      "FY 2017, 2nd Q, Jan. 1-March 31, 2017.csv 17 17\n",
      "FY 2017, 3rd Q, April 1-June 30, 2017.csv 17 17\n",
      "FY 2017, 4th Q, July 1-Sept. 30, 2017.csv 17 17\n",
      "FY 2018, 1st Q, Oct. 1-Dec. 31, 2017.csv 17 17\n",
      "FY 2018, 2nd Q, Jan. 1-March 31, 2018.csv 17 17\n",
      "FY 2018, 3rd Q, April 1-June 30, 2018.csv 17 17\n",
      "FY 2018, 4th Q, July 1-Sept. 30, 2018.csv 17 17\n",
      "FY 2019, 1st Q, Oct. 1-Dec. 31, 2018.csv 17 17\n",
      "FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019.csv 17 17\n"
     ]
    }
   ],
   "source": [
    "dfs = []\n",
    "\n",
    "for fname in os.listdir('data/USCIS/massaged'):\n",
    "    if 'FYs' in fname:\n",
    "        # skip the 2010-2013 CSVs for now\n",
    "        continue\n",
    "    df = pandas.read_csv('data/USCIS/massaged/' + fname, header=0, encoding=encoding)\n",
    "    # make sure whitespace is remove so all column names are exactly the same\n",
    "    df.columns = [name.strip() for name in df.columns]\n",
    "    # rename the column\n",
    "    df = df.rename(columns={'Unnamed: 1':\"field_office\"})\n",
    "    # remove the whitespace from the names\n",
    "    df['field_office'] = df.field_office.map(lambda name: name.strip() if type(name) == str else float('nan'))\n",
    "    # keep track of which quarter this is from\n",
    "    df['quarter'] = fname.split('.csv')[0]\n",
    "    # fill the state downwards so it's on each row until the next state\n",
    "    df['state'] = df[df.columns[0]].fillna(axis=0, method='ffill').map(lambda name: name.strip() if type(name) == str else float('nan'))\n",
    "    # now we can drop any rows that have no city specified\n",
    "    df.dropna(subset=['field_office'], inplace=True)\n",
    "    \n",
    "    dfs.append(df)\n",
    "    # keep track of how many columns are in each quarter's CSV and whether there are any non-unique column names (looks like there aren't)\n",
    "    print(fname, len(df.columns), len(set(df.columns)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>field_office</th>\n",
       "      <th>Applications Received2</th>\n",
       "      <th>Approved3</th>\n",
       "      <th>Denied4</th>\n",
       "      <th>Pending5</th>\n",
       "      <th>quarter</th>\n",
       "      <th>state</th>\n",
       "      <th>code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>257</td>\n",
       "      <td>316</td>\n",
       "      <td>39</td>\n",
       "      <td>365</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Phoenix</td>\n",
       "      <td>2,759</td>\n",
       "      <td>2,399</td>\n",
       "      <td>312</td>\n",
       "      <td>4,700</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>636</td>\n",
       "      <td>513</td>\n",
       "      <td>80</td>\n",
       "      <td>1,166</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Fort Smith</td>\n",
       "      <td>232</td>\n",
       "      <td>202</td>\n",
       "      <td>22</td>\n",
       "      <td>411</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>1,582</td>\n",
       "      <td>1,928</td>\n",
       "      <td>121</td>\n",
       "      <td>2,004</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>California</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>Moscow</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>Russia</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>Johannesburg</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>427</th>\n",
       "      <td>Seoul</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>Bangkok</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>London</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2288 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     field_office Applications Received2 Approved3 Denied4 Pending5  \\\n",
       "11      Anchorage                    257       316      39      365   \n",
       "15        Phoenix                  2,759     2,399     312    4,700   \n",
       "17         Tucson                    636       513      80    1,166   \n",
       "21     Fort Smith                    232       202      22      411   \n",
       "25    Chula Vista                  1,582     1,928     121    2,004   \n",
       "..            ...                    ...       ...     ...      ...   \n",
       "419        Moscow                   N/A       N/A     N/A      N/A    \n",
       "423  Johannesburg                   N/A       N/A     N/A      N/A    \n",
       "427         Seoul                   N/A       N/A     N/A      N/A    \n",
       "431       Bangkok                   N/A       N/A     N/A      N/A    \n",
       "435        London                   N/A       N/A     N/A      N/A    \n",
       "\n",
       "                                    quarter           state code  \n",
       "11   FY 2013, 4th Q, July 1- Sept. 30, 2013          Alaska  NaN  \n",
       "15   FY 2013, 4th Q, July 1- Sept. 30, 2013         Arizona  NaN  \n",
       "17   FY 2013, 4th Q, July 1- Sept. 30, 2013         Arizona  NaN  \n",
       "21   FY 2013, 4th Q, July 1- Sept. 30, 2013        Arkansas  NaN  \n",
       "25   FY 2013, 4th Q, July 1- Sept. 30, 2013      California  NaN  \n",
       "..                                      ...             ...  ...  \n",
       "419    FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019          Russia  NaN  \n",
       "423    FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019    South Africa  NaN  \n",
       "427    FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019     South Korea  NaN  \n",
       "431    FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019        Thailand  NaN  \n",
       "435    FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019  United Kingdom  NaN  \n",
       "\n",
       "[2288 rows x 8 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pandas.concat(dfs)\n",
    "# delete columns we don't care about, like military naturalizations\n",
    "del df['Unnamed: 0']\n",
    "del df['Applications Received2 .1']\n",
    "del df['Applications Received2.1']\n",
    "del df['Applications Received2.2']\n",
    "del df['Applications Received2 .2']\n",
    "del df['Approved3.1']\n",
    "del df['Approved3 .1']\n",
    "del df['Approved3 .2']\n",
    "del df['Denied4.1']\n",
    "del df['Denied4 .2']\n",
    "del df['Pending5.1']\n",
    "del df['Approved3.2']\n",
    "del df['Denied4 .1']\n",
    "del df['Denied4.2']\n",
    "del df['Pending5 .1']\n",
    "del df['Pending5 .2']\n",
    "del df['Pending5.2']\n",
    "del df['USCIS Field Office or Service Center Location']\n",
    "df.rename(columns={'Unnamed: 2':'code'}, inplace=True)\n",
    "df['code'] = df['code'].map(lambda code: code.strip() if type(code) == str else float('nan'))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make sure that the state is correct by looking at all the state,city pairs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Anchorage, Alaska\n",
      "Phoenix, Arizona\n",
      "Tucson, Arizona\n",
      "Fort Smith, Arkansas\n",
      "Chula Vista, California\n",
      "Fresno, California\n",
      "Imperial, California\n",
      "Los Angeles, California\n",
      "Los Angeles County, California\n",
      "Sacramento, California\n",
      "San Bernardino, California\n",
      "San Diego, California\n",
      "San Fernando Valley, California\n",
      "San Francisco, California\n",
      "San Jose, California\n",
      "Santa Ana, California\n",
      "Denver, Colorado\n",
      "Hartford, Connecticut\n",
      "Dover AFB, Delaware\n",
      "Washington, District of Columbia\n",
      "Hialeah, Florida\n",
      "Jacksonville, Florida\n",
      "Kendall, Florida\n",
      "Miami, Florida\n",
      "Oakland Park, Florida\n",
      "Orlando, Florida\n",
      "Tampa, Florida\n",
      "West Palm Beach, Florida\n",
      "Atlanta, Georgia\n",
      "Honolulu, Hawaii\n",
      "Boise, Idaho\n",
      "Chicago, Illinois\n",
      "Indianapolis, Indiana\n",
      "Des Moines, Iowa\n",
      "Wichita, Kansas\n",
      "Louisville, Kentucky\n",
      "New Orleans, Louisiana\n",
      "Portland, Maine\n",
      "Baltimore, Maryland\n",
      "Boston, Massachusetts\n",
      "Lawrence, Massachusetts\n",
      "Detroit, Michigan\n",
      "St. Paul, Minnesota\n",
      "Kansas City, Missouri\n",
      "St. Louis, Missouri\n",
      "Helena, Montana\n",
      "Omaha, Nebraska\n",
      "Las Vegas, Nevada\n",
      "Reno, Nevada\n",
      "Manchester, New Hampshire\n",
      "Mount Laurel, New Jersey\n",
      "Newark, New Jersey\n",
      "Albuquerque, New Mexico\n",
      "Albany, New York\n",
      "Buffalo, New York\n",
      "Long Island, New York\n",
      "New York, New York\n",
      "Queens, New York\n",
      "Charlotte, North Carolina\n",
      "Raleigh, North Carolina\n",
      "Cincinnati, Ohio\n",
      "Cleveland, Ohio\n",
      "Columbus, Ohio\n",
      "Oklahoma City, Oklahoma\n",
      "Portland, Oregon\n",
      "Philadelphia, Pennsylvania\n",
      "Pittsburgh, Pennsylvania\n",
      "Providence, Rhode Island\n",
      "Charleston, South Carolina\n",
      "Memphis, Tennessee\n",
      "Dallas, Texas\n",
      "El Paso, Texas\n",
      "Harlingen, Texas\n",
      "Houston, Texas\n",
      "San Antonio, Texas\n",
      "Salt Lake City, Utah\n",
      "St. Albans, Vermont\n",
      "Norfolk, Virginia\n",
      "Seattle, Washington\n",
      "Spokane, Washington\n",
      "Yakima, Washington\n",
      "Milwaukee, Wisconsin\n",
      "Hagatna, Guam\n",
      "Charlotte Amalie, U.S. Virgin Islands\n",
      "Christiansted, U.S. Virgin Islands\n",
      "San Juan, Puerto Rico\n",
      "Beijing, China\n",
      "Guangzhou, China\n",
      "Havana, Cuba\n",
      "Santo Domingo, Dominican Republic\n",
      "San Salvador, El Salvador\n",
      "Frankfurt, Germany\n",
      "Accra, Ghana\n",
      "Athens, Greece\n",
      "Guatemala City, Guatemala\n",
      "Port-au-Prince, Haiti\n",
      "New Delhi, India\n",
      "Rome, Italy\n",
      "Amman, Jordan\n",
      "Nairobi, Kenya\n",
      "Ciudad Juarez, Mexico\n",
      "Mexico City, Mexico\n",
      "Monterrey, Mexico\n",
      "Lima, Peru\n",
      "Manila, Philippines\n",
      "Moscow, Russia\n",
      "Johannesburg, South Africa\n",
      "Seoul, South Korea\n",
      "Bangkok, Thailand\n",
      "London, United Kingdom\n",
      "Fort Meyers, Florida\n",
      "Greer, South Carolina\n",
      "Montgomery, Alabama\n",
      "Brooklyn, New York\n",
      "Charleston, West Virginia\n",
      "Agana, Guam\n",
      "Nashville, Tennessee\n"
     ]
    }
   ],
   "source": [
    "printed = set()\n",
    "for i,row in df.iterrows():\n",
    "    full_name = f'{row.field_office}, {row.state}'\n",
    "    if full_name in printed:\n",
    "        continue\n",
    "    printed.add(full_name)\n",
    "    print(full_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Everything looks right"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add in year + FY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>field_office</th>\n",
       "      <th>Applications Received2</th>\n",
       "      <th>Approved3</th>\n",
       "      <th>Denied4</th>\n",
       "      <th>Pending5</th>\n",
       "      <th>quarter</th>\n",
       "      <th>state</th>\n",
       "      <th>code</th>\n",
       "      <th>year</th>\n",
       "      <th>fy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>New York</td>\n",
       "      <td>6,593</td>\n",
       "      <td>6,244</td>\n",
       "      <td>1,599</td>\n",
       "      <td>29,542</td>\n",
       "      <td>FY 2018, 1st Q, Oct. 1-Dec. 31, 2017</td>\n",
       "      <td>New York</td>\n",
       "      <td>NYC</td>\n",
       "      <td>2017</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>Salt Lake City</td>\n",
       "      <td>1,274</td>\n",
       "      <td>1,118</td>\n",
       "      <td>78</td>\n",
       "      <td>1,459</td>\n",
       "      <td>FY 2015, 3rd Q, April 1-June 30, 2015</td>\n",
       "      <td>Utah</td>\n",
       "      <td>SLC</td>\n",
       "      <td>2015</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>Santo Domingo</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>FY 2018, 4th Q, July 1-Sept. 30, 2018</td>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>Oklahoma City</td>\n",
       "      <td>800</td>\n",
       "      <td>287</td>\n",
       "      <td>35</td>\n",
       "      <td>2,056</td>\n",
       "      <td>FY 2014, 3rd Q, April 1-June 30, 2014</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2014</td>\n",
       "      <td>FY 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401</th>\n",
       "      <td>Amman</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>N/A</td>\n",
       "      <td>FY 2018, 1st Q, Oct. 1-Dec. 31, 2017</td>\n",
       "      <td>Jordan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       field_office Applications Received2 Approved3 Denied4 Pending5  \\\n",
       "199        New York                  6,593     6,244   1,599   29,542   \n",
       "241  Salt Lake City                  1,274     1,118      78    1,459   \n",
       "351   Santo Domingo                   N/A       N/A     N/A      N/A    \n",
       "203   Oklahoma City                    800       287      35    2,056   \n",
       "401           Amman                   N/A       N/A     N/A      N/A    \n",
       "\n",
       "                                   quarter               state code  year  \\\n",
       "199   FY 2018, 1st Q, Oct. 1-Dec. 31, 2017            New York  NYC  2017   \n",
       "241  FY 2015, 3rd Q, April 1-June 30, 2015                Utah  SLC  2015   \n",
       "351  FY 2018, 4th Q, July 1-Sept. 30, 2018  Dominican Republic  NaN  2018   \n",
       "203  FY 2014, 3rd Q, April 1-June 30, 2014            Oklahoma  NaN  2014   \n",
       "401   FY 2018, 1st Q, Oct. 1-Dec. 31, 2017              Jordan  NaN  2017   \n",
       "\n",
       "          fy  \n",
       "199  FY 2018  \n",
       "241  FY 2015  \n",
       "351  FY 2018  \n",
       "203  FY 2014  \n",
       "401  FY 2018  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['year'] = df.quarter.map(lambda q: q[-4:])\n",
    "df['fy'] = df.quarter.map(lambda q: q.split(',')[0])\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding in older CSVs\n",
    "\n",
    "First we'll need a way of looking up the city given the field office code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Anchorage', 'Alaska')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "code_to_city = {}\n",
    "code_to_state = {}\n",
    "city_to_code = {}\n",
    "for i,entry in df.dropna(subset=['code']).iterrows():\n",
    "#     print(entry.field_office, entry.code)\n",
    "    code_to_city[entry.code.strip()] = entry.field_office.strip()\n",
    "    code_to_state[entry.code.strip()] = entry.state.strip()\n",
    "    city_to_code[entry.field_office.strip()] = entry.code.strip()\n",
    "code_to_city['ANC'], code_to_state['ANC']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a few missing entries I discovered when running the code below, so I'll put them in here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# discovered a few missing entries\n",
    "code_to_city['CSC'] = float('nan')\n",
    "code_to_state['CSC'] = 'California'\n",
    "\n",
    "code_to_city['NBC'] = 'National Benefits Center'\n",
    "code_to_state['NBC'] = float('nan')\n",
    "\n",
    "code_to_city['NSC'] = 'NEBRASKA SERVICE CENTER'\n",
    "code_to_state['NSC'] = 'Nebraska'\n",
    "\n",
    "code_to_city['VSC'] = 'VERMONT SERVICE CENTER'\n",
    "code_to_state['VSC'] = 'Vermont'\n",
    "\n",
    "code_to_city['TSC'] = 'TEXAS SERVICE CENTER'\n",
    "code_to_state['TSC'] = 'Texas'\n",
    "\n",
    "# don't know what this is, and it doesn't have a label\n",
    "code_to_city['GCU'] = \"Garden City\"\n",
    "code_to_state['GCU'] = \"New York\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This opens the single CSV covering the fiscal years 2010, 2011, 2012, and 2013, cleans up and/or renames columns for concatenation with the dataframe from above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fy</th>\n",
       "      <th>code</th>\n",
       "      <th>Description</th>\n",
       "      <th>Receipts</th>\n",
       "      <th>Approvals</th>\n",
       "      <th>Denials</th>\n",
       "      <th>Pending</th>\n",
       "      <th>Completions</th>\n",
       "      <th>state</th>\n",
       "      <th>field_office</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FY 2010</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>ALBUQUERQUE NM</td>\n",
       "      <td>1,657</td>\n",
       "      <td>1,560</td>\n",
       "      <td>75</td>\n",
       "      <td>604</td>\n",
       "      <td>1,657</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>Albuquerque</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FY 2010</td>\n",
       "      <td>AGA</td>\n",
       "      <td>AGANA GUAM</td>\n",
       "      <td>672</td>\n",
       "      <td>650</td>\n",
       "      <td>55</td>\n",
       "      <td>304</td>\n",
       "      <td>765</td>\n",
       "      <td>Guam</td>\n",
       "      <td>Agana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FY 2010</td>\n",
       "      <td>ALB</td>\n",
       "      <td>ALBANY NY</td>\n",
       "      <td>2,018</td>\n",
       "      <td>1,809</td>\n",
       "      <td>123</td>\n",
       "      <td>774</td>\n",
       "      <td>1,936</td>\n",
       "      <td>New York</td>\n",
       "      <td>Albany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FY 2010</td>\n",
       "      <td>ANC</td>\n",
       "      <td>ANCHORAGE AK</td>\n",
       "      <td>956</td>\n",
       "      <td>775</td>\n",
       "      <td>98</td>\n",
       "      <td>385</td>\n",
       "      <td>912</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Anchorage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FY 2010</td>\n",
       "      <td>ATL</td>\n",
       "      <td>ATLANTA GA</td>\n",
       "      <td>21,342</td>\n",
       "      <td>19,368</td>\n",
       "      <td>1,766</td>\n",
       "      <td>8,938</td>\n",
       "      <td>21,937</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>Atlanta</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699</th>\n",
       "      <td>FY 2013</td>\n",
       "      <td>TUC</td>\n",
       "      <td>TUCSON AZ/SUB</td>\n",
       "      <td>496</td>\n",
       "      <td>554</td>\n",
       "      <td>102</td>\n",
       "      <td>970</td>\n",
       "      <td>666</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Tucson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>701</th>\n",
       "      <td>FY 2013</td>\n",
       "      <td>WAS</td>\n",
       "      <td>WASHINGTON DC</td>\n",
       "      <td>3,988</td>\n",
       "      <td>3,955</td>\n",
       "      <td>368</td>\n",
       "      <td>9,426</td>\n",
       "      <td>4,342</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>703</th>\n",
       "      <td>FY 2013</td>\n",
       "      <td>WIC</td>\n",
       "      <td>WICHITA KS</td>\n",
       "      <td>258</td>\n",
       "      <td>178</td>\n",
       "      <td>31</td>\n",
       "      <td>543</td>\n",
       "      <td>210</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>Wichita</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>FY 2013</td>\n",
       "      <td>WPB</td>\n",
       "      <td>WEST PALM BEACH FL</td>\n",
       "      <td>2,631</td>\n",
       "      <td>2,790</td>\n",
       "      <td>574</td>\n",
       "      <td>6,182</td>\n",
       "      <td>3,376</td>\n",
       "      <td>Florida</td>\n",
       "      <td>West Palm Beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>FY 2013</td>\n",
       "      <td>YAK</td>\n",
       "      <td>YAKIMA WA</td>\n",
       "      <td>313</td>\n",
       "      <td>251</td>\n",
       "      <td>20</td>\n",
       "      <td>344</td>\n",
       "      <td>271</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Yakima</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>351 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          fy code         Description Receipts Approvals Denials Pending  \\\n",
       "1    FY 2010  ABQ     ALBUQUERQUE NM     1,657     1,560      75     604   \n",
       "3    FY 2010  AGA         AGANA GUAM       672       650      55     304   \n",
       "5    FY 2010  ALB          ALBANY NY     2,018     1,809     123     774   \n",
       "7    FY 2010  ANC       ANCHORAGE AK       956       775      98     385   \n",
       "9    FY 2010  ATL         ATLANTA GA    21,342    19,368   1,766   8,938   \n",
       "..       ...  ...                 ...      ...       ...     ...     ...   \n",
       "699  FY 2013  TUC      TUCSON AZ/SUB       496       554     102     970   \n",
       "701  FY 2013  WAS      WASHINGTON DC     3,988     3,955     368   9,426   \n",
       "703  FY 2013  WIC          WICHITA KS      258       178      31     543   \n",
       "705  FY 2013  WPB  WEST PALM BEACH FL    2,631     2,790     574   6,182   \n",
       "707  FY 2013  YAK           YAKIMA WA      313       251      20     344   \n",
       "\n",
       "    Completions                 state     field_office  \n",
       "1         1,657            New Mexico      Albuquerque  \n",
       "3           765                  Guam            Agana  \n",
       "5         1,936              New York           Albany  \n",
       "7           912                Alaska        Anchorage  \n",
       "9        21,937               Georgia          Atlanta  \n",
       "..          ...                   ...              ...  \n",
       "699         666               Arizona           Tucson  \n",
       "701       4,342  District of Columbia       Washington  \n",
       "703         210                Kansas          Wichita  \n",
       "705       3,376               Florida  West Palm Beach  \n",
       "707         271            Washington           Yakima  \n",
       "\n",
       "[351 rows x 10 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "field_office_code = 'code'\n",
    "\n",
    "df2 = pandas.read_csv('data/USCIS/massaged/FYs 2010-2013, Oct. 2009-Dec. 2012.csv', encoding=encoding)\n",
    "df2.dropna(subset=[field_office_code], inplace=True)\n",
    "df2['state'] = df2[field_office_code].map(lambda code: code_to_state[code])\n",
    "df2['field_office'] = df2[field_office_code].map(lambda code: code_to_city[code])\n",
    "df2['Fiscal Year'].fillna(method='ffill', inplace=True)\n",
    "df2.rename(columns={'Fiscal Year':'fy'}, inplace=True)\n",
    "df2['fy'] = df2.fy.map(lambda y: 'FY ' + y)\n",
    "\n",
    "for col in [' Receipts .1', ' Approvals .1', ' Denials .1',\n",
    "       ' Pending .1', ' Receipts .2', ' Pending .2']:\n",
    "    del df2[col]\n",
    "df2.columns = [name.strip() for name in df2.columns]\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we combine these, let's make sure there's a code on each row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['code'] = df.field_office.map(lambda city: city_to_code[city] if city in city_to_code else float('nan'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>field_office</th>\n",
       "      <th>Applications Received2</th>\n",
       "      <th>Approved3</th>\n",
       "      <th>Denied4</th>\n",
       "      <th>Pending5</th>\n",
       "      <th>quarter</th>\n",
       "      <th>state</th>\n",
       "      <th>code</th>\n",
       "      <th>year</th>\n",
       "      <th>fy</th>\n",
       "      <th>Description</th>\n",
       "      <th>Receipts</th>\n",
       "      <th>Approvals</th>\n",
       "      <th>Denials</th>\n",
       "      <th>Pending</th>\n",
       "      <th>Completions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2010</td>\n",
       "      <td>ALBUQUERQUE NM</td>\n",
       "      <td>1,657</td>\n",
       "      <td>1,560</td>\n",
       "      <td>75</td>\n",
       "      <td>604</td>\n",
       "      <td>1,657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Providence</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>PRO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2010</td>\n",
       "      <td>PROVIDENCE RI</td>\n",
       "      <td>2,510</td>\n",
       "      <td>2,053</td>\n",
       "      <td>189</td>\n",
       "      <td>949</td>\n",
       "      <td>2,272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Portland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>POO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2010</td>\n",
       "      <td>PORTLAND OR</td>\n",
       "      <td>6,040</td>\n",
       "      <td>5,312</td>\n",
       "      <td>311</td>\n",
       "      <td>2,341</td>\n",
       "      <td>5,659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Portland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Maine</td>\n",
       "      <td>POM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2010</td>\n",
       "      <td>PORTLAND ME</td>\n",
       "      <td>888</td>\n",
       "      <td>822</td>\n",
       "      <td>62</td>\n",
       "      <td>245</td>\n",
       "      <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>PIT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2010</td>\n",
       "      <td>PITTSBURGH PA</td>\n",
       "      <td>2,509</td>\n",
       "      <td>2,445</td>\n",
       "      <td>70</td>\n",
       "      <td>976</td>\n",
       "      <td>2,534</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Boise</td>\n",
       "      <td>463</td>\n",
       "      <td>366</td>\n",
       "      <td>64</td>\n",
       "      <td>695</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>Idaho</td>\n",
       "      <td>BOI</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2019</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Honolulu</td>\n",
       "      <td>851</td>\n",
       "      <td>935</td>\n",
       "      <td>146</td>\n",
       "      <td>3,446</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>HHW</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2019</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4,861</td>\n",
       "      <td>5,539</td>\n",
       "      <td>391</td>\n",
       "      <td>22,458</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2019</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Tampa</td>\n",
       "      <td>2,989</td>\n",
       "      <td>3,456</td>\n",
       "      <td>533</td>\n",
       "      <td>4,742</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>Florida</td>\n",
       "      <td>TAM</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2019</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Des Moines</td>\n",
       "      <td>901</td>\n",
       "      <td>653</td>\n",
       "      <td>61</td>\n",
       "      <td>3,875</td>\n",
       "      <td>FY 2019, 1st Q, Oct. 1-Dec. 31, 2018</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>DSM</td>\n",
       "      <td>2018</td>\n",
       "      <td>FY 2019</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2639 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    field_office Applications Received2 Approved3 Denied4 Pending5  \\\n",
       "1    Albuquerque                    NaN       NaN     NaN      NaN   \n",
       "125   Providence                    NaN       NaN     NaN      NaN   \n",
       "123     Portland                    NaN       NaN     NaN      NaN   \n",
       "121     Portland                    NaN       NaN     NaN      NaN   \n",
       "119   Pittsburgh                    NaN       NaN     NaN      NaN   \n",
       "..           ...                    ...       ...     ...      ...   \n",
       "97         Boise                    463       366      64      695   \n",
       "93      Honolulu                    851       935     146    3,446   \n",
       "89       Atlanta                  4,861     5,539     391   22,458   \n",
       "83         Tampa                  2,989     3,456     533    4,742   \n",
       "109   Des Moines                    901       653      61    3,875   \n",
       "\n",
       "                                  quarter         state code  year       fy  \\\n",
       "1                                     NaN    New Mexico  ABQ   NaN  FY 2010   \n",
       "125                                   NaN  Rhode Island  PRO   NaN  FY 2010   \n",
       "123                                   NaN        Oregon  POO   NaN  FY 2010   \n",
       "121                                   NaN         Maine  POM   NaN  FY 2010   \n",
       "119                                   NaN  Pennsylvania  PIT   NaN  FY 2010   \n",
       "..                                    ...           ...  ...   ...      ...   \n",
       "97   FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019         Idaho  BOI  2019  FY 2019   \n",
       "93   FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019        Hawaii  HHW  2019  FY 2019   \n",
       "89   FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019       Georgia  ATL  2019  FY 2019   \n",
       "83   FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019       Florida  TAM  2019  FY 2019   \n",
       "109  FY 2019, 1st Q, Oct. 1-Dec. 31, 2018          Iowa  DSM  2018  FY 2019   \n",
       "\n",
       "         Description Receipts Approvals Denials Pending Completions  \n",
       "1    ALBUQUERQUE NM     1,657     1,560      75     604       1,657  \n",
       "125   PROVIDENCE RI     2,510     2,053     189     949       2,272  \n",
       "123     PORTLAND OR     6,040     5,312     311   2,341       5,659  \n",
       "121     PORTLAND ME       888       822      62     245         900  \n",
       "119   PITTSBURGH PA     2,509     2,445      70     976       2,534  \n",
       "..               ...      ...       ...     ...     ...         ...  \n",
       "97               NaN      NaN       NaN     NaN     NaN         NaN  \n",
       "93               NaN      NaN       NaN     NaN     NaN         NaN  \n",
       "89               NaN      NaN       NaN     NaN     NaN         NaN  \n",
       "83               NaN      NaN       NaN     NaN     NaN         NaN  \n",
       "109              NaN      NaN       NaN     NaN     NaN         NaN  \n",
       "\n",
       "[2639 rows x 16 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pandas.concat([df, df2])\n",
    "df3.sort_values(by='fy')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have columns with the same name that could be converted to numeric values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_columns(_df, col1, col2):\n",
    "    return pandas.to_numeric(_df[col1].fillna('0').str.replace(',',''), errors='coerce') + pandas.to_numeric(_df[col2].fillna('0').str.replace(',',''), errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['Applications Received2'] = add_columns(df3, 'Applications Received2', 'Receipts')\n",
    "df3['Approved3'] = add_columns(df3, 'Approved3', 'Approvals')\n",
    "df3['Denied4'] = add_columns(df3, 'Denied4', 'Denials')\n",
    "df3['Pending5'] = add_columns(df3, 'Pending5', 'Pending')\n",
    "\n",
    "del df3['Receipts']\n",
    "del df3['Approvals']\n",
    "del df3['Denials']\n",
    "del df3['Pending']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data transitions from yearly to quarterly in 2013, and it's not immediately clear if there's overlap or not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>field_office</th>\n",
       "      <th>Applications Received2</th>\n",
       "      <th>Approved3</th>\n",
       "      <th>Denied4</th>\n",
       "      <th>Pending5</th>\n",
       "      <th>quarter</th>\n",
       "      <th>state</th>\n",
       "      <th>code</th>\n",
       "      <th>year</th>\n",
       "      <th>fy</th>\n",
       "      <th>Description</th>\n",
       "      <th>Completions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>257.0</td>\n",
       "      <td>316.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>365.0</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>ANC</td>\n",
       "      <td>2013</td>\n",
       "      <td>FY 2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>258.0</td>\n",
       "      <td>287.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>368.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>ANC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2013</td>\n",
       "      <td>ANCHORAGE AK</td>\n",
       "      <td>349</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    field_office  Applications Received2  Approved3  Denied4  Pending5  \\\n",
       "11     Anchorage                   257.0      316.0     39.0     365.0   \n",
       "537    Anchorage                   258.0      287.0     52.0     368.0   \n",
       "\n",
       "                                    quarter   state code  year       fy  \\\n",
       "11   FY 2013, 4th Q, July 1- Sept. 30, 2013  Alaska  ANC  2013  FY 2013   \n",
       "537                                     NaN  Alaska  ANC   NaN  FY 2013   \n",
       "\n",
       "       Description Completions  \n",
       "11             NaN         NaN  \n",
       "537  ANCHORAGE AK          349  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3[(df3.fy == 'FY 2013') & (df3.code == 'ANC')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>field_office</th>\n",
       "      <th>Applications Received2</th>\n",
       "      <th>Approved3</th>\n",
       "      <th>Denied4</th>\n",
       "      <th>Pending5</th>\n",
       "      <th>quarter</th>\n",
       "      <th>state</th>\n",
       "      <th>code</th>\n",
       "      <th>year</th>\n",
       "      <th>fy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Omaha</td>\n",
       "      <td>688</td>\n",
       "      <td>555</td>\n",
       "      <td>59</td>\n",
       "      <td>1,044</td>\n",
       "      <td>FY 2015, 1st Q, Oct. 1-Dec. 31, 2014</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>OMA</td>\n",
       "      <td>2014</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>Long Island</td>\n",
       "      <td>3,639</td>\n",
       "      <td>4,513</td>\n",
       "      <td>499</td>\n",
       "      <td>12,306</td>\n",
       "      <td>FY 2018, 1st Q, Oct. 1-Dec. 31, 2017</td>\n",
       "      <td>New York</td>\n",
       "      <td>LNY</td>\n",
       "      <td>2017</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>Salt Lake City</td>\n",
       "      <td>907</td>\n",
       "      <td>972</td>\n",
       "      <td>88</td>\n",
       "      <td>2,398</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>Utah</td>\n",
       "      <td>SLC</td>\n",
       "      <td>2013</td>\n",
       "      <td>FY 2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>1,207</td>\n",
       "      <td>320</td>\n",
       "      <td>75</td>\n",
       "      <td>3,034</td>\n",
       "      <td>FY 2017, 1st Q, Oct. 1-Dec. 31, 2016</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>MIL</td>\n",
       "      <td>2016</td>\n",
       "      <td>FY 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>311</td>\n",
       "      <td>215</td>\n",
       "      <td>35</td>\n",
       "      <td>501</td>\n",
       "      <td>FY 2015, 1st Q, Oct. 1-Dec. 31, 2014</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>ANC</td>\n",
       "      <td>2014</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       field_office Applications Received2 Approved3 Denied4 Pending5  \\\n",
       "153           Omaha                    688       555      59    1,044   \n",
       "197     Long Island                  3,639     4,513     499   12,306   \n",
       "241  Salt Lake City                    907       972      88    2,398   \n",
       "265       Milwaukee                  1,207       320      75    3,034   \n",
       "11        Anchorage                    311       215      35      501   \n",
       "\n",
       "                                    quarter      state code  year       fy  \n",
       "153    FY 2015, 1st Q, Oct. 1-Dec. 31, 2014   Nebraska  OMA  2014  FY 2015  \n",
       "197    FY 2018, 1st Q, Oct. 1-Dec. 31, 2017   New York  LNY  2017  FY 2018  \n",
       "241  FY 2013, 4th Q, July 1- Sept. 30, 2013       Utah  SLC  2013  FY 2013  \n",
       "265    FY 2017, 1st Q, Oct. 1-Dec. 31, 2016  Wisconsin  MIL  2016  FY 2017  \n",
       "11     FY 2015, 1st Q, Oct. 1-Dec. 31, 2014     Alaska  ANC  2014  FY 2015  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>field_office</th>\n",
       "      <th>Applications Received2</th>\n",
       "      <th>Approved3</th>\n",
       "      <th>Denied4</th>\n",
       "      <th>Pending5</th>\n",
       "      <th>quarter</th>\n",
       "      <th>state</th>\n",
       "      <th>code</th>\n",
       "      <th>year</th>\n",
       "      <th>fy</th>\n",
       "      <th>Description</th>\n",
       "      <th>Completions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Louisville</td>\n",
       "      <td>711.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>961.0</td>\n",
       "      <td>FY 2015, 1st Q, Oct. 1-Dec. 31, 2014</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>LOU</td>\n",
       "      <td>2014</td>\n",
       "      <td>FY 2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>387.0</td>\n",
       "      <td>258.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>626.0</td>\n",
       "      <td>FY 2016, 2nd Q, Jan. 1-March 31, 2016</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2016</td>\n",
       "      <td>FY 2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Jacksonville</td>\n",
       "      <td>970.0</td>\n",
       "      <td>621.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>FY 2015, 1st Q, Oct. 1-Dec. 31, 2014</td>\n",
       "      <td>Florida</td>\n",
       "      <td>JAC</td>\n",
       "      <td>2014</td>\n",
       "      <td>FY 2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>Beijing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2016, 4th Q, July 1-Sept. 30, 2016</td>\n",
       "      <td>China</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016</td>\n",
       "      <td>FY 2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>San Fernando Valley</td>\n",
       "      <td>3412.0</td>\n",
       "      <td>2492.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>6992.0</td>\n",
       "      <td>FY 2016, 1st Q, Oct. 1-Dec.31, 2015</td>\n",
       "      <td>California</td>\n",
       "      <td>SFV</td>\n",
       "      <td>2015</td>\n",
       "      <td>FY 2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            field_office  Applications Received2  Approved3  Denied4  \\\n",
       "113           Louisville                   711.0      608.0    113.0   \n",
       "29              Imperial                   387.0      258.0     61.0   \n",
       "69          Jacksonville                   970.0      621.0     77.0   \n",
       "331              Beijing                     NaN        NaN      NaN   \n",
       "41   San Fernando Valley                  3412.0     2492.0    221.0   \n",
       "\n",
       "     Pending5                                quarter       state code  year  \\\n",
       "113     961.0   FY 2015, 1st Q, Oct. 1-Dec. 31, 2014    Kentucky  LOU  2014   \n",
       "29      626.0  FY 2016, 2nd Q, Jan. 1-March 31, 2016  California  IMP  2016   \n",
       "69     1963.0   FY 2015, 1st Q, Oct. 1-Dec. 31, 2014     Florida  JAC  2014   \n",
       "331       NaN  FY 2016, 4th Q, July 1-Sept. 30, 2016       China  NaN  2016   \n",
       "41     6992.0    FY 2016, 1st Q, Oct. 1-Dec.31, 2015  California  SFV  2015   \n",
       "\n",
       "          fy Description Completions  \n",
       "113  FY 2015         NaN         NaN  \n",
       "29   FY 2016         NaN         NaN  \n",
       "69   FY 2015         NaN         NaN  \n",
       "331  FY 2016         NaN         NaN  \n",
       "41   FY 2016         NaN         NaN  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df3['Description']\n",
    "del df3['Completions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2639, 2288)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df3), len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_csv('data/first_pass.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}