data-reshaping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping Clinics Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Absolute Medical Care</td>\n",
       "      <td>Medical Director: Hau La, M.D.</td>\n",
       "      <td>Facility License Number: 00000592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>713 B President Place</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Status: Lic Surrendered</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Smyrna, TN 37167</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date of Original Licensure: 03/07/2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>615-667-1299</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date of Expiration: 03/31/2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1 Disciplinary Action(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.0</td>\n",
       "      <td>ACCELERATED MEDICAL OF SEYMOUR</td>\n",
       "      <td>Medical Director: GHOLAMREZA SHAREGHI</td>\n",
       "      <td>Facility License Number: 00000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>319 BLUE PEACOCK WAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Status: Closed Appl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Seymour, TN 37865</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date of Original Licensure:</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>865-216-8930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date of Expiration:</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0                               1                                      2  \\\n",
       "0  1.0           Absolute Medical Care         Medical Director: Hau La, M.D.   \n",
       "1  NaN          713 B President Place                                     NaN   \n",
       "2  NaN                Smyrna, TN 37167                                    NaN   \n",
       "3  NaN                    615-667-1299                                    NaN   \n",
       "4  NaN                             NaN                                    NaN   \n",
       "5  2.0  ACCELERATED MEDICAL OF SEYMOUR  Medical Director: GHOLAMREZA SHAREGHI   \n",
       "6  NaN           319 BLUE PEACOCK WAY                                     NaN   \n",
       "7  NaN               Seymour, TN 37865                                    NaN   \n",
       "8  NaN                    865-216-8930                                    NaN   \n",
       "9  NaN                             NaN                                    NaN   \n",
       "\n",
       "                                        3  \n",
       "0       Facility License Number: 00000592  \n",
       "1                 Status: Lic Surrendered  \n",
       "2  Date of Original Licensure: 03/07/2016  \n",
       "3          Date of Expiration: 03/31/2018  \n",
       "4                1 Disciplinary Action(s)  \n",
       "5       Facility License Number: 00000000  \n",
       "6                     Status: Closed Appl  \n",
       "7            Date of Original Licensure:   \n",
       "8                    Date of Expiration:   \n",
       "9                 No Disciplinary Actions  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get current working directory\n",
    "cwd = os.getcwd()\n",
    "\n",
    "# set input and output Excel files\n",
    "in_excel_file = os.path.join(cwd, 'data', 'pain_clinics_historic_to_20190820.xlsx')\n",
    "out_excel_file = os.path.join(cwd, 'data', 'KnoxvilleAreapain_clinics.xlsx')\n",
    "\n",
    "# read Excel file \n",
    "df = pd.read_excel(in_excel_file,header=None)\n",
    "\n",
    "# show the first ten rows\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# define a list for storing info about each clinic\n",
    "clinics = []\n",
    "\n",
    "# loop through each row\n",
    "for row_index in range(0, len(df.index)):\n",
    "    row = df.iloc[row_index]\n",
    "    if row[0] > 0:\n",
    "        \n",
    "        clinic = {}\n",
    "        clinic['Id'] = row[0]\n",
    "       \n",
    "        # fix records with missing information, such as city name, phone, etc.\n",
    "        if clinic['Id'] == 55:\n",
    "            df.set_value(row_index + 2, 1, 'Johnson City, TN 37601')\n",
    "        if clinic['Id'] == 542:\n",
    "            df.set_value(row_index + 2, 1, 'Columbia, TN 38401')  \n",
    "        if clinic['Id'] == 98:\n",
    "            df.set_value(row_index, 2, 'Medical Director: NA')\n",
    "        if clinic['Id'] in [122, 145, 398, 496, 542, 545, 548, 553, 639, 752]:\n",
    "            df.set_value(row_index + 3, 1, '000-000-0000')         \n",
    "\n",
    "        # extract clinic info\n",
    "        clinic['Name'] = row[1]\n",
    "        clinic['MedDirector'] = str(row[2]).split(\":\")[1].strip()\n",
    "        clinic['FacLicNum'] = row[3].split(\":\")[1].strip()\n",
    "        clinic['Street'] = df.iloc[row_index + 1][1].strip()\n",
    "        clinic['Status'] = df.iloc[row_index + 1][3].split(\":\")[1].strip()\n",
    "        clinic['City'] = df.iloc[row_index + 2][1].split(',')[0].strip()\n",
    "        clinic['State'] = df.iloc[row_index + 2][1].split(',')[1].strip().split(' ')[0]\n",
    "        clinic['Zip'] = df.iloc[row_index + 2][1].split(',')[1].strip().split(' ')[1]\n",
    "        clinic['OrgLic'] = df.iloc[row_index + 2][3].split(\":\")[1].strip()\n",
    "        clinic['Phone'] = df.iloc[row_index + 3][1].strip()\n",
    "        clinic['ExpDate'] = df.iloc[row_index + 3][3].split(\":\")[1].strip()\n",
    "        clinic['Displinary'] = df.iloc[row_index + 4][3].strip()\n",
    "        clinics.append(clinic)        \n",
    "        \n",
    "    else:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of clinics: 849\n"
     ]
    }
   ],
   "source": [
    "print(f\"Total number of clinics: {len(clinics)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert list to datafroame\n",
    "out_df = pd.DataFrame(clinics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>MedDirector</th>\n",
       "      <th>FacLicNum</th>\n",
       "      <th>Street</th>\n",
       "      <th>Status</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Zip</th>\n",
       "      <th>OrgLic</th>\n",
       "      <th>Phone</th>\n",
       "      <th>ExpDate</th>\n",
       "      <th>Displinary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Absolute Medical Care</td>\n",
       "      <td>Hau La, M.D.</td>\n",
       "      <td>00000592</td>\n",
       "      <td>713 B President Place</td>\n",
       "      <td>Lic Surrendered</td>\n",
       "      <td>Smyrna</td>\n",
       "      <td>TN</td>\n",
       "      <td>37167</td>\n",
       "      <td>03/07/2016</td>\n",
       "      <td>615-667-1299</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>1 Disciplinary Action(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>ACCELERATED MEDICAL OF SEYMOUR</td>\n",
       "      <td>GHOLAMREZA SHAREGHI</td>\n",
       "      <td>00000000</td>\n",
       "      <td>319 BLUE PEACOCK WAY</td>\n",
       "      <td>Closed Appl</td>\n",
       "      <td>Seymour</td>\n",
       "      <td>TN</td>\n",
       "      <td>37865</td>\n",
       "      <td></td>\n",
       "      <td>865-216-8930</td>\n",
       "      <td></td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>ACCEPTANCE HEALTH</td>\n",
       "      <td>FRANCISCO AVILA, M.D.</td>\n",
       "      <td>00000530</td>\n",
       "      <td>1012 SOUTH CHANCERY STREET</td>\n",
       "      <td>Facility Closed</td>\n",
       "      <td>Mc Minnville</td>\n",
       "      <td>TN</td>\n",
       "      <td>37110</td>\n",
       "      <td>11/18/2014</td>\n",
       "      <td>931-507-6325</td>\n",
       "      <td>11/30/2016</td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>ACCESS SOLUTIONS WELLNESS, P.C.</td>\n",
       "      <td>LAWRENCE JOSEPH VALDEZ, M.D.</td>\n",
       "      <td>00000276</td>\n",
       "      <td>805 S. CHURCH STREET SUITE 20</td>\n",
       "      <td>Expired</td>\n",
       "      <td>Murfreesboro</td>\n",
       "      <td>TN</td>\n",
       "      <td>37130</td>\n",
       "      <td>04/03/2012</td>\n",
       "      <td>615-962-9011</td>\n",
       "      <td>04/30/2014</td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Active Pain Management Solutions</td>\n",
       "      <td>David McAfee, M.D.</td>\n",
       "      <td>00000637</td>\n",
       "      <td>620 S Jefferson Ave, Suite 202</td>\n",
       "      <td>Facility Closed</td>\n",
       "      <td>Cookeville</td>\n",
       "      <td>TN</td>\n",
       "      <td>38501</td>\n",
       "      <td>02/02/2017</td>\n",
       "      <td>931-526-7246</td>\n",
       "      <td>02/28/2019</td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id                              Name                   MedDirector  \\\n",
       "0  1.0             Absolute Medical Care                  Hau La, M.D.   \n",
       "1  2.0    ACCELERATED MEDICAL OF SEYMOUR           GHOLAMREZA SHAREGHI   \n",
       "2  3.0                 ACCEPTANCE HEALTH         FRANCISCO AVILA, M.D.   \n",
       "3  4.0   ACCESS SOLUTIONS WELLNESS, P.C.  LAWRENCE JOSEPH VALDEZ, M.D.   \n",
       "4  5.0  Active Pain Management Solutions            David McAfee, M.D.   \n",
       "\n",
       "  FacLicNum                          Street           Status          City  \\\n",
       "0  00000592           713 B President Place  Lic Surrendered        Smyrna   \n",
       "1  00000000            319 BLUE PEACOCK WAY      Closed Appl       Seymour   \n",
       "2  00000530      1012 SOUTH CHANCERY STREET  Facility Closed  Mc Minnville   \n",
       "3  00000276   805 S. CHURCH STREET SUITE 20          Expired  Murfreesboro   \n",
       "4  00000637  620 S Jefferson Ave, Suite 202  Facility Closed    Cookeville   \n",
       "\n",
       "  State    Zip      OrgLic         Phone     ExpDate                Displinary  \n",
       "0    TN  37167  03/07/2016  615-667-1299  03/31/2018  1 Disciplinary Action(s)  \n",
       "1    TN  37865              865-216-8930               No Disciplinary Actions  \n",
       "2    TN  37110  11/18/2014  931-507-6325  11/30/2016   No Disciplinary Actions  \n",
       "3    TN  37130  04/03/2012  615-962-9011  04/30/2014   No Disciplinary Actions  \n",
       "4    TN  38501  02/02/2017  931-526-7246  02/28/2019   No Disciplinary Actions  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show the first five rows. Note that columns are order alphabetically\n",
    "out_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>Street</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Zip</th>\n",
       "      <th>Phone</th>\n",
       "      <th>MedDirector</th>\n",
       "      <th>FacLicNum</th>\n",
       "      <th>Status</th>\n",
       "      <th>OrgLic</th>\n",
       "      <th>ExpDate</th>\n",
       "      <th>Displinary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Absolute Medical Care</td>\n",
       "      <td>713 B President Place</td>\n",
       "      <td>Smyrna</td>\n",
       "      <td>TN</td>\n",
       "      <td>37167</td>\n",
       "      <td>615-667-1299</td>\n",
       "      <td>Hau La, M.D.</td>\n",
       "      <td>00000592</td>\n",
       "      <td>Lic Surrendered</td>\n",
       "      <td>03/07/2016</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>1 Disciplinary Action(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>ACCELERATED MEDICAL OF SEYMOUR</td>\n",
       "      <td>319 BLUE PEACOCK WAY</td>\n",
       "      <td>Seymour</td>\n",
       "      <td>TN</td>\n",
       "      <td>37865</td>\n",
       "      <td>865-216-8930</td>\n",
       "      <td>GHOLAMREZA SHAREGHI</td>\n",
       "      <td>00000000</td>\n",
       "      <td>Closed Appl</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>ACCEPTANCE HEALTH</td>\n",
       "      <td>1012 SOUTH CHANCERY STREET</td>\n",
       "      <td>Mc Minnville</td>\n",
       "      <td>TN</td>\n",
       "      <td>37110</td>\n",
       "      <td>931-507-6325</td>\n",
       "      <td>FRANCISCO AVILA, M.D.</td>\n",
       "      <td>00000530</td>\n",
       "      <td>Facility Closed</td>\n",
       "      <td>11/18/2014</td>\n",
       "      <td>11/30/2016</td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>ACCESS SOLUTIONS WELLNESS, P.C.</td>\n",
       "      <td>805 S. CHURCH STREET SUITE 20</td>\n",
       "      <td>Murfreesboro</td>\n",
       "      <td>TN</td>\n",
       "      <td>37130</td>\n",
       "      <td>615-962-9011</td>\n",
       "      <td>LAWRENCE JOSEPH VALDEZ, M.D.</td>\n",
       "      <td>00000276</td>\n",
       "      <td>Expired</td>\n",
       "      <td>04/03/2012</td>\n",
       "      <td>04/30/2014</td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Active Pain Management Solutions</td>\n",
       "      <td>620 S Jefferson Ave, Suite 202</td>\n",
       "      <td>Cookeville</td>\n",
       "      <td>TN</td>\n",
       "      <td>38501</td>\n",
       "      <td>931-526-7246</td>\n",
       "      <td>David McAfee, M.D.</td>\n",
       "      <td>00000637</td>\n",
       "      <td>Facility Closed</td>\n",
       "      <td>02/02/2017</td>\n",
       "      <td>02/28/2019</td>\n",
       "      <td>No Disciplinary Actions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id                              Name                          Street  \\\n",
       "0  1.0             Absolute Medical Care           713 B President Place   \n",
       "1  2.0    ACCELERATED MEDICAL OF SEYMOUR            319 BLUE PEACOCK WAY   \n",
       "2  3.0                 ACCEPTANCE HEALTH      1012 SOUTH CHANCERY STREET   \n",
       "3  4.0   ACCESS SOLUTIONS WELLNESS, P.C.   805 S. CHURCH STREET SUITE 20   \n",
       "4  5.0  Active Pain Management Solutions  620 S Jefferson Ave, Suite 202   \n",
       "\n",
       "           City State    Zip         Phone                   MedDirector  \\\n",
       "0        Smyrna    TN  37167  615-667-1299                  Hau La, M.D.   \n",
       "1       Seymour    TN  37865  865-216-8930           GHOLAMREZA SHAREGHI   \n",
       "2  Mc Minnville    TN  37110  931-507-6325         FRANCISCO AVILA, M.D.   \n",
       "3  Murfreesboro    TN  37130  615-962-9011  LAWRENCE JOSEPH VALDEZ, M.D.   \n",
       "4    Cookeville    TN  38501  931-526-7246            David McAfee, M.D.   \n",
       "\n",
       "  FacLicNum           Status      OrgLic     ExpDate                Displinary  \n",
       "0  00000592  Lic Surrendered  03/07/2016  03/31/2018  1 Disciplinary Action(s)  \n",
       "1  00000000      Closed Appl                           No Disciplinary Actions  \n",
       "2  00000530  Facility Closed  11/18/2014  11/30/2016   No Disciplinary Actions  \n",
       "3  00000276          Expired  04/03/2012  04/30/2014   No Disciplinary Actions  \n",
       "4  00000637  Facility Closed  02/02/2017  02/28/2019   No Disciplinary Actions  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's reorder the columns\n",
    "col_names = ['Id', 'Name', 'Street', 'City', 'State', 'Zip', 'Phone', 'MedDirector', 'FacLicNum', 'Status', 'OrgLic',\n",
    "            'ExpDate', 'Displinary']\n",
    "out_df = out_df.reindex(columns=col_names)\n",
    "\n",
    "# show the first five rows \n",
    "out_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the dataframe to Excel\n",
    "out_df.to_excel(out_excel_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py37",
   "language": "python",
   "name": "py37"
  },
  "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}