{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "24085186-3472-43d3-8b87-b5191c4f6ca6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "\n",
    "module_path = os.path.abspath(os.path.join(\"../..\"))\n",
    "if module_path not in sys.path:\n",
    "    sys.path.append(module_path)\n",
    "\n",
    "from data_pipeline.config import settings\n",
    "from data_pipeline.etl.base import ExtractTransformLoad\n",
    "from data_pipeline.etl.sources.census.etl import CensusETL\n",
    "from data_pipeline.etl.sources.geo_utils import add_tracts_for_geometries\n",
    "from data_pipeline.utils import unzip_file_from_url"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbecd665-1c8a-40fe-a7fc-684ecf73f991",
   "metadata": {},
   "source": [
    "# Grab the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f451ea70-917c-45f9-adf9-9306436b955d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2022-08-16 11:50:57,573 [data_pipeline.utils] INFO     Downloading https://justice40-data.s3.amazonaws.com/data-sources/eAMLIS export of all data.tsv.zip\n",
      "2022-08-16 11:50:57,857 [data_pipeline.utils] INFO     Extracting /home/matt/active/justice40-tool/data/data-pipeline/data_pipeline/data/tmp/abandoned_mine_lands/downloaded-af59fffe-aec2-48b4-a57f-716b8dc7e0a3.zip\n"
     ]
    }
   ],
   "source": [
    "tmp_path = ExtractTransformLoad.DATA_PATH / \"tmp\" / \"abandoned_mine_lands\"\n",
    "# Create directory if it doesn't exist\n",
    "tmp_path.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "eamlis_path_in_s3 = (\n",
    "    settings.AWS_JUSTICE40_DATASOURCES_URL\n",
    "    + \"/eAMLIS export of all data.tsv.zip\"\n",
    ")\n",
    "\n",
    "unzip_file_from_url(\n",
    "    file_url=eamlis_path_in_s3,\n",
    "    download_path=tmp_path,\n",
    "    unzipped_file_path=tmp_path,\n",
    ")\n",
    "\n",
    "eamlis_path = tmp_path / \"eAMLIS export of all data.tsv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5cee0a59-4f69-4678-a7ab-877e57d06f1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/matt/active/justice40-tool/data/data-pipeline/data_pipeline/data/tmp/abandoned_mine_lands/eAMLIS export of all data.tsv'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(eamlis_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5b8119d8-315b-4e87-b286-3767158d63ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(eamlis_path, sep=\"\\t\", low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4fbac263-5868-4fbc-bcb7-168ef479af53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(57149, 42)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf = gpd.GeoDataFrame(\n",
    "    df,\n",
    "    geometry=gpd.points_from_xy(\n",
    "        x=df[\"Longitude\"],\n",
    "        y=df[\"Latitude\"],\n",
    "    ),\n",
    "    crs=\"epsg:4326\",\n",
    ")\n",
    "gdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3b176f62-2d13-4bd2-9211-0ac7c2807146",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3977, 42)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.drop_duplicates(subset=[\"geometry\"], inplace=True, keep=\"last\")\n",
    "gdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a0116c52-58f5-48a6-aa9e-c49873ebafa7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2022-08-16 11:51:28,795 [data_pipeline.etl.sources.geo_utils] DEBUG    Appending tract data to dataframe\n",
      "2022-08-16 11:51:28,796 [data_pipeline.etl.sources.geo_utils] INFO     Loading tract geometry data from census ETL\n",
      "2022-08-16 11:51:28,796 [data_pipeline.etl.sources.geo_utils] DEBUG    Loading existing tract geojson\n",
      "/home/matt/.cache/pypoetry/virtualenvs/justice40-data-pipeline-IwBjhw-4-py3.10/lib/python3.10/site-packages/IPython/core/interactiveshell.py:3553: FutureWarning: The `op` parameter is deprecated and will be removed in a future release. Please use the `predicate` parameter instead.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "gdf_tracts = add_tracts_for_geometries(gdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2a18ba4d-274b-4640-a83c-02ae1d02837c",
   "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>AMLIS Key</th>\n",
       "      <th>State/Tribe</th>\n",
       "      <th>County</th>\n",
       "      <th>Congressional District</th>\n",
       "      <th>Quadrangle Name</th>\n",
       "      <th>Watershed</th>\n",
       "      <th>HUC Code</th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>...</th>\n",
       "      <th>Funded GPRA Acres</th>\n",
       "      <th>Funded Metric Units</th>\n",
       "      <th>Completed Standard Units</th>\n",
       "      <th>Completed Costs</th>\n",
       "      <th>Completed GPRA Acres</th>\n",
       "      <th>Completed Metric Units</th>\n",
       "      <th>Unnamed: 40</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>GEOID10_TRACT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty GeoDataFrame\n",
       "Columns: [AMLIS Key, State/Tribe, County, Congressional District, Quadrangle Name, Watershed, HUC Code, FIPS Code, Latitude, Longitude, Funding Source / Program, Problem Area Name, Problem Area Number, Planning Unit Name, Planning Unit Number, Problem Priority, Problem Type, Mining Type, Ore Types, Date Prepared, Date Revised, Private Owner %, State Owner %, Other Federal Owner %, Park Service Owner %, Forest Service Owner %, Indian Owner %, BLM Owner %, Unfunded Standard Units, Unfunded Costs, Unfunded GPRA Acres, Unfunded Metric Units, Funded Standard Units, Funded Costs, Funded GPRA Acres, Funded Metric Units, Completed Standard Units, Completed Costs, Completed GPRA Acres, Completed Metric Units, Unnamed: 40, geometry, index_right, GEOID10_TRACT]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 44 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_tracts[gdf_tracts.GEOID10_TRACT.isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e9aaa563-4c11-46b0-a64d-6ce053412e9c",
   "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>AMLIS Key</th>\n",
       "      <th>State/Tribe</th>\n",
       "      <th>County</th>\n",
       "      <th>Congressional District</th>\n",
       "      <th>Quadrangle Name</th>\n",
       "      <th>Watershed</th>\n",
       "      <th>HUC Code</th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>...</th>\n",
       "      <th>Funded GPRA Acres</th>\n",
       "      <th>Funded Metric Units</th>\n",
       "      <th>Completed Standard Units</th>\n",
       "      <th>Completed Costs</th>\n",
       "      <th>Completed GPRA Acres</th>\n",
       "      <th>Completed Metric Units</th>\n",
       "      <th>Unnamed: 40</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>GEOID10_TRACT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AK000001</td>\n",
       "      <td>AK</td>\n",
       "      <td>MATANUSKA-SUSITNA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ANCHORAGE C-8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02170</td>\n",
       "      <td>61.6</td>\n",
       "      <td>-149.8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>900.00</td>\n",
       "      <td>33200.0</td>\n",
       "      <td>12.86</td>\n",
       "      <td>274.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-149.80000 61.60000)</td>\n",
       "      <td>9900</td>\n",
       "      <td>02170000401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AK000003</td>\n",
       "      <td>AK</td>\n",
       "      <td>VALDEZ-CORDOVA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Valdez C-1</td>\n",
       "      <td>19050003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02-26</td>\n",
       "      <td>61.6</td>\n",
       "      <td>-144.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.34</td>\n",
       "      <td>9200.0</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-144.00000 61.60000)</td>\n",
       "      <td>9918</td>\n",
       "      <td>02261000100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>AK000080</td>\n",
       "      <td>AK</td>\n",
       "      <td>VALDEZ-CORDOVA CENSU</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MCCARTHY C-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02261</td>\n",
       "      <td>61.5</td>\n",
       "      <td>-142.8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.00</td>\n",
       "      <td>9924.0</td>\n",
       "      <td>0.40</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-142.80000 61.50000)</td>\n",
       "      <td>9918</td>\n",
       "      <td>02261000100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>AK000096</td>\n",
       "      <td>AK</td>\n",
       "      <td>VALDEZ-CORDOVA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MCCARTHY C-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>61.6</td>\n",
       "      <td>-142.8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.00</td>\n",
       "      <td>29729.0</td>\n",
       "      <td>0.20</td>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-142.80000 61.60000)</td>\n",
       "      <td>9918</td>\n",
       "      <td>02261000100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AK000006</td>\n",
       "      <td>AK</td>\n",
       "      <td>MATANUSKA-SUSITNA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ANCHORAGE C-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>61.7</td>\n",
       "      <td>-149.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>9225.0</td>\n",
       "      <td>0.30</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-149.00000 61.70000)</td>\n",
       "      <td>9938</td>\n",
       "      <td>02170000200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    AMLIS Key State/Tribe                County  Congressional District  \\\n",
       "2    AK000001          AK     MATANUSKA-SUSITNA                     1.0   \n",
       "6    AK000003          AK        VALDEZ-CORDOVA                     1.0   \n",
       "100  AK000080          AK  VALDEZ-CORDOVA CENSU                     1.0   \n",
       "113  AK000096          AK        VALDEZ-CORDOVA                     1.0   \n",
       "12   AK000006          AK     MATANUSKA-SUSITNA                     1.0   \n",
       "\n",
       "    Quadrangle Name Watershed  HUC Code FIPS Code  Latitude  Longitude  ...  \\\n",
       "2     ANCHORAGE C-8       NaN       NaN     02170      61.6     -149.8  ...   \n",
       "6        Valdez C-1  19050003       NaN     02-26      61.6     -144.0  ...   \n",
       "100    MCCARTHY C-5       NaN       NaN     02261      61.5     -142.8  ...   \n",
       "113    MCCARTHY C-6       NaN       NaN    Alaska      61.6     -142.8  ...   \n",
       "12    ANCHORAGE C-6       NaN       NaN    Alaska      61.7     -149.0  ...   \n",
       "\n",
       "    Funded GPRA Acres Funded Metric Units  Completed Standard Units  \\\n",
       "2                 0.0                 0.0                    900.00   \n",
       "6                 0.0                 0.0                      0.34   \n",
       "100               0.0                 0.0                      4.00   \n",
       "113               0.0                 0.0                      2.00   \n",
       "12                0.0                 0.0                      3.00   \n",
       "\n",
       "    Completed Costs Completed GPRA Acres Completed Metric Units Unnamed: 40  \\\n",
       "2           33200.0                12.86                 274.30         NaN   \n",
       "6            9200.0                 0.03                   0.34         NaN   \n",
       "100          9924.0                 0.40                   4.00         NaN   \n",
       "113         29729.0                 0.20                   2.00         NaN   \n",
       "12           9225.0                 0.30                   3.00         NaN   \n",
       "\n",
       "                        geometry index_right GEOID10_TRACT  \n",
       "2    POINT (-149.80000 61.60000)        9900   02170000401  \n",
       "6    POINT (-144.00000 61.60000)        9918   02261000100  \n",
       "100  POINT (-142.80000 61.50000)        9918   02261000100  \n",
       "113  POINT (-142.80000 61.60000)        9918   02261000100  \n",
       "12   POINT (-149.00000 61.70000)        9938   02170000200  \n",
       "\n",
       "[5 rows x 44 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_tracts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "901f8c2f-b27a-4223-ad73-71daf51f6bd6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2034, 44)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_tracts.drop_duplicates(\"GEOID10_TRACT\").shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "65bfb3e7-d562-4d59-a2ef-f59d385a7c23",
   "metadata": {},
   "outputs": [],
   "source": [
    "census_tracts = gpd.read_file(CensusETL.NATIONAL_TRACT_JSON_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6b7011aa-49e8-4cf8-877f-b3b36805c18e",
   "metadata": {},
   "outputs": [],
   "source": [
    "census_tracts.set_index(\"GEOID10\", drop=False, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "4334eb8e-c8eb-486a-a4e0-a455f18c038c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3976, 44)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_tracts.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "cbc84001-bb43-46dc-bc6a-306f0e169a20",
   "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>AMLIS Key</th>\n",
       "      <th>State/Tribe</th>\n",
       "      <th>County</th>\n",
       "      <th>Congressional District</th>\n",
       "      <th>Quadrangle Name</th>\n",
       "      <th>Watershed</th>\n",
       "      <th>HUC Code</th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>...</th>\n",
       "      <th>Funded GPRA Acres</th>\n",
       "      <th>Funded Metric Units</th>\n",
       "      <th>Completed Standard Units</th>\n",
       "      <th>Completed Costs</th>\n",
       "      <th>Completed GPRA Acres</th>\n",
       "      <th>Completed Metric Units</th>\n",
       "      <th>Unnamed: 40</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>GEOID10_TRACT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>AK000080</td>\n",
       "      <td>AK</td>\n",
       "      <td>VALDEZ-CORDOVA CENSU</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MCCARTHY C-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02261</td>\n",
       "      <td>61.5</td>\n",
       "      <td>-142.8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9924.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-142.80000 61.50000)</td>\n",
       "      <td>9918</td>\n",
       "      <td>02261000100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>AK000096</td>\n",
       "      <td>AK</td>\n",
       "      <td>VALDEZ-CORDOVA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MCCARTHY C-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>61.6</td>\n",
       "      <td>-142.8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>29729.0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-142.80000 61.60000)</td>\n",
       "      <td>9918</td>\n",
       "      <td>02261000100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AK000015</td>\n",
       "      <td>AK</td>\n",
       "      <td>MATANUSKA-SUSITNA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ANCHORAGE D-4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02170</td>\n",
       "      <td>61.7</td>\n",
       "      <td>-148.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4100.0</td>\n",
       "      <td>0.7</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-148.20000 61.70000)</td>\n",
       "      <td>9938</td>\n",
       "      <td>02170000200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>AK000040</td>\n",
       "      <td>AK</td>\n",
       "      <td>MATANUSKA-SUSITNA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ANCHORAGE C-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02170</td>\n",
       "      <td>61.7</td>\n",
       "      <td>-148.8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20284.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-148.80000 61.70000)</td>\n",
       "      <td>9938</td>\n",
       "      <td>02170000200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>AK000099</td>\n",
       "      <td>AK</td>\n",
       "      <td>MATANUSKA-SUSITNA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02170</td>\n",
       "      <td>61.7</td>\n",
       "      <td>-148.4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-148.40000 61.70000)</td>\n",
       "      <td>9938</td>\n",
       "      <td>02170000200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57095</th>\n",
       "      <td>WY176742</td>\n",
       "      <td>WY</td>\n",
       "      <td>Campbell County</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Little Thunder Reservoir</td>\n",
       "      <td>UPPER CHEYENNE</td>\n",
       "      <td>10120103.0</td>\n",
       "      <td>56005</td>\n",
       "      <td>43.7</td>\n",
       "      <td>-105.4</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>1407322.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>8.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-105.40000 43.70000)</td>\n",
       "      <td>28394</td>\n",
       "      <td>56005000100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56861</th>\n",
       "      <td>WY082926</td>\n",
       "      <td>WY</td>\n",
       "      <td>PLATTE</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Guernsey Reservoir</td>\n",
       "      <td>GLENDO RESERVOIR</td>\n",
       "      <td>10180008.0</td>\n",
       "      <td>56031</td>\n",
       "      <td>42.3</td>\n",
       "      <td>-104.7</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>293122.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-104.70000 42.30000)</td>\n",
       "      <td>28402</td>\n",
       "      <td>56031959100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56864</th>\n",
       "      <td>WY086744</td>\n",
       "      <td>WY</td>\n",
       "      <td>PLATTE</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HELL GAP</td>\n",
       "      <td>GLENDO RESERVOIR</td>\n",
       "      <td>10180008.0</td>\n",
       "      <td>56031</td>\n",
       "      <td>42.4</td>\n",
       "      <td>-104.7</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18848.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-104.70000 42.40000)</td>\n",
       "      <td>28402</td>\n",
       "      <td>56031959100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56930</th>\n",
       "      <td>WY102624</td>\n",
       "      <td>WY</td>\n",
       "      <td>FREMONT</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Lookout Butte</td>\n",
       "      <td>LOWER WIND</td>\n",
       "      <td>10080005.0</td>\n",
       "      <td>56013</td>\n",
       "      <td>43.3</td>\n",
       "      <td>-108.7</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-108.70000 43.30000)</td>\n",
       "      <td>28442</td>\n",
       "      <td>56013940201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57021</th>\n",
       "      <td>WY132533</td>\n",
       "      <td>WY</td>\n",
       "      <td>FREMONT</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Eagle Point</td>\n",
       "      <td>LOWER WIND</td>\n",
       "      <td>10080005.0</td>\n",
       "      <td>56013</td>\n",
       "      <td>43.4</td>\n",
       "      <td>-108.7</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POINT (-108.70000 43.40000)</td>\n",
       "      <td>28442</td>\n",
       "      <td>56013940201</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1942 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      AMLIS Key State/Tribe                County  Congressional District  \\\n",
       "100    AK000080          AK  VALDEZ-CORDOVA CENSU                     1.0   \n",
       "113    AK000096          AK        VALDEZ-CORDOVA                     1.0   \n",
       "30     AK000015          AK     MATANUSKA-SUSITNA                     1.0   \n",
       "45     AK000040          AK     MATANUSKA-SUSITNA                     1.0   \n",
       "117    AK000099          AK     MATANUSKA-SUSITNA                     1.0   \n",
       "...         ...         ...                   ...                     ...   \n",
       "57095  WY176742          WY       Campbell County                     1.0   \n",
       "56861  WY082926          WY                PLATTE                     1.0   \n",
       "56864  WY086744          WY                PLATTE                     1.0   \n",
       "56930  WY102624          WY               FREMONT                     1.0   \n",
       "57021  WY132533          WY               FREMONT                     1.0   \n",
       "\n",
       "                Quadrangle Name         Watershed    HUC Code FIPS Code  \\\n",
       "100                MCCARTHY C-5               NaN         NaN     02261   \n",
       "113                MCCARTHY C-6               NaN         NaN    Alaska   \n",
       "30                ANCHORAGE D-4               NaN         NaN     02170   \n",
       "45                ANCHORAGE C-6               NaN         NaN     02170   \n",
       "117                         NaN               NaN         NaN     02170   \n",
       "...                         ...               ...         ...       ...   \n",
       "57095  Little Thunder Reservoir    UPPER CHEYENNE  10120103.0     56005   \n",
       "56861        Guernsey Reservoir  GLENDO RESERVOIR  10180008.0     56031   \n",
       "56864                  HELL GAP  GLENDO RESERVOIR  10180008.0     56031   \n",
       "56930             Lookout Butte        LOWER WIND  10080005.0     56013   \n",
       "57021               Eagle Point        LOWER WIND  10080005.0     56013   \n",
       "\n",
       "       Latitude  Longitude  ... Funded GPRA Acres Funded Metric Units  \\\n",
       "100        61.5     -142.8  ...               0.0                 0.0   \n",
       "113        61.6     -142.8  ...               0.0                 0.0   \n",
       "30         61.7     -148.2  ...               0.0                 0.0   \n",
       "45         61.7     -148.8  ...               0.0                 0.0   \n",
       "117        61.7     -148.4  ...               0.0                 0.0   \n",
       "...         ...        ...  ...               ...                 ...   \n",
       "57095      43.7     -105.4  ...               3.0                 3.0   \n",
       "56861      42.3     -104.7  ...               0.0                 0.0   \n",
       "56864      42.4     -104.7  ...               0.0                 0.0   \n",
       "56930      43.3     -108.7  ...               0.0                 0.0   \n",
       "57021      43.4     -108.7  ...               0.0                 0.0   \n",
       "\n",
       "       Completed Standard Units Completed Costs Completed GPRA Acres  \\\n",
       "100                         4.0          9924.0                  0.4   \n",
       "113                         2.0         29729.0                  0.2   \n",
       "30                          7.0          4100.0                  0.7   \n",
       "45                          1.0         20284.0                  0.1   \n",
       "117                         0.0             0.0                  0.0   \n",
       "...                         ...             ...                  ...   \n",
       "57095                       8.6       1407322.0                  8.6   \n",
       "56861                       1.0        293122.0                  1.0   \n",
       "56864                       1.0         18848.0                  0.1   \n",
       "56930                       0.0             0.0                  0.0   \n",
       "57021                       0.0             0.0                  0.0   \n",
       "\n",
       "      Completed Metric Units Unnamed: 40                     geometry  \\\n",
       "100                      4.0         NaN  POINT (-142.80000 61.50000)   \n",
       "113                      2.0         NaN  POINT (-142.80000 61.60000)   \n",
       "30                       7.0         NaN  POINT (-148.20000 61.70000)   \n",
       "45                       1.0         NaN  POINT (-148.80000 61.70000)   \n",
       "117                      0.0         NaN  POINT (-148.40000 61.70000)   \n",
       "...                      ...         ...                          ...   \n",
       "57095                    8.6         NaN  POINT (-105.40000 43.70000)   \n",
       "56861                    0.4         NaN  POINT (-104.70000 42.30000)   \n",
       "56864                    1.0         NaN  POINT (-104.70000 42.40000)   \n",
       "56930                    0.0         NaN  POINT (-108.70000 43.30000)   \n",
       "57021                    0.0         NaN  POINT (-108.70000 43.40000)   \n",
       "\n",
       "      index_right GEOID10_TRACT  \n",
       "100          9918   02261000100  \n",
       "113          9918   02261000100  \n",
       "30           9938   02170000200  \n",
       "45           9938   02170000200  \n",
       "117          9938   02170000200  \n",
       "...           ...           ...  \n",
       "57095       28394   56005000100  \n",
       "56861       28402   56031959100  \n",
       "56864       28402   56031959100  \n",
       "56930       28442   56013940201  \n",
       "57021       28442   56013940201  \n",
       "\n",
       "[1942 rows x 44 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_tracts[gdf_tracts.GEOID10_TRACT.duplicated()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdf572df-8a20-4711-812c-376887de8d30",
   "metadata": {},
   "source": [
    "# Assemble test data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6820da8-d61d-4da7-870a-d1e648152ae2",
   "metadata": {},
   "source": [
    "## Get some test tracts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "78cf15fc-a9a6-41bc-ab90-9c94a32e0fcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "example_geoids = pd.read_csv(\n",
    "    \"../tests/sources/example/data/extract.csv\", dtype=\"object\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c0129782-bc75-4cb5-b53f-4a99d4473aa6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15, 2)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "example_geoids.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2647d12f-3eab-4cbc-a656-a514a2c06084",
   "metadata": {},
   "outputs": [],
   "source": [
    "tract_df = gdf_tracts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7ac85550-c8b9-4978-869b-1a233f89a1b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_exaple_data = pd.merge(\n",
    "    example_geoids[\"GEOID10_TRACT\"],\n",
    "    tract_df,\n",
    "    on=\"GEOID10_TRACT\",\n",
    "    how=\"left\",\n",
    "    indicator=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e3e36b6f-8edc-4459-9703-83b790778ce6",
   "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>GEOID10_TRACT</th>\n",
       "      <th>AMLIS Key</th>\n",
       "      <th>State/Tribe</th>\n",
       "      <th>County</th>\n",
       "      <th>Congressional District</th>\n",
       "      <th>Quadrangle Name</th>\n",
       "      <th>Watershed</th>\n",
       "      <th>HUC Code</th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>...</th>\n",
       "      <th>Funded GPRA Acres</th>\n",
       "      <th>Funded Metric Units</th>\n",
       "      <th>Completed Standard Units</th>\n",
       "      <th>Completed Costs</th>\n",
       "      <th>Completed GPRA Acres</th>\n",
       "      <th>Completed Metric Units</th>\n",
       "      <th>Unnamed: 40</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06027000800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06069000802</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06061021322</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15001021010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15001021101</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15007040603</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15007040700</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>15009030100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15009030201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15001021402</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>15001021800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>15009030402</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>15009030800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>15003010201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15007040604</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   GEOID10_TRACT AMLIS Key State/Tribe County  Congressional District  \\\n",
       "0    06027000800       NaN         NaN    NaN                     NaN   \n",
       "1    06069000802       NaN         NaN    NaN                     NaN   \n",
       "2    06061021322       NaN         NaN    NaN                     NaN   \n",
       "3    15001021010       NaN         NaN    NaN                     NaN   \n",
       "4    15001021101       NaN         NaN    NaN                     NaN   \n",
       "5    15007040603       NaN         NaN    NaN                     NaN   \n",
       "6    15007040700       NaN         NaN    NaN                     NaN   \n",
       "7    15009030100       NaN         NaN    NaN                     NaN   \n",
       "8    15009030201       NaN         NaN    NaN                     NaN   \n",
       "9    15001021402       NaN         NaN    NaN                     NaN   \n",
       "10   15001021800       NaN         NaN    NaN                     NaN   \n",
       "11   15009030402       NaN         NaN    NaN                     NaN   \n",
       "12   15009030800       NaN         NaN    NaN                     NaN   \n",
       "13   15003010201       NaN         NaN    NaN                     NaN   \n",
       "14   15007040604       NaN         NaN    NaN                     NaN   \n",
       "\n",
       "   Quadrangle Name Watershed  HUC Code FIPS Code  Latitude  ...  \\\n",
       "0              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "1              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "2              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "3              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "4              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "5              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "6              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "7              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "8              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "9              NaN       NaN       NaN       NaN       NaN  ...   \n",
       "10             NaN       NaN       NaN       NaN       NaN  ...   \n",
       "11             NaN       NaN       NaN       NaN       NaN  ...   \n",
       "12             NaN       NaN       NaN       NaN       NaN  ...   \n",
       "13             NaN       NaN       NaN       NaN       NaN  ...   \n",
       "14             NaN       NaN       NaN       NaN       NaN  ...   \n",
       "\n",
       "    Funded GPRA Acres Funded Metric Units Completed Standard Units  \\\n",
       "0                 NaN                 NaN                      NaN   \n",
       "1                 NaN                 NaN                      NaN   \n",
       "2                 NaN                 NaN                      NaN   \n",
       "3                 NaN                 NaN                      NaN   \n",
       "4                 NaN                 NaN                      NaN   \n",
       "5                 NaN                 NaN                      NaN   \n",
       "6                 NaN                 NaN                      NaN   \n",
       "7                 NaN                 NaN                      NaN   \n",
       "8                 NaN                 NaN                      NaN   \n",
       "9                 NaN                 NaN                      NaN   \n",
       "10                NaN                 NaN                      NaN   \n",
       "11                NaN                 NaN                      NaN   \n",
       "12                NaN                 NaN                      NaN   \n",
       "13                NaN                 NaN                      NaN   \n",
       "14                NaN                 NaN                      NaN   \n",
       "\n",
       "    Completed Costs Completed GPRA Acres Completed Metric Units Unnamed: 40  \\\n",
       "0               NaN                  NaN                    NaN         NaN   \n",
       "1               NaN                  NaN                    NaN         NaN   \n",
       "2               NaN                  NaN                    NaN         NaN   \n",
       "3               NaN                  NaN                    NaN         NaN   \n",
       "4               NaN                  NaN                    NaN         NaN   \n",
       "5               NaN                  NaN                    NaN         NaN   \n",
       "6               NaN                  NaN                    NaN         NaN   \n",
       "7               NaN                  NaN                    NaN         NaN   \n",
       "8               NaN                  NaN                    NaN         NaN   \n",
       "9               NaN                  NaN                    NaN         NaN   \n",
       "10              NaN                  NaN                    NaN         NaN   \n",
       "11              NaN                  NaN                    NaN         NaN   \n",
       "12              NaN                  NaN                    NaN         NaN   \n",
       "13              NaN                  NaN                    NaN         NaN   \n",
       "14              NaN                  NaN                    NaN         NaN   \n",
       "\n",
       "   geometry index_right     _merge  \n",
       "0      None         NaN  left_only  \n",
       "1      None         NaN  left_only  \n",
       "2      None         NaN  left_only  \n",
       "3      None         NaN  left_only  \n",
       "4      None         NaN  left_only  \n",
       "5      None         NaN  left_only  \n",
       "6      None         NaN  left_only  \n",
       "7      None         NaN  left_only  \n",
       "8      None         NaN  left_only  \n",
       "9      None         NaN  left_only  \n",
       "10     None         NaN  left_only  \n",
       "11     None         NaN  left_only  \n",
       "12     None         NaN  left_only  \n",
       "13     None         NaN  left_only  \n",
       "14     None         NaN  left_only  \n",
       "\n",
       "[15 rows x 45 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_exaple_data[merged_exaple_data[\"_merge\"] == \"left_only\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "76f66b32-ce2e-4ad1-93f2-eb566be2e04f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AMLIS Key': 'AK000001',\n",
       " 'State/Tribe': 'AK',\n",
       " 'County': 'MATANUSKA-SUSITNA',\n",
       " 'Congressional District': 1.0,\n",
       " 'Quadrangle Name': 'ANCHORAGE C-8',\n",
       " 'Watershed': nan,\n",
       " 'HUC Code': nan,\n",
       " 'FIPS Code': '02170',\n",
       " 'Latitude': 61.6,\n",
       " 'Longitude': -149.8,\n",
       " 'Funding Source / Program': 'FRA',\n",
       " 'Problem Area Name': 'EAST HOUSTON MINE',\n",
       " 'Problem Area Number': 1,\n",
       " 'Planning Unit Name': 'HOUSTON',\n",
       " 'Planning Unit Number': '1',\n",
       " 'Problem Priority': '1',\n",
       " 'Problem Type': 'VO',\n",
       " 'Mining Type': 'S',\n",
       " 'Ore Types': nan,\n",
       " 'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       " 'Date Revised': '4/23/2014 6:40:28 PM',\n",
       " 'Private Owner %': 0.0,\n",
       " 'State Owner %': 0.0,\n",
       " 'Other Federal Owner %': 0.0,\n",
       " 'Park Service Owner %': 0.0,\n",
       " 'Forest Service Owner %': 0.0,\n",
       " 'Indian Owner %': 0.0,\n",
       " 'BLM Owner %': 0.0,\n",
       " 'Unfunded Standard Units': 0.0,\n",
       " 'Unfunded Costs': 0.0,\n",
       " 'Unfunded GPRA Acres': 0.0,\n",
       " 'Unfunded Metric Units': 0.0,\n",
       " 'Funded Standard Units': 0.0,\n",
       " 'Funded Costs': 0.0,\n",
       " 'Funded GPRA Acres': 0.0,\n",
       " 'Funded Metric Units': 0.0,\n",
       " 'Completed Standard Units': 2.0,\n",
       " 'Completed Costs': 10000.0,\n",
       " 'Completed GPRA Acres': 0.2,\n",
       " 'Completed Metric Units': 2.0,\n",
       " 'Unnamed: 40': nan,\n",
       " 'geometry': <shapely.geometry.point.Point at 0x7f858be68850>}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict(df.iloc[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3caf8290-8248-44e5-8f3b-c67c54e834de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['AMLIS Key', 'BLM Owner %', 'Completed Costs', 'Completed GPRA Acres',\n",
       "       'Completed Metric Units', 'Completed Standard Units',\n",
       "       'Congressional District', 'County', 'Date Prepared', 'Date Revised',\n",
       "       'FIPS Code', 'Forest Service Owner %', 'Funded Costs',\n",
       "       'Funded GPRA Acres', 'Funded Metric Units', 'Funded Standard Units',\n",
       "       'Funding Source / Program', 'HUC Code', 'Indian Owner %', 'Latitude',\n",
       "       'Longitude', 'Mining Type', 'Ore Types', 'Other Federal Owner %',\n",
       "       'Park Service Owner %', 'Planning Unit Name', 'Planning Unit Number',\n",
       "       'Private Owner %', 'Problem Area Name', 'Problem Area Number',\n",
       "       'Problem Priority', 'Problem Type', 'Quadrangle Name', 'State Owner %',\n",
       "       'State/Tribe', 'Unfunded Costs', 'Unfunded GPRA Acres',\n",
       "       'Unfunded Metric Units', 'Unfunded Standard Units', 'Unnamed: 40',\n",
       "       'Watershed', '_merge', 'geometry', 'index_right'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_exaple_data.columns.difference([\"GEOID10_TRACT\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2f0cd691-069e-4367-8f42-e77efd649e77",
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_fake_eamlis_row(tract: str, state: str) -> dict:\n",
    "    generate_fake_eamlis_row._row_id += 1\n",
    "    centroid = census_tracts.loc[tract].geometry.centroid\n",
    "    return {\n",
    "        \"AMLIS Key\": f\"{state}{str(generate_fake_eamlis_row._row_id).zfill(6)}\",\n",
    "        \"State/Tribe\": state,\n",
    "        \"County\": \"MATANUSKA-SUSITNA\",\n",
    "        \"Congressional District\": 1.0,\n",
    "        \"Quadrangle Name\": \"ANCHORAGE C-8\",\n",
    "        \"Watershed\": np.nan,\n",
    "        \"HUC Code\": np.nan,\n",
    "        \"FIPS Code\": \"02170\",\n",
    "        \"Latitude\": centroid.y,\n",
    "        \"Longitude\": centroid.x,\n",
    "        \"Funding Source / Program\": \"FRA\",\n",
    "        \"Problem Area Name\": \"EAST HOUSTON MINE\",\n",
    "        \"Problem Area Number\": 1,\n",
    "        \"Planning Unit Name\": \"HOUSTON\",\n",
    "        \"Planning Unit Number\": \"1\",\n",
    "        \"Problem Priority\": \"1\",\n",
    "        \"Problem Type\": \"VO\",\n",
    "        \"Mining Type\": \"S\",\n",
    "        \"Ore Types\": np.nan,\n",
    "        \"Date Prepared\": \"12/3/1986 12:00:00 AM\",\n",
    "        \"Date Revised\": \"4/23/2014 6:40:28 PM\",\n",
    "        \"Private Owner %\": 0.0,\n",
    "        \"State Owner %\": 0.0,\n",
    "        \"Other Federal Owner %\": 0.0,\n",
    "        \"Park Service Owner %\": 0.0,\n",
    "        \"Forest Service Owner %\": 0.0,\n",
    "        \"Indian Owner %\": 0.0,\n",
    "        \"BLM Owner %\": 0.0,\n",
    "        \"Unfunded Standard Units\": 0.0,\n",
    "        \"Unfunded Costs\": 0.0,\n",
    "        \"Unfunded GPRA Acres\": 0.0,\n",
    "        \"Unfunded Metric Units\": 0.0,\n",
    "        \"Funded Standard Units\": 0.0,\n",
    "        \"Funded Costs\": 0.0,\n",
    "        \"Funded GPRA Acres\": 0.0,\n",
    "        \"Funded Metric Units\": 0.0,\n",
    "        \"Completed Standard Units\": 2.0,\n",
    "        \"Completed Costs\": 10000.0,\n",
    "        \"Completed GPRA Acres\": 0.2,\n",
    "        \"Completed Metric Units\": 2.0,\n",
    "        \"Unnamed: 40\": np.nan,\n",
    "    }\n",
    "\n",
    "\n",
    "generate_fake_eamlis_row._row_id = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "6ab61dda-8642-4706-9b67-b03ed27713fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = []\n",
    "for tract in merged_exaple_data[\n",
    "    merged_exaple_data[\"_merge\"] == \"left_only\"\n",
    "].GEOID10_TRACT:\n",
    "    state = \"HI\"\n",
    "    if tract.startswith(\"06\"):\n",
    "        state = \"CA\"\n",
    "    rows.append(generate_fake_eamlis_row(tract, state))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "93714c4d-3e1d-49c1-9bac-4e553e0bae08",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(rows).to_csv(\n",
    "    \"/home/matt/active/justice40-tool/data/data-pipeline/data_pipeline/tests/sources/eamlis/data/eAMLIS export of all data.tsv.zip\",\n",
    "    index=False,\n",
    "    sep=\"\\t\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "776d4dab-4067-4d36-9ce3-d7c7094ea8c6",
   "metadata": {},
   "source": [
    "## Get the points for the geolocation mock"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a630a0f8-e922-4eb3-9937-6c4b590fd527",
   "metadata": {},
   "outputs": [],
   "source": [
    "lookup_table = {}\n",
    "for tract in merged_exaple_data[\n",
    "    merged_exaple_data[\"_merge\"] == \"left_only\"\n",
    "].GEOID10_TRACT:\n",
    "    centroid = census_tracts.loc[tract].geometry.centroid\n",
    "    lookup_table[(centroid.x, centroid.y)] = tract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "d828b2e0-424e-4040-b167-da46734bcf4d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'AMLIS Key': 'CA000001',\n",
       "  'State/Tribe': 'CA',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 36.25161281807095,\n",
       "  'Longitude': -117.11772856883819,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'CA000002',\n",
       "  'State/Tribe': 'CA',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 36.5498780497345,\n",
       "  'Longitude': -121.0070599015156,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'CA000003',\n",
       "  'State/Tribe': 'CA',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 38.84602113669345,\n",
       "  'Longitude': -121.40564726784282,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000004',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 19.49784370888389,\n",
       "  'Longitude': -155.10321769858746,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000005',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 19.446650238354696,\n",
       "  'Longitude': -154.89548634140738,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000006',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 21.904412260968197,\n",
       "  'Longitude': -159.43665201302525,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000007',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 21.94208315793464,\n",
       "  'Longitude': -159.52362041178708,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000008',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 20.72796381691298,\n",
       "  'Longitude': -156.14177664396527,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000009',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 20.86486713282688,\n",
       "  'Longitude': -156.2497797752935,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000010',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 19.516629328900667,\n",
       "  'Longitude': -155.91378867633992,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000011',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 20.164406070883054,\n",
       "  'Longitude': -155.81110884967674,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000012',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 20.825369670478306,\n",
       "  'Longitude': -156.33064622489087,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000013',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 20.9170439162332,\n",
       "  'Longitude': -156.54289869319305,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000014',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 21.556464980367483,\n",
       "  'Longitude': -157.89225964427064,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan},\n",
       " {'AMLIS Key': 'HI000015',\n",
       "  'State/Tribe': 'HI',\n",
       "  'County': 'MATANUSKA-SUSITNA',\n",
       "  'Congressional District': 1.0,\n",
       "  'Quadrangle Name': 'ANCHORAGE C-8',\n",
       "  'Watershed': nan,\n",
       "  'HUC Code': nan,\n",
       "  'FIPS Code': '02170',\n",
       "  'Latitude': 21.90754283544759,\n",
       "  'Longitude': -159.48416846823164,\n",
       "  'Funding Source / Program': 'FRA',\n",
       "  'Problem Area Name': 'EAST HOUSTON MINE',\n",
       "  'Problem Area Number': 1,\n",
       "  'Planning Unit Name': 'HOUSTON',\n",
       "  'Planning Unit Number': '1',\n",
       "  'Problem Priority': '1',\n",
       "  'Problem Type': 'VO',\n",
       "  'Mining Type': 'S',\n",
       "  'Ore Types': nan,\n",
       "  'Date Prepared': '12/3/1986 12:00:00 AM',\n",
       "  'Date Revised': '4/23/2014 6:40:28 PM',\n",
       "  'Private Owner %': 0.0,\n",
       "  'State Owner %': 0.0,\n",
       "  'Other Federal Owner %': 0.0,\n",
       "  'Park Service Owner %': 0.0,\n",
       "  'Forest Service Owner %': 0.0,\n",
       "  'Indian Owner %': 0.0,\n",
       "  'BLM Owner %': 0.0,\n",
       "  'Unfunded Standard Units': 0.0,\n",
       "  'Unfunded Costs': 0.0,\n",
       "  'Unfunded GPRA Acres': 0.0,\n",
       "  'Unfunded Metric Units': 0.0,\n",
       "  'Funded Standard Units': 0.0,\n",
       "  'Funded Costs': 0.0,\n",
       "  'Funded GPRA Acres': 0.0,\n",
       "  'Funded Metric Units': 0.0,\n",
       "  'Completed Standard Units': 2.0,\n",
       "  'Completed Costs': 10000.0,\n",
       "  'Completed GPRA Acres': 0.2,\n",
       "  'Completed Metric Units': 2.0,\n",
       "  'Unnamed: 40': nan}]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "d6b1d835-deb2-4bbd-b33e-460ba94cf192",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{(-117.11772856883819, 36.25161281807095): '06027000800',\n",
       " (-121.0070599015156, 36.5498780497345): '06069000802',\n",
       " (-121.40564726784282, 38.84602113669345): '06061021322',\n",
       " (-155.10321769858746, 19.49784370888389): '15001021010',\n",
       " (-154.89548634140738, 19.446650238354696): '15001021101',\n",
       " (-159.43665201302525, 21.904412260968197): '15007040603',\n",
       " (-159.52362041178708, 21.94208315793464): '15007040700',\n",
       " (-156.14177664396527, 20.72796381691298): '15009030100',\n",
       " (-156.2497797752935, 20.86486713282688): '15009030201',\n",
       " (-155.91378867633992, 19.516629328900667): '15001021402',\n",
       " (-155.81110884967674, 20.164406070883054): '15001021800',\n",
       " (-156.33064622489087, 20.825369670478306): '15009030402',\n",
       " (-156.54289869319305, 20.9170439162332): '15009030800',\n",
       " (-157.89225964427064, 21.556464980367483): '15003010201',\n",
       " (-159.48416846823164, 21.90754283544759): '15007040604'}"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup_table"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
