Data_Prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Collecting the Public Comments Regarding the EPA's Revised Definition of \"Waters of the United States\" (WOTUS)\n",
    "\n",
    "The Environmental Protection Agency (EPA) and the the Department of the Army conducted a reevaluation and revision of the definition of \"Waters of the United States\" under an executive order from President Trump. Their proposed rule redefines the scope of waters federally regulated under the Clean Water Act (CWA). This rule was open for public comment until April 15, 2019 at [regulations.gov](https://www.regulations.gov/docket?D=EPA-HQ-OW-2018-0149).\n",
    "\n",
    "As of October 2019, there were 625,521 comments received on the docket, of which 11,412 are posted and publicly available under \"public submissions\". The discrepancy can be attributed to the following note on the site:\n",
    "\n",
    ">Agencies review all submissions, however some agencies may choose to redact, or withhold, certain submissions (or portions thereof) such as those containing private or proprietary information, inappropriate language, or duplicate/near duplicate examples of a mass-mail campaign.\n",
    "\n",
    "The regulations.gov site actually has an API to request data programmatically, however API keys are only available to organizations (not individuals). Thus, this project uses a web scraping program to loop over a `.csv` file with a selection of docket information (including unique pages for each public submission) to collect and compile the comments. The original `.csv` file  is saved in the `/Data` directory, or it can be exported directly from the page [to view public comments](https://www.regulations.gov/docketBrowser?rpp=25&so=DESC&sb=commentDueDate&po=0&dct=PS&D=EPA-HQ-OW-2018-0149), the scraper uses a simplified version with comment IDs and URLs only. This file is generated below.\n",
    "\n",
    "There are two noteworthy items about the web scraper. First, the regulations.gov site is rendered using JavaScript, so the web scraper needs to use the [Selenium package](https://github.com/SeleniumHQ/selenium/) to open a browser, pause to let the page load, then grab the comment. Second, not all comments are available as text on the site, since it allows public submissions in attachment form as well as typing them in directly. Approximately 28% of the comments are in attachment form (most of which are `pdf` format), and the scraper could not collect the information within."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Library Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import sys\n",
    "import sqlite3\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore') \n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.set_option('display.max_columns', 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sanitize Docket File for Web Scraper Use\n",
    "\n",
    "This section imports the original docket information file, removes comments in attachment form and saves only the necessary columns (Document ID and Document Detail) for the web scraper."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Document Title</th>\n",
       "      <th>Document Type</th>\n",
       "      <th>Attachment Count</th>\n",
       "      <th>Submitter Last Name</th>\n",
       "      <th>Submitter First Name</th>\n",
       "      <th>Organization</th>\n",
       "      <th>Document ID</th>\n",
       "      <th>Posted Date</th>\n",
       "      <th>Received Date</th>\n",
       "      <th>Comment Start Date</th>\n",
       "      <th>Comment Due Date</th>\n",
       "      <th>Implementation Date</th>\n",
       "      <th>Effective Date</th>\n",
       "      <th>Related RINs</th>\n",
       "      <th>Document SubType</th>\n",
       "      <th>Subject</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Status</th>\n",
       "      <th>Source Citation</th>\n",
       "      <th>OMB Approval Number</th>\n",
       "      <th>FR Citation</th>\n",
       "      <th>Federal Register Number</th>\n",
       "      <th>Start End Page</th>\n",
       "      <th>Special Instructions</th>\n",
       "      <th>Legacy ID</th>\n",
       "      <th>Post Mark Date</th>\n",
       "      <th>File Type</th>\n",
       "      <th>Number Of Pages</th>\n",
       "      <th>Paper Width</th>\n",
       "      <th>Paper Length</th>\n",
       "      <th>Exhibit Type</th>\n",
       "      <th>Exhibit Location</th>\n",
       "      <th>Document Field_1</th>\n",
       "      <th>Document Field_2</th>\n",
       "      <th>Document Detail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>04/29/2019</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</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>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>04/29/2019</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</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>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Comment submitted by Mike Hook, Craighead Coun...</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6690</td>\n",
       "      <td>04/29/2019</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Government Local</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</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>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>05/01/2019</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</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>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comment submitted by Anne Hulick, CT Director,...</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-8930</td>\n",
       "      <td>05/04/2019</td>\n",
       "      <td>04/15/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Company/Organization Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</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>04/15/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Document Title       Document Type  \\\n",
       "0                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "1                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "2  Comment submitted by Mike Hook, Craighead Coun...  PUBLIC SUBMISSIONS   \n",
       "3                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "4  Comment submitted by Anne Hulick, CT Director,...  PUBLIC SUBMISSIONS   \n",
       "\n",
       "   Attachment Count  Submitter Last Name  Submitter First Name  Organization  \\\n",
       "0               NaN                  NaN                   NaN           NaN   \n",
       "1               NaN                  NaN                   NaN           NaN   \n",
       "2               1.0                  NaN                   NaN           NaN   \n",
       "3               NaN                  NaN                   NaN           NaN   \n",
       "4               1.0                  NaN                   NaN           NaN   \n",
       "\n",
       "                Document ID Posted Date Received Date  Comment Start Date  \\\n",
       "0  EPA-HQ-OW-2018-0149-6689  04/29/2019    04/11/2019                 NaN   \n",
       "1  EPA-HQ-OW-2018-0149-6688  04/29/2019    04/11/2019                 NaN   \n",
       "2  EPA-HQ-OW-2018-0149-6690  04/29/2019    04/11/2019                 NaN   \n",
       "3  EPA-HQ-OW-2018-0149-7377  05/01/2019    04/11/2019                 NaN   \n",
       "4  EPA-HQ-OW-2018-0149-8930  05/04/2019    04/15/2019                 NaN   \n",
       "\n",
       "   Comment Due Date  Implementation Date  Effective Date  Related RINs  \\\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",
       "\n",
       "               Document SubType  Subject Abstract  Status  Source Citation  \\\n",
       "0                Public Comment      NaN      NaN  Posted              NaN   \n",
       "1                Public Comment      NaN      NaN  Posted              NaN   \n",
       "2              Government Local      NaN      NaN  Posted              NaN   \n",
       "3                Public Comment      NaN      NaN  Posted              NaN   \n",
       "4  Company/Organization Comment      NaN      NaN  Posted              NaN   \n",
       "\n",
       "   OMB Approval Number  FR Citation  Federal Register Number  Start End Page  \\\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",
       "\n",
       "   Special Instructions  Legacy ID Post Mark Date File Type  Number Of Pages  \\\n",
       "0                   NaN        NaN     04/11/2019       NaN              1.0   \n",
       "1                   NaN        NaN     04/11/2019       NaN              1.0   \n",
       "2                   NaN        NaN     04/11/2019       NaN              1.0   \n",
       "3                   NaN        NaN     04/11/2019       NaN              1.0   \n",
       "4                   NaN        NaN     04/15/2019       NaN              1.0   \n",
       "\n",
       "   Paper Width  Paper Length  Exhibit Type  Exhibit Location  \\\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",
       "\n",
       "   Document Field_1  Document Field_2  \\\n",
       "0               NaN               NaN   \n",
       "1               NaN               NaN   \n",
       "2               NaN               NaN   \n",
       "3               NaN               NaN   \n",
       "4               NaN               NaN   \n",
       "\n",
       "                                     Document Detail  \n",
       "0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "1  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "2  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "3  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "4  https://www.regulations.gov/document?D=EPA-HQ-...  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "docket = pd.read_csv('./Data/DOCKET_EPA-HQ-OW-2018-0149.csv', header=2)\n",
    "\n",
    "docket.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 11412 entries, 0 to 11411\n",
      "Data columns (total 35 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   Document Title           11412 non-null  object \n",
      " 1   Document Type            11412 non-null  object \n",
      " 2   Attachment Count         3224 non-null   float64\n",
      " 3   Submitter Last Name      0 non-null      float64\n",
      " 4   Submitter First Name     0 non-null      float64\n",
      " 5   Organization             0 non-null      float64\n",
      " 6   Document ID              11411 non-null  object \n",
      " 7   Posted Date              11411 non-null  object \n",
      " 8   Received Date            11411 non-null  object \n",
      " 9   Comment Start Date       0 non-null      float64\n",
      " 10  Comment Due Date         0 non-null      float64\n",
      " 11  Implementation Date      0 non-null      float64\n",
      " 12  Effective Date           0 non-null      float64\n",
      " 13  Related RINs             0 non-null      float64\n",
      " 14  Document SubType         11409 non-null  object \n",
      " 15  Subject                  0 non-null      float64\n",
      " 16  Abstract                 72 non-null     object \n",
      " 17  Status                   11411 non-null  object \n",
      " 18  Source Citation          0 non-null      float64\n",
      " 19  OMB Approval Number      0 non-null      float64\n",
      " 20  FR Citation              0 non-null      float64\n",
      " 21  Federal Register Number  0 non-null      float64\n",
      " 22  Start End Page           0 non-null      float64\n",
      " 23  Special Instructions     0 non-null      float64\n",
      " 24  Legacy ID                0 non-null      float64\n",
      " 25  Post Mark Date           11353 non-null  object \n",
      " 26  File Type                50 non-null     object \n",
      " 27  Number Of Pages          11411 non-null  float64\n",
      " 28  Paper Width              0 non-null      float64\n",
      " 29  Paper Length             0 non-null      float64\n",
      " 30  Exhibit Type             0 non-null      float64\n",
      " 31  Exhibit Location         0 non-null      float64\n",
      " 32  Document Field_1         0 non-null      float64\n",
      " 33  Document Field_2         0 non-null      float64\n",
      " 34  Document Detail          11411 non-null  object \n",
      "dtypes: float64(24), object(11)\n",
      "memory usage: 3.0+ MB\n"
     ]
    }
   ],
   "source": [
    "docket.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 11411 entries, 0 to 11411\n",
      "Data columns (total 13 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   Document Title    11411 non-null  object \n",
      " 1   Document Type     11411 non-null  object \n",
      " 2   Attachment Count  3224 non-null   float64\n",
      " 3   Document ID       11411 non-null  object \n",
      " 4   Posted Date       11411 non-null  object \n",
      " 5   Received Date     11411 non-null  object \n",
      " 6   Document SubType  11409 non-null  object \n",
      " 7   Abstract          72 non-null     object \n",
      " 8   Status            11411 non-null  object \n",
      " 9   Post Mark Date    11353 non-null  object \n",
      " 10  File Type         50 non-null     object \n",
      " 11  Number Of Pages   11411 non-null  float64\n",
      " 12  Document Detail   11411 non-null  object \n",
      "dtypes: float64(2), object(11)\n",
      "memory usage: 1.2+ MB\n"
     ]
    }
   ],
   "source": [
    "# Drop columns with which have all null values\n",
    "docket.dropna(axis=1, how='all', inplace=True)\n",
    "\n",
    "# Drop row with withdrawn comment (almost all nulls)\n",
    "docket.dropna(thresh=6, inplace=True)\n",
    "\n",
    "docket.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Document Title              object\n",
       "Document Type               object\n",
       "Attachment Count           float64\n",
       "Document ID                 object\n",
       "Posted Date         datetime64[ns]\n",
       "Received Date       datetime64[ns]\n",
       "Document SubType            object\n",
       "Abstract                    object\n",
       "Status                      object\n",
       "Post Mark Date              object\n",
       "File Type                   object\n",
       "Number Of Pages            float64\n",
       "Document Detail             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Covert date columns to datetime format\n",
    "for col in ['Posted Date', 'Received Date', 'Post Mark Date']:\n",
    "    docket[col] = pd.to_datetime(docket[col], format='%m/%d/%Y')\n",
    "\n",
    "docket.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>Document Title</th>\n",
       "      <th>Document Type</th>\n",
       "      <th>Attachment Count</th>\n",
       "      <th>Document ID</th>\n",
       "      <th>Posted Date</th>\n",
       "      <th>Received Date</th>\n",
       "      <th>Document SubType</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Status</th>\n",
       "      <th>Post Mark Date</th>\n",
       "      <th>File Type</th>\n",
       "      <th>Number Of Pages</th>\n",
       "      <th>Document Detail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Comment submitted by Mike Hook, Craighead Coun...</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6690</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Government Local</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comment submitted by Anne Hulick, CT Director,...</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-8930</td>\n",
       "      <td>2019-05-04</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>Company/Organization Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>04/15/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Document Title       Document Type  \\\n",
       "0                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "1                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "2  Comment submitted by Mike Hook, Craighead Coun...  PUBLIC SUBMISSIONS   \n",
       "3                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "4  Comment submitted by Anne Hulick, CT Director,...  PUBLIC SUBMISSIONS   \n",
       "\n",
       "   Attachment Count               Document ID Posted Date Received Date  \\\n",
       "0               NaN  EPA-HQ-OW-2018-0149-6689  2019-04-29    2019-04-11   \n",
       "1               NaN  EPA-HQ-OW-2018-0149-6688  2019-04-29    2019-04-11   \n",
       "2               1.0  EPA-HQ-OW-2018-0149-6690  2019-04-29    2019-04-11   \n",
       "3               NaN  EPA-HQ-OW-2018-0149-7377  2019-05-01    2019-04-11   \n",
       "4               1.0  EPA-HQ-OW-2018-0149-8930  2019-05-04    2019-04-15   \n",
       "\n",
       "               Document SubType Abstract  Status Post Mark Date File Type  \\\n",
       "0                Public Comment      NaN  Posted     04/11/2019       NaN   \n",
       "1                Public Comment      NaN  Posted     04/11/2019       NaN   \n",
       "2              Government Local      NaN  Posted     04/11/2019       NaN   \n",
       "3                Public Comment      NaN  Posted     04/11/2019       NaN   \n",
       "4  Company/Organization Comment      NaN  Posted     04/15/2019       NaN   \n",
       "\n",
       "   Number Of Pages                                    Document Detail  \n",
       "0              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "1              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "2              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "3              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "4              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "docket.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0     2913\n",
       "2.0      264\n",
       "3.0       12\n",
       "4.0        8\n",
       "9.0        5\n",
       "19.0       4\n",
       "6.0        3\n",
       "20.0       3\n",
       "11.0       3\n",
       "12.0       3\n",
       "10.0       2\n",
       "5.0        1\n",
       "17.0       1\n",
       "7.0        1\n",
       "16.0       1\n",
       "Name: Attachment Count, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "docket['Attachment Count'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pdf     35\n",
       "jpg     10\n",
       "png      3\n",
       "docx     2\n",
       "Name: File Type, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "docket['File Type'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total comments posted directly to site: 8,187\n"
     ]
    }
   ],
   "source": [
    "# Simplify DataFrame, save as file for scraper\n",
    "\n",
    "# Remove comments where it's imbedded in an attachment\n",
    "scraper = docket[docket['Attachment Count'].isnull()]\n",
    "\n",
    "# Save only critical info for scraper\n",
    "scraper = scraper[['Document ID', 'Document Detail']]\n",
    "\n",
    "print('Total comments posted directly to site: {0:,.0f}'.format(len(scraper)))\n",
    "\n",
    "# Uncomment next line to generate the simplified scraper CSV file\n",
    "# scraper.to_csv('./Data/scraper_csv.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create DataFrame with Comments\n",
    "\n",
    "This section builds the full DataFrame of docket information with the text of each comment and the sample of labels. It imports all comments into separate DataFrame objects, concatenates them to create one DF of all IDs and comment text, then merges it with the `docket` DataFrame to combine the comments with other metadata. There were 41 files with blanks for the comment that needed to be manually copied in. Finally, another join is performed to add the labels.\n",
    "\n",
    "This object is then serialized and saved to avoid this step going forward.\n",
    "\n",
    "Note: this code assumes you have run the text file web scraper (`scraper_text.py` - see README.md for instructions) to collect the comments - otherwise the `./Data/Comments/` directory is empty."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8187"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Collect a list of all comment files (saved as .txt format)\n",
    "comment_files = glob.glob('./Data/Comments/*.txt')\n",
    "len(comment_files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create DF for each comment, concatenate into one DF\n",
    "# Uncomment code to build the comment DF from the scraped files\n",
    "\n",
    "# dfs = []\n",
    "\n",
    "# for cf in comment_files:\n",
    "#     try:\n",
    "#         tmp = pd.read_csv(cf, sep=r'\\n', header=None)\n",
    "#         tmp.index = ['Document ID', 'Comment']\n",
    "#         tmp = tmp.transpose()\n",
    "#         dfs.append(tmp)\n",
    "#     except ValueError as e:\n",
    "#         print(cf)\n",
    "#         continue\n",
    "\n",
    "\n",
    "# com_df = pd.concat(dfs, ignore_index=True)\n",
    "\n",
    "# com_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Serialize com_df to pickled object\n",
    "# com_df.to_pickle('./Data/comments_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Document ID</th>\n",
       "      <th>Comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-8815</td>\n",
       "      <td>EPAs proposal would result in a profound weake...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-3940</td>\n",
       "      <td>I am commenting to urge that this new regulati...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-3798</td>\n",
       "      <td>It is absolutely laughable to pretend in regul...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-6186</td>\n",
       "      <td>I strongly oppose the EPAs proposed replacemen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-7298</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Document ID                                            Comment\n",
       "0  EPA-HQ-OW-2018-0149-8815  EPAs proposal would result in a profound weake...\n",
       "1  EPA-HQ-OW-2018-0149-3940  I am commenting to urge that this new regulati...\n",
       "2  EPA-HQ-OW-2018-0149-3798  It is absolutely laughable to pretend in regul...\n",
       "3  EPA-HQ-OW-2018-0149-6186  I strongly oppose the EPAs proposed replacemen...\n",
       "4  EPA-HQ-OW-2018-0149-7298  I write to express my opposition to the Enviro..."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read serialized DF vs re-building it\n",
    "com_df = pd.read_pickle('./Data/comments_df.pkl')\n",
    "\n",
    "com_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8187, 2)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "com_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge docket DF with comments DF\n",
    "# Uncomment code to perform the merge directly\n",
    "# full_df = pd.merge(left=docket, right=com_df, how='right', on='Document ID')\n",
    "\n",
    "# full_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove mostly/all-null and columns with all same values\n",
    "# cols_to_drop = ['Document Type', 'Attachment Count', 'Abstract', 'Status', 'File Type', 'Number Of Pages']\n",
    "\n",
    "# full_df.drop(cols_to_drop, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Serialize full DF as pickle object\n",
    "# full_df.to_pickle('./Data/full_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Document Title</th>\n",
       "      <th>Document ID</th>\n",
       "      <th>Posted Date</th>\n",
       "      <th>Received Date</th>\n",
       "      <th>Document SubType</th>\n",
       "      <th>Post Mark Date</th>\n",
       "      <th>Document Detail</th>\n",
       "      <th>Comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>Please abandon this proposed rollback of the 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6707</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>My husband and I have two wonderful daughters,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comment submitted by T. Dahlin</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7983</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-12</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/12/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>Please do not change the definitions within th...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Document Title               Document ID Posted Date  \\\n",
       "0        Anonymous public comment  EPA-HQ-OW-2018-0149-6689  2019-04-29   \n",
       "1        Anonymous public comment  EPA-HQ-OW-2018-0149-6688  2019-04-29   \n",
       "2        Anonymous public comment  EPA-HQ-OW-2018-0149-7377  2019-05-01   \n",
       "3        Anonymous public comment  EPA-HQ-OW-2018-0149-6707  2019-04-29   \n",
       "4  Comment submitted by T. Dahlin  EPA-HQ-OW-2018-0149-7983  2019-05-01   \n",
       "\n",
       "  Received Date Document SubType Post Mark Date  \\\n",
       "0    2019-04-11   Public Comment     04/11/2019   \n",
       "1    2019-04-11   Public Comment     04/11/2019   \n",
       "2    2019-04-11   Public Comment     04/11/2019   \n",
       "3    2019-04-11   Public Comment     04/11/2019   \n",
       "4    2019-04-12   Public Comment     04/12/2019   \n",
       "\n",
       "                                     Document Detail  \\\n",
       "0  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "1  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "2  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "3  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "4  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "\n",
       "                                             Comment  \n",
       "0  I write to express my opposition to the Enviro...  \n",
       "1  I write to express my opposition to the Enviro...  \n",
       "2  Please abandon this proposed rollback of the 2...  \n",
       "3  My husband and I have two wonderful daughters,...  \n",
       "4  Please do not change the definitions within th...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test pickle load\n",
    "full_df = pd.read_pickle('./Data/full_df.pkl')\n",
    "\n",
    "full_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8187, 8)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 8187 entries, 0 to 8186\n",
      "Data columns (total 8 columns):\n",
      " #   Column            Non-Null Count  Dtype         \n",
      "---  ------            --------------  -----         \n",
      " 0   Document Title    8187 non-null   object        \n",
      " 1   Document ID       8187 non-null   object        \n",
      " 2   Posted Date       8187 non-null   datetime64[ns]\n",
      " 3   Received Date     8187 non-null   datetime64[ns]\n",
      " 4   Document SubType  8187 non-null   object        \n",
      " 5   Post Mark Date    8134 non-null   object        \n",
      " 6   Document Detail   8187 non-null   object        \n",
      " 7   Comment           8187 non-null   object        \n",
      "dtypes: datetime64[ns](2), object(6)\n",
      "memory usage: 575.6+ KB\n"
     ]
    }
   ],
   "source": [
    "full_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Comment</th>\n",
       "      <th>Support_Rule_Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Please abandon this proposed rollback of the 2...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>My husband and I have two wonderful daughters,...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Please do not change the definitions within th...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Comment  Support_Rule_Change\n",
       "0  I write to express my opposition to the Enviro...                  0.0\n",
       "1  I write to express my opposition to the Enviro...                  0.0\n",
       "2  Please abandon this proposed rollback of the 2...                  0.0\n",
       "3  My husband and I have two wonderful daughters,...                  0.0\n",
       "4  Please do not change the definitions within th...                  0.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in labeled sample\n",
    "labels = pd.read_excel('./Data/comments_labeled.xlsx', index_col=0)\n",
    "labels.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 8187 entries, 0 to 8186\n",
      "Data columns (total 2 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   Comment              8187 non-null   object \n",
      " 1   Support_Rule_Change  1221 non-null   float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 191.9+ KB\n"
     ]
    }
   ],
   "source": [
    "labels.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Document Title</th>\n",
       "      <th>Document ID</th>\n",
       "      <th>Posted Date</th>\n",
       "      <th>Received Date</th>\n",
       "      <th>Document SubType</th>\n",
       "      <th>Post Mark Date</th>\n",
       "      <th>Document Detail</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Comment_DROP</th>\n",
       "      <th>Support_Rule_Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>Please abandon this proposed rollback of the 2...</td>\n",
       "      <td>Please abandon this proposed rollback of the 2...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6707</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>My husband and I have two wonderful daughters,...</td>\n",
       "      <td>My husband and I have two wonderful daughters,...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comment submitted by T. Dahlin</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7983</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-12</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/12/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>Please do not change the definitions within th...</td>\n",
       "      <td>Please do not change the definitions within th...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Document Title               Document ID Posted Date  \\\n",
       "0        Anonymous public comment  EPA-HQ-OW-2018-0149-6689  2019-04-29   \n",
       "1        Anonymous public comment  EPA-HQ-OW-2018-0149-6688  2019-04-29   \n",
       "2        Anonymous public comment  EPA-HQ-OW-2018-0149-7377  2019-05-01   \n",
       "3        Anonymous public comment  EPA-HQ-OW-2018-0149-6707  2019-04-29   \n",
       "4  Comment submitted by T. Dahlin  EPA-HQ-OW-2018-0149-7983  2019-05-01   \n",
       "\n",
       "  Received Date Document SubType Post Mark Date  \\\n",
       "0    2019-04-11   Public Comment     04/11/2019   \n",
       "1    2019-04-11   Public Comment     04/11/2019   \n",
       "2    2019-04-11   Public Comment     04/11/2019   \n",
       "3    2019-04-11   Public Comment     04/11/2019   \n",
       "4    2019-04-12   Public Comment     04/12/2019   \n",
       "\n",
       "                                     Document Detail  \\\n",
       "0  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "1  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "2  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "3  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "4  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "\n",
       "                                             Comment  \\\n",
       "0  I write to express my opposition to the Enviro...   \n",
       "1  I write to express my opposition to the Enviro...   \n",
       "2  Please abandon this proposed rollback of the 2...   \n",
       "3  My husband and I have two wonderful daughters,...   \n",
       "4  Please do not change the definitions within th...   \n",
       "\n",
       "                                        Comment_DROP  Support_Rule_Change  \n",
       "0  I write to express my opposition to the Enviro...                  0.0  \n",
       "1  I write to express my opposition to the Enviro...                  0.0  \n",
       "2  Please abandon this proposed rollback of the 2...                  0.0  \n",
       "3  My husband and I have two wonderful daughters,...                  0.0  \n",
       "4  Please do not change the definitions within th...                  0.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge labels into full DF\n",
    "full_labeled = full_df.join(labels, rsuffix='_DROP')\n",
    "full_labeled.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8187, 10)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_labeled.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that comments align\n",
    "test = (full_labeled['Comment'] == full_labeled['Comment_DROP']).all()\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Document Title</th>\n",
       "      <th>Document ID</th>\n",
       "      <th>Posted Date</th>\n",
       "      <th>Received Date</th>\n",
       "      <th>Document SubType</th>\n",
       "      <th>Post Mark Date</th>\n",
       "      <th>Document Detail</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Support_Rule_Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>Please abandon this proposed rollback of the 2...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6707</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/11/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>My husband and I have two wonderful daughters,...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comment submitted by T. Dahlin</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7983</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-12</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>04/12/2019</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "      <td>Please do not change the definitions within th...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Document Title               Document ID Posted Date  \\\n",
       "0        Anonymous public comment  EPA-HQ-OW-2018-0149-6689  2019-04-29   \n",
       "1        Anonymous public comment  EPA-HQ-OW-2018-0149-6688  2019-04-29   \n",
       "2        Anonymous public comment  EPA-HQ-OW-2018-0149-7377  2019-05-01   \n",
       "3        Anonymous public comment  EPA-HQ-OW-2018-0149-6707  2019-04-29   \n",
       "4  Comment submitted by T. Dahlin  EPA-HQ-OW-2018-0149-7983  2019-05-01   \n",
       "\n",
       "  Received Date Document SubType Post Mark Date  \\\n",
       "0    2019-04-11   Public Comment     04/11/2019   \n",
       "1    2019-04-11   Public Comment     04/11/2019   \n",
       "2    2019-04-11   Public Comment     04/11/2019   \n",
       "3    2019-04-11   Public Comment     04/11/2019   \n",
       "4    2019-04-12   Public Comment     04/12/2019   \n",
       "\n",
       "                                     Document Detail  \\\n",
       "0  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "1  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "2  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "3  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "4  https://www.regulations.gov/document?D=EPA-HQ-...   \n",
       "\n",
       "                                             Comment  Support_Rule_Change  \n",
       "0  I write to express my opposition to the Enviro...                  0.0  \n",
       "1  I write to express my opposition to the Enviro...                  0.0  \n",
       "2  Please abandon this proposed rollback of the 2...                  0.0  \n",
       "3  My husband and I have two wonderful daughters,...                  0.0  \n",
       "4  Please do not change the definitions within th...                  0.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop extra comment column\n",
    "full_labeled.drop('Comment_DROP', axis=1, inplace=True)\n",
    "\n",
    "full_labeled.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Serialize full labeled DF as pickle object\n",
    "# full_labeled.to_pickle('./Data/full_labeled.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export Docket and Label Data to SQLite Database\n",
    "\n",
    "There are two versions of the web scraper as noted in the `README.md` file. Instead of running the web scraper a second time to build the database of comments, I used the comment DataFrame created above to populate the tables. The docket and label data are also inserted into their own tables in the database in the code below to have all the information captured in the database file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the database\n",
    "conn = sqlite3.connect('./Data/comments.db')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>I write to express my opposition to the Enviro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>Please abandon this proposed rollback of the 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-6707</td>\n",
       "      <td>My husband and I have two wonderful daughters,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-7983</td>\n",
       "      <td>Please do not change the definitions within th...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         ID                                            Comment\n",
       "0  EPA-HQ-OW-2018-0149-6689  I write to express my opposition to the Enviro...\n",
       "1  EPA-HQ-OW-2018-0149-6688  I write to express my opposition to the Enviro...\n",
       "2  EPA-HQ-OW-2018-0149-7377  Please abandon this proposed rollback of the 2...\n",
       "3  EPA-HQ-OW-2018-0149-6707  My husband and I have two wonderful daughters,...\n",
       "4  EPA-HQ-OW-2018-0149-7983  Please do not change the definitions within th..."
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comment_table = full_labeled[[\"Document ID\", \"Comment\"]]\n",
    "comment_table.columns = ['ID', 'Comment']\n",
    "comment_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the table and set primary key\n",
    "create_comments_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS comments (\n",
    "                                   ID text PRIMARY KEY,\n",
    "                                   Comment text\n",
    "                               );\"\"\"\n",
    "\n",
    "cursor.execute(create_comments_table_sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save ID and comment into database table\n",
    "comment_table.to_sql(name='comments', con=conn, if_exists='append', index=False)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Document Title</th>\n",
       "      <th>Document Type</th>\n",
       "      <th>Attachment Count</th>\n",
       "      <th>Document ID</th>\n",
       "      <th>Posted Date</th>\n",
       "      <th>Received Date</th>\n",
       "      <th>Document SubType</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Status</th>\n",
       "      <th>Post Mark Date</th>\n",
       "      <th>File Type</th>\n",
       "      <th>Number Of Pages</th>\n",
       "      <th>Document Detail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Comment submitted by Mike Hook, Craighead Coun...</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-6690</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Government Local</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anonymous public comment</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>Public Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comment submitted by Anne Hulick, CT Director,...</td>\n",
       "      <td>PUBLIC SUBMISSIONS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>EPA-HQ-OW-2018-0149-8930</td>\n",
       "      <td>2019-05-04</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>Company/Organization Comment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Posted</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.regulations.gov/document?D=EPA-HQ-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Document Title       Document Type  \\\n",
       "0                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "1                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "2  Comment submitted by Mike Hook, Craighead Coun...  PUBLIC SUBMISSIONS   \n",
       "3                           Anonymous public comment  PUBLIC SUBMISSIONS   \n",
       "4  Comment submitted by Anne Hulick, CT Director,...  PUBLIC SUBMISSIONS   \n",
       "\n",
       "   Attachment Count               Document ID Posted Date Received Date  \\\n",
       "0               NaN  EPA-HQ-OW-2018-0149-6689  2019-04-29    2019-04-11   \n",
       "1               NaN  EPA-HQ-OW-2018-0149-6688  2019-04-29    2019-04-11   \n",
       "2               1.0  EPA-HQ-OW-2018-0149-6690  2019-04-29    2019-04-11   \n",
       "3               NaN  EPA-HQ-OW-2018-0149-7377  2019-05-01    2019-04-11   \n",
       "4               1.0  EPA-HQ-OW-2018-0149-8930  2019-05-04    2019-04-15   \n",
       "\n",
       "               Document SubType Abstract  Status Post Mark Date File Type  \\\n",
       "0                Public Comment      NaN  Posted     2019-04-11       NaN   \n",
       "1                Public Comment      NaN  Posted     2019-04-11       NaN   \n",
       "2              Government Local      NaN  Posted     2019-04-11       NaN   \n",
       "3                Public Comment      NaN  Posted     2019-04-11       NaN   \n",
       "4  Company/Organization Comment      NaN  Posted     2019-04-15       NaN   \n",
       "\n",
       "   Number Of Pages                                    Document Detail  \n",
       "0              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "1              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "2              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "3              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  \n",
       "4              1.0  https://www.regulations.gov/document?D=EPA-HQ-...  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "docket.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 11411 entries, 0 to 11411\n",
      "Data columns (total 13 columns):\n",
      " #   Column            Non-Null Count  Dtype         \n",
      "---  ------            --------------  -----         \n",
      " 0   Document Title    11411 non-null  object        \n",
      " 1   Document Type     11411 non-null  object        \n",
      " 2   Attachment Count  3224 non-null   float64       \n",
      " 3   Document ID       11411 non-null  object        \n",
      " 4   Posted Date       11411 non-null  datetime64[ns]\n",
      " 5   Received Date     11411 non-null  datetime64[ns]\n",
      " 6   Document SubType  11409 non-null  object        \n",
      " 7   Abstract          72 non-null     object        \n",
      " 8   Status            11411 non-null  object        \n",
      " 9   Post Mark Date    11353 non-null  datetime64[ns]\n",
      " 10  File Type         50 non-null     object        \n",
      " 11  Number Of Pages   11411 non-null  float64       \n",
      " 12  Document Detail   11411 non-null  object        \n",
      "dtypes: datetime64[ns](3), float64(2), object(8)\n",
      "memory usage: 1.2+ MB\n"
     ]
    }
   ],
   "source": [
    "docket.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "docket.columns = ['doc_title', 'doc_type', 'attachment_count', 'ID',\n",
    "                  'posted_date', 'received_date', 'doc_subtype',\n",
    "                  'abstract', 'status', 'post_mark_date', 'file_type',\n",
    "                  'number_of_pages', 'doc_link']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the table and set primary key\n",
    "create_docket_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS docket (\n",
    "                                 doc_title text,\n",
    "                                 doc_type text,\n",
    "                                 attachment_count smallint,\n",
    "                                 ID text PRIMARY KEY,\n",
    "                                 posted_date date,\n",
    "                                 received_date date,\n",
    "                                 doc_subtype text,\n",
    "                                 abstract text,\n",
    "                                 status text,\n",
    "                                 post_mark_date date,\n",
    "                                 file_type text,\n",
    "                                 number_of_pages smallint,\n",
    "                                 doc_link text\n",
    "                             );\"\"\"\n",
    "\n",
    "cursor.execute(create_comments_table_sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save to database table\n",
    "docket.to_sql(name='docket', con=conn, if_exists='append', index=False)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table with ID and labels\n",
    "labels_table = full_labeled[[\"Document ID\", \"Support_Rule_Change\"]]\n",
    "labels_table.columns = [\"ID\", \"Support_Rule_Change\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>Support_Rule_Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-6689</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-6688</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-7377</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-6707</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EPA-HQ-OW-2018-0149-7983</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         ID  Support_Rule_Change\n",
       "0  EPA-HQ-OW-2018-0149-6689                  0.0\n",
       "1  EPA-HQ-OW-2018-0149-6688                  0.0\n",
       "2  EPA-HQ-OW-2018-0149-7377                  0.0\n",
       "3  EPA-HQ-OW-2018-0149-6707                  0.0\n",
       "4  EPA-HQ-OW-2018-0149-7983                  0.0"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "labels_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove unlabeled rows\n",
    "labels_table.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1221 entries, 0 to 8186\n",
      "Data columns (total 2 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   ID                   1221 non-null   object \n",
      " 1   Support_Rule_Change  1221 non-null   float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 28.6+ KB\n"
     ]
    }
   ],
   "source": [
    "labels_table.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the table and set primary key\n",
    "create_labels_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS labels (\n",
    "                                 ID text PRIMARY KEY,\n",
    "                                 Support_Rule_Change smallint\n",
    "                             );\"\"\"\n",
    "\n",
    "cursor.execute(create_labels_table_sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save ID and label into database table\n",
    "labels_table.to_sql(name='labels', con=conn, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test that the tables saved correctly\n",
    "# tmp = pd.read_sql(\"SELECT * FROM labels\", conn)\n",
    "# tmp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save all database changes\n",
    "conn.commit()\n",
    "\n",
    "# Close connection\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}