Data Cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary\n",
    "This notebook removes comments containing words irrelevant to Title IX from a databse. The database was web scraped from the regulations.gov Title IX public request for comments.\n",
    "\n",
    "The cleaned data is then saved for use in later notebooks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas\n",
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load in the database file.\n",
    "json_data = json.load(open('./data/db3.json'))\n",
    "\n",
    "# Convert the data from regular JSON to a more easily-manipulated dataframe\n",
    "data = json_normalize(json_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the database imported, lets inspect it and make sure it's ready for analysis and use in further notebooks."
   ]
  },
  {
   "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>doc._id</th>\n",
       "      <th>doc._rev</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",
       "      <th>id</th>\n",
       "      <th>key</th>\n",
       "      <th>value.rev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0f3b11691179a9abe35b8f18a9000950</td>\n",
       "      <td>1-4773e83804ba65b25953a680828dc134</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>0f3b11691179a9abe35b8f18a9000950</td>\n",
       "      <td>0f3b11691179a9abe35b8f18a9000950</td>\n",
       "      <td>1-4773e83804ba65b25953a680828dc134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0f3b11691179a9abe35b8f18a9001152</td>\n",
       "      <td>1-49b2757833fa575401bca5e42cbae985</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>0f3b11691179a9abe35b8f18a9001152</td>\n",
       "      <td>0f3b11691179a9abe35b8f18a9001152</td>\n",
       "      <td>1-49b2757833fa575401bca5e42cbae985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0f3b11691179a9abe35b8f18a900209a</td>\n",
       "      <td>1-72d2132b690f9c0de839db6908f376b7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>0f3b11691179a9abe35b8f18a900209a</td>\n",
       "      <td>0f3b11691179a9abe35b8f18a900209a</td>\n",
       "      <td>1-72d2132b690f9c0de839db6908f376b7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            doc._id                            doc._rev  \\\n",
       "0  0f3b11691179a9abe35b8f18a9000950  1-4773e83804ba65b25953a680828dc134   \n",
       "1  0f3b11691179a9abe35b8f18a9001152  1-49b2757833fa575401bca5e42cbae985   \n",
       "2  0f3b11691179a9abe35b8f18a900209a  1-72d2132b690f9c0de839db6908f376b7   \n",
       "\n",
       "  doc.attachment_download  doc.attachment_download -href doc.attachment_name  \\\n",
       "0                      NaN                           NaN                 NaN   \n",
       "1                      NaN                           NaN                 NaN   \n",
       "2                      NaN                           NaN                 NaN   \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                                id  \\\n",
       "0  Heather Hirsch        MN   55016  0f3b11691179a9abe35b8f18a9000950   \n",
       "1  Maryann Decker        UT   84737  0f3b11691179a9abe35b8f18a9001152   \n",
       "2    Greg Lofgren        WI   53704  0f3b11691179a9abe35b8f18a900209a   \n",
       "\n",
       "                                key                           value.rev  \n",
       "0  0f3b11691179a9abe35b8f18a9000950  1-4773e83804ba65b25953a680828dc134  \n",
       "1  0f3b11691179a9abe35b8f18a9001152  1-49b2757833fa575401bca5e42cbae985  \n",
       "2  0f3b11691179a9abe35b8f18a900209a  1-72d2132b690f9c0de839db6908f376b7  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lets see how many comments (data samples) are present.\n",
    "len(data)\n",
    "\n",
    "# Take a look at the last 3 comments\n",
    "display(data[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above output has a few unnecessary columns, namely, ones that were created when the database was imported from a CouchDB instance. The next code block removes these columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop couchDB labels from the dataframe.\n",
    "data.drop(labels=['doc._id', 'doc._rev', 'id', 'key', 'value.rev'], inplace=True, axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the dataset \"cleaned up\" we inspect it again for any further issues."
   ]
  },
  {
   "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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  doc.attachment_download  doc.attachment_download -href doc.attachment_name  \\\n",
       "0                      NaN                           NaN                 NaN   \n",
       "\n",
       "  doc.category       doc.city  \\\n",
       "0         None  United States   \n",
       "\n",
       "                                    doc.comment_body      doc.country  \\\n",
       "0  Dear Assistant General Counsel Hilary Malawer,...  Parent/Relative   \n",
       "\n",
       "         doc.name doc.state doc.zip  \n",
       "0  Heather Hirsch        MN   55016  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(data[:1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The cleaned up database is exported as `data_cleaned` for use in other notebooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_json('./data/data_cleaned.json', orient='records')"
   ]
  }
 ],
 "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
}