opioid-files/opioid-file-me-data-loader-cleaner.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>REPORTER_DEA_NO</th>\n",
       "      <th>REPORTER_BUS_ACT</th>\n",
       "      <th>REPORTER_NAME</th>\n",
       "      <th>REPORTER_ADDL_CO_INFO</th>\n",
       "      <th>REPORTER_ADDRESS1</th>\n",
       "      <th>REPORTER_ADDRESS2</th>\n",
       "      <th>REPORTER_CITY</th>\n",
       "      <th>REPORTER_STATE</th>\n",
       "      <th>REPORTER_ZIP</th>\n",
       "      <th>REPORTER_COUNTY</th>\n",
       "      <th>...</th>\n",
       "      <th>DOSAGE_UNIT</th>\n",
       "      <th>TRANSACTION_ID</th>\n",
       "      <th>Product_Name</th>\n",
       "      <th>Ingredient_Name</th>\n",
       "      <th>Measure</th>\n",
       "      <th>MME_Conversion_Factor</th>\n",
       "      <th>Combined_Labeler_Name</th>\n",
       "      <th>Revised_Company_Name</th>\n",
       "      <th>Reporter_family</th>\n",
       "      <th>dos_str</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PB0020139</td>\n",
       "      <td>DISTRIBUTOR</td>\n",
       "      <td>BURLINGTON DRUG COMPANY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91 CATAMOUNT DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MILTON</td>\n",
       "      <td>VT</td>\n",
       "      <td>5468</td>\n",
       "      <td>CHITTENDEN</td>\n",
       "      <td>...</td>\n",
       "      <td>500.0</td>\n",
       "      <td>601003321</td>\n",
       "      <td>HYDROCODONE BITARTRATE AND ACETA 10M</td>\n",
       "      <td>HYDROCODONE BITARTRATE HEMIPENTAHYDRATE</td>\n",
       "      <td>TAB</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Par Pharmaceutical</td>\n",
       "      <td>Endo Pharmaceuticals, Inc.</td>\n",
       "      <td>Burlington Drug Company</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PB0020139</td>\n",
       "      <td>DISTRIBUTOR</td>\n",
       "      <td>BURLINGTON DRUG COMPANY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91 CATAMOUNT DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MILTON</td>\n",
       "      <td>VT</td>\n",
       "      <td>5468</td>\n",
       "      <td>CHITTENDEN</td>\n",
       "      <td>...</td>\n",
       "      <td>500.0</td>\n",
       "      <td>601003928</td>\n",
       "      <td>ENDOCET OXYCODO HCL5MG&amp;AC TAB</td>\n",
       "      <td>OXYCODONE HYDROCHLORIDE</td>\n",
       "      <td>TAB</td>\n",
       "      <td>1.5</td>\n",
       "      <td>Par Pharmaceutical</td>\n",
       "      <td>Endo Pharmaceuticals, Inc.</td>\n",
       "      <td>Burlington Drug Company</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PB0020139</td>\n",
       "      <td>DISTRIBUTOR</td>\n",
       "      <td>BURLINGTON DRUG COMPANY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91 CATAMOUNT DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MILTON</td>\n",
       "      <td>VT</td>\n",
       "      <td>5468</td>\n",
       "      <td>CHITTENDEN</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>602007127</td>\n",
       "      <td>ENDOCET - 10MG OXYCODONE.HCL/325MG A</td>\n",
       "      <td>OXYCODONE HYDROCHLORIDE</td>\n",
       "      <td>TAB</td>\n",
       "      <td>1.5</td>\n",
       "      <td>Par Pharmaceutical</td>\n",
       "      <td>Endo Pharmaceuticals, Inc.</td>\n",
       "      <td>Burlington Drug Company</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PB0020139</td>\n",
       "      <td>DISTRIBUTOR</td>\n",
       "      <td>BURLINGTON DRUG COMPANY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91 CATAMOUNT DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MILTON</td>\n",
       "      <td>VT</td>\n",
       "      <td>5468</td>\n",
       "      <td>CHITTENDEN</td>\n",
       "      <td>...</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>603003319</td>\n",
       "      <td>HYDROCODONE.BIT. &amp; ACETA  5MG &amp; 500M</td>\n",
       "      <td>HYDROCODONE BITARTRATE HEMIPENTAHYDRATE</td>\n",
       "      <td>TAB</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Par Pharmaceutical</td>\n",
       "      <td>Endo Pharmaceuticals, Inc.</td>\n",
       "      <td>Burlington Drug Company</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PB0020139</td>\n",
       "      <td>DISTRIBUTOR</td>\n",
       "      <td>BURLINGTON DRUG COMPANY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91 CATAMOUNT DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MILTON</td>\n",
       "      <td>VT</td>\n",
       "      <td>5468</td>\n",
       "      <td>CHITTENDEN</td>\n",
       "      <td>...</td>\n",
       "      <td>500.0</td>\n",
       "      <td>604002293</td>\n",
       "      <td>HYDROCODONE.BIT./ACET.,10MG &amp; 325MG/</td>\n",
       "      <td>HYDROCODONE BITARTRATE HEMIPENTAHYDRATE</td>\n",
       "      <td>TAB</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Par Pharmaceutical</td>\n",
       "      <td>Endo Pharmaceuticals, Inc.</td>\n",
       "      <td>Burlington Drug Company</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  REPORTER_DEA_NO REPORTER_BUS_ACT            REPORTER_NAME  \\\n",
       "0       PB0020139      DISTRIBUTOR  BURLINGTON DRUG COMPANY   \n",
       "1       PB0020139      DISTRIBUTOR  BURLINGTON DRUG COMPANY   \n",
       "2       PB0020139      DISTRIBUTOR  BURLINGTON DRUG COMPANY   \n",
       "3       PB0020139      DISTRIBUTOR  BURLINGTON DRUG COMPANY   \n",
       "4       PB0020139      DISTRIBUTOR  BURLINGTON DRUG COMPANY   \n",
       "\n",
       "  REPORTER_ADDL_CO_INFO REPORTER_ADDRESS1 REPORTER_ADDRESS2 REPORTER_CITY  \\\n",
       "0                   NaN   91 CATAMOUNT DR               NaN        MILTON   \n",
       "1                   NaN   91 CATAMOUNT DR               NaN        MILTON   \n",
       "2                   NaN   91 CATAMOUNT DR               NaN        MILTON   \n",
       "3                   NaN   91 CATAMOUNT DR               NaN        MILTON   \n",
       "4                   NaN   91 CATAMOUNT DR               NaN        MILTON   \n",
       "\n",
       "  REPORTER_STATE  REPORTER_ZIP REPORTER_COUNTY  ... DOSAGE_UNIT  \\\n",
       "0             VT          5468      CHITTENDEN  ...       500.0   \n",
       "1             VT          5468      CHITTENDEN  ...       500.0   \n",
       "2             VT          5468      CHITTENDEN  ...       100.0   \n",
       "3             VT          5468      CHITTENDEN  ...      1000.0   \n",
       "4             VT          5468      CHITTENDEN  ...       500.0   \n",
       "\n",
       "  TRANSACTION_ID                          Product_Name  \\\n",
       "0      601003321  HYDROCODONE BITARTRATE AND ACETA 10M   \n",
       "1      601003928         ENDOCET OXYCODO HCL5MG&AC TAB   \n",
       "2      602007127  ENDOCET - 10MG OXYCODONE.HCL/325MG A   \n",
       "3      603003319  HYDROCODONE.BIT. & ACETA  5MG & 500M   \n",
       "4      604002293  HYDROCODONE.BIT./ACET.,10MG & 325MG/   \n",
       "\n",
       "                           Ingredient_Name Measure MME_Conversion_Factor  \\\n",
       "0  HYDROCODONE BITARTRATE HEMIPENTAHYDRATE     TAB                   1.0   \n",
       "1                  OXYCODONE HYDROCHLORIDE     TAB                   1.5   \n",
       "2                  OXYCODONE HYDROCHLORIDE     TAB                   1.5   \n",
       "3  HYDROCODONE BITARTRATE HEMIPENTAHYDRATE     TAB                   1.0   \n",
       "4  HYDROCODONE BITARTRATE HEMIPENTAHYDRATE     TAB                   1.0   \n",
       "\n",
       "  Combined_Labeler_Name        Revised_Company_Name          Reporter_family  \\\n",
       "0    Par Pharmaceutical  Endo Pharmaceuticals, Inc.  Burlington Drug Company   \n",
       "1    Par Pharmaceutical  Endo Pharmaceuticals, Inc.  Burlington Drug Company   \n",
       "2    Par Pharmaceutical  Endo Pharmaceuticals, Inc.  Burlington Drug Company   \n",
       "3    Par Pharmaceutical  Endo Pharmaceuticals, Inc.  Burlington Drug Company   \n",
       "4    Par Pharmaceutical  Endo Pharmaceuticals, Inc.  Burlington Drug Company   \n",
       "\n",
       "  dos_str  \n",
       "0    10.0  \n",
       "1     5.0  \n",
       "2    10.0  \n",
       "3     5.0  \n",
       "4    10.0  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "cwd=os.getcwd()\n",
    "\n",
    "df = pd.read_csv('https://www.washingtonpost.com/wp-stat/dea-pain-pill-database/summary/arcos-me-statewide-itemized.tsv.gz', compression='gzip', delimiter='\\t', low_memory=False)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['farmington', 'bangor', 'naples', 'calais', 'skowhegan', 'berwick',\n",
       "       'rockland', 'kingfield', 'kennebunkport', 'princeton', 'portland',\n",
       "       'kennebunk', 'freeport', 'waterville', 'brewer', 'gardiner',\n",
       "       'kittery', 'falmouth', 'south portland', 'biddeford', 'old town',\n",
       "       'lewiston', 'fairfield', 'bridgton', 'bar harbor', 'sanford',\n",
       "       'brunswick', 'bucksport', 'fryeburg', 'cornish', 'lisbon',\n",
       "       'machias', 'hampden', 'houlton', 'pittsfield', 'paris', 'winslow',\n",
       "       'ellsworth', 'boothbay harbor', 'westbrook', 'old orchard beach',\n",
       "       'millinocket', 'damariscotta', 'caribou', 'gray', 'topsham',\n",
       "       'newport', 'saco', 'madawaska', 'auburn', 'oakland', 'manchester',\n",
       "       'augusta', 'lincoln', 'rumford', 'dexter', 'fort fairfield',\n",
       "       'van buren', 'gorham', 'bethel', 'buxton', 'winthrop', 'yarmouth',\n",
       "       'livermore falls', 'wells', 'scarborough', 'fort kent',\n",
       "       'dover-foxcroft', 'camden', 'belfast', 'presque isle', 'milo',\n",
       "       'guilford', 'blue hill', 'thomaston', 'palmyra', 'oxford',\n",
       "       'windham', 'mexico', 'york', 'bath', 'cape elizabeth',\n",
       "       'greenville', 'standish', 'east millinocket', 'jay', 'waterboro',\n",
       "       'rockport', 'milbridge', 'mechanic falls', 'mars hill', 'orono',\n",
       "       'patten', 'indian island', 'south berwick', 'waldoboro', 'albion',\n",
       "       'corinth', 'randolph', 'china', 'marshfield', 'norway', 'unity',\n",
       "       'dedham', 'raymond', 'greene', 'madison', 'woolwich', 'sullivan',\n",
       "       'bingham', 'rangeley', 'southwest harbor', 'stonington',\n",
       "       'northport', 'veazie', 'west gardiner', 'acton', 'new gloucester',\n",
       "       'harpswell', 'searsport', 'cumberland', 'orrington', 'perry',\n",
       "       'windsor', 'wayne', 'monmouth', 'eliot', 'south thomaston',\n",
       "       'hollis', 'sidney', 'parkman', 'palermo', 'lebanon', 'union',\n",
       "       'warren', 'mount desert', 'belgrade', 'winter harbor', 'steuben',\n",
       "       'stockholm', 'boothbay', 'brownfield', 'alfred', 'exeter',\n",
       "       'vassalboro'], dtype=object)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "townfix = {\n",
    "    'lisbon falls':'lisbon'\n",
    "    ,'bridgeton':'bridgton'\n",
    "    ,'cumberland center':'cumberland'\n",
    "    ,'grey':'gray'\n",
    "    ,'north windham':'windham'\n",
    "    ,'steep falls':'standish'\n",
    "    ,'seal harbor':'mount desert'\n",
    "    ,'east boothbay':'boothbay'\n",
    "    ,'no. vassalboro':'vassalboro'\n",
    "    ,'south paris':'paris'\n",
    "    ,'east waterboro':'waterboro'\n",
    "    ,'hollis center':'hollis'\n",
    "    ,'springvale':'sanford'\n",
    "    ,'dover foxcroft':'dover-foxcroft'\n",
    "}\n",
    "\n",
    "df['lower_city'] = df['BUYER_CITY'].str.lower()\n",
    "df['lower_city'].replace(townfix,value=None,inplace=True)\n",
    "df['lower_city'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(cwd+'/processed_files/arcos-me-statewide-cleaned-towns.csv')"
   ]
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}