opioid-us-2006-2014.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import calendar\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import os"
   ]
  },
  {
   "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>State</th>\n",
       "      <th>Abbrev</th>\n",
       "      <th>Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Ala.</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>Ariz.</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Ark.</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>Calif.</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Abbrev Code\n",
       "0     Alabama    Ala.   AL\n",
       "1      Alaska  Alaska   AK\n",
       "2     Arizona   Ariz.   AZ\n",
       "3    Arkansas    Ark.   AR\n",
       "4  California  Calif.   CA"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_path = \"/media/hdd/Dropbox/git/Opioids-Tennessee/data/states.csv\"\n",
    "states = pd.read_csv(states_path)\n",
    "states.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY']\n"
     ]
    }
   ],
   "source": [
    "states_abbr = list(states['Code'])\n",
    "print(states_abbr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_dir = \"/media/hdd/Dropbox/git/Opioids-Tennessee/output2/\"\n",
    "for state in states_abbr:\n",
    "    state_dir = os.path.join(output_dir, state)\n",
    "    if not os.path.exists(state_dir):\n",
    "        os.mkdir(state_dir)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = \"/media/hdd/Data/Opioids/processed ARCOS 2006-2014 12.5.2019.csv\"\n",
    "out_dir = '/media/hdd/GitHub/Opioids-Tennessee/output2/'\n",
    "\n",
    "batch_size = 1000000\n",
    "\n",
    "start = 0\n",
    "end = 600\n",
    "\n",
    "# total = 0\n",
    "\n",
    "for i in range(start, end):\n",
    "    print(\"Processing epoch {} ...\".format(i+1))\n",
    "    indices_to_skip = np.arange(1, (i+1)*batch_size+1, 1).tolist()\n",
    "    data = pd.read_csv(data_path, sep='|', header=0, skiprows=indices_to_skip, nrows=batch_size)\n",
    "    for state in states_abbr:\n",
    "        state_data = data[data['BUYER_STATE']==state]\n",
    "#         total = total + len(tn)\n",
    "#         print(\"Number of TN records in this epoch: {}\".format(len(tn)))\n",
    "#         print(\"Cumulative number of TN records: {}\".format(total))\n",
    "        out_name = state + '_' + str(i+1).zfill(3) + '.csv'\n",
    "        out_path = os.path.join(out_dir, state, out_name)\n",
    "        if len(state_date) > 0:\n",
    "            state_data.to_csv(out_path, sep = \"|\", index=False)\n",
    "        del state_data\n",
    "    del data\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing AL ...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/qiusheng/.conda/envs/dl/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3051: DtypeWarning: Columns (3,5,22,25) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing AK ...\n",
      "Processing AZ ...\n",
      "Processing AR ...\n",
      "Processing CA ...\n",
      "Processing CO ...\n",
      "Processing CT ...\n",
      "Processing DE ...\n",
      "Processing DC ...\n",
      "Processing FL ...\n",
      "Processing GA ...\n",
      "Processing HI ...\n",
      "Processing ID ...\n",
      "Processing IL ...\n",
      "Processing IN ...\n",
      "Processing IA ...\n",
      "Processing KS ...\n",
      "Processing KY ...\n",
      "Processing LA ...\n",
      "Processing ME ...\n",
      "Processing MD ...\n",
      "Processing MA ...\n",
      "Processing MI ...\n",
      "Processing MN ...\n",
      "Processing MS ...\n",
      "Processing MO ...\n",
      "Processing MT ...\n",
      "Processing NE ...\n",
      "Processing NV ...\n",
      "Processing NH ...\n",
      "Processing NJ ...\n",
      "Processing NM ...\n",
      "Processing NY ...\n",
      "Processing NC ...\n",
      "Processing ND ...\n",
      "Processing OH ...\n",
      "Processing OK ...\n",
      "Processing OR ...\n",
      "Processing PA ...\n",
      "Processing RI ...\n",
      "Processing SC ...\n",
      "Processing SD ...\n",
      "Processing TN ...\n",
      "Processing TX ...\n",
      "Processing UT ...\n",
      "Processing VT ...\n",
      "Processing VA ...\n",
      "Processing WA ...\n",
      "Processing WV ...\n",
      "Processing WI ...\n",
      "Processing WY ...\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import os\n",
    "\n",
    "in_dir = '/media/hdd/Dropbox/git/Opioids-Tennessee/output' # use your path\n",
    "out_dir = '/media/hdd/Team-Drives/Health-GIS/Opioid/By-state/'\n",
    "\n",
    "states_path = \"/media/hdd/Dropbox/git/Opioids-Tennessee/data/states.csv\"\n",
    "states = pd.read_csv(states_path)\n",
    "states_abbr = list(states['Code'])\n",
    "# print(states_abbr)\n",
    "\n",
    "for state in states_abbr:\n",
    "\n",
    "    print(\"Processing {} ...\".format(state))\n",
    "    work_dir = os.path.join(in_dir, state)\n",
    "    all_files = glob.glob(work_dir + \"/*.csv\")\n",
    "\n",
    "    li = []\n",
    "\n",
    "    for filename in all_files:\n",
    "        df = pd.read_csv(filename, index_col=None, header=0, sep='|')\n",
    "        # Convert TRANSACTION_DATE from String to DateTime and pad zeros\n",
    "        df['TRANSACTION_DATE'] = pd.to_datetime(df['TRANSACTION_DATE'].astype(str).str.zfill(8), format='%m%d%Y')\n",
    "        li.append(df)\n",
    "    if len(li) > 0:\n",
    "        frame = pd.concat(li, axis=0, ignore_index=True)\n",
    "        out_path = os.path.join(out_dir, state + '.csv')\n",
    "        frame.to_csv(out_path, sep = \"|\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}