<jupyter_start><jupyter_text>Collecting the Public Comments Regarding the EPA's Revised Definition of "Waters of the United States" (WOTUS)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).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:>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.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.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.  Library Imports<jupyter_code>import glob
import sys
import sqlite3
import warnings
warnings.filterwarnings('ignore') 

import numpy as np
import pandas as pd
pd.set_option('display.max_columns', 50)<jupyter_output><empty_output><jupyter_text>Sanitize Docket File for Web Scraper UseThis 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.<jupyter_code>docket = pd.read_csv('./Data/DOCKET_EPA-HQ-OW-2018-0149.csv', header=2)

docket.head()
docket.info()
# Drop columns with which have all null values
docket.dropna(axis=1, how='all', inplace=True)

# Drop row with withdrawn comment (almost all nulls)
docket.dropna(thresh=6, inplace=True)

docket.info()
# Covert date columns to datetime format
for col in ['Posted Date', 'Received Date', 'Post Mark Date']:
    docket[col] = pd.to_datetime(docket[col], format='%m/%d/%Y')

docket.dtypes
docket.head()
docket['Attachment Count'].value_counts()
docket['File Type'].value_counts()
# Simplify DataFrame, save as file for scraper

# Remove comments where it's imbedded in an attachment
scraper = docket[docket['Attachment Count'].isnull()]

# Save only critical info for scraper
scraper = scraper[['Document ID', 'Document Detail']]

print('Total comments posted directly to site: {0:,.0f}'.format(len(scraper)))

# Uncomment next line to generate the simplified scraper CSV file
# scraper.to_csv('./Data/scraper_csv.csv', index=False)<jupyter_output>Total comments posted directly to site: 8,187<jupyter_text>Create DataFrame with CommentsThis 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.This object is then serialized and saved to avoid this step going forward.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.<jupyter_code># Collect a list of all comment files (saved as .txt format)
comment_files = glob.glob('./Data/Comments/*.txt')
len(comment_files)
# Create DF for each comment, concatenate into one DF
# Uncomment code to build the comment DF from the scraped files

# dfs = []

# for cf in comment_files:
#     try:
#         tmp = pd.read_csv(cf, sep=r'\n', header=None)
#         tmp.index = ['Document ID', 'Comment']
#         tmp = tmp.transpose()
#         dfs.append(tmp)
#     except ValueError as e:
#         print(cf)
#         continue


# com_df = pd.concat(dfs, ignore_index=True)

# com_df.head()
# Serialize com_df to pickled object
# com_df.to_pickle('./Data/comments_df.pkl')
# Read serialized DF vs re-building it
com_df = pd.read_pickle('./Data/comments_df.pkl')

com_df.head()
com_df.shape
# Merge docket DF with comments DF
# Uncomment code to perform the merge directly
# full_df = pd.merge(left=docket, right=com_df, how='right', on='Document ID')

# full_df.head()
# Remove mostly/all-null and columns with all same values
# cols_to_drop = ['Document Type', 'Attachment Count', 'Abstract', 'Status', 'File Type', 'Number Of Pages']

# full_df.drop(cols_to_drop, axis=1, inplace=True)
# Serialize full DF as pickle object
# full_df.to_pickle('./Data/full_df.pkl')
# Test pickle load
full_df = pd.read_pickle('./Data/full_df.pkl')

full_df.head()
full_df.shape
full_df.info()
# Read in labeled sample
labels = pd.read_excel('./Data/comments_labeled.xlsx', index_col=0)
labels.head()
labels.info()
# Merge labels into full DF
full_labeled = full_df.join(labels, rsuffix='_DROP')
full_labeled.head()
full_labeled.shape
# Check that comments align
test = (full_labeled['Comment'] == full_labeled['Comment_DROP']).all()
test
# Drop extra comment column
full_labeled.drop('Comment_DROP', axis=1, inplace=True)

full_labeled.head()
# Serialize full labeled DF as pickle object
# full_labeled.to_pickle('./Data/full_labeled.pkl')<jupyter_output><empty_output><jupyter_text>Export Docket and Label Data to SQLite DatabaseThere 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.<jupyter_code># Connect to the database
conn = sqlite3.connect('./Data/comments.db')
cursor = conn.cursor()
comment_table = full_labeled[["Document ID", "Comment"]]
comment_table.columns = ['ID', 'Comment']
comment_table.head()
# Create the table and set primary key
create_comments_table_sql = """CREATE TABLE IF NOT EXISTS comments (
                                   ID text PRIMARY KEY,
                                   Comment text
                               );"""

cursor.execute(create_comments_table_sql)
conn.commit()
# Save ID and comment into database table
comment_table.to_sql(name='comments', con=conn, if_exists='append', index=False)
conn.commit()
docket.head()
docket.info()
docket.columns = ['doc_title', 'doc_type', 'attachment_count', 'ID',
                  'posted_date', 'received_date', 'doc_subtype',
                  'abstract', 'status', 'post_mark_date', 'file_type',
                  'number_of_pages', 'doc_link']
# Create the table and set primary key
create_docket_table_sql = """CREATE TABLE IF NOT EXISTS docket (
                                 doc_title text,
                                 doc_type text,
                                 attachment_count smallint,
                                 ID text PRIMARY KEY,
                                 posted_date date,
                                 received_date date,
                                 doc_subtype text,
                                 abstract text,
                                 status text,
                                 post_mark_date date,
                                 file_type text,
                                 number_of_pages smallint,
                                 doc_link text
                             );"""

cursor.execute(create_comments_table_sql)
conn.commit()
# Save to database table
docket.to_sql(name='docket', con=conn, if_exists='append', index=False)
conn.commit()
# Create table with ID and labels
labels_table = full_labeled[["Document ID", "Support_Rule_Change"]]
labels_table.columns = ["ID", "Support_Rule_Change"]
labels_table.head()
# Remove unlabeled rows
labels_table.dropna(inplace=True)
labels_table.info()
# Create the table and set primary key
create_labels_table_sql = """CREATE TABLE IF NOT EXISTS labels (
                                 ID text PRIMARY KEY,
                                 Support_Rule_Change smallint
                             );"""

cursor.execute(create_labels_table_sql)
conn.commit()
# Save ID and label into database table
labels_table.to_sql(name='labels', con=conn, if_exists='append', index=False)
# Test that the tables saved correctly
# tmp = pd.read_sql("SELECT * FROM labels", conn)
# tmp.head()
# Save all database changes
conn.commit()

# Close connection
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0873279067430409, 'fraction_numerical': 0.006833484071952568, 'mean_word_length': 3.6810912511759173, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 17, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 20, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '100240', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2835, 'n_tokens_neox': 2693, 'n_words': 1050, 'path': 'Data_Prep.ipynb', 'repo_name': 'Data-Science-for-Conservation/WOTUS_Revision', 'size': '9951'}