concat_excel_quarters.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "q_sheet_to_name = {\n",
    "    \"fy2019_q4\": \"FY 2019, 4th Q, July 1- Sept. 30, 2019\",\n",
    "    \"fy2019_q3\": \"FY 2019, 3rd Q, April 1 - June 30, 2019\",\n",
    "    \"fy2020_q1\": \"FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019\",\n",
    "    \"fy2020_q2_values\": \"FY 2020, 2nd Q, Jan. 1 - March 31 2020\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>state</th>\n",
       "      <th>field_office</th>\n",
       "      <th>code</th>\n",
       "      <th>Applications Received2</th>\n",
       "      <th>Approved3</th>\n",
       "      <th>Denied4</th>\n",
       "      <th>Pending5</th>\n",
       "      <th>quarter</th>\n",
       "      <th>year</th>\n",
       "      <th>fy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>CLT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019, 3rd Q, April 1 - June 30, 2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Florida</td>\n",
       "      <td>Kendall</td>\n",
       "      <td>KND</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019, 3rd Q, April 1 - June 30, 2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>California</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>SBD</td>\n",
       "      <td>4835</td>\n",
       "      <td>5023</td>\n",
       "      <td>562</td>\n",
       "      <td>11225</td>\n",
       "      <td>FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>Boise</td>\n",
       "      <td>BOI</td>\n",
       "      <td>4080</td>\n",
       "      <td>4852</td>\n",
       "      <td>684</td>\n",
       "      <td>8272</td>\n",
       "      <td>FY 2019, 3rd Q, April 1 - June 30, 2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>Kansas City</td>\n",
       "      <td>KAN</td>\n",
       "      <td>1269</td>\n",
       "      <td>1498</td>\n",
       "      <td>95</td>\n",
       "      <td>1683</td>\n",
       "      <td>FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Washington</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>SEA</td>\n",
       "      <td>4603</td>\n",
       "      <td>2539</td>\n",
       "      <td>273</td>\n",
       "      <td>18639</td>\n",
       "      <td>FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>FY 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              state    field_office code Applications Received2 Approved3  \\\n",
       "95   North Carolina       Charlotte  CLT                    NaN       NaN   \n",
       "35          Florida         Kendall  KND                    NaN       NaN   \n",
       "16       California  San Bernardino  SBD                   4835      5023   \n",
       "46            Idaho           Boise  BOI                   4080      4852   \n",
       "70         Missouri     Kansas City  KAN                   1269      1498   \n",
       "128      Washington         Seattle  SEA                   4603      2539   \n",
       "\n",
       "    Denied4 Pending5                                  quarter  year       fy  \n",
       "95      NaN      NaN  FY 2019, 3rd Q, April 1 - June 30, 2019  2019  FY 2019  \n",
       "35      NaN      NaN  FY 2019, 3rd Q, April 1 - June 30, 2019  2019  FY 2019  \n",
       "16      562    11225   FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019  2019  FY 2020  \n",
       "46      684     8272  FY 2019, 3rd Q, April 1 - June 30, 2019  2019  FY 2019  \n",
       "70       95     1683   FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019  2019  FY 2020  \n",
       "128     273    18639   FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019  2019  FY 2020  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs = []\n",
    "for sheet_name, quarter in q_sheet_to_name.items():\n",
    "    df = pandas.read_excel('data/USCIS/Naturalizations by field office.xlsx', sheet_name=sheet_name, \n",
    "                           skiprows=8, usecols=\"A:G\")\n",
    "    df.columns = ['state', 'field_office', 'code', \n",
    "                  'Applications Received2', 'Approved3', 'Denied4', 'Pending5']\n",
    "\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",
    "    df = df.rename(columns={'Unnamed: 1':\"field_office\"})\n",
    "    df['field_office'] = df.field_office.map(lambda name: name.strip() if type(name) == str else float('nan'))\n",
    "    df['quarter'] = quarter\n",
    "    df['year'] = df.quarter.map(lambda q: q[-4:])\n",
    "    df['fy'] = df.quarter.map(lambda q: q.split(',')[0])\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",
    "    \n",
    "    dfs.append(df.dropna(subset=['field_office']))\n",
    "pandas.concat(dfs).sample(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>fy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Columbus</td>\n",
       "      <td>4280</td>\n",
       "      <td>4346</td>\n",
       "      <td>225</td>\n",
       "      <td>16802</td>\n",
       "      <td>FY 2019, 3rd Q, April 1 - June 30, 2019</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>CLM</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Columbus</td>\n",
       "      <td>1731</td>\n",
       "      <td>1665</td>\n",
       "      <td>152</td>\n",
       "      <td>2737</td>\n",
       "      <td>FY 2019, 4th Q, July 1- Sept. 30, 2019</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>CLM</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>Ciudad Juarez</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019, 4th Q, July 1- Sept. 30, 2019</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      field_office Applications Received2 Approved3 Denied4 Pending5  \\\n",
       "100       Columbus                   4280      4346     225    16802   \n",
       "99        Columbus                   1731      1665     152     2737   \n",
       "181  Ciudad Juarez                    NaN       NaN     NaN      NaN   \n",
       "\n",
       "                                     quarter   state code       fy  \n",
       "100  FY 2019, 3rd Q, April 1 - June 30, 2019    Ohio  CLM  FY 2019  \n",
       "99    FY 2019, 4th Q, July 1- Sept. 30, 2019    Ohio  CLM  FY 2019  \n",
       "181   FY 2019, 4th Q, July 1- Sept. 30, 2019  Mexico  NaN  FY 2019  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_data = pandas.concat(dfs)[['field_office',  'Applications Received2', 'Approved3', \n",
    "                    'Denied4', 'Pending5', 'quarter', 'state', 'code', 'fy']]\n",
    "new_data.sample(3)"
   ]
  },
  {
   "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>fy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>561</td>\n",
       "      <td>390</td>\n",
       "      <td>91</td>\n",
       "      <td>935</td>\n",
       "      <td>FY 2019, 4th Q, July 1- Sept. 30, 2019</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019, 3rd Q, April 1 - June 30, 2019</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>561</td>\n",
       "      <td>390</td>\n",
       "      <td>91</td>\n",
       "      <td>935</td>\n",
       "      <td>FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>FY 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>477</td>\n",
       "      <td>320</td>\n",
       "      <td>115</td>\n",
       "      <td>1296</td>\n",
       "      <td>FY 2020, 2nd Q, Jan. 1 - March 31 2020</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>FY 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   field_office Applications Received2 Approved3 Denied4 Pending5  \\\n",
       "12     Imperial                    561       390      91      935   \n",
       "13     Imperial                    NaN       NaN     NaN      NaN   \n",
       "12     Imperial                    561       390      91      935   \n",
       "10     Imperial                    477       320     115     1296   \n",
       "\n",
       "                                    quarter       state code       fy  \n",
       "12   FY 2019, 4th Q, July 1- Sept. 30, 2019  California  IMP  FY 2019  \n",
       "13  FY 2019, 3rd Q, April 1 - June 30, 2019  California  IMP  FY 2019  \n",
       "12   FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019  California  IMP  FY 2020  \n",
       "10   FY 2020, 2nd Q, Jan. 1 - March 31 2020  California  IMP  FY 2020  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_data[new_data['code'] == 'IMP']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### def try_convert(val):\n",
    "    try:\n",
    "        return float(val)\n",
    "    except:\n",
    "        return float(0)\n",
    "for col_name in ['Applications Received2', 'Approved3', 'Denied4', 'Pending5']:\n",
    "    new_data[col_name] = new_data[col_name].map(lambda val: try_convert(val))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Concat in any PDFs converted to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "master_df = pandas.read_csv('data/first_pass.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>29</th>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2018, 3rd Q, April 1-June 30, 2018</td>\n",
       "      <td>California</td>\n",
       "      <td>CVC</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Sacramento</td>\n",
       "      <td>3427.0</td>\n",
       "      <td>2507.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>4183.0</td>\n",
       "      <td>FY 2015, 2nd Q, Jan. 1-March 31, 2015</td>\n",
       "      <td>California</td>\n",
       "      <td>SAC</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>Houston</td>\n",
       "      <td>5907.0</td>\n",
       "      <td>4204.0</td>\n",
       "      <td>312.0</td>\n",
       "      <td>17379.0</td>\n",
       "      <td>FY 2014, 4th Q, July 1-Sept. 30, 2014</td>\n",
       "      <td>Texas</td>\n",
       "      <td>HOU</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FY 2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    field_office  Applications Received2  Approved3  Denied4  Pending5  \\\n",
       "29   Chula Vista                     NaN        NaN      NaN       NaN   \n",
       "35    Sacramento                  3427.0     2507.0    264.0    4183.0   \n",
       "235      Houston                  5907.0     4204.0    312.0   17379.0   \n",
       "\n",
       "                                   quarter       state code    year       fy  \n",
       "29   FY 2018, 3rd Q, April 1-June 30, 2018  California  CVC  2018.0  FY 2018  \n",
       "35   FY 2015, 2nd Q, Jan. 1-March 31, 2015  California  SAC  2015.0  FY 2015  \n",
       "235  FY 2014, 4th Q, July 1-Sept. 30, 2014       Texas  HOU  2014.0  FY 2014  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_df.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_master = pandas.concat([master_df, new_data])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2639, 439, 3078)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(master_df), len(new_data), len(new_master)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_master.to_csv('data/master_df.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>408</td>\n",
       "      <td>611</td>\n",
       "      <td>121</td>\n",
       "      <td>810</td>\n",
       "      <td>FY 2013, 4th Q, July 1- Sept. 30, 2013</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>FY 2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>321</td>\n",
       "      <td>517</td>\n",
       "      <td>66</td>\n",
       "      <td>550</td>\n",
       "      <td>FY 2014, 1st Q, Oct. 1-Dec. 31, 2013</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>FY 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>357</td>\n",
       "      <td>337</td>\n",
       "      <td>53</td>\n",
       "      <td>546</td>\n",
       "      <td>FY 2014, 2nd Q, Jan. 1-March 31, 2014</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FY 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>458</td>\n",
       "      <td>328</td>\n",
       "      <td>51</td>\n",
       "      <td>622</td>\n",
       "      <td>FY 2014, 3rd Q, April 1-June 30, 2014</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FY 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>299</td>\n",
       "      <td>400</td>\n",
       "      <td>81</td>\n",
       "      <td>421</td>\n",
       "      <td>FY 2014, 4th Q, July 1-Sept. 30, 2014</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FY 2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>231</td>\n",
       "      <td>241</td>\n",
       "      <td>71</td>\n",
       "      <td>340</td>\n",
       "      <td>FY 2015, 1st Q, Oct. 1-Dec. 31, 2014</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>314</td>\n",
       "      <td>215</td>\n",
       "      <td>42</td>\n",
       "      <td>399</td>\n",
       "      <td>FY 2015, 2nd Q, Jan. 1-March 31, 2015</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>378</td>\n",
       "      <td>217</td>\n",
       "      <td>53</td>\n",
       "      <td>508</td>\n",
       "      <td>FY 2015, 3rd Q, April 1-June 30, 2015</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>298</td>\n",
       "      <td>307</td>\n",
       "      <td>46</td>\n",
       "      <td>476</td>\n",
       "      <td>FY 2015, 4th Q, July 1-Sept. 30, 2015</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>FY 2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>363</td>\n",
       "      <td>264</td>\n",
       "      <td>37</td>\n",
       "      <td>557</td>\n",
       "      <td>FY 2016, 1st Q, Oct. 1-Dec.31, 2015</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>FY 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>387</td>\n",
       "      <td>258</td>\n",
       "      <td>61</td>\n",
       "      <td>626</td>\n",
       "      <td>FY 2016, 2nd Q, Jan. 1-March 31, 2016</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>FY 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>582</td>\n",
       "      <td>356</td>\n",
       "      <td>30</td>\n",
       "      <td>843</td>\n",
       "      <td>FY 2016, 3rd Q, April 1-June 30, 2016</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>FY 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>535</td>\n",
       "      <td>336</td>\n",
       "      <td>45</td>\n",
       "      <td>995</td>\n",
       "      <td>FY 2016, 4th Q, July 1-Sept. 30, 2016</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>FY 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>573</td>\n",
       "      <td>218</td>\n",
       "      <td>61</td>\n",
       "      <td>1285</td>\n",
       "      <td>FY 2017, 1st Q, Oct. 1-Dec. 31, 2016</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>FY 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>491</td>\n",
       "      <td>474</td>\n",
       "      <td>98</td>\n",
       "      <td>1203</td>\n",
       "      <td>FY 2017, 2nd Q, Jan. 1-March 31, 2017</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>FY 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>561</td>\n",
       "      <td>429</td>\n",
       "      <td>104</td>\n",
       "      <td>1228</td>\n",
       "      <td>FY 2017, 3rd Q, April 1-June 30, 2017</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>FY 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>305</td>\n",
       "      <td>425</td>\n",
       "      <td>87</td>\n",
       "      <td>1023</td>\n",
       "      <td>FY 2017, 4th Q, July 1-Sept. 30, 2017</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>FY 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>318</td>\n",
       "      <td>344</td>\n",
       "      <td>107</td>\n",
       "      <td>890</td>\n",
       "      <td>FY 2018, 1st Q, Oct. 1-Dec. 31, 2017</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>248</td>\n",
       "      <td>233</td>\n",
       "      <td>58</td>\n",
       "      <td>825</td>\n",
       "      <td>FY 2018, 2nd Q, Jan. 1-March 31, 2018</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>592</td>\n",
       "      <td>292</td>\n",
       "      <td>69</td>\n",
       "      <td>541</td>\n",
       "      <td>FY 2018, 3rd Q, April 1-June 30, 2018</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>346</td>\n",
       "      <td>410</td>\n",
       "      <td>142</td>\n",
       "      <td>1229</td>\n",
       "      <td>FY 2018, 4th Q, July 1-Sept. 30, 2018</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>FY 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>367</td>\n",
       "      <td>239</td>\n",
       "      <td>122</td>\n",
       "      <td>1250</td>\n",
       "      <td>FY 2019, 1st Q, Oct. 1-Dec. 31, 2018</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>412</td>\n",
       "      <td>384</td>\n",
       "      <td>194</td>\n",
       "      <td>1095</td>\n",
       "      <td>FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>762</td>\n",
       "      <td>677</td>\n",
       "      <td>52</td>\n",
       "      <td>297</td>\n",
       "      <td>NaN</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>1174</td>\n",
       "      <td>963</td>\n",
       "      <td>29</td>\n",
       "      <td>391</td>\n",
       "      <td>NaN</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>421</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>3232</td>\n",
       "      <td>1642</td>\n",
       "      <td>86</td>\n",
       "      <td>1817</td>\n",
       "      <td>NaN</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>601</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>925</td>\n",
       "      <td>670</td>\n",
       "      <td>72</td>\n",
       "      <td>1936</td>\n",
       "      <td>NaN</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>561</td>\n",
       "      <td>390</td>\n",
       "      <td>91</td>\n",
       "      <td>935</td>\n",
       "      <td>FY 2019, 4th Q, July 1- Sept. 30, 2019</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019, 3rd Q, April 1 - June 30, 2019</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>561</td>\n",
       "      <td>390</td>\n",
       "      <td>91</td>\n",
       "      <td>935</td>\n",
       "      <td>FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Imperial</td>\n",
       "      <td>477</td>\n",
       "      <td>320</td>\n",
       "      <td>115</td>\n",
       "      <td>1296</td>\n",
       "      <td>FY 2020, 2nd Q, Jan. 1 - March 31 2020</td>\n",
       "      <td>California</td>\n",
       "      <td>IMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FY 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    field_office Applications Received2 Approved3 Denied4 Pending5  \\\n",
       "29      Imperial                    408       611     121      810   \n",
       "29      Imperial                    321       517      66      550   \n",
       "29      Imperial                    357       337      53      546   \n",
       "29      Imperial                    458       328      51      622   \n",
       "29      Imperial                    299       400      81      421   \n",
       "29      Imperial                    231       241      71      340   \n",
       "29      Imperial                    314       215      42      399   \n",
       "29      Imperial                    378       217      53      508   \n",
       "29      Imperial                    298       307      46      476   \n",
       "29      Imperial                    363       264      37      557   \n",
       "29      Imperial                    387       258      61      626   \n",
       "29      Imperial                    582       356      30      843   \n",
       "29      Imperial                    535       336      45      995   \n",
       "29      Imperial                    573       218      61     1285   \n",
       "29      Imperial                    491       474      98     1203   \n",
       "33      Imperial                    561       429     104     1228   \n",
       "33      Imperial                    305       425      87     1023   \n",
       "33      Imperial                    318       344     107      890   \n",
       "33      Imperial                    248       233      58      825   \n",
       "33      Imperial                    592       292      69      541   \n",
       "33      Imperial                    346       410     142     1229   \n",
       "31      Imperial                    367       239     122     1250   \n",
       "31      Imperial                    412       384     194     1095   \n",
       "71      Imperial                    762       677      52      297   \n",
       "245     Imperial                   1174       963      29      391   \n",
       "421     Imperial                   3232      1642      86     1817   \n",
       "601     Imperial                    925       670      72     1936   \n",
       "12      Imperial                    561       390      91      935   \n",
       "13      Imperial                    NaN       NaN     NaN      NaN   \n",
       "12      Imperial                    561       390      91      935   \n",
       "10      Imperial                    477       320     115     1296   \n",
       "\n",
       "                                     quarter       state code    year       fy  \n",
       "29    FY 2013, 4th Q, July 1- Sept. 30, 2013  California  IMP  2013.0  FY 2013  \n",
       "29      FY 2014, 1st Q, Oct. 1-Dec. 31, 2013  California  IMP  2013.0  FY 2014  \n",
       "29     FY 2014, 2nd Q, Jan. 1-March 31, 2014  California  IMP  2014.0  FY 2014  \n",
       "29     FY 2014, 3rd Q, April 1-June 30, 2014  California  IMP  2014.0  FY 2014  \n",
       "29     FY 2014, 4th Q, July 1-Sept. 30, 2014  California  IMP  2014.0  FY 2014  \n",
       "29      FY 2015, 1st Q, Oct. 1-Dec. 31, 2014  California  IMP  2014.0  FY 2015  \n",
       "29     FY 2015, 2nd Q, Jan. 1-March 31, 2015  California  IMP  2015.0  FY 2015  \n",
       "29     FY 2015, 3rd Q, April 1-June 30, 2015  California  IMP  2015.0  FY 2015  \n",
       "29     FY 2015, 4th Q, July 1-Sept. 30, 2015  California  IMP  2015.0  FY 2015  \n",
       "29       FY 2016, 1st Q, Oct. 1-Dec.31, 2015  California  IMP  2015.0  FY 2016  \n",
       "29     FY 2016, 2nd Q, Jan. 1-March 31, 2016  California  IMP  2016.0  FY 2016  \n",
       "29     FY 2016, 3rd Q, April 1-June 30, 2016  California  IMP  2016.0  FY 2016  \n",
       "29     FY 2016, 4th Q, July 1-Sept. 30, 2016  California  IMP  2016.0  FY 2016  \n",
       "29      FY 2017, 1st Q, Oct. 1-Dec. 31, 2016  California  IMP  2016.0  FY 2017  \n",
       "29     FY 2017, 2nd Q, Jan. 1-March 31, 2017  California  IMP  2017.0  FY 2017  \n",
       "33     FY 2017, 3rd Q, April 1-June 30, 2017  California  IMP  2017.0  FY 2017  \n",
       "33     FY 2017, 4th Q, July 1-Sept. 30, 2017  California  IMP  2017.0  FY 2017  \n",
       "33      FY 2018, 1st Q, Oct. 1-Dec. 31, 2017  California  IMP  2017.0  FY 2018  \n",
       "33     FY 2018, 2nd Q, Jan. 1-March 31, 2018  California  IMP  2018.0  FY 2018  \n",
       "33     FY 2018, 3rd Q, April 1-June 30, 2018  California  IMP  2018.0  FY 2018  \n",
       "33     FY 2018, 4th Q, July 1-Sept. 30, 2018  California  IMP  2018.0  FY 2018  \n",
       "31      FY 2019, 1st Q, Oct. 1-Dec. 31, 2018  California  IMP  2018.0  FY 2019  \n",
       "31      FY 2019, 2nd Q, Jan. 1-Mar. 31, 2019  California  IMP  2019.0  FY 2019  \n",
       "71                                       NaN  California  IMP     NaN  FY 2010  \n",
       "245                                      NaN  California  IMP     NaN  FY 2011  \n",
       "421                                      NaN  California  IMP     NaN  FY 2012  \n",
       "601                                      NaN  California  IMP     NaN  FY 2013  \n",
       "12    FY 2019, 4th Q, July 1- Sept. 30, 2019  California  IMP     NaN  FY 2019  \n",
       "13   FY 2019, 3rd Q, April 1 - June 30, 2019  California  IMP     NaN  FY 2019  \n",
       "12    FY 2020, 1st Q, Oct. 1 - Dec. 31, 2019  California  IMP     NaN  FY 2020  \n",
       "10    FY 2020, 2nd Q, Jan. 1 - March 31 2020  California  IMP     NaN  FY 2020  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_master[new_master['code']=='IMP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}