code/hm_notebooks/4_annual_pdfs_to_csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7ae60924",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T06:48:03.723125Z",
     "start_time": "2021-05-18T06:48:03.718605Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tabula\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "184aa5b2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T06:49:00.609156Z",
     "start_time": "2021-05-18T06:49:00.606375Z"
    }
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows',None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc890c60",
   "metadata": {},
   "source": [
    "# Annual fire #s and acres - wild and Rx - by State\n",
    "Methods for cleaning determined in separate shitshow of a notebook, final functions here.\n",
    "\n",
    "* All States of interest contained on pages 1, 2, 3, 7, 8, 10 of [NICC Redbooks](https://www.nifc.gov/fire-information/statistics) for years 2010-2013.\n",
    "    * p1, p2-8, and p10 all require separate treatment because tabula likes to make you work for it\n",
    "    * function for each page defined separately before combining, because possible that the pdfs per year are _slightly_ different enough to break the combined function; if have to resort to processing individual documents manually, easier to do with functions broken out\n",
    "\n",
    "* Format slightly changes in 2014; new function required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9666393e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T06:48:04.578290Z",
     "start_time": "2021-05-18T06:48:04.574946Z"
    }
   },
   "outputs": [],
   "source": [
    "west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c780c06",
   "metadata": {},
   "source": [
    "### p1 methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c20c7c07",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T06:48:06.264435Z",
     "start_time": "2021-05-18T06:48:06.257661Z"
    }
   },
   "outputs": [],
   "source": [
    "#define function for p1 cleaning.  \n",
    "#all other pages way worse, but in the same way so can clean together\n",
    "\n",
    "def p1_clean(filename):\n",
    "    west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']\n",
    "    table = tabula.read_pdf(filename, pages=1)\n",
    "    \n",
    "    df = table[0].copy()\n",
    "    df.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'fires_acres', 'Unnamed: 2': 'rx_fires', 'Rx': 'rx_acres'}, inplace=True)\n",
    "    df.drop(index=0, inplace=True)\n",
    "    df[['wildfires', 'wild_acres']] = df['fires_acres'].str.split(expand=True)\n",
    "    df.drop('fires_acres', axis=1, inplace=True)\n",
    "    df['state'] = df['state'].fillna(method='ffill')\n",
    "    df = df.loc[df['state'].isin(west_states)]\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c4a57ff",
   "metadata": {},
   "source": [
    "### p2-8 methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "aead9a0c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T06:48:08.105452Z",
     "start_time": "2021-05-18T06:48:08.093089Z"
    }
   },
   "outputs": [],
   "source": [
    "# define function for p2-9 cleaning\n",
    "\n",
    "def p2to9_clean(filename):\n",
    "    west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']\n",
    "    table = tabula.read_pdf(filename, pages=1)\n",
    "    \n",
    "    df = table[0].copy()\n",
    "    \n",
    "    df['Wildland'] = df['Wildland'].fillna('Totals')\n",
    "    df.drop(columns=['Unnamed: 1', 'Unnamed: 2', 'Unnamed: 10'], inplace=True)\n",
    "    \n",
    "    df['Rx'].fillna(df['Unnamed: 3'], inplace=True)\n",
    "    df['Unnamed: 4'].fillna(df['Unnamed: 5'], inplace=True)\n",
    "    df['Unnamed: 6'].fillna(df['Unnamed: 7'], inplace=True)\n",
    "    df['Unnamed: 8'].fillna(df['Unnamed: 9'], inplace=True)\n",
    "    \n",
    "    df.drop(columns=['Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7', 'Unnamed: 9'], inplace=True)\n",
    "    \n",
    "    df.rename(columns={'Unnamed: 0':'state', 'Wildland': 'agency', 'Rx':'wildfires',\n",
    "                     'Unnamed: 4':'wild_acres', 'Unnamed: 6':'rx_fires', \n",
    "                      'Unnamed: 8': 'rx_acres'}, inplace=True)\n",
    "    \n",
    "    df.drop(index=0, inplace=True)\n",
    "    \n",
    "    df['state'] = df['state'].fillna(method='ffill')\n",
    "    \n",
    "    df = df.loc[df['state'].isin(west_states)]\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f26fb22a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T06:48:08.886492Z",
     "start_time": "2021-05-18T06:48:08.877935Z"
    }
   },
   "outputs": [],
   "source": [
    "# define function for p10 cleaning: ok very few changes from p2 I overreacted\n",
    "\n",
    "def p10_clean(filename):\n",
    "    west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']\n",
    "    table = tabula.read_pdf(filename, pages=1)\n",
    "    \n",
    "    df = table[0].copy()\n",
    "    \n",
    "    df['Unnamed: 1'] = df['Unnamed: 1'].fillna(df['Rx'])\n",
    "    df.drop(columns=['Rx', 'Wildland', 'Unnamed: 10'], inplace=True)\n",
    "    \n",
    "    df['Unnamed: 2'].fillna(df['Unnamed: 3'], inplace=True)\n",
    "    df['Unnamed: 4'].fillna(df['Unnamed: 5'], inplace=True)\n",
    "    df['Unnamed: 6'].fillna(df['Unnamed: 7'], inplace=True)\n",
    "    df['Unnamed: 8'].fillna(df['Unnamed: 9'], inplace=True)\n",
    "    \n",
    "    df.drop(columns=['Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7', 'Unnamed: 9'], inplace=True)\n",
    "    \n",
    "    df.rename(columns={'Unnamed: 0':'state', 'Unnamed: 1': 'agency', 'Unnamed: 2':'wildfires',\n",
    "                     'Unnamed: 4':'wild_acres', 'Unnamed: 6':'rx_fires', \n",
    "                      'Unnamed: 8': 'rx_acres'}, inplace=True)\n",
    "    \n",
    "    df.drop(index=0, inplace=True)\n",
    "    \n",
    "    df['state'] = df['state'].fillna(method='ffill')\n",
    "    \n",
    "    df = df.loc[df['state'].isin(west_states)]\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4c31728",
   "metadata": {},
   "source": [
    "### United into big ol function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "4e29fcb0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T07:51:49.107178Z",
     "start_time": "2021-05-18T07:51:49.083976Z"
    }
   },
   "outputs": [],
   "source": [
    "# from scratch (all functions in here instead of defined elsewhere)\n",
    "\n",
    "def pdf_no_more(input_path, output_path, year):\n",
    "\n",
    "    pdf_list = os.listdir(input_path)\n",
    "    \n",
    "    west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']\n",
    "    \n",
    "    df_list = []\n",
    "    \n",
    "    for i in pdf_list:\n",
    "        filepath = input_path + i\n",
    "\n",
    "        table = tabula.read_pdf(filepath, pages=1)\n",
    "        df = table[0].copy()\n",
    "\n",
    "        \n",
    "        if int(filepath[-5]) == 1:\n",
    "            print(f'processing page Alpha {filepath[-5]}')\n",
    "\n",
    "            df.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'fires_acres', 'Unnamed: 2': 'rx_fires', 'Rx': 'rx_acres'}, inplace=True)\n",
    "            df.drop(index=0, inplace=True)\n",
    "            df[['wildfires', 'wild_acres']] = df['fires_acres'].str.split(expand=True)\n",
    "            df.drop('fires_acres', axis=1, inplace=True)\n",
    "            df['state'] = df['state'].fillna(method='ffill')\n",
    "            df = df.loc[df['state'].isin(west_states)]\n",
    "            \n",
    "            df_list.append(df)\n",
    "\n",
    "            \n",
    "        if int(filepath[-5]) == 0: #page 10 will read as 0 - only last digit referenced\n",
    "            \n",
    "            print(f'processing page Omega {filepath[-5]}')\n",
    "            df['Unnamed: 1'] = df['Unnamed: 1'].fillna('Totals')\n",
    "            df.drop(columns=['Rx', 'Wildland', 'Unnamed: 10'], inplace=True)\n",
    "\n",
    "            df['Unnamed: 2'].fillna(df['Unnamed: 3'], inplace=True)\n",
    "            df['Unnamed: 4'].fillna(df['Unnamed: 5'], inplace=True)\n",
    "            df['Unnamed: 6'].fillna(df['Unnamed: 7'], inplace=True)\n",
    "            df['Unnamed: 8'].fillna(df['Unnamed: 9'], inplace=True)\n",
    "\n",
    "            df.drop(columns=['Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7', 'Unnamed: 9'], inplace=True)\n",
    "\n",
    "            df.rename(columns={'Unnamed: 0':'state', 'Unnamed: 1': 'agency', 'Unnamed: 2':'wildfires',\n",
    "                             'Unnamed: 4':'wild_acres', 'Unnamed: 6':'rx_fires', \n",
    "                              'Unnamed: 8': 'rx_acres'}, inplace=True)\n",
    "\n",
    "            df.drop(index=0, inplace=True)\n",
    "            df['state'] = df['state'].fillna(method='ffill')\n",
    "            df = df.loc[df['state'].isin(west_states)]\n",
    "            \n",
    "            df_list.append(df)\n",
    "        \n",
    "        if 1 < int(filepath[-5]) & int(filepath[-5]) < 10:\n",
    "\n",
    "            print(f'processing page Medio {filepath[-5]}')\n",
    "\n",
    "            df['Wildland'] = df['Wildland'].fillna('Totals')\n",
    "            df.drop(columns=['Unnamed: 1', 'Unnamed: 2', 'Unnamed: 10'], inplace=True)\n",
    "\n",
    "            df['Rx'].fillna(df['Unnamed: 3'], inplace=True)\n",
    "            df['Unnamed: 4'].fillna(df['Unnamed: 5'], inplace=True)\n",
    "            df['Unnamed: 6'].fillna(df['Unnamed: 7'], inplace=True)\n",
    "            df['Unnamed: 8'].fillna(df['Unnamed: 9'], inplace=True)\n",
    "\n",
    "            df.drop(columns=['Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7', 'Unnamed: 9'], inplace=True)\n",
    "\n",
    "            df.rename(columns={'Unnamed: 0':'state', 'Wildland': 'agency', 'Rx':'wildfires',\n",
    "                             'Unnamed: 4':'wild_acres', 'Unnamed: 6':'rx_fires', \n",
    "                              'Unnamed: 8': 'rx_acres'}, inplace=True)\n",
    "\n",
    "            df.drop(index=0, inplace=True)\n",
    "            df['state'] = df['state'].fillna(method='ffill')\n",
    "            df = df.loc[df['state'].isin(west_states)]\n",
    "            \n",
    "            df_list.append(df)\n",
    "            \n",
    "    final = pd.concat(df_list)\n",
    "#     final.to_csv(f'{output_path}/annual_{year}_agency.csv', index=False)\n",
    "    \n",
    "#     final.loc[final['agency']=='Totals'].to_csv(f'{output_path}/annual_{year}_tot.csv', index=False)\n",
    "    \n",
    "    return final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "40807f60",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T07:51:51.874231Z",
     "start_time": "2021-05-18T07:51:51.871417Z"
    }
   },
   "outputs": [],
   "source": [
    "input_path_1 = '../scratch_data/pdf_extracts/bystate_2010_extracted-pages/'\n",
    "input_path_2 = '../scratch_data/pdf_extracts/bystate_2011_extracted-pages/'\n",
    "input_path_3 = '../scratch_data/pdf_extracts/bystate_2012_extracted-pages/'\n",
    "input_path_4 = '../scratch_data/pdf_extracts/bystate_2013_extracted-pages/'\n",
    "output_path = '../scratch_data/pdf_extracts/resulting_csvs/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "4d50fe7e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T07:56:45.616231Z",
     "start_time": "2021-05-18T07:56:45.613926Z"
    }
   },
   "outputs": [],
   "source": [
    "#pdf_no_more(input_path_4, output_path, 2013) #breaks on 2013.  we had a good run."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cbd4548",
   "metadata": {},
   "source": [
    "## Construct 2013 manually\n",
    "#### Another big format change coming 2014-2016, so not worth automating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "5f7014d1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:11:00.858387Z",
     "start_time": "2021-05-18T08:10:59.610569Z"
    }
   },
   "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>agency</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AZ</td>\n",
       "      <td>BIA</td>\n",
       "      <td>13</td>\n",
       "      <td>18,215</td>\n",
       "      <td>419</td>\n",
       "      <td>47,132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AZ</td>\n",
       "      <td>BLM</td>\n",
       "      <td>11</td>\n",
       "      <td>916</td>\n",
       "      <td>162</td>\n",
       "      <td>8,164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AZ</td>\n",
       "      <td>DDQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AZ</td>\n",
       "      <td>FWS</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>28</td>\n",
       "      <td>3,336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AZ</td>\n",
       "      <td>NPS</td>\n",
       "      <td>2</td>\n",
       "      <td>236</td>\n",
       "      <td>62</td>\n",
       "      <td>1,707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AZ</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>224</td>\n",
       "      <td>8,721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AZ</td>\n",
       "      <td>PRI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AZ</td>\n",
       "      <td>ST</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>128</td>\n",
       "      <td>7,936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AZ</td>\n",
       "      <td>USFS</td>\n",
       "      <td>107</td>\n",
       "      <td>30,101</td>\n",
       "      <td>732</td>\n",
       "      <td>28,275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Totals</td>\n",
       "      <td>134</td>\n",
       "      <td>49,491</td>\n",
       "      <td>1,756</td>\n",
       "      <td>105,281</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  agency rx_fires rx_acres wildfires wild_acres\n",
       "22    AZ     BIA       13   18,215       419     47,132\n",
       "23    AZ     BLM       11      916       162      8,164\n",
       "24    AZ     DDQ        0        0         1         10\n",
       "25    AZ     FWS        1       23        28      3,336\n",
       "26    AZ     NPS        2      236        62      1,707\n",
       "27    AZ    OTHR        0        0       224      8,721\n",
       "28    AZ     PRI        0        0         0          0\n",
       "29    AZ      ST        0        0       128      7,936\n",
       "30    AZ    USFS      107   30,101       732     28,275\n",
       "31    AZ  Totals      134   49,491     1,756    105,281"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AZ_2013 = p1_clean('../scratch_data/pdf_extracts/bystate_2013_extracted-pages/2013-1.pdf')\n",
    "AZ_2013 #checked against doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "23510868",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:07:15.726958Z",
     "start_time": "2021-05-18T08:07:15.719059Z"
    }
   },
   "outputs": [],
   "source": [
    "def midpages_2013(filename):\n",
    "\n",
    "    west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']\n",
    "    table = tabula.read_pdf(filename, pages=1)\n",
    "    \n",
    "    df = table[0].copy()\n",
    "    \n",
    "    df.drop(columns=['Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7'], inplace=True)\n",
    "    \n",
    "    df.rename(columns={'Unnamed: 0':'state', 'Wildland': 'agency', 'Rx':'wildfires',\n",
    "                     'Unnamed: 4':'wild_acres', 'Unnamed: 6':'rx_fires', \n",
    "                      'Unnamed: 8': 'rx_acres'}, inplace=True)\n",
    "    \n",
    "    df.drop(index=0, inplace=True)\n",
    "    \n",
    "    df['state'] = df['state'].fillna(method='ffill')\n",
    "    \n",
    "    df = df.loc[df['state'].isin(west_states)]\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "1817e04f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:55:57.488823Z",
     "start_time": "2021-05-18T08:55:56.079001Z"
    }
   },
   "outputs": [],
   "source": [
    "path = '../scratch_data/pdf_extracts/bystate_2013_extracted-pages/'\n",
    "pdf_list = os.listdir(path)\n",
    "\n",
    "all_paths = [path + i for i in pdf_list] #order = 3, 2, 10, 1, 7, 8\n",
    "\n",
    "p2 = midpages_2013(all_paths[1])\n",
    "p3 = midpages_2013(all_paths[0])\n",
    "#p7 = midpages_2013(all_paths[4])  #no states of interest on this page\n",
    "p8 = midpages_2013('../scratch_data/pdf_extracts/bystate_2013_extracted-pages/2013-8.pdf')  #broken ^^"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "02cce926",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:39:11.687237Z",
     "start_time": "2021-05-18T08:39:11.173762Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pdf = '../scratch_data/pdf_extracts/bystate_2013_extracted-pages/2013-10.pdf'\n",
    "p10 = tabula.read_pdf(pdf, pages=1)\n",
    "p10 = p10[0]\n",
    "#p10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "59bf0d7e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:39:12.456808Z",
     "start_time": "2021-05-18T08:39:12.436511Z"
    }
   },
   "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>agency</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>UT</td>\n",
       "      <td>BIA</td>\n",
       "      <td>62</td>\n",
       "      <td>213</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UT</td>\n",
       "      <td>BLM</td>\n",
       "      <td>417</td>\n",
       "      <td>24,394</td>\n",
       "      <td>16</td>\n",
       "      <td>1,048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>UT</td>\n",
       "      <td>DDQ</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>UT</td>\n",
       "      <td>FWS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>UT</td>\n",
       "      <td>NPS</td>\n",
       "      <td>25</td>\n",
       "      <td>65</td>\n",
       "      <td>5</td>\n",
       "      <td>672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>UT</td>\n",
       "      <td>PRI</td>\n",
       "      <td>1</td>\n",
       "      <td>16,505</td>\n",
       "      <td>5</td>\n",
       "      <td>191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>UT</td>\n",
       "      <td>ST</td>\n",
       "      <td>506</td>\n",
       "      <td>5,011</td>\n",
       "      <td>35</td>\n",
       "      <td>1,124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>UT</td>\n",
       "      <td>USFS</td>\n",
       "      <td>264</td>\n",
       "      <td>24,094</td>\n",
       "      <td>50</td>\n",
       "      <td>10,994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>UT</td>\n",
       "      <td>Totals</td>\n",
       "      <td>1,276</td>\n",
       "      <td>70,282</td>\n",
       "      <td>111</td>\n",
       "      <td>14,029</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  agency wildfires wild_acres rx_fires rx_acres\n",
       "16    UT     BIA        62        213        0        0\n",
       "17    UT     BLM       417     24,394       16    1,048\n",
       "18    UT     DDQ         1          0        0        0\n",
       "19    UT     FWS         0          0        0        0\n",
       "20    UT     NPS        25         65        5      672\n",
       "21    UT     PRI         1     16,505        5      191\n",
       "22    UT      ST       506      5,011       35    1,124\n",
       "23    UT    USFS       264     24,094       50   10,994\n",
       "24    UT  Totals     1,276     70,282      111   14,029"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p10.drop(columns=['Wildland', 'Rx', 'Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7'], inplace=True)\n",
    "\n",
    "p10.rename(columns={'Unnamed: 0':'state', 'Unnamed: 1': 'agency', 'Unnamed: 2':'wildfires',\n",
    "                 'Unnamed: 4':'wild_acres', 'Unnamed: 6':'rx_fires', \n",
    "                  'Unnamed: 8': 'rx_acres'}, inplace=True)\n",
    "\n",
    "p10.drop(index=0, inplace=True)\n",
    "\n",
    "p10['state'] = p10['state'].fillna(method='ffill')\n",
    "\n",
    "p10 = p10.loc[p10['state'].isin(west_states)]\n",
    "\n",
    "p10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "44a6a4be",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:23:19.941735Z",
     "start_time": "2021-05-18T08:23:19.422242Z"
    }
   },
   "outputs": [],
   "source": [
    "#oh good, this one goes to 11\n",
    "pdf = '../scratch_data/pdf_extracts/bystate_2013_extracted-pages/2013-11.pdf'\n",
    "p11 = tabula.read_pdf(pdf, pages=1)\n",
    "p11 = p11[0]  #I thiiink same as mediopages function?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "203666dd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:26:16.530026Z",
     "start_time": "2021-05-18T08:26:16.025204Z"
    }
   },
   "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>agency</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA</td>\n",
       "      <td>BIA</td>\n",
       "      <td>349</td>\n",
       "      <td>38,542</td>\n",
       "      <td>14</td>\n",
       "      <td>814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WA</td>\n",
       "      <td>BLM</td>\n",
       "      <td>38</td>\n",
       "      <td>6,618</td>\n",
       "      <td>2</td>\n",
       "      <td>822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WA</td>\n",
       "      <td>CNTY</td>\n",
       "      <td>3</td>\n",
       "      <td>4,510</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WA</td>\n",
       "      <td>FWS</td>\n",
       "      <td>49</td>\n",
       "      <td>967</td>\n",
       "      <td>16</td>\n",
       "      <td>940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>WA</td>\n",
       "      <td>NPS</td>\n",
       "      <td>42</td>\n",
       "      <td>264</td>\n",
       "      <td>12</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WA</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>WA</td>\n",
       "      <td>ST</td>\n",
       "      <td>741</td>\n",
       "      <td>93,656</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>WA</td>\n",
       "      <td>USFS</td>\n",
       "      <td>305</td>\n",
       "      <td>8,046</td>\n",
       "      <td>108</td>\n",
       "      <td>10,328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WA</td>\n",
       "      <td>Totals</td>\n",
       "      <td>1,527</td>\n",
       "      <td>152,603</td>\n",
       "      <td>152</td>\n",
       "      <td>13,145</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state  agency wildfires wild_acres rx_fires rx_acres\n",
       "1    WA     BIA       349     38,542       14      814\n",
       "2    WA     BLM        38      6,618        2      822\n",
       "3    WA    CNTY         3      4,510        0        0\n",
       "4    WA     FWS        49        967       16      940\n",
       "5    WA     NPS        42        264       12      241\n",
       "6    WA    OTHR         0          0        0        0\n",
       "7    WA      ST       741     93,656        0        0\n",
       "8    WA    USFS       305      8,046      108   10,328\n",
       "9    WA  Totals     1,527    152,603      152   13,145"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p11_final = midpages_2013('../scratch_data/pdf_extracts/bystate_2013_extracted-pages/2013-11.pdf')\n",
    "p11_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "125fef27",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:57:33.748366Z",
     "start_time": "2021-05-18T08:57:33.728837Z"
    }
   },
   "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>agency</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AZ</td>\n",
       "      <td>BIA</td>\n",
       "      <td>13</td>\n",
       "      <td>18,215</td>\n",
       "      <td>419</td>\n",
       "      <td>47,132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AZ</td>\n",
       "      <td>BLM</td>\n",
       "      <td>11</td>\n",
       "      <td>916</td>\n",
       "      <td>162</td>\n",
       "      <td>8,164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AZ</td>\n",
       "      <td>DDQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AZ</td>\n",
       "      <td>FWS</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>28</td>\n",
       "      <td>3,336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AZ</td>\n",
       "      <td>NPS</td>\n",
       "      <td>2</td>\n",
       "      <td>236</td>\n",
       "      <td>62</td>\n",
       "      <td>1,707</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state agency rx_fires rx_acres wildfires wild_acres\n",
       "22    AZ    BIA       13   18,215       419     47,132\n",
       "23    AZ    BLM       11      916       162      8,164\n",
       "24    AZ    DDQ        0        0         1         10\n",
       "25    AZ    FWS        1       23        28      3,336\n",
       "26    AZ    NPS        2      236        62      1,707"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_2013 = pd.concat([AZ_2013, p2, p3, p8, p10, p11_final])\n",
    "all_2013.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "77434164",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T08:59:30.218516Z",
     "start_time": "2021-05-18T08:59:30.211608Z"
    }
   },
   "outputs": [],
   "source": [
    "all_2013.to_csv(output_path + 'annual_2013_agency.csv', index=False)\n",
    "all_2013.loc[all_2013['agency'] == 'Totals'].to_csv(output_path + 'annual_2013.tot.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b8a2fbb",
   "metadata": {},
   "source": [
    "## 2014-2015 completely different formats than priors\n",
    "Curses.  All pages unique.  Proceeding manually for all, last push."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "a018bdb0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:28:30.037348Z",
     "start_time": "2021-05-18T11:28:30.032661Z"
    }
   },
   "outputs": [],
   "source": [
    "filename14 = '../scratch_data/pdf_extracts/2014.pdf'\n",
    "filename15 = '../scratch_data/pdf_extracts/2015.pdf'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "939fe620",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:31:00.157311Z",
     "start_time": "2021-05-18T11:31:00.151042Z"
    }
   },
   "outputs": [],
   "source": [
    "def clean1_newformat(filename):\n",
    "    west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']\n",
    "    table = tabula.read_pdf(filename, pages=1)\n",
    "    df = table[0].copy()\n",
    "    df.drop(columns=['Unnamed: 2', 'Unnamed: 4', 'Wildland', \n",
    "                     'Unnamed: 5', 'Unnamed: 8', 'Rx'], inplace=True)\n",
    "    df.rename(columns = {'Unnamed: 0':'state', 'Unnamed: 1':'agency', 'Unnamed: 3':'wildfires',\n",
    "                        'Unnamed: 6': 'wild_acres', 'Unnamed: 7': 'rx_fires', \n",
    "                         'Unnamed: 9': 'rx_acres'}, inplace=True)\n",
    "    df['agency'] = df['agency'].fillna('Totals')\n",
    "    df['state'] = df['state'].fillna(method = 'ffill')\n",
    "    df = df.loc[df['state'].isin(west_states)]\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "4aeae856",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:31:29.868755Z",
     "start_time": "2021-05-18T11:31:28.501618Z"
    }
   },
   "outputs": [],
   "source": [
    "p1_2014 = clean1_newformat(filename14)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "2af05347",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:31:48.416146Z",
     "start_time": "2021-05-18T11:31:48.407144Z"
    }
   },
   "outputs": [],
   "source": [
    "def hopefully_more_than_one_page(file, page): #nope just one page.\n",
    "    west_states = ['CA', 'OR', 'WA', 'AZ', 'NV', 'UT', 'ID']\n",
    "    table = tabula.read_pdf(file, pages=page)\n",
    "    df = table[0].copy()\n",
    "    \n",
    "    df['Unnamed: 2'] = np.where(df['Unnamed: 2']=='Totals', df['Unnamed: 11'], df['Unnamed: 2'])\n",
    "    \n",
    "    #manually fix rx_fires_total outside of function for P2 ONLY\n",
    "    \n",
    "    df.rename(columns = {'Unnamed: 0': 'state', 'Wildland':'agency', 'Rx':'wildfires',\n",
    "                       'Unnamed: 1':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                       'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "    \n",
    "    df['wild_acres'].fillna(df['Unnamed: 8'], inplace=True)\n",
    "    df['wildfires'].fillna(df['Unnamed: 5'], inplace=True)\n",
    "    df['rx_acres'] = df.rx_acres.fillna(df['Unnamed: 14'])\n",
    "    \n",
    "    df['state'].fillna(method='ffill', inplace=True)\n",
    "    df['agency'] = [i if i not in west_states else 'Totals' for i in df['agency']]\n",
    "    \n",
    "    df.drop(df.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "    df.drop(index=0, inplace=True)\n",
    "    df = df.loc[df['state'].isin(west_states)]\n",
    "    \n",
    "        \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "604b0335",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:36:06.681232Z",
     "start_time": "2021-05-18T11:36:06.082264Z"
    }
   },
   "outputs": [],
   "source": [
    "p2_2014 = hopefully_more_than_one_page(filename14, 2) \n",
    "p2_2014['rx_fires'][9] = 515 #note to manually fix units in rx_fires_tot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "b625837d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:36:52.255796Z",
     "start_time": "2021-05-18T11:36:51.654946Z"
    }
   },
   "outputs": [],
   "source": [
    "table = tabula.read_pdf(filename14, pages=3)\n",
    "p3 = table[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "4e6341bc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:43:59.503968Z",
     "start_time": "2021-05-18T11:43:59.483962Z"
    }
   },
   "outputs": [],
   "source": [
    "#now that you've started cut-pasting intsead of generalizing a function,\n",
    "#this chunk is good for 3 pages\n",
    "p3['Unnamed: 2'] = np.where(p3['Unnamed: 2']=='Totals', p3['Unnamed: 11'], p3['Unnamed: 2'])\n",
    "p3.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'wildfires',\n",
    "                   'Rx':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                   'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "p3['wild_acres'].fillna(p3['Unnamed: 8'], inplace=True)\n",
    "p3['wildfires'].fillna(p3['Unnamed: 5'], inplace=True)\n",
    "p3['rx_acres'] = p3.rx_acres.fillna(p3['Unnamed: 14'])\n",
    "p3['state'].fillna(method='ffill', inplace=True)\n",
    "p3['agency'] = [i if i not in west_states else 'Totals' for i in p3['agency']]\n",
    "p3.drop(p3.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "p3.drop(index=0, inplace=True)\n",
    "p3 = p3.loc[p3['state'].isin(west_states)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "3c889d5f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:44:55.332030Z",
     "start_time": "2021-05-18T11:44:54.767280Z"
    }
   },
   "outputs": [],
   "source": [
    "table = tabula.read_pdf(filename14, pages=7)\n",
    "p7 = table[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "30378ebb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:46:35.619544Z",
     "start_time": "2021-05-18T11:46:35.601585Z"
    }
   },
   "outputs": [],
   "source": [
    "p7['Unnamed: 2'] = np.where(p7['Unnamed: 2']=='Totals', p7['Unnamed: 11'], p7['Unnamed: 2'])\n",
    "p7.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'wildfires',\n",
    "                   'Rx':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                   'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "p7['wild_acres'].fillna(p7['Unnamed: 8'], inplace=True)\n",
    "p7['wildfires'].fillna(p7['Unnamed: 5'], inplace=True)\n",
    "p7['rx_acres'] = p7.rx_acres.fillna(p7['Unnamed: 14'])\n",
    "p7['state'].fillna(method='ffill', inplace=True)\n",
    "p7['agency'] = [i if i not in west_states else 'Totals' for i in p7['agency']]\n",
    "p7.drop(p7.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "p7.drop(index=0, inplace=True)\n",
    "p7 = p7.loc[p7['state'].isin(west_states)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "c51acdf8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:48:20.363200Z",
     "start_time": "2021-05-18T11:48:20.359795Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/helenmeigs/miniforge3/lib/python3.9/site-packages/IPython/core/interactiveshell.py:3441: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "p7['rx_fires'][9] = 27\n",
    "p7['rx_fires'][36] = 658"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "6cab49c7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:48:48.572298Z",
     "start_time": "2021-05-18T11:48:47.982956Z"
    }
   },
   "outputs": [],
   "source": [
    "table = tabula.read_pdf(filename14, pages=9)\n",
    "p9 = table[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "7b41fb78",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:50:20.408014Z",
     "start_time": "2021-05-18T11:50:20.388748Z"
    }
   },
   "outputs": [],
   "source": [
    "p9['Unnamed: 2'] = np.where(p9['Unnamed: 2']=='Totals', p9['Unnamed: 11'], p9['Unnamed: 2'])\n",
    "p9.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'wildfires',\n",
    "                   'Rx':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                   'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "\n",
    "p9['wild_acres'].fillna(p9['Unnamed: 8'], inplace=True)\n",
    "p9['wildfires'].fillna(p9['Unnamed: 5'], inplace=True)\n",
    "p9['rx_acres'] = p9.rx_acres.fillna(p9['Unnamed: 14'])\n",
    "\n",
    "p9['state'].fillna(method='ffill', inplace=True)\n",
    "p9['agency'] = [i if i not in west_states else 'Totals' for i in p9['agency']]\n",
    "\n",
    "p9.drop(p9.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "p9.drop(index=0, inplace=True)\n",
    "p9 = p9.loc[p9['state'].isin(west_states)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "ff27b341",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:54:06.387866Z",
     "start_time": "2021-05-18T11:54:06.383600Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/helenmeigs/miniforge3/lib/python3.9/site-packages/IPython/core/interactiveshell.py:3441: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "p9['rx_acres'][12] = 15801\n",
    "p9['rx_acres'][33] = 15197"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "9455fbbd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:56:40.411124Z",
     "start_time": "2021-05-18T11:56:40.407791Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'../scratch_data/pdf_extracts/resulting_csvs/'"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "2984321d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T11:57:51.713731Z",
     "start_time": "2021-05-18T11:57:51.703800Z"
    }
   },
   "outputs": [],
   "source": [
    "all_2014 = pd.concat([p1_2014, p2_2014, p3, p7, p9])\n",
    "all_2014.to_csv(output_path + 'annual_2014_agency.csv', index=False)\n",
    "all_2014.loc[all_2014.agency == 'Totals'].to_csv(output_path + 'annual_2014_tot.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec8a9b67",
   "metadata": {},
   "source": [
    "## 2015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "a93cc1a8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:32:28.688834Z",
     "start_time": "2021-05-18T20:32:24.875943Z"
    }
   },
   "outputs": [],
   "source": [
    "pages = [1, 2, 3, 7, 9]\n",
    "\n",
    "pdf_list=[]\n",
    "\n",
    "for i in pages:\n",
    "    table = tabula.read_pdf(filename15, pages=i) #filename15 defined above\n",
    "    pdf_list.append(table[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "e0ec6611",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:32:32.123823Z",
     "start_time": "2021-05-18T20:32:32.101082Z"
    }
   },
   "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>agency</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AZ</td>\n",
       "      <td>BIA</td>\n",
       "      <td>369</td>\n",
       "      <td>56,361</td>\n",
       "      <td>23</td>\n",
       "      <td>7,084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AZ</td>\n",
       "      <td>BLM</td>\n",
       "      <td>147</td>\n",
       "      <td>2,675</td>\n",
       "      <td>16</td>\n",
       "      <td>3,976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AZ</td>\n",
       "      <td>DOD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AZ</td>\n",
       "      <td>FWS</td>\n",
       "      <td>6</td>\n",
       "      <td>62</td>\n",
       "      <td>3</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AZ</td>\n",
       "      <td>NPS</td>\n",
       "      <td>30</td>\n",
       "      <td>3,745</td>\n",
       "      <td>7</td>\n",
       "      <td>485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AZ</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AZ</td>\n",
       "      <td>ST</td>\n",
       "      <td>404</td>\n",
       "      <td>4,671</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AZ</td>\n",
       "      <td>USFS</td>\n",
       "      <td>706</td>\n",
       "      <td>92,638</td>\n",
       "      <td>175</td>\n",
       "      <td>85,347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Totals</td>\n",
       "      <td>1,662</td>\n",
       "      <td>160,152</td>\n",
       "      <td>224</td>\n",
       "      <td>96,973</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  agency wildfires wild_acres rx_fires rx_acres\n",
       "22    AZ     BIA       369     56,361       23    7,084\n",
       "23    AZ     BLM       147      2,675       16    3,976\n",
       "24    AZ     DOD         0          0        0        0\n",
       "25    AZ     FWS         6         62        3       81\n",
       "26    AZ     NPS        30      3,745        7      485\n",
       "27    AZ    OTHR         0          0        0        0\n",
       "28    AZ      ST       404      4,671        0        0\n",
       "29    AZ    USFS       706     92,638      175   85,347\n",
       "30    AZ  Totals     1,662    160,152      224   96,973"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p1 = pdf_list[0].copy()\n",
    "\n",
    "p1['Unnamed: 1'].fillna('Totals', inplace=True)\n",
    "p1.drop(['Unnamed: 2', 'Unnamed: 4', 'Wildland', 'Unnamed: 6', 'Unnamed: 8', 'Rx'],\n",
    "       axis=1, inplace=True)\n",
    "p1.rename(columns = {'Unnamed: 0':'state', 'Unnamed: 1': 'agency', 'Unnamed: 3': 'wildfires',\n",
    "                    'Unnamed: 5': 'wild_acres', 'Unnamed: 7': 'rx_fires', 'Unnamed: 9': 'rx_acres'},\n",
    "         inplace=True)\n",
    "p1.state.fillna(method='ffill', inplace=True)\n",
    "p1 = p1.loc[p1.state.isin(west_states)]\n",
    "p1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "5a3f581f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:32:36.890110Z",
     "start_time": "2021-05-18T20:32:36.865818Z"
    }
   },
   "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>agency</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CA</td>\n",
       "      <td>BIA</td>\n",
       "      <td>178</td>\n",
       "      <td>360</td>\n",
       "      <td>2</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CA</td>\n",
       "      <td>BLM</td>\n",
       "      <td>97</td>\n",
       "      <td>18,058</td>\n",
       "      <td>4</td>\n",
       "      <td>803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA</td>\n",
       "      <td>C&amp;L</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>DOD</td>\n",
       "      <td>115</td>\n",
       "      <td>11,394</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CA</td>\n",
       "      <td>FWS</td>\n",
       "      <td>18</td>\n",
       "      <td>23</td>\n",
       "      <td>33</td>\n",
       "      <td>8,462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CA</td>\n",
       "      <td>NPS</td>\n",
       "      <td>126</td>\n",
       "      <td>9,834</td>\n",
       "      <td>24</td>\n",
       "      <td>1,208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CA</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CA</td>\n",
       "      <td>ST</td>\n",
       "      <td>6,550</td>\n",
       "      <td>316,217</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CA</td>\n",
       "      <td>USFS</td>\n",
       "      <td>1,656</td>\n",
       "      <td>537,446</td>\n",
       "      <td>553</td>\n",
       "      <td>36,298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CA</td>\n",
       "      <td>Totals</td>\n",
       "      <td>8,745</td>\n",
       "      <td>893,362</td>\n",
       "      <td>617</td>\n",
       "      <td>46,829</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  agency wildfires wild_acres rx_fires rx_acres\n",
       "1     CA     BIA       178        360        2       28\n",
       "2     CA     BLM        97     18,058        4      803\n",
       "3     CA     C&L         4         15        1       30\n",
       "4     CA     DOD       115     11,394        0        0\n",
       "5     CA     FWS        18         23       33    8,462\n",
       "6     CA     NPS       126      9,834       24    1,208\n",
       "7     CA    OTHR         1         15        0        0\n",
       "8     CA      ST     6,550    316,217        0        0\n",
       "9     CA    USFS     1,656    537,446      553   36,298\n",
       "10    CA  Totals     8,745    893,362      617   46,829"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p2 = pdf_list[1].copy()\n",
    "\n",
    "p2['Unnamed: 2'] = np.where(p2['Unnamed: 2']=='Totals', 617, p2['Unnamed: 2']) #hardcode rx_fires_tot\n",
    "p2['Rx'].fillna(p2['Unnamed: 5'], inplace=True)\n",
    "p2['Unnamed: 1'].fillna(p2['Unnamed: 8'], inplace=True)\n",
    "p2['Unnamed: 3'] = p2['Unnamed: 3'].fillna(p2['Unnamed: 14'])\n",
    "p2.rename(columns = {'Unnamed: 0': 'state', 'Wildland':'agency', 'Rx':'wildfires',\n",
    "                   'Unnamed: 1':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                   'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "p2['state'].fillna(method='ffill', inplace=True)\n",
    "p2['agency'] = [i if i not in west_states else 'Totals' for i in p2['agency']]\n",
    "p2.drop(p2.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "p2.drop(index=0, inplace=True)\n",
    "p2 = p2.loc[p2['state'].isin(west_states)]\n",
    "p2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "77712b70",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:35:56.509152Z",
     "start_time": "2021-05-18T20:35:56.489956Z"
    }
   },
   "outputs": [],
   "source": [
    "p3 = pdf_list[2].copy()\n",
    "\n",
    "p3['Unnamed: 2'] = np.where(p3['Unnamed: 2']=='Totals', p3['Unnamed: 11'], p3['Unnamed: 2'])\n",
    "p3.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'wildfires',\n",
    "                   'Rx':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                   'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "p3['wild_acres'].fillna(p3['Unnamed: 8'], inplace=True)\n",
    "p3['wildfires'].fillna(p3['Unnamed: 5'], inplace=True)\n",
    "p3['rx_acres'] = p3.rx_acres.fillna(p3['Unnamed: 14'])\n",
    "p3['state'].fillna(method='ffill', inplace=True)\n",
    "p3['agency'] = [i if i not in west_states else 'Totals' for i in p3['agency']]\n",
    "p3.drop(p3.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "p3.drop(index=0, inplace=True)\n",
    "p3 = p3.loc[p3['state'].isin(west_states)] #checked against doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "id": "18fbe143",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:40:58.817771Z",
     "start_time": "2021-05-18T20:40:58.789687Z"
    }
   },
   "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>agency</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NV</td>\n",
       "      <td>BIA</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NV</td>\n",
       "      <td>BLM</td>\n",
       "      <td>351</td>\n",
       "      <td>17,747</td>\n",
       "      <td>10</td>\n",
       "      <td>700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NV</td>\n",
       "      <td>C&amp;L</td>\n",
       "      <td>34</td>\n",
       "      <td>156</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NV</td>\n",
       "      <td>FWS</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NV</td>\n",
       "      <td>NPS</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NV</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NV</td>\n",
       "      <td>ST</td>\n",
       "      <td>52</td>\n",
       "      <td>5,048</td>\n",
       "      <td>18</td>\n",
       "      <td>2,706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NV</td>\n",
       "      <td>USFS</td>\n",
       "      <td>88</td>\n",
       "      <td>19,518</td>\n",
       "      <td>4</td>\n",
       "      <td>172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NV</td>\n",
       "      <td>Totals</td>\n",
       "      <td>551</td>\n",
       "      <td>42,479</td>\n",
       "      <td>36</td>\n",
       "      <td>4,916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>OR</td>\n",
       "      <td>BIA</td>\n",
       "      <td>62</td>\n",
       "      <td>71,804</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>OR</td>\n",
       "      <td>BLM</td>\n",
       "      <td>256</td>\n",
       "      <td>269,955</td>\n",
       "      <td>127</td>\n",
       "      <td>22,713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>OR</td>\n",
       "      <td>C&amp;L</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>OR</td>\n",
       "      <td>FWS</td>\n",
       "      <td>6</td>\n",
       "      <td>149</td>\n",
       "      <td>17</td>\n",
       "      <td>9,704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>OR</td>\n",
       "      <td>NPS</td>\n",
       "      <td>21</td>\n",
       "      <td>14,627</td>\n",
       "      <td>2</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>OR</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>OR</td>\n",
       "      <td>ST</td>\n",
       "      <td>1,139</td>\n",
       "      <td>72,439</td>\n",
       "      <td>24</td>\n",
       "      <td>3,059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>OR</td>\n",
       "      <td>USFS</td>\n",
       "      <td>1,104</td>\n",
       "      <td>256,835</td>\n",
       "      <td>379</td>\n",
       "      <td>52,303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>OR</td>\n",
       "      <td>Totals</td>\n",
       "      <td>2,588</td>\n",
       "      <td>685,809</td>\n",
       "      <td>551</td>\n",
       "      <td>87,973</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  agency wildfires wild_acres rx_fires rx_acres\n",
       "1     NV     BIA         3          2        0        0\n",
       "2     NV     BLM       351     17,747       10      700\n",
       "3     NV     C&L        34        156        0        0\n",
       "4     NV     FWS         7          0        2      722\n",
       "5     NV     NPS        16          8        2      616\n",
       "6     NV    OTHR         0          0        0        0\n",
       "7     NV      ST        52      5,048       18    2,706\n",
       "8     NV    USFS        88     19,518        4      172\n",
       "9     NV  Totals       551     42,479       36    4,916\n",
       "28    OR     BIA        62     71,804        2       90\n",
       "29    OR     BLM       256    269,955      127   22,713\n",
       "30    OR     C&L         0          0        0        0\n",
       "31    OR     FWS         6        149       17    9,704\n",
       "32    OR     NPS        21     14,627        2      104\n",
       "33    OR    OTHR         0          0        0        0\n",
       "34    OR      ST     1,139     72,439       24    3,059\n",
       "35    OR    USFS     1,104    256,835      379   52,303\n",
       "36    OR  Totals     2,588    685,809      551   87,973"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p7 = pdf_list[3].copy()\n",
    "\n",
    "p7['Unnamed: 2'] = np.where(p7['Unnamed: 2']=='Totals', p7['Unnamed: 11'], p7['Unnamed: 2'])\n",
    "p7.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'wildfires',\n",
    "                   'Rx':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                   'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "p7['wild_acres'].fillna(p7['Unnamed: 8'], inplace=True)\n",
    "p7['wildfires'].fillna(p7['Unnamed: 5'], inplace=True)\n",
    "p7['rx_acres'] = p7.rx_acres.fillna(p7['Unnamed: 14'])\n",
    "p7['state'].fillna(method='ffill', inplace=True)\n",
    "p7['agency'] = [i if i not in west_states else 'Totals' for i in p7['agency']]\n",
    "p7.drop(p7.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "p7.drop(index=0, inplace=True)\n",
    "p7 = p7.loc[p7['state'].isin(west_states)]\n",
    "p7['rx_fires'][9] = 36\n",
    "p7['rx_fires'][36] = 551\n",
    "p7 #checked against doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "8c114589",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:43:03.660643Z",
     "start_time": "2021-05-18T20:43:03.630890Z"
    }
   },
   "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>agency</th>\n",
       "      <th>wildfires</th>\n",
       "      <th>wild_acres</th>\n",
       "      <th>rx_fires</th>\n",
       "      <th>rx_acres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UT</td>\n",
       "      <td>BIA</td>\n",
       "      <td>30</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UT</td>\n",
       "      <td>BLM</td>\n",
       "      <td>233</td>\n",
       "      <td>997</td>\n",
       "      <td>23</td>\n",
       "      <td>461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>UT</td>\n",
       "      <td>DOD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>UT</td>\n",
       "      <td>FWS</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1,060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UT</td>\n",
       "      <td>NPS</td>\n",
       "      <td>14</td>\n",
       "      <td>55</td>\n",
       "      <td>7</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>UT</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1,120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>UT</td>\n",
       "      <td>ST</td>\n",
       "      <td>456</td>\n",
       "      <td>4,306</td>\n",
       "      <td>31</td>\n",
       "      <td>734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>UT</td>\n",
       "      <td>USFS</td>\n",
       "      <td>197</td>\n",
       "      <td>4,819</td>\n",
       "      <td>46</td>\n",
       "      <td>15,538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>UT</td>\n",
       "      <td>Totals</td>\n",
       "      <td>930</td>\n",
       "      <td>10,203</td>\n",
       "      <td>119.0</td>\n",
       "      <td>19,031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>WA</td>\n",
       "      <td>BIA</td>\n",
       "      <td>290</td>\n",
       "      <td>330,657</td>\n",
       "      <td>10</td>\n",
       "      <td>4,436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>WA</td>\n",
       "      <td>BLM</td>\n",
       "      <td>50</td>\n",
       "      <td>46,333</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>WA</td>\n",
       "      <td>C&amp;L</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>WA</td>\n",
       "      <td>FWS</td>\n",
       "      <td>37</td>\n",
       "      <td>25,495</td>\n",
       "      <td>4</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>WA</td>\n",
       "      <td>NPS</td>\n",
       "      <td>53</td>\n",
       "      <td>11,511</td>\n",
       "      <td>4</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>WA</td>\n",
       "      <td>OTHR</td>\n",
       "      <td>117</td>\n",
       "      <td>241</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>WA</td>\n",
       "      <td>ST</td>\n",
       "      <td>1,067</td>\n",
       "      <td>376,420</td>\n",
       "      <td>2</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>WA</td>\n",
       "      <td>USFS</td>\n",
       "      <td>399</td>\n",
       "      <td>347,007</td>\n",
       "      <td>18</td>\n",
       "      <td>2,254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>WA</td>\n",
       "      <td>Totals</td>\n",
       "      <td>2,013</td>\n",
       "      <td>1,137,664</td>\n",
       "      <td>38.0</td>\n",
       "      <td>7,062</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  agency wildfires wild_acres rx_fires rx_acres\n",
       "1     UT     BIA        30         23        0        0\n",
       "2     UT     BLM       233        997       23      461\n",
       "3     UT     DOD         0          0        0        0\n",
       "4     UT     FWS         0          3        1    1,060\n",
       "5     UT     NPS        14         55        7      118\n",
       "6     UT    OTHR         0          0       11    1,120\n",
       "7     UT      ST       456      4,306       31      734\n",
       "8     UT    USFS       197      4,819       46   15,538\n",
       "9     UT  Totals       930     10,203    119.0   19,031\n",
       "22    WA     BIA       290    330,657       10    4,436\n",
       "23    WA     BLM        50     46,333        0        0\n",
       "24    WA     C&L         0          0        0        0\n",
       "25    WA     FWS        37     25,495        4      151\n",
       "26    WA     NPS        53     11,511        4      120\n",
       "27    WA    OTHR       117        241        0        0\n",
       "28    WA      ST     1,067    376,420        2      101\n",
       "29    WA    USFS       399    347,007       18    2,254\n",
       "30    WA  Totals     2,013  1,137,664     38.0    7,062"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p9 = pdf_list[4].copy()\n",
    "\n",
    "p9['Unnamed: 2'] = np.where(p9['Unnamed: 2']=='Totals', p9['Unnamed: 11'], p9['Unnamed: 2'])\n",
    "p9.rename(columns = {'Unnamed: 0': 'state', 'Unnamed: 1':'agency', 'Wildland':'wildfires',\n",
    "                   'Rx':'wild_acres', 'Unnamed: 2':'rx_fires',\n",
    "                   'Unnamed: 3': 'rx_acres'}, inplace=True)\n",
    "p9['wild_acres'].fillna(p9['Unnamed: 8'], inplace=True)\n",
    "p9['wildfires'].fillna(p9['Unnamed: 5'], inplace=True)\n",
    "p9['rx_acres'] = p9.rx_acres.fillna(p9['Unnamed: 14'])\n",
    "p9['state'].fillna(method='ffill', inplace=True)\n",
    "p9['agency'] = [i if i not in west_states else 'Totals' for i in p9['agency']]\n",
    "p9.drop(p9.filter(regex='Unnamed').columns, axis=1, inplace=True)\n",
    "p9.drop(index=0, inplace=True)\n",
    "p9 = p9.loc[p9['state'].isin(west_states)]\n",
    "p9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "id": "c09f4d3d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:45:26.144217Z",
     "start_time": "2021-05-18T20:45:26.137153Z"
    }
   },
   "outputs": [],
   "source": [
    "all_2015 = pd.concat([p1, p2, p3, p7, p9])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "7f8a1c11",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:46:10.261278Z",
     "start_time": "2021-05-18T20:46:10.256563Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'../scratch_data/pdf_extracts/resulting_csvs/'"
      ]
     },
     "execution_count": 202,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "2661f754",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-18T20:47:10.109792Z",
     "start_time": "2021-05-18T20:47:10.099661Z"
    }
   },
   "outputs": [],
   "source": [
    "all_2015.reset_index(drop=True, inplace=True)\n",
    "all_2015.to_csv(output_path+'annual_2015_agency.csv', index=False)\n",
    "all_2015.loc[all_2015['agency']=='Totals'].to_csv(output_path+'annual_2015_tot.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3bbb21a",
   "metadata": {},
   "source": [
    "## Literally just type out 2016 like a plebe because it's a disaster\n",
    "States split across pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b364d49",
   "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.9.2"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}