Download Attachments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary\n",
    "This notebook takes a look at the `data_cleaned` file from the Data Cleanup notebook. \n",
    "The comments are separated into two new datasets: `has_attachment` and `no_attachment`. The notebook then downloads the attachments from comments in the `has_attachment` dataset from the regulations.gov website. \n",
    "The downloaded attachments are saved in this repository under the /attachments folder.\n",
    "\n",
    "The `has_attachment` and `no_attachments` datasets are then exported for use in other notebooks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas\n",
    "import urllib.request\n",
    "import tempfile\n",
    "import os\n",
    "import time\n",
    "from subprocess import (PIPE, Popen)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Separating the two datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dataset from the Data Cleanup notebook, `data_cleaned` is imported. The following two codeblocks then separate this database into two new ones, `has_attachment` and `no_attachment`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the cleaned data.\n",
    "data = pandas.read_json('./data/data_cleaned.json', orient='records', dtype='false')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "520"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get a subset of the database with only documents containing attachments.\n",
    "has_attachment = data.dropna(subset=['doc.attachment_download -href'])\n",
    "\n",
    "# Take a look at how many comments are in this subset and what some of the comments look like.\n",
    "len(has_attachment)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16012"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create another subset of the database, but this time with comments that _don't_ have an attachment\n",
    "no_attachment = data[pandas.isnull(data['doc.attachment_download -href'])]\n",
    "\n",
    "# See how many comments are in this subset and the first few comments.\n",
    "len(no_attachment)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To confirm that we aren't missing any comments, we check that the length of `has_attachment` and `no_attachment` sum to the length of the original dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(has_attachment) + len(no_attachment) == len(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspecting the new datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first 3 comments of each dataset are displayed below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>doc.attachment_download</th>\n",
       "      <th>doc.attachment_download -href</th>\n",
       "      <th>doc.attachment_name</th>\n",
       "      <th>doc.category</th>\n",
       "      <th>doc.city</th>\n",
       "      <th>doc.comment_body</th>\n",
       "      <th>doc.country</th>\n",
       "      <th>doc.name</th>\n",
       "      <th>doc.state</th>\n",
       "      <th>doc.zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2406</th>\n",
       "      <td></td>\n",
       "      <td>https://www.regulations.gov/contentStreamer?do...</td>\n",
       "      <td>None</td>\n",
       "      <td>Law firm</td>\n",
       "      <td>Arlington</td>\n",
       "      <td>As a lawyer who once practiced education and c...</td>\n",
       "      <td>United States</td>\n",
       "      <td>Hans Bader</td>\n",
       "      <td>VA</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2425</th>\n",
       "      <td></td>\n",
       "      <td>https://www.regulations.gov/contentStreamer?do...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>National Advocacy Organization</td>\n",
       "      <td>Attached, please find comments from The Educat...</td>\n",
       "      <td>None</td>\n",
       "      <td>Daria Hall</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2427</th>\n",
       "      <td></td>\n",
       "      <td>https://www.regulations.gov/contentStreamer?do...</td>\n",
       "      <td>None</td>\n",
       "      <td>Community Organization</td>\n",
       "      <td>Oakton</td>\n",
       "      <td>Dear Members of the United State Department of...</td>\n",
       "      <td>United States</td>\n",
       "      <td>Janet Samuelson</td>\n",
       "      <td>VA</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     doc.attachment_download   \\\n",
       "2406                            \n",
       "2425                            \n",
       "2427                            \n",
       "\n",
       "                          doc.attachment_download -href doc.attachment_name  \\\n",
       "2406  https://www.regulations.gov/contentStreamer?do...                None   \n",
       "2425  https://www.regulations.gov/contentStreamer?do...                None   \n",
       "2427  https://www.regulations.gov/contentStreamer?do...                None   \n",
       "\n",
       "                doc.category                        doc.city  \\\n",
       "2406                Law firm                       Arlington   \n",
       "2425                    None  National Advocacy Organization   \n",
       "2427  Community Organization                          Oakton   \n",
       "\n",
       "                                       doc.comment_body    doc.country  \\\n",
       "2406  As a lawyer who once practiced education and c...  United States   \n",
       "2425  Attached, please find comments from The Educat...           None   \n",
       "2427  Dear Members of the United State Department of...  United States   \n",
       "\n",
       "             doc.name doc.state doc.zip  \n",
       "2406       Hans Bader        VA    None  \n",
       "2425       Daria Hall      None    None  \n",
       "2427  Janet Samuelson        VA    None  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(has_attachment[:3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "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>doc.attachment_download</th>\n",
       "      <th>doc.attachment_download -href</th>\n",
       "      <th>doc.attachment_name</th>\n",
       "      <th>doc.category</th>\n",
       "      <th>doc.city</th>\n",
       "      <th>doc.comment_body</th>\n",
       "      <th>doc.country</th>\n",
       "      <th>doc.name</th>\n",
       "      <th>doc.state</th>\n",
       "      <th>doc.zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>United States</td>\n",
       "      <td>Dear Assistant General Counsel Hilary Malawer,...</td>\n",
       "      <td>Parent/Relative</td>\n",
       "      <td>Heather Hirsch</td>\n",
       "      <td>MN</td>\n",
       "      <td>55016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>United States</td>\n",
       "      <td>Dear Assistant General Counsel Hilary Malawer,...</td>\n",
       "      <td>Other</td>\n",
       "      <td>Maryann Decker</td>\n",
       "      <td>UT</td>\n",
       "      <td>84737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>United States</td>\n",
       "      <td>Dear Assistant General Counsel Hilary Malawer,...</td>\n",
       "      <td>Other</td>\n",
       "      <td>Greg Lofgren</td>\n",
       "      <td>WI</td>\n",
       "      <td>53704</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  doc.attachment_download  doc.attachment_download -href doc.attachment_name  \\\n",
       "0                     None                          None                None   \n",
       "1                     None                          None                None   \n",
       "2                     None                          None                None   \n",
       "\n",
       "  doc.category       doc.city  \\\n",
       "0         None  United States   \n",
       "1         None  United States   \n",
       "2         None  United States   \n",
       "\n",
       "                                    doc.comment_body      doc.country  \\\n",
       "0  Dear Assistant General Counsel Hilary Malawer,...  Parent/Relative   \n",
       "1  Dear Assistant General Counsel Hilary Malawer,...            Other   \n",
       "2  Dear Assistant General Counsel Hilary Malawer,...            Other   \n",
       "\n",
       "         doc.name doc.state doc.zip  \n",
       "0  Heather Hirsch        MN   55016  \n",
       "1  Maryann Decker        UT   84737  \n",
       "2    Greg Lofgren        WI   53704  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(no_attachment[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because the indices of each of the databases are out of order, they are reindexed.\n",
    "\n",
    "The two datasets are then saved for later use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "has_attachment.reset_index(drop=True);\n",
    "no_attachment.reset_index(drop=True);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving the `has_attachment` dataset as `has_attachment.json`\n",
    "has_attachment.to_json('./data/has_attachment.json', orient='records')\n",
    "\n",
    "# Saving the `no_attachment` dataset as `no_attachment.json`\n",
    "no_attachment.to_json('./data/no_attachment.json', orient='records')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Downloading the attachments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Downloading the attachments is a multi-step process.\n",
    "\n",
    "Attachments are either PDFs or Microsoft Word documents. The document type is first identified and then downloaded according its type. The name of the document is the same as the document's index in `has_attachment`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Header information for downloading the PDF attachment.\n",
    "user_agent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:58.0) Gecko/20100101 Firefox/58.0'\n",
    "headers = {'User-Agent': user_agent}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download all attachments as .pdf or .docx and name them based on their index in `has_attachment`.\n",
    "def download_attachments(row):\n",
    "    global counter\n",
    "    url = str(row['doc.attachment_download -href'])\n",
    "    \n",
    "    # If the last 5 characters in the URL are `msw12` \n",
    "    # then the file is a Microsoft Word document, otherwise it's a PDF\n",
    "    if (url[-5:] == 'msw12'):\n",
    "        extension = '.docx'\n",
    "    else:\n",
    "        extension = '.pdf'\n",
    "        \n",
    "    name = str(counter) + extension\n",
    "    \n",
    "    try:\n",
    "        request = urllib.request.Request(url, headers=headers)\n",
    "        response = urllib.request.urlopen(request).read()        \n",
    "        file = open(name, 'wb+')\n",
    "        file.write(response)\n",
    "        file.close()\n",
    "        \n",
    "    except:\n",
    "        print(\"failed to download\", str(counter))\n",
    "    \n",
    "    # To prevent the download server from blocking our IP, we wait 3 seconds before the next download.\n",
    "    time.sleep(3)\n",
    "    counter = counter + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14509    None\n",
       "14510    None\n",
       "14512    None\n",
       "14523    None\n",
       "14524    None\n",
       "14540    None\n",
       "14541    None\n",
       "14543    None\n",
       "14544    None\n",
       "14545    None\n",
       "14553    None\n",
       "15065    None\n",
       "15093    None\n",
       "15096    None\n",
       "15170    None\n",
       "15205    None\n",
       "15231    None\n",
       "15232    None\n",
       "15248    None\n",
       "15265    None\n",
       "15268    None\n",
       "15272    None\n",
       "15273    None\n",
       "15281    None\n",
       "15295    None\n",
       "15296    None\n",
       "15299    None\n",
       "15303    None\n",
       "15305    None\n",
       "15307    None\n",
       "15308    None\n",
       "15309    None\n",
       "15330    None\n",
       "15331    None\n",
       "15332    None\n",
       "15338    None\n",
       "15396    None\n",
       "15422    None\n",
       "15438    None\n",
       "15443    None\n",
       "15457    None\n",
       "15481    None\n",
       "15484    None\n",
       "15495    None\n",
       "15550    None\n",
       "15561    None\n",
       "15573    None\n",
       "15578    None\n",
       "15585    None\n",
       "15591    None\n",
       "15595    None\n",
       "15609    None\n",
       "16150    None\n",
       "16211    None\n",
       "16213    None\n",
       "16324    None\n",
       "16349    None\n",
       "dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Counter for filenames in download_attachments.\n",
    "counter = 1\n",
    "os.chdir(\"../..\")\n",
    "# Save files in resources/attachments.\n",
    "os.chdir(\"./resources/attachments\")\n",
    "\n",
    "# Download all the attachments in has_attachment.\n",
    "has_attachment.apply(download_attachments, axis=1)"
   ]
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}