source-list-documents-merge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9e39d1c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/shelbyjouppi/.pyenv/versions/3.9.7/lib/python3.9/site-packages/pandas/compat/__init__.py:124: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.\n",
      "  warnings.warn(msg)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "05c64bd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_csv(\"csv/EGLE-AQD-documents.csv\")\n",
    "df2 = pd.read_csv(\"csv/EGLE-AQD-source-list-final.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "62f061c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['datetime'] = pd.to_datetime(df1.date.astype(str), format = \"%Y%m%d\", errors = 'coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2481c34a",
   "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>source_id</th>\n",
       "      <th>doc_type</th>\n",
       "      <th>date</th>\n",
       "      <th>doc_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N8277</td>\n",
       "      <td>ACO</td>\n",
       "      <td>2015-12-17</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N8274</td>\n",
       "      <td>ACO</td>\n",
       "      <td>2015-12-17</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>U04060035</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2019-07-16</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>U04060035</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2020-05-07</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N7824</td>\n",
       "      <td>FCE</td>\n",
       "      <td>2016-05-25</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18299</th>\n",
       "      <td>N1160</td>\n",
       "      <td>test</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18300</th>\n",
       "      <td>K3249</td>\n",
       "      <td>test</td>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18301</th>\n",
       "      <td>A2931</td>\n",
       "      <td>TEST</td>\n",
       "      <td>2021-12-15</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18302</th>\n",
       "      <td>B7221</td>\n",
       "      <td>TEST</td>\n",
       "      <td>2021-11-17</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18303</th>\n",
       "      <td>N6226</td>\n",
       "      <td>TEST</td>\n",
       "      <td>2021-10-26</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18304 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       source_id doc_type       date  \\\n",
       "0          N8277      ACO 2015-12-17   \n",
       "1          N8274      ACO 2015-12-17   \n",
       "2      U04060035      SAR 2019-07-16   \n",
       "3      U04060035      SAR 2020-05-07   \n",
       "4          N7824      FCE 2016-05-25   \n",
       "...          ...      ...        ...   \n",
       "18299      N1160     test 2021-08-17   \n",
       "18300      K3249     test 2021-10-12   \n",
       "18301      A2931     TEST 2021-12-15   \n",
       "18302      B7221     TEST 2021-11-17   \n",
       "18303      N6226     TEST 2021-10-26   \n",
       "\n",
       "                                                 doc_url  \n",
       "0      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "1      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "2      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "3      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "4      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "...                                                  ...  \n",
       "18299  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18300  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18301  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18302  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18303  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "\n",
       "[18304 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['date'] = df1['datetime']\n",
    "df1 = df1.drop(['datetime'],axis=1)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0d86715a",
   "metadata": {
    "scrolled": true
   },
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>county</th>\n",
       "      <th>full_address</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A6260</td>\n",
       "      <td>ALGONAC CAST PRODUCTS INC</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>9300 STONE ROAD, ALGONAC, MI</td>\n",
       "      <td>[-82.54572189999999, 42.6299997]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N8094</td>\n",
       "      <td>SPEEDWAY SUPERAMERICA LLC (REM SITE SSA 8702)</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>440 POINTE TREMBLE RD, ALGONAC, MI</td>\n",
       "      <td>[-82.5371631, 42.6138238]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N6769</td>\n",
       "      <td>SUNSATION PRODUCTS INC</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>9635 KRETZ DR, ALGONAC, MI</td>\n",
       "      <td>[-82.5700738, 42.6222489]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P1024</td>\n",
       "      <td>ALTA EQUIPMENT COMPANY</td>\n",
       "      <td>48001</td>\n",
       "      <td>KENT</td>\n",
       "      <td>8840 BYRON COMMERCE DRIVE, BYRON CENTER, MI</td>\n",
       "      <td>[-85.6703763, 42.80414830000001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>U741802378</td>\n",
       "      <td>FORMER RESTAURANT</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>4219 POINTE TREMBLE ROAD, CLAY TOWNSHIP, MI</td>\n",
       "      <td>[-82.56713599999999, 42.620197]</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5832</th>\n",
       "      <td>N1465</td>\n",
       "      <td>LL WRIGHT HIGH SCHOOL</td>\n",
       "      <td>49938</td>\n",
       "      <td>GOGEBIC</td>\n",
       "      <td>AYER STREET, IRONWOOD, MI</td>\n",
       "      <td>[-90.14738439999999, 46.4579818]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5833</th>\n",
       "      <td>P1069</td>\n",
       "      <td>ALTA EQUIPMENT COMPANY</td>\n",
       "      <td>49779</td>\n",
       "      <td>PRESQUE ISLE</td>\n",
       "      <td>1035 CALCITE ROAD, ROGERS CITY, MI</td>\n",
       "      <td>[-83.8075728, 45.41887500000001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5834</th>\n",
       "      <td>P0067</td>\n",
       "      <td>LAURIE HAMMAR</td>\n",
       "      <td>49880</td>\n",
       "      <td>DELTA</td>\n",
       "      <td>15524 NORTH ROCK 38TH RD, ROCK, MI</td>\n",
       "      <td>[-87.14493639999999, 46.10608380000001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5835</th>\n",
       "      <td>N1973</td>\n",
       "      <td>ABHE &amp; SOVBODA, INC.</td>\n",
       "      <td>48060</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>BLUE WATER BRIDGE &amp; GRATIOT, PORT HURON, MI</td>\n",
       "      <td>[-82.4243249, 43.00017649999999]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5836</th>\n",
       "      <td>U82060106</td>\n",
       "      <td>WAYNE COUNTY GENERAL COMPLAINT SOURCE</td>\n",
       "      <td>48150</td>\n",
       "      <td>WAYNE</td>\n",
       "      <td>WAYNE COUNTY GENERAL COMPLAINT SOURCE, WAYNE C...</td>\n",
       "      <td>[-83.36362679999999, 42.2678114]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5837 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id                                           name zip_code  \\\n",
       "0          A6260                      ALGONAC CAST PRODUCTS INC    48001   \n",
       "1          N8094  SPEEDWAY SUPERAMERICA LLC (REM SITE SSA 8702)    48001   \n",
       "2          N6769                         SUNSATION PRODUCTS INC    48001   \n",
       "3          P1024                         ALTA EQUIPMENT COMPANY    48001   \n",
       "4     U741802378                              FORMER RESTAURANT    48001   \n",
       "...          ...                                            ...      ...   \n",
       "5832       N1465                          LL WRIGHT HIGH SCHOOL    49938   \n",
       "5833       P1069                         ALTA EQUIPMENT COMPANY    49779   \n",
       "5834       P0067                                  LAURIE HAMMAR    49880   \n",
       "5835       N1973                           ABHE & SOVBODA, INC.    48060   \n",
       "5836   U82060106          WAYNE COUNTY GENERAL COMPLAINT SOURCE    48150   \n",
       "\n",
       "            county                                       full_address  \\\n",
       "0      SAINT CLAIR                       9300 STONE ROAD, ALGONAC, MI   \n",
       "1      SAINT CLAIR                 440 POINTE TREMBLE RD, ALGONAC, MI   \n",
       "2      SAINT CLAIR                         9635 KRETZ DR, ALGONAC, MI   \n",
       "3             KENT        8840 BYRON COMMERCE DRIVE, BYRON CENTER, MI   \n",
       "4      SAINT CLAIR        4219 POINTE TREMBLE ROAD, CLAY TOWNSHIP, MI   \n",
       "...            ...                                                ...   \n",
       "5832       GOGEBIC                          AYER STREET, IRONWOOD, MI   \n",
       "5833  PRESQUE ISLE                 1035 CALCITE ROAD, ROGERS CITY, MI   \n",
       "5834         DELTA                 15524 NORTH ROCK 38TH RD, ROCK, MI   \n",
       "5835   SAINT CLAIR        BLUE WATER BRIDGE & GRATIOT, PORT HURON, MI   \n",
       "5836         WAYNE  WAYNE COUNTY GENERAL COMPLAINT SOURCE, WAYNE C...   \n",
       "\n",
       "                                     geometry  \n",
       "0            [-82.54572189999999, 42.6299997]  \n",
       "1                   [-82.5371631, 42.6138238]  \n",
       "2                   [-82.5700738, 42.6222489]  \n",
       "3            [-85.6703763, 42.80414830000001]  \n",
       "4             [-82.56713599999999, 42.620197]  \n",
       "...                                       ...  \n",
       "5832         [-90.14738439999999, 46.4579818]  \n",
       "5833         [-83.8075728, 45.41887500000001]  \n",
       "5834  [-87.14493639999999, 46.10608380000001]  \n",
       "5835         [-82.4243249, 43.00017649999999]  \n",
       "5836         [-83.36362679999999, 42.2678114]  \n",
       "\n",
       "[5837 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['zip_code'] = df2['zip_code'].astype(str).str[:5]\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "de054434",
   "metadata": {
    "scrolled": true
   },
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>county</th>\n",
       "      <th>full_address</th>\n",
       "      <th>geometry</th>\n",
       "      <th>source_id</th>\n",
       "      <th>doc_type</th>\n",
       "      <th>date</th>\n",
       "      <th>doc_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A6260</td>\n",
       "      <td>ALGONAC CAST PRODUCTS INC</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>9300 STONE ROAD, ALGONAC, MI</td>\n",
       "      <td>[-82.54572189999999, 42.6299997]</td>\n",
       "      <td>A6260</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2014-08-27</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N8094</td>\n",
       "      <td>SPEEDWAY SUPERAMERICA LLC (REM SITE SSA 8702)</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>440 POINTE TREMBLE RD, ALGONAC, MI</td>\n",
       "      <td>[-82.5371631, 42.6138238]</td>\n",
       "      <td>N8094</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2018-07-18</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N6769</td>\n",
       "      <td>SUNSATION PRODUCTS INC</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>9635 KRETZ DR, ALGONAC, MI</td>\n",
       "      <td>[-82.5700738, 42.6222489]</td>\n",
       "      <td>N6769</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2015-04-16</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>N6769</td>\n",
       "      <td>SUNSATION PRODUCTS INC</td>\n",
       "      <td>48001</td>\n",
       "      <td>SAINT CLAIR</td>\n",
       "      <td>9635 KRETZ DR, ALGONAC, MI</td>\n",
       "      <td>[-82.5700738, 42.6222489]</td>\n",
       "      <td>N6769</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2020-07-29</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P1024</td>\n",
       "      <td>ALTA EQUIPMENT COMPANY</td>\n",
       "      <td>48001</td>\n",
       "      <td>KENT</td>\n",
       "      <td>8840 BYRON COMMERCE DRIVE, BYRON CENTER, MI</td>\n",
       "      <td>[-85.6703763, 42.80414830000001]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19007</th>\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>A3990</td>\n",
       "      <td>FCE</td>\n",
       "      <td>2016-09-19</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19008</th>\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>M1894</td>\n",
       "      <td>FCE</td>\n",
       "      <td>2018-06-26</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19009</th>\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>U482994292</td>\n",
       "      <td>ACO</td>\n",
       "      <td>2022-01-06</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19010</th>\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>B025</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2016-01-14</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19011</th>\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>N819</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2017-03-30</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19012 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                           name zip_code  \\\n",
       "0      A6260                      ALGONAC CAST PRODUCTS INC    48001   \n",
       "1      N8094  SPEEDWAY SUPERAMERICA LLC (REM SITE SSA 8702)    48001   \n",
       "2      N6769                         SUNSATION PRODUCTS INC    48001   \n",
       "3      N6769                         SUNSATION PRODUCTS INC    48001   \n",
       "4      P1024                         ALTA EQUIPMENT COMPANY    48001   \n",
       "...      ...                                            ...      ...   \n",
       "19007    NaN                                            NaN      NaN   \n",
       "19008    NaN                                            NaN      NaN   \n",
       "19009    NaN                                            NaN      NaN   \n",
       "19010    NaN                                            NaN      NaN   \n",
       "19011    NaN                                            NaN      NaN   \n",
       "\n",
       "            county                                 full_address  \\\n",
       "0      SAINT CLAIR                 9300 STONE ROAD, ALGONAC, MI   \n",
       "1      SAINT CLAIR           440 POINTE TREMBLE RD, ALGONAC, MI   \n",
       "2      SAINT CLAIR                   9635 KRETZ DR, ALGONAC, MI   \n",
       "3      SAINT CLAIR                   9635 KRETZ DR, ALGONAC, MI   \n",
       "4             KENT  8840 BYRON COMMERCE DRIVE, BYRON CENTER, MI   \n",
       "...            ...                                          ...   \n",
       "19007          NaN                                          NaN   \n",
       "19008          NaN                                          NaN   \n",
       "19009          NaN                                          NaN   \n",
       "19010          NaN                                          NaN   \n",
       "19011          NaN                                          NaN   \n",
       "\n",
       "                               geometry   source_id doc_type       date  \\\n",
       "0      [-82.54572189999999, 42.6299997]       A6260      SAR 2014-08-27   \n",
       "1             [-82.5371631, 42.6138238]       N8094      SAR 2018-07-18   \n",
       "2             [-82.5700738, 42.6222489]       N6769      SAR 2015-04-16   \n",
       "3             [-82.5700738, 42.6222489]       N6769      SAR 2020-07-29   \n",
       "4      [-85.6703763, 42.80414830000001]         NaN      NaN        NaT   \n",
       "...                                 ...         ...      ...        ...   \n",
       "19007                               NaN       A3990      FCE 2016-09-19   \n",
       "19008                               NaN       M1894      FCE 2018-06-26   \n",
       "19009                               NaN  U482994292      ACO 2022-01-06   \n",
       "19010                               NaN        B025      SAR 2016-01-14   \n",
       "19011                               NaN        N819      SAR 2017-03-30   \n",
       "\n",
       "                                                 doc_url  \n",
       "0      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "1      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "2      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "3      https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "4                                                    NaN  \n",
       "...                                                  ...  \n",
       "19007  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "19008  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "19009  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "19010  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "19011  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "\n",
       "[19012 rows x 10 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's make sure we get everything just for good measure\n",
    "all_docs = df2.merge(df1, how='outer', left_on='id', right_on='source_id',)\n",
    "all_docs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dd47f4ed",
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>county</th>\n",
       "      <th>full_address</th>\n",
       "      <th>geometry</th>\n",
       "      <th>source_id</th>\n",
       "      <th>doc_type</th>\n",
       "      <th>date</th>\n",
       "      <th>doc_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18974</th>\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>U56150576</td>\n",
       "      <td>ASBVN</td>\n",
       "      <td>2015-06-23</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18975</th>\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>U821509395</td>\n",
       "      <td>ASBVN</td>\n",
       "      <td>2015-11-03</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18976</th>\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>U8218100271</td>\n",
       "      <td>ENFN</td>\n",
       "      <td>2019-05-14</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18977</th>\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>U684800241</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2018-01-16</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18978</th>\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>U331802396</td>\n",
       "      <td>ASBVN</td>\n",
       "      <td>2018-06-28</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id name zip_code county full_address geometry    source_id doc_type  \\\n",
       "18974  NaN  NaN      NaN    NaN          NaN      NaN    U56150576    ASBVN   \n",
       "18975  NaN  NaN      NaN    NaN          NaN      NaN   U821509395    ASBVN   \n",
       "18976  NaN  NaN      NaN    NaN          NaN      NaN  U8218100271     ENFN   \n",
       "18977  NaN  NaN      NaN    NaN          NaN      NaN   U684800241      SAR   \n",
       "18978  NaN  NaN      NaN    NaN          NaN      NaN   U331802396    ASBVN   \n",
       "\n",
       "            date                                            doc_url  \n",
       "18974 2015-06-23  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18975 2015-11-03  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18976 2019-05-14  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18977 2018-01-16  https://www.deq.state.mi.us/aps/downloads/SRN/...  \n",
       "18978 2018-06-28  https://www.deq.state.mi.us/aps/downloads/SRN/...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_docs[all_docs.id.isnull()].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f9fdb8c9",
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>county</th>\n",
       "      <th>full_address</th>\n",
       "      <th>geometry</th>\n",
       "      <th>source_id</th>\n",
       "      <th>doc_type</th>\n",
       "      <th>date</th>\n",
       "      <th>doc_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P1024</td>\n",
       "      <td>ALTA EQUIPMENT COMPANY</td>\n",
       "      <td>48001</td>\n",
       "      <td>KENT</td>\n",
       "      <td>8840 BYRON COMMERCE DRIVE, BYRON CENTER, MI</td>\n",
       "      <td>[-85.6703763, 42.80414830000001]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>P1089</td>\n",
       "      <td>CARL SCHEGEL</td>\n",
       "      <td>48001</td>\n",
       "      <td>JACKSON</td>\n",
       "      <td>4500 MANN ROAD, CONCORD, MI</td>\n",
       "      <td>[-84.68608139999999, 42.1971472]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>P1015</td>\n",
       "      <td>ENBRIDGE ENERGY, LP</td>\n",
       "      <td>48001</td>\n",
       "      <td>IRON</td>\n",
       "      <td>SEC 35, T43N, R32W, CRYSTAL FALLS, MI</td>\n",
       "      <td>[-88.3340242, 46.0980066]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>P1147</td>\n",
       "      <td>ST. MARY'S CEMENT, INC.</td>\n",
       "      <td>48001</td>\n",
       "      <td>WAYNE</td>\n",
       "      <td>9333 DEARBORN STREET, DETROIT, MI</td>\n",
       "      <td>[-83.1290408, 42.2929286]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>P1090</td>\n",
       "      <td>GRAND RAPIDS CREMATORY, INC.</td>\n",
       "      <td>48001</td>\n",
       "      <td>KENT</td>\n",
       "      <td>1649 VIEWPOND DRIVE SE, KENTWOOD, MI</td>\n",
       "      <td>[-85.62398549999999, 42.880871]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id                          name zip_code   county  \\\n",
       "4   P1024        ALTA EQUIPMENT COMPANY    48001     KENT   \n",
       "6   P1089                  CARL SCHEGEL    48001  JACKSON   \n",
       "7   P1015           ENBRIDGE ENERGY, LP    48001     IRON   \n",
       "11  P1147       ST. MARY'S CEMENT, INC.    48001    WAYNE   \n",
       "14  P1090  GRAND RAPIDS CREMATORY, INC.    48001     KENT   \n",
       "\n",
       "                                   full_address  \\\n",
       "4   8840 BYRON COMMERCE DRIVE, BYRON CENTER, MI   \n",
       "6                   4500 MANN ROAD, CONCORD, MI   \n",
       "7         SEC 35, T43N, R32W, CRYSTAL FALLS, MI   \n",
       "11            9333 DEARBORN STREET, DETROIT, MI   \n",
       "14         1649 VIEWPOND DRIVE SE, KENTWOOD, MI   \n",
       "\n",
       "                            geometry source_id doc_type date doc_url  \n",
       "4   [-85.6703763, 42.80414830000001]       NaN      NaN  NaT     NaN  \n",
       "6   [-84.68608139999999, 42.1971472]       NaN      NaN  NaT     NaN  \n",
       "7          [-88.3340242, 46.0980066]       NaN      NaN  NaT     NaN  \n",
       "11         [-83.1290408, 42.2929286]       NaN      NaN  NaT     NaN  \n",
       "14   [-85.62398549999999, 42.880871]       NaN      NaN  NaT     NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_docs[all_docs.date.isnull()].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a00dedb5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# For practicality's sake let's just keep the records that have documents and IDs\n",
    "most_docs = df1.merge(df2, right_on='id', left_on='source_id')\n",
    "most_docs = most_docs.drop(['id'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8cdf84aa",
   "metadata": {
    "scrolled": true
   },
   "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>source_id</th>\n",
       "      <th>doc_type</th>\n",
       "      <th>date</th>\n",
       "      <th>doc_url</th>\n",
       "      <th>name</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>county</th>\n",
       "      <th>full_address</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N8277</td>\n",
       "      <td>ACO</td>\n",
       "      <td>2015-12-17</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>TUSCOLA ENERGY INC - RUMBLE SHARP CTB</td>\n",
       "      <td>48767</td>\n",
       "      <td>TUSCOLA</td>\n",
       "      <td>SE SW SW SEC 19 T14N R8E, AKRON, MI</td>\n",
       "      <td>[-83.5141259, 43.5680758]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N8274</td>\n",
       "      <td>ACO</td>\n",
       "      <td>2015-12-17</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>TUSCOLA ENERGY INC-BOYCE JR TRUSTEE 1&amp;STREETER 1</td>\n",
       "      <td>48701</td>\n",
       "      <td>TUSCOLA</td>\n",
       "      <td>SW SW SW SEC 24 T14N R7E, AKRON, MI</td>\n",
       "      <td>[-83.4647598, 43.5678261]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>U04060035</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2019-07-16</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>49776</td>\n",
       "      <td>ALPENA</td>\n",
       "      <td>ALPENA COUNTY, ALPENA, MI</td>\n",
       "      <td>[-83.4327528, 45.0616794]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>U04060035</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2020-05-07</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>49776</td>\n",
       "      <td>ALPENA</td>\n",
       "      <td>ALPENA COUNTY, ALPENA, MI</td>\n",
       "      <td>[-83.4327528, 45.0616794]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N7824</td>\n",
       "      <td>FCE</td>\n",
       "      <td>2016-05-25</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>RIVERSIDE - FOREST HOME 12</td>\n",
       "      <td>49611</td>\n",
       "      <td>ANTRIM</td>\n",
       "      <td>SW 1/4 NE 1/4 SEC 12 T30N R8W, BELLAIRE, MI</td>\n",
       "      <td>[-85.2111728, 44.9802822]</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18261</th>\n",
       "      <td>P1100</td>\n",
       "      <td>FCE</td>\n",
       "      <td>2021-10-07</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>CORE ENERGY, INC. - CHESTER 16</td>\n",
       "      <td>49751</td>\n",
       "      <td>OTSEGO</td>\n",
       "      <td>SEC 22, T30N, R02W, CHESTER TWP, MI</td>\n",
       "      <td>[-85.8690933, 43.158429]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18262</th>\n",
       "      <td>P1100</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2021-10-07</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>CORE ENERGY, INC. - CHESTER 16</td>\n",
       "      <td>49751</td>\n",
       "      <td>OTSEGO</td>\n",
       "      <td>SEC 22, T30N, R02W, CHESTER TWP, MI</td>\n",
       "      <td>[-85.8690933, 43.158429]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18263</th>\n",
       "      <td>P0985</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2021-09-20</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>RIVERSIDE ENERGY MICHIGAN, LLC</td>\n",
       "      <td>49751</td>\n",
       "      <td>OTSEGO</td>\n",
       "      <td>CHESTER 12 SKLARCZYK C4-13 T30N-R2W SEC 13 NE ...</td>\n",
       "      <td>[-85.8690933, 43.158429]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18264</th>\n",
       "      <td>P1219</td>\n",
       "      <td>SAR</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>BOLLE CONTRACTING</td>\n",
       "      <td>49868</td>\n",
       "      <td>LUCE</td>\n",
       "      <td>14304 GEORGE STREET, NEWBERRY, MI</td>\n",
       "      <td>[-85.51806959999999, 46.3443703]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18265</th>\n",
       "      <td>P1219</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-12-14</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>BOLLE CONTRACTING</td>\n",
       "      <td>49868</td>\n",
       "      <td>LUCE</td>\n",
       "      <td>14304 GEORGE STREET, NEWBERRY, MI</td>\n",
       "      <td>[-85.51806959999999, 46.3443703]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18266 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       source_id doc_type       date  \\\n",
       "0          N8277      ACO 2015-12-17   \n",
       "1          N8274      ACO 2015-12-17   \n",
       "2      U04060035      SAR 2019-07-16   \n",
       "3      U04060035      SAR 2020-05-07   \n",
       "4          N7824      FCE 2016-05-25   \n",
       "...          ...      ...        ...   \n",
       "18261      P1100      FCE 2021-10-07   \n",
       "18262      P1100      SAR 2021-10-07   \n",
       "18263      P0985      SAR 2021-09-20   \n",
       "18264      P1219      SAR 2021-11-09   \n",
       "18265      P1219       VN 2021-12-14   \n",
       "\n",
       "                                                 doc_url  \\\n",
       "0      https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "1      https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "2      https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "3      https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "4      https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "...                                                  ...   \n",
       "18261  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "18262  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "18263  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "18264  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "18265  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "\n",
       "                                                   name zip_code   county  \\\n",
       "0                 TUSCOLA ENERGY INC - RUMBLE SHARP CTB    48767  TUSCOLA   \n",
       "1      TUSCOLA ENERGY INC-BOYCE JR TRUSTEE 1&STREETER 1    48701  TUSCOLA   \n",
       "2                                               UNKNOWN    49776   ALPENA   \n",
       "3                                               UNKNOWN    49776   ALPENA   \n",
       "4                            RIVERSIDE - FOREST HOME 12    49611   ANTRIM   \n",
       "...                                                 ...      ...      ...   \n",
       "18261                    CORE ENERGY, INC. - CHESTER 16    49751   OTSEGO   \n",
       "18262                    CORE ENERGY, INC. - CHESTER 16    49751   OTSEGO   \n",
       "18263                    RIVERSIDE ENERGY MICHIGAN, LLC    49751   OTSEGO   \n",
       "18264                                 BOLLE CONTRACTING    49868     LUCE   \n",
       "18265                                 BOLLE CONTRACTING    49868     LUCE   \n",
       "\n",
       "                                            full_address  \\\n",
       "0                    SE SW SW SEC 19 T14N R8E, AKRON, MI   \n",
       "1                    SW SW SW SEC 24 T14N R7E, AKRON, MI   \n",
       "2                              ALPENA COUNTY, ALPENA, MI   \n",
       "3                              ALPENA COUNTY, ALPENA, MI   \n",
       "4            SW 1/4 NE 1/4 SEC 12 T30N R8W, BELLAIRE, MI   \n",
       "...                                                  ...   \n",
       "18261                SEC 22, T30N, R02W, CHESTER TWP, MI   \n",
       "18262                SEC 22, T30N, R02W, CHESTER TWP, MI   \n",
       "18263  CHESTER 12 SKLARCZYK C4-13 T30N-R2W SEC 13 NE ...   \n",
       "18264                  14304 GEORGE STREET, NEWBERRY, MI   \n",
       "18265                  14304 GEORGE STREET, NEWBERRY, MI   \n",
       "\n",
       "                               geometry  \n",
       "0             [-83.5141259, 43.5680758]  \n",
       "1             [-83.4647598, 43.5678261]  \n",
       "2             [-83.4327528, 45.0616794]  \n",
       "3             [-83.4327528, 45.0616794]  \n",
       "4             [-85.2111728, 44.9802822]  \n",
       "...                                 ...  \n",
       "18261          [-85.8690933, 43.158429]  \n",
       "18262          [-85.8690933, 43.158429]  \n",
       "18263          [-85.8690933, 43.158429]  \n",
       "18264  [-85.51806959999999, 46.3443703]  \n",
       "18265  [-85.51806959999999, 46.3443703]  \n",
       "\n",
       "[18266 rows x 9 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "most_docs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "dc1e4485",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's save this\n",
    "most_docs.to_csv(\"csv/EGLE-AQD-documents-source-info.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cf9a0434",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filtering for Violation Notices\n",
    "vn = most_docs[most_docs.doc_type == 'VN']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "099a4b5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "vn = vn.drop(['id'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61f2195b",
   "metadata": {},
   "outputs": [],
   "source": [
    "vn.to_csv(\"csv/EGLE-AQD-violation-notices.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "86ca2e68",
   "metadata": {
    "scrolled": true
   },
   "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>source_id</th>\n",
       "      <th>doc_type</th>\n",
       "      <th>date</th>\n",
       "      <th>doc_url</th>\n",
       "      <th>name</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>county</th>\n",
       "      <th>full_address</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>N7828</td>\n",
       "      <td>VN</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>MUSKEGON DEVELOPMENT, DOVER FACILITY</td>\n",
       "      <td>48617</td>\n",
       "      <td>OTSEGO</td>\n",
       "      <td>NE 1/4 SEC 30 T31N R2W, DOVER TWP, MI</td>\n",
       "      <td>[-84.16461629999999, 41.87079440000001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>B8878</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-12-19</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>COOPER FOUNDRY INC.</td>\n",
       "      <td>49009</td>\n",
       "      <td>KALAMAZOO</td>\n",
       "      <td>8216 DOUGLAS AVE, KALAMAZOO, MI</td>\n",
       "      <td>[-85.6099768, 42.3796287]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>P1030</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-08-21</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>MCCOIG MATERIALS, LLC</td>\n",
       "      <td>48001</td>\n",
       "      <td>WAYNE</td>\n",
       "      <td>1441 SPRINGWELLS COURT, DETROIT, MI</td>\n",
       "      <td>[-83.10444849999999, 42.2901586]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>P1112</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-08-10</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>MAYZIE, INC. DBA PET PASSAGES</td>\n",
       "      <td>48001</td>\n",
       "      <td>OAKLAND</td>\n",
       "      <td>4577 LAPEER ROAD, ORION TWP, MI</td>\n",
       "      <td>[-83.2413538, 42.7152162]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>P1000</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>https://www.deq.state.mi.us/aps/downloads/SRN/...</td>\n",
       "      <td>QUALITY ROASTING, LLC</td>\n",
       "      <td>48001</td>\n",
       "      <td>TUSCOLA</td>\n",
       "      <td>135 S. BRADLEYVILLE ROAD, REESE, MI</td>\n",
       "      <td>[-83.62141240000001, 43.4917903]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   source_id doc_type       date  \\\n",
       "22     N7828       VN 2016-05-31   \n",
       "33     B8878       VN 2019-12-19   \n",
       "69     P1030       VN 2019-08-21   \n",
       "76     P1112       VN 2021-08-10   \n",
       "80     P1000       VN 2021-04-29   \n",
       "\n",
       "                                              doc_url  \\\n",
       "22  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "33  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "69  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "76  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "80  https://www.deq.state.mi.us/aps/downloads/SRN/...   \n",
       "\n",
       "                                    name zip_code     county  \\\n",
       "22  MUSKEGON DEVELOPMENT, DOVER FACILITY    48617     OTSEGO   \n",
       "33                   COOPER FOUNDRY INC.    49009  KALAMAZOO   \n",
       "69                 MCCOIG MATERIALS, LLC    48001      WAYNE   \n",
       "76         MAYZIE, INC. DBA PET PASSAGES    48001    OAKLAND   \n",
       "80                 QUALITY ROASTING, LLC    48001    TUSCOLA   \n",
       "\n",
       "                             full_address  \\\n",
       "22  NE 1/4 SEC 30 T31N R2W, DOVER TWP, MI   \n",
       "33        8216 DOUGLAS AVE, KALAMAZOO, MI   \n",
       "69    1441 SPRINGWELLS COURT, DETROIT, MI   \n",
       "76        4577 LAPEER ROAD, ORION TWP, MI   \n",
       "80    135 S. BRADLEYVILLE ROAD, REESE, MI   \n",
       "\n",
       "                                   geometry  \n",
       "22  [-84.16461629999999, 41.87079440000001]  \n",
       "33                [-85.6099768, 42.3796287]  \n",
       "69         [-83.10444849999999, 42.2901586]  \n",
       "76                [-83.2413538, 42.7152162]  \n",
       "80         [-83.62141240000001, 43.4917903]  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vn.head()"
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}