Analyzing Medicare Part D Prescriber Data 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook we will explore the Medicare Part D prescribing data that was released through the CMS data portal at https://data.cms.gov/resource/4uvc-gbfz. The goal is to demonstrate how this data can be used to find prescribers who prescribe in a similar way. For a psychiatrist in Washington, D.C. we will determine which prescribers prescribe most closely by computing a distance matrix based on the total claims. More information about the data can be found at [Prescriber_Methods.pdf](https://data.cms.gov/api/views/4uvc-gbfz/files/9hYW-2MBrXAbhKuRXBE0AMbG7pRA5Q0MQCsDIJzlkTI?download=true&filename=Prescriber_Methods.pdf)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "md_base_url = \"https://data.cms.gov/resource/4uvc-gbfz\" # URL For The Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data set contains 23,650,520 rows. We are not going to pull the entire data set down but a subset of the data.  To determine which state to pull down we use the Socrata SODA API to do the computation server side."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://data.cms.gov/resource/4uvc-gbfz?$select=count(*)\n"
     ]
    },
    {
     "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23645873</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      count\n",
       "0  23645873"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_url = md_base_url + \"?\" + \"$select=count(*)\"\n",
    "print(count_url)\n",
    "pd.read_json(count_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://data.cms.gov/resource/4uvc-gbfz?$select=nppes_provider_state,count(nppes_provider_state)&$group=nppes_provider_state\n"
     ]
    },
    {
     "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>count_nppes_provider_state</th>\n",
       "      <th>nppes_provider_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>49</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>30</td>\n",
       "      <td>AA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25</td>\n",
       "      <td>XX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>348</td>\n",
       "      <td>MP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>240</td>\n",
       "      <td>AE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2738</td>\n",
       "      <td>VI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1816</td>\n",
       "      <td>GU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1072</td>\n",
       "      <td>ZZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>28329</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>95</td>\n",
       "      <td>AP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>33658</td>\n",
       "      <td>WY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>39185</td>\n",
       "      <td>DC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    count_nppes_provider_state nppes_provider_state\n",
       "0                           49                   AS\n",
       "1                           30                   AA\n",
       "2                           25                   XX\n",
       "3                          348                   MP\n",
       "4                          240                   AE\n",
       "5                         2738                   VI\n",
       "6                         1816                   GU\n",
       "7                         1072                   ZZ\n",
       "8                        28329                   AK\n",
       "10                          95                   AP\n",
       "11                       33658                   WY\n",
       "13                       39185                   DC"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_by_state_url = md_base_url + \"?\" + \"$select=nppes_provider_state,count(nppes_provider_state)&$group=nppes_provider_state\"\n",
    "print(count_by_state_url)\n",
    "by_state_df = pd.read_json(count_by_state_url)\n",
    "by_state_df[by_state_df.count_nppes_provider_state < 50000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Socrata SODA version 2.0 of the API has a limit of 50,000 rows. It is possible to implement a strategy using the `$limit` and `$offset` operators in their API to extract larger datasets. We will focus on extracting data for prescribers that list their location in the District of Columbia. Rather than using the `pd.read_json()` using the `requests` library will be demonstrated. This will let us see the header of the HTTP response of the request."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution_count": 80
   },
   "outputs": [],
   "source": [
    "r = requests.get(md_base_url, params={'$where': \"nppes_provider_state='GU'\", \"$limit\": 50000})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Server': 'nginx', 'Date': 'Mon, 01 Jul 2019 19:54:11 GMT', 'Content-Type': 'application/json; charset=utf-8', 'Transfer-Encoding': 'chunked', 'Connection': 'keep-alive', 'X-Socrata-RequestId': '2twgvkwhsjbvm5f7s8wchylcz', 'Access-Control-Allow-Origin': '*', 'ETag': '\"875632d8396f74cd57a172aa550c8d06--gzip\"', 'Last-Modified': 'Wed, 14 Nov 2018 19:13:19 UTC', 'X-SODA2-Warning': 'X-SODA2-Fields, X-SODA2-Types, and X-SODA2-Legacy-Types are deprecated', 'X-SODA2-Fields': '[\"drug_name\",\"total_day_supply\",\"npi\",\"nppes_provider_first_name\",\"total_claim_count_ge65\",\"generic_name\",\"total_30_day_fill_count_ge65\",\"specialty_desc\",\"total_day_supply_ge65\",\"total_claim_count\",\"ge65_redact_flag\",\"nppes_provider_city\",\"total_30_day_fill_count\",\"bene_count_ge65\",\"nppes_provider_state\",\"nppes_provider_last_org_name\",\"total_drug_cost\",\"description_flag\",\"bene_count_ge65_redact_flag\",\"total_drug_cost_ge65\",\"bene_count\"]', 'X-SODA2-Types': '[\"text\",\"number\",\"text\",\"text\",\"number\",\"text\",\"number\",\"text\",\"number\",\"number\",\"text\",\"text\",\"number\",\"number\",\"text\",\"text\",\"number\",\"text\",\"text\",\"number\",\"number\"]', 'X-SODA2-Legacy-Types': 'true', 'Vary': 'Accept-Encoding', 'Content-Encoding': 'gzip', 'Age': '275998', 'X-Socrata-Region': 'aws-us-east-1-fedramp-prod', 'Strict-Transport-Security': 'max-age=31536000; includeSubDomains'}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_df = pd.read_json(r.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Uncomment if you are unable to install the requests library\n",
    "# prescriber_df = pd.read_json(prescriber_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>bene_count</th>\n",
       "      <th>bene_count_ge65</th>\n",
       "      <th>bene_count_ge65_redact_flag</th>\n",
       "      <th>description_flag</th>\n",
       "      <th>drug_name</th>\n",
       "      <th>ge65_redact_flag</th>\n",
       "      <th>generic_name</th>\n",
       "      <th>npi</th>\n",
       "      <th>nppes_provider_city</th>\n",
       "      <th>nppes_provider_first_name</th>\n",
       "      <th>...</th>\n",
       "      <th>nppes_provider_state</th>\n",
       "      <th>specialty_desc</th>\n",
       "      <th>total_30_day_fill_count</th>\n",
       "      <th>total_30_day_fill_count_ge65</th>\n",
       "      <th>total_claim_count</th>\n",
       "      <th>total_claim_count_ge65</th>\n",
       "      <th>total_day_supply</th>\n",
       "      <th>total_day_supply_ge65</th>\n",
       "      <th>total_drug_cost</th>\n",
       "      <th>total_drug_cost_ge65</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>ALENDRONATE SODIUM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ALENDRONATE SODIUM</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>30.2</td>\n",
       "      <td>30.2</td>\n",
       "      <td>17</td>\n",
       "      <td>17.0</td>\n",
       "      <td>890</td>\n",
       "      <td>890.0</td>\n",
       "      <td>209.08</td>\n",
       "      <td>209.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>AMLODIPINE BESYLATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AMLODIPINE BESYLATE</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>18</td>\n",
       "      <td>18.0</td>\n",
       "      <td>660</td>\n",
       "      <td>660.0</td>\n",
       "      <td>125.40</td>\n",
       "      <td>125.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>ATORVASTATIN CALCIUM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATORVASTATIN CALCIUM</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>71.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>59</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2130</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>1114.96</td>\n",
       "      <td>1114.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>CLOPIDOGREL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLOPIDOGREL BISULFATE</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>14</td>\n",
       "      <td>14.0</td>\n",
       "      <td>600</td>\n",
       "      <td>600.0</td>\n",
       "      <td>203.31</td>\n",
       "      <td>203.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>COLCRYS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COLCHICINE</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11</td>\n",
       "      <td>11.0</td>\n",
       "      <td>330</td>\n",
       "      <td>330.0</td>\n",
       "      <td>1769.88</td>\n",
       "      <td>1769.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>#</td>\n",
       "      <td>S</td>\n",
       "      <td>DIOVAN</td>\n",
       "      <td>#</td>\n",
       "      <td>VALSARTAN</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5536.67</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>DONEPEZIL HCL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DONEPEZIL HCL</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>27.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>810</td>\n",
       "      <td>810.0</td>\n",
       "      <td>369.81</td>\n",
       "      <td>369.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>LEVOTHYROXINE SODIUM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LEVOTHYROXINE SODIUM</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>57.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>53</td>\n",
       "      <td>53.0</td>\n",
       "      <td>1473</td>\n",
       "      <td>1473.0</td>\n",
       "      <td>383.44</td>\n",
       "      <td>383.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>LISINOPRIL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LISINOPRIL</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>37.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>35</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1110</td>\n",
       "      <td>1110.0</td>\n",
       "      <td>236.70</td>\n",
       "      <td>236.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>LOSARTAN POTASSIUM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LOSARTAN POTASSIUM</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>32.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>24</td>\n",
       "      <td>24.0</td>\n",
       "      <td>960</td>\n",
       "      <td>960.0</td>\n",
       "      <td>276.27</td>\n",
       "      <td>276.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>LOVASTATIN</td>\n",
       "      <td>*</td>\n",
       "      <td>LOVASTATIN</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>570</td>\n",
       "      <td>NaN</td>\n",
       "      <td>190.94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>#</td>\n",
       "      <td>S</td>\n",
       "      <td>METFORMIN HCL</td>\n",
       "      <td>#</td>\n",
       "      <td>METFORMIN HCL</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>990</td>\n",
       "      <td>NaN</td>\n",
       "      <td>385.34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>#</td>\n",
       "      <td>S</td>\n",
       "      <td>METOPROLOL TARTRATE</td>\n",
       "      <td>#</td>\n",
       "      <td>METOPROLOL TARTRATE</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>32.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>960</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.49</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>PRAVASTATIN SODIUM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRAVASTATIN SODIUM</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>23.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>23</td>\n",
       "      <td>23.0</td>\n",
       "      <td>690</td>\n",
       "      <td>690.0</td>\n",
       "      <td>252.33</td>\n",
       "      <td>252.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>WARFARIN SODIUM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WARFARIN SODIUM</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13.0</td>\n",
       "      <td>390</td>\n",
       "      <td>390.0</td>\n",
       "      <td>103.34</td>\n",
       "      <td>103.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>ZOLPIDEM TARTRATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ZOLPIDEM TARTRATE</td>\n",
       "      <td>1003850462</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>DAVINA</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13.0</td>\n",
       "      <td>390</td>\n",
       "      <td>390.0</td>\n",
       "      <td>92.88</td>\n",
       "      <td>92.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>ALENDRONATE SODIUM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ALENDRONATE SODIUM</td>\n",
       "      <td>1023081304</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>PABLO</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13.0</td>\n",
       "      <td>382</td>\n",
       "      <td>382.0</td>\n",
       "      <td>105.53</td>\n",
       "      <td>105.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*</td>\n",
       "      <td>S</td>\n",
       "      <td>AMLODIPINE BESYLATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AMLODIPINE BESYLATE</td>\n",
       "      <td>1023081304</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>PABLO</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>110.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>88</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3300</td>\n",
       "      <td>2340.0</td>\n",
       "      <td>598.13</td>\n",
       "      <td>450.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>#</td>\n",
       "      <td>S</td>\n",
       "      <td>ATORVASTATIN CALCIUM</td>\n",
       "      <td>#</td>\n",
       "      <td>ATORVASTATIN CALCIUM</td>\n",
       "      <td>1023081304</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>PABLO</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>45.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1350</td>\n",
       "      <td>NaN</td>\n",
       "      <td>618.07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>BD ULTRA-FINE PEN NEEDLE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NEEDLES, INSULIN DISPOSABLE</td>\n",
       "      <td>1023081304</td>\n",
       "      <td>TAMUNING</td>\n",
       "      <td>PABLO</td>\n",
       "      <td>...</td>\n",
       "      <td>GU</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>295</td>\n",
       "      <td>0.0</td>\n",
       "      <td>382.87</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    bene_count  bene_count_ge65 bene_count_ge65_redact_flag description_flag  \\\n",
       "0          NaN              NaN                           *                S   \n",
       "1          NaN              NaN                           *                S   \n",
       "2         14.0             14.0                         NaN                S   \n",
       "3          NaN              NaN                           *                S   \n",
       "4          NaN              NaN                           *                S   \n",
       "5          NaN              NaN                           #                S   \n",
       "6          NaN              NaN                           *                S   \n",
       "7          NaN              NaN                           *                S   \n",
       "8          NaN              NaN                           *                S   \n",
       "9          NaN              NaN                           *                S   \n",
       "10         NaN              NaN                           *                S   \n",
       "11         NaN              NaN                           #                S   \n",
       "12         NaN              NaN                           #                S   \n",
       "13         NaN              NaN                           *                S   \n",
       "14         NaN              NaN                           *                S   \n",
       "15         NaN              NaN                           *                S   \n",
       "16         NaN              NaN                           *                S   \n",
       "17        12.0              NaN                           *                S   \n",
       "18         NaN              NaN                           #                S   \n",
       "19         NaN              0.0                         NaN                S   \n",
       "\n",
       "                   drug_name ge65_redact_flag                 generic_name  \\\n",
       "0         ALENDRONATE SODIUM              NaN           ALENDRONATE SODIUM   \n",
       "1        AMLODIPINE BESYLATE              NaN          AMLODIPINE BESYLATE   \n",
       "2       ATORVASTATIN CALCIUM              NaN         ATORVASTATIN CALCIUM   \n",
       "3                CLOPIDOGREL              NaN        CLOPIDOGREL BISULFATE   \n",
       "4                    COLCRYS              NaN                   COLCHICINE   \n",
       "5                     DIOVAN                #                    VALSARTAN   \n",
       "6              DONEPEZIL HCL              NaN                DONEPEZIL HCL   \n",
       "7       LEVOTHYROXINE SODIUM              NaN         LEVOTHYROXINE SODIUM   \n",
       "8                 LISINOPRIL              NaN                   LISINOPRIL   \n",
       "9         LOSARTAN POTASSIUM              NaN           LOSARTAN POTASSIUM   \n",
       "10                LOVASTATIN                *                   LOVASTATIN   \n",
       "11             METFORMIN HCL                #                METFORMIN HCL   \n",
       "12       METOPROLOL TARTRATE                #          METOPROLOL TARTRATE   \n",
       "13        PRAVASTATIN SODIUM              NaN           PRAVASTATIN SODIUM   \n",
       "14           WARFARIN SODIUM              NaN              WARFARIN SODIUM   \n",
       "15         ZOLPIDEM TARTRATE              NaN            ZOLPIDEM TARTRATE   \n",
       "16        ALENDRONATE SODIUM              NaN           ALENDRONATE SODIUM   \n",
       "17       AMLODIPINE BESYLATE              NaN          AMLODIPINE BESYLATE   \n",
       "18      ATORVASTATIN CALCIUM                #         ATORVASTATIN CALCIUM   \n",
       "19  BD ULTRA-FINE PEN NEEDLE              NaN  NEEDLES, INSULIN DISPOSABLE   \n",
       "\n",
       "           npi nppes_provider_city nppes_provider_first_name  ...  \\\n",
       "0   1003850462            TAMUNING                    DAVINA  ...   \n",
       "1   1003850462            TAMUNING                    DAVINA  ...   \n",
       "2   1003850462            TAMUNING                    DAVINA  ...   \n",
       "3   1003850462            TAMUNING                    DAVINA  ...   \n",
       "4   1003850462            TAMUNING                    DAVINA  ...   \n",
       "5   1003850462            TAMUNING                    DAVINA  ...   \n",
       "6   1003850462            TAMUNING                    DAVINA  ...   \n",
       "7   1003850462            TAMUNING                    DAVINA  ...   \n",
       "8   1003850462            TAMUNING                    DAVINA  ...   \n",
       "9   1003850462            TAMUNING                    DAVINA  ...   \n",
       "10  1003850462            TAMUNING                    DAVINA  ...   \n",
       "11  1003850462            TAMUNING                    DAVINA  ...   \n",
       "12  1003850462            TAMUNING                    DAVINA  ...   \n",
       "13  1003850462            TAMUNING                    DAVINA  ...   \n",
       "14  1003850462            TAMUNING                    DAVINA  ...   \n",
       "15  1003850462            TAMUNING                    DAVINA  ...   \n",
       "16  1023081304            TAMUNING                     PABLO  ...   \n",
       "17  1023081304            TAMUNING                     PABLO  ...   \n",
       "18  1023081304            TAMUNING                     PABLO  ...   \n",
       "19  1023081304            TAMUNING                     PABLO  ...   \n",
       "\n",
       "   nppes_provider_state     specialty_desc total_30_day_fill_count  \\\n",
       "0                    GU    Family Practice                    30.2   \n",
       "1                    GU    Family Practice                    22.0   \n",
       "2                    GU    Family Practice                    71.0   \n",
       "3                    GU    Family Practice                    20.0   \n",
       "4                    GU    Family Practice                    11.0   \n",
       "5                    GU    Family Practice                    40.0   \n",
       "6                    GU    Family Practice                    27.0   \n",
       "7                    GU    Family Practice                    57.0   \n",
       "8                    GU    Family Practice                    37.0   \n",
       "9                    GU    Family Practice                    32.0   \n",
       "10                   GU    Family Practice                    19.0   \n",
       "11                   GU    Family Practice                    33.0   \n",
       "12                   GU    Family Practice                    32.0   \n",
       "13                   GU    Family Practice                    23.0   \n",
       "14                   GU    Family Practice                    13.0   \n",
       "15                   GU    Family Practice                    13.0   \n",
       "16                   GU  Internal Medicine                    13.0   \n",
       "17                   GU  Internal Medicine                   110.0   \n",
       "18                   GU  Internal Medicine                    45.0   \n",
       "19                   GU  Internal Medicine                    11.0   \n",
       "\n",
       "    total_30_day_fill_count_ge65  total_claim_count  total_claim_count_ge65  \\\n",
       "0                           30.2                 17                    17.0   \n",
       "1                           22.0                 18                    18.0   \n",
       "2                           71.0                 59                    59.0   \n",
       "3                           20.0                 14                    14.0   \n",
       "4                           11.0                 11                    11.0   \n",
       "5                            NaN                 40                     NaN   \n",
       "6                           27.0                 15                    15.0   \n",
       "7                           57.0                 53                    53.0   \n",
       "8                           37.0                 35                    35.0   \n",
       "9                           32.0                 24                    24.0   \n",
       "10                           NaN                 17                     NaN   \n",
       "11                           NaN                 31                     NaN   \n",
       "12                           NaN                 26                     NaN   \n",
       "13                          23.0                 23                    23.0   \n",
       "14                          13.0                 13                    13.0   \n",
       "15                          13.0                 13                    13.0   \n",
       "16                          13.0                 13                    13.0   \n",
       "17                          78.0                 88                    64.0   \n",
       "18                           NaN                 35                     NaN   \n",
       "19                           0.0                 11                     0.0   \n",
       "\n",
       "    total_day_supply  total_day_supply_ge65  total_drug_cost  \\\n",
       "0                890                  890.0           209.08   \n",
       "1                660                  660.0           125.40   \n",
       "2               2130                 2130.0          1114.96   \n",
       "3                600                  600.0           203.31   \n",
       "4                330                  330.0          1769.88   \n",
       "5               1200                    NaN          5536.67   \n",
       "6                810                  810.0           369.81   \n",
       "7               1473                 1473.0           383.44   \n",
       "8               1110                 1110.0           236.70   \n",
       "9                960                  960.0           276.27   \n",
       "10               570                    NaN           190.94   \n",
       "11               990                    NaN           385.34   \n",
       "12               960                    NaN           226.49   \n",
       "13               690                  690.0           252.33   \n",
       "14               390                  390.0           103.34   \n",
       "15               390                  390.0            92.88   \n",
       "16               382                  382.0           105.53   \n",
       "17              3300                 2340.0           598.13   \n",
       "18              1350                    NaN           618.07   \n",
       "19               295                    0.0           382.87   \n",
       "\n",
       "    total_drug_cost_ge65  \n",
       "0                 209.08  \n",
       "1                 125.40  \n",
       "2                1114.96  \n",
       "3                 203.31  \n",
       "4                1769.88  \n",
       "5                    NaN  \n",
       "6                 369.81  \n",
       "7                 383.44  \n",
       "8                 236.70  \n",
       "9                 276.27  \n",
       "10                   NaN  \n",
       "11                   NaN  \n",
       "12                   NaN  \n",
       "13                252.33  \n",
       "14                103.34  \n",
       "15                 92.88  \n",
       "16                105.53  \n",
       "17                450.31  \n",
       "18                   NaN  \n",
       "19                  0.00  \n",
       "\n",
       "[20 rows x 21 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prescriber_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_df = prescriber_df.sort_values(by=[\"specialty_desc\",\"npi\",\"drug_name\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A prescriber is identified by the NPI (National Provider Identifier). The NPI is a 10-digit number. We will use the Bloom API as an example of what information is linked to the NPI for a prescriber. This NPI associated data is self-reported by the provider. When a provider writes a prescription and the prescription is filled by a patient the NPI and the drug prescribed is recorded in the Medicare claims database. This data was aggregated over calendar year 2013 to form the underlying database.  This allows us to connect what drugs the prescriber prescribed to Medicare members. A prescriber may prescribe to other patients not in Medicare. This data is not in this data set. To protect the privacy of individuals only drugs with more than 11 claims during the year are included in this data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\"1487818670\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get(\"http://www.bloomapi.com/api/search\", \n",
    "                 params={\"limit\": 10,\"offset\": 0, \"key1\": \"npi\", \"op1\": \"eq\", \"value1\": 1487818670})\n",
    "#r.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prescriber_df.to_csv(\"./data/medicare_dc_prescriber_raw_2013.csv\")\n",
    "# Uncomment last line if there are API Connection Problems\n",
    "# prescriber_df = pd.read_csv(\"./data/medicare_dc_prescriber_raw_2013.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "npi_drug_cross_df = pd.crosstab(prescriber_df[\"npi\"], prescriber_df[\"drug_name\"], values=prescriber_df[\"total_claim_count\"], \n",
    "                                  aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you have worked with Microsoft Excel you might be familiar with the concept of a pivot table. We want to create a dataframe where each row is a prescriber identified by their NPI and the columns of the table are the names of the drugs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>drug_name</th>\n",
       "      <th>ABILIFY</th>\n",
       "      <th>ACETAMINOPHEN-CODEINE</th>\n",
       "      <th>ACETAZOLAMIDE</th>\n",
       "      <th>ACTONEL</th>\n",
       "      <th>ACYCLOVIR</th>\n",
       "      <th>ADCIRCA</th>\n",
       "      <th>ADVAIR DISKUS</th>\n",
       "      <th>AFEDITAB CR</th>\n",
       "      <th>ALENDRONATE SODIUM</th>\n",
       "      <th>ALLOPURINOL</th>\n",
       "      <th>...</th>\n",
       "      <th>VENLAFAXINE HCL ER</th>\n",
       "      <th>VERAPAMIL ER</th>\n",
       "      <th>VESICARE</th>\n",
       "      <th>VOLTAREN</th>\n",
       "      <th>VYTORIN</th>\n",
       "      <th>WARFARIN SODIUM</th>\n",
       "      <th>ZAFIRLUKAST</th>\n",
       "      <th>ZETIA</th>\n",
       "      <th>ZOLPIDEM TARTRATE</th>\n",
       "      <th>ZOSTAVAX</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npi</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1003850462</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1023081304</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1033264072</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1043403447</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1063517639</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 277 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "drug_name   ABILIFY  ACETAMINOPHEN-CODEINE  ACETAZOLAMIDE  ACTONEL  ACYCLOVIR  \\\n",
       "npi                                                                             \n",
       "1003850462      NaN                    NaN            NaN      NaN        NaN   \n",
       "1023081304      NaN                    NaN            NaN      NaN        NaN   \n",
       "1033264072      NaN                    NaN            NaN      NaN        NaN   \n",
       "1043403447      NaN                    NaN            NaN      NaN        NaN   \n",
       "1063517639      NaN                    NaN            NaN      NaN        NaN   \n",
       "\n",
       "drug_name   ADCIRCA  ADVAIR DISKUS  AFEDITAB CR  ALENDRONATE SODIUM  \\\n",
       "npi                                                                   \n",
       "1003850462      NaN            NaN          NaN                17.0   \n",
       "1023081304      NaN            NaN          NaN                13.0   \n",
       "1033264072      NaN            NaN          NaN                 NaN   \n",
       "1043403447      NaN            NaN          NaN                 NaN   \n",
       "1063517639      NaN            NaN          NaN                 NaN   \n",
       "\n",
       "drug_name   ALLOPURINOL  ...  VENLAFAXINE HCL ER  VERAPAMIL ER  VESICARE  \\\n",
       "npi                      ...                                               \n",
       "1003850462          NaN  ...                 NaN           NaN       NaN   \n",
       "1023081304          NaN  ...                 NaN           NaN       NaN   \n",
       "1033264072         29.0  ...                 NaN           NaN       NaN   \n",
       "1043403447          NaN  ...                 NaN           NaN       NaN   \n",
       "1063517639         87.0  ...                 NaN          18.0       NaN   \n",
       "\n",
       "drug_name   VOLTAREN  VYTORIN  WARFARIN SODIUM  ZAFIRLUKAST  ZETIA  \\\n",
       "npi                                                                  \n",
       "1003850462       NaN      NaN             13.0          NaN    NaN   \n",
       "1023081304       NaN      NaN             11.0          NaN    NaN   \n",
       "1033264072       NaN      NaN              NaN          NaN    NaN   \n",
       "1043403447       NaN      NaN              NaN          NaN    NaN   \n",
       "1063517639       NaN      NaN             23.0          NaN    NaN   \n",
       "\n",
       "drug_name   ZOLPIDEM TARTRATE  ZOSTAVAX  \n",
       "npi                                      \n",
       "1003850462               13.0       NaN  \n",
       "1023081304                NaN       NaN  \n",
       "1033264072                NaN       NaN  \n",
       "1043403447                NaN       NaN  \n",
       "1063517639                NaN       NaN  \n",
       "\n",
       "[5 rows x 277 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "npi_drug_cross_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To replace the NaN (not a number) from the cells with zero the `fillna()` method is used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "npi_drug_cross_df = npi_drug_cross_df.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now it is simple to find the total number of claims for the drug Abilify."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "npi_drug_cross_df[\"ZOSTAVAX\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To count the number of drugs we will add a new field called `drug_count` to the dataframe. Then we will use the `groupby()` and `agg()` methods to create a new dataframe. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_df[\"drug_count\"] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_specialty_df = prescriber_df.groupby([\"npi\",\"specialty_desc\"]).agg({\"total_claim_count\": np.sum,  \"drug_count\": np.sum})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "To merge the tables we need to flatten the indices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_specialty_df.reset_index(level=[\"npi\",\"specialty_desc\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>npi</th>\n",
       "      <th>specialty_desc</th>\n",
       "      <th>total_claim_count</th>\n",
       "      <th>drug_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1003850462</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>409</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1023081304</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>733</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1033264072</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>611</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1043403447</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>229</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1063517639</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>3795</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1073674685</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>3398</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1104975689</td>\n",
       "      <td>General Surgery</td>\n",
       "      <td>75</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1114107109</td>\n",
       "      <td>Psychiatry &amp; Neurology</td>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1124082227</td>\n",
       "      <td>Physician Assistant</td>\n",
       "      <td>805</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1124089867</td>\n",
       "      <td>Physician Assistant</td>\n",
       "      <td>51</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          npi          specialty_desc  total_claim_count  drug_count\n",
       "0  1003850462         Family Practice                409          16\n",
       "1  1023081304       Internal Medicine                733          27\n",
       "2  1033264072         Family Practice                611          25\n",
       "3  1043403447       Internal Medicine                229          10\n",
       "4  1063517639       Internal Medicine               3795          71\n",
       "5  1073674685       Internal Medicine               3398          60\n",
       "6  1104975689         General Surgery                 75           5\n",
       "7  1114107109  Psychiatry & Neurology                 28           2\n",
       "8  1124082227     Physician Assistant                805          29\n",
       "9  1124089867     Physician Assistant                 51           4"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prescriber_specialty_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_specialty_drugs_df = pd.merge(prescriber_specialty_df, npi_drug_cross_df.reset_index(level=[\"npi\"]), on=\"npi\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>npi</th>\n",
       "      <th>specialty_desc</th>\n",
       "      <th>total_claim_count</th>\n",
       "      <th>drug_count</th>\n",
       "      <th>ABILIFY</th>\n",
       "      <th>ACETAMINOPHEN-CODEINE</th>\n",
       "      <th>ACETAZOLAMIDE</th>\n",
       "      <th>ACTONEL</th>\n",
       "      <th>ACYCLOVIR</th>\n",
       "      <th>ADCIRCA</th>\n",
       "      <th>...</th>\n",
       "      <th>VENLAFAXINE HCL ER</th>\n",
       "      <th>VERAPAMIL ER</th>\n",
       "      <th>VESICARE</th>\n",
       "      <th>VOLTAREN</th>\n",
       "      <th>VYTORIN</th>\n",
       "      <th>WARFARIN SODIUM</th>\n",
       "      <th>ZAFIRLUKAST</th>\n",
       "      <th>ZETIA</th>\n",
       "      <th>ZOLPIDEM TARTRATE</th>\n",
       "      <th>ZOSTAVAX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1003850462</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>409</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1023081304</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>733</td>\n",
       "      <td>27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1033264072</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>611</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1043403447</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>229</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1063517639</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>3795</td>\n",
       "      <td>71</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 281 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          npi     specialty_desc  total_claim_count  drug_count  ABILIFY  \\\n",
       "0  1003850462    Family Practice                409          16      0.0   \n",
       "1  1023081304  Internal Medicine                733          27      0.0   \n",
       "2  1033264072    Family Practice                611          25      0.0   \n",
       "3  1043403447  Internal Medicine                229          10      0.0   \n",
       "4  1063517639  Internal Medicine               3795          71      0.0   \n",
       "\n",
       "   ACETAMINOPHEN-CODEINE  ACETAZOLAMIDE  ACTONEL  ACYCLOVIR  ADCIRCA  ...  \\\n",
       "0                    0.0            0.0      0.0        0.0      0.0  ...   \n",
       "1                    0.0            0.0      0.0        0.0      0.0  ...   \n",
       "2                    0.0            0.0      0.0        0.0      0.0  ...   \n",
       "3                    0.0            0.0      0.0        0.0      0.0  ...   \n",
       "4                    0.0            0.0      0.0        0.0      0.0  ...   \n",
       "\n",
       "   VENLAFAXINE HCL ER  VERAPAMIL ER  VESICARE  VOLTAREN  VYTORIN  \\\n",
       "0                 0.0           0.0       0.0       0.0      0.0   \n",
       "1                 0.0           0.0       0.0       0.0      0.0   \n",
       "2                 0.0           0.0       0.0       0.0      0.0   \n",
       "3                 0.0           0.0       0.0       0.0      0.0   \n",
       "4                 0.0          18.0       0.0       0.0      0.0   \n",
       "\n",
       "   WARFARIN SODIUM  ZAFIRLUKAST  ZETIA  ZOLPIDEM TARTRATE  ZOSTAVAX  \n",
       "0             13.0          0.0    0.0               13.0       0.0  \n",
       "1             11.0          0.0    0.0                0.0       0.0  \n",
       "2              0.0          0.0    0.0                0.0       0.0  \n",
       "3              0.0          0.0    0.0                0.0       0.0  \n",
       "4             23.0          0.0    0.0                0.0       0.0  \n",
       "\n",
       "[5 rows x 281 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prescriber_specialty_drugs_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will show that different medical specialties prescribe different numbers of drugs. We expect that internal medicine which see a large number of different patients will prescribe the most number of drugs compared to a specialized field such as psychiatry."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x28dab8aaeb8>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYMAAAELCAYAAAA7h+qnAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAG4hJREFUeJzt3XucHFWd9/HPNySShAABErmF7LCEy6K7Iowosir3xyAKCrzAx9WIPPLSVQKyPMLqruJtFx5l0cHbsoBElkXugoEgiNxEbiGBJAQkESLOkg3hfkkgIfk9f5wzpDPpnumedHVNT77v12teU11dl19VdfevTp2qcxQRmJnZhm1Y2QGYmVn5nAzMzMzJwMzMnAzMzAwnAzMzw8nAzMxwMjAzM5wMzMwMJwMzMwOGlx1AvcaNGxcdHR1lh2Fm1lYeeOCBZyJifH/TtU0y6OjoYObMmWWHYWbWViT9qZ7pfJnIzMycDMzMzMnAzMxwMjAzM5wMzMwMJwMzM8PJwMzMaKPnDMrW1dXFwoULC1l2d3c3ABMmTGj6sidNmsTUqVObvlwzG1qcDAaB5cuXlx2CmW3gnAzqVOTZdc+yu7q6CluHmVlfXGdgZmZOBmZm5mRgZmY4GZiZGU4GZmaGk4GZmeFkYGZmOBmYmRlOBmZmhpOBmZnhZGBmZjgZmJkZTgZmZoaTgZmZ4WRgZmY4GZiZGU4GZmZGC5KBpC9JeljSPEmXShopaUdJ90paIOkySW8pOg4zM6ut0GQgaXtgKtAZEW8HNgKOBc4CzomInYHngeOLjMPMzPrWistEw4FRkoYDo4HFwAHAlfn9acARLYjDzMxqKDQZRMR/A98DniQlgReBB4AXIuKNPFk3sH2RcZiZWd+Kvky0BXA4sCOwHbAJMLnKpFFj/hMkzZQ0c+nSpcUFama2gSv6MtFBwBMRsTQiVgJXA+8FxubLRgATgKeqzRwR50VEZ0R0jh8/vuBQzcw2XEUngyeB90gaLUnAgcB84FbgqDzNFODaguMwM7M+FF1ncC+pongWMDev7zzgNOAUSQuBrYALiozDzMz6Nrz/SdZPRHwd+Hqv0Y8Dexe9bjMzq4+fQDYzMycDMzNzMjAzM5wMzMwMJwMzM8PJwMzMcDIwMzOcDMzMDCcDMzPDycDMzHAyMDMznAzMzAwnAzMzw8nAzMxwMjAzM5wMzMwMJwMzM8PJwMzMcDIwMzOcDMzMDCcDMzPDycDMzHAyMDMznAzMzAwnAzMzw8nAzMxwMjAzM5wMzMwMJwMzM8PJwMzMcDIwMzOcDMzMDCcDMzPDycDMzHAyMDMzWpAMJI2VdKWkRyU9ImkfSVtKulnSgvx/i6LjMDOz2lpRMvgBcGNE7Aa8A3gEOB24JSJ2Bm7Jr83MrCSFJgNJmwHvBy4AiIgVEfECcDgwLU82DTiiyDjMzKxvwwte/l8CS4GfSXoH8ABwErB1RCwGiIjFkt7arBV2dXWxcOHCZi2uJRYsWADA1KlTS46kfpMmTWqreM2sb0Ung+HAnsCJEXGvpB/QwCUhSScAJwBMnDixrnkWLlzI7LnzWT16ywGEWw6tCAAe+OP/lBxJfYYte67sEMysyYpOBt1Ad0Tcm19fSUoGSyRtm0sF2wJPV5s5Is4DzgPo7OyMele6evSWvLb7YesXudU0cv70skMwsyYrtM4gIv4H+LOkXfOoA4H5wHXAlDxuCnBtkXGYmVnfii4ZAJwIXCLpLcDjwHGkJHS5pOOBJ4GjWxCHmZnVUHgyiIgHgc4qbx1Y9LrNzKw+fgLZzMzqTwaSdqxnnJmZtZ9GSgZXVRl3ZbMCMTOz8vRbZyBpN+BtwOaSPlbx1mbAyKICMzOz1qmnAnlX4DBgLPDhivEvA58tIigzM2utfpNBRFwLXCtpn4i4uwUxmZlZizVya+lCSV8BOirni4jPNDsoMzNrrUaSwbXAncBvgFXFhGNmZmVoJBmMjojTCovEzMxK08itpdMlHVpYJGZmVppGksFJpISwXNJLkl6W9FJRgZmZWevUfZkoIjYtMhAzMytP3clA0vurjY+IO5oXjpmZlaGRCuT/WzE8Etib1I3lAU2NyMzMWq6Ry0SVTx8jaQfg/zU9IjMza7n1acK6G3h7swIxM7PyNFJncC7Q0w/xMGAP4KEigjIzs9ZqpM5gZsXwG8ClEXFXk+MxM7MSNFJnMC33Y7xLHvWHYkIyM7NWa+Qy0X7ANGARIGAHSVN8a6mZWftr5DLR2cAhEfEHAEm7AJcCexURmJmZtU4jdxON6EkEABHxGDCi+SGZmVmrNVSBLOkC4OL8+hOkh87MzKzNNZIMPg98AZhKqjO4A/hxEUGZmVlrNZIMhgM/iIh/A5C0EbBxIVGZmVlLNVJncAswquL1KFKvZ2Zm1uYaSQYjI+KVnhd5eHTzQzIzs1ZrJBm8KmnPnheS9gKWNz8kMzNrtUbqDE4GrpD0VH69LXBM80MyM7NWa6Q5ivsl7QbsSrqb6NGIWNnzvqSDI+LmAmI0M7OCNdSEdUSsjIh5ETG3MhFkZzUxLjMza6H16c+gNzVxWWZm1kLNTAbR/yRmZjYYNTMZmJlZm2pmMljUxGWZmVkLNdKfwceqjH4RmBsRT0dEtffNzKwNNPKcwfHAPsCt+fV+wD3ALpK+GREX15oxt2M0E/jviDhM0o7AL4AtgVnAJyNixQDiX0d3dzfDlr3IyPnTm7E4q2LYsmfp7n6j7DDMrIkauUy0GviriDgyIo4EdgdeB94NnNbPvCcBj1S8Pgs4JyJ2Bp4nJRozMytJIyWDjohYUvH6aWCXiHhOUu9nDt4kaQLwIeA7wCmSBBwA/O88yTTgDOAnjQRey4QJE1jy+nBe2/2wZizOqhg5fzoTJmxTdhhm1kSNJIM7JU0HrsivjwTukLQJ8EIf830f+DKwaX69FfBCRPRcZ+gGtq82o6QTgBMAJk6c2ECoZmbWiEYuE30BuAjYA3gn8HPgCxHxakTsX20GSYcBT0dEZY9o1R5Oq/qMQkScFxGdEdE5fvz4BkI1M7NGNNI2UQBX5r967Qt8RNKhwEhgM1JJYayk4bl0MAF4qo9lmJlZweouGUh6WdJL+e81SaskvdTXPBHxjxExISI6gGOB30bEJ0h3JB2VJ5sCXDvA+M3MrAkaKRlsWvla0hHA3gNc72nALyR9G5gNXDDA5ZiZWRM0UoG8loj4paTTG5j+NuC2PPw4A08kZmbWZAN9AnkY0IkbpzMzGxIaKRl8uGL4DVJbRIc3NRozMytFXckgNycxJyLOKTgeMzMrQV13E0XEKuAjBcdiZmYlaeQy0e8l/RC4DHi1Z2REzGp6VGZm1lKNJIP35v/fyP9FqkA+oKkRmZlZy/WbDCSdkgenk378K5uT8N1EZmZDQD0lg56HzXYF3kV6Wliku4vuKCguMzNroX6TQUR8A0DSTcCeEfFyfn0Ga1owNTOzNtZIq6UTgcreyFYAHU2NxszMStFIBfLFwH2SriHVFXyU1DGNmZm1uUYaqvuOpBnA+/Ko4yJidjFhmZlZKzXUUF1+psDPFZiZDTGN1BmYmdkQ5WRgZmZOBmZm5mRgZmY4GZiZGU4GZmaGk4GZmeFkYGZmOBmYmRlOBmZmhpOBmZnhZGBmZjgZmJkZTgZmZoaTgZmZ4WRgZmY4GZiZGU4GZmaGk4GZmeFkYGZmOBmYmRlOBmZmRsHJQNIOkm6V9IikhyWdlMdvKelmSQvy/y2KjMPMzPpWdMngDeAfIuKvgPcAX5C0O3A6cEtE7Azckl+bmVlJCk0GEbE4Imbl4ZeBR4DtgcOBaXmyacARRcZhZmZ9a1mdgaQO4J3AvcDWEbEYUsIA3tqqOMzMbF0tSQaSxgBXASdHxEsNzHeCpJmSZi5durS4AM3MNnCFJwNJI0iJ4JKIuDqPXiJp2/z+tsDT1eaNiPMiojMiOsePH190qGZmG6yi7yYScAHwSET8W8Vb1wFT8vAU4Noi4zAzs74NL3j5+wKfBOZKejCP+wpwJnC5pOOBJ4GjC47DzMz6UGgyiIjfAarx9oFFrtvMzOrnJ5DNzMzJwMzMnAzM1sszzzzDiSeeyLPPPlt2KGbrxcnAbD1MmzaNOXPmMG3atP4nNhvEnAzMBuiZZ55hxowZRAQzZsxw6cDampOB2QBNmzaNiABg9erVLh1YW3MyMBugm2++mZUrVwKwcuVKbrrpppIjMhs4JwOzATr44IMZMWIEACNGjOCQQw4pOSKzgXMyMBugKVOmkFpcgWHDhjFlypR+5jAbvJwMzAZo3LhxTJ48GUlMnjyZrbbaquyQzAas6LaJzIa0KVOmsGjRIpcKrO05GZith3HjxnHuueeWHYbZehuSyWDYsucYOX962WHUTa+l/n5i5GYlR1KfYcueA7YpOwwza6IhlwwmTZpUdggNW7DgZQB23qldfmC3acv9bGa1DblkMHXq1LJDaFhPzF1dXSVHYmYbKt9NZGZmTgZmZuZkYGZmOBmYmRlOBmZmxhC8m8jMytHV1cWMGTOavtxly5a92VR4O5HE6NGjm77cyZMnF3LXpEsGZmbmkoGZNcfUqVPb8jkfS5wMbNDo6upi4cKFhSy7u7ub5cuXF7LsoowaNYoJEyYUsuxJkyb5h9vW4mRgg8bChQt5bN4sJo5Z1fRlr1o2jNWr1PTlFmnVypd4bdHipi/3yVc2avoyrf05GdigMnHMKv6p85WywxjSvj1zTNkh2CDkCmQzM3MyMDMzJwMzM8N1BnUr8k6XBQsWAMU0v91Od410d3fz6ssb+Zp2wf708kZs0t1ddhg2yDgZDAKjRo0qOwQz28A5GdSpXc6u29mECRN47Y3FvpuoYN+eOYaRBT2/YO3LdQZmZuZkYGZmvkxkg8yTr7RXBfKSZel8auvRq0uOpH5PvrIRu5QdhA06pSUDSR8EfgBsBJwfEWeWFYsNDpMmTSo7hIatyHeCjezYueRI6rcL7bmvrVilJANJGwE/Ag4GuoH7JV0XEfPLiMcGhyIr6Yu8Nbgo7XRbsLW/suoM9gYWRsTjEbEC+AVweEmxmA3YqFGjfGuwDQllXSbaHvhzxetu4N0lxWIbAJ9hm/WtrJJBtbaE1+nXTtIJkmZKmrl06dIWhGVmtmEqKxl0AztUvJ4APNV7oog4LyI6I6Jz/PjxLQvOzGxDU1YyuB/YWdKOkt4CHAtcV1IsZmYbvFLqDCLiDUlfBH5NurX0woh4uIxYzMysxOcMIuIG4Iay1m9mZmu4OQozM3MyMDMzJwMzMwMUsc7t/YOSpKXAn8qOo0DjgGfKDsIGxMeuvQ314/cXEdHvvfltkwyGOkkzI6Kz7DiscT527c3HL/FlIjMzczIwMzMng8HkvLIDsAHzsWtvPn64zsDMzHDJwMzMGOLJQNIrdUxzsqTRLYilQ9K8GuND0rcqxo2TtFLSDxtcxyJJ4/Lw7/uZ9puSDmpk+QMhaZWkByXNk3RFs/a1pDMknVrjvf62faykv29CDOMk3SppjqT7JNXsvDkf47MrXp8q6Yz1jaGOGN/8TLQbSdtI+oWkP0qaL+kGSQPqvlnSp3u+T5I+J+lT/Ux/kaSjBrKudjWkk0GdTgYa+oHK3XY20+PAYRWvjwbWq+G+iHhvP+9/LSJ+sz7rqNPyiNgjIt4OrAA+V/QK+9t2YCxQNRk0eGw/D9wREX8DHEHavlpeBz7W7B9mJUPueyxJwDXAbRGxU0TsDnwF2LqeefvaJxHx04j4efOiHRqG3IeoGkn7SbpN0pWSHpV0Sf7ATAW2A26VdGue9hBJd0ualc9kx+TxiyR9TdLvgKPz8s7KZ4SPSXpfnq5D0p15/lmS+vthAlgOPCKp517nY4DLK+IfL+kqSffnv33z+K0k3SRptqR/p6LToMpSkaQvS5or6SFJZ+Zxb5755G37Ro53rqTd8vhNJF2Y1zlb0vp2TXonMCkv9/oczzxJx0g6UNI1FTEfLOnqPPzBHNtDkm6pWN7u+Tg8no/lWtsuaYykWyq2qyf+M4Gdconlu/nzcauk/wLmSvqWpJMqlvedyuVXWEHqi4OIeCp34VrLG6SKyi/1fqOP47tW6Sfvq47894ikHwOzgB0kfTxv4zxJZ1ULQNIp+f15kk6uGP/P+Xtxs6RLlUotO0maVTHNzpIe6GP7mm1/YGVE/LRnREQ8CMyudkxr7JPj8nfzdmDfim15c79K2kPSPUqlu2skbdE7kPzZnJ3Xd6GkjfP4Q/N++52kLknTJQ2TtEDS+DzNMEkL1Q6ls4gYsn/AK/n/fsCLpC/uMOBu4G/ze4uAcXl4HHAHsEl+fRrwtYrpvlyx7NuAs/PwocBv8vBoYGQe3hmYmYc7gHlVYuwA5gEfAb6XY7wF+DTwwzzNf1XEOxF4JA93VcT3IVJvceN6bftk4PfA6Px6y/z/IuCoim07MQ//PXB+Hv4X4O/y8FjgsZ59M4BjMBy4lnQ2fSTwHxXTbE5KZI8C4yu2+cPAeFIXqTv2iv+MvF0b5+P2LDCiyjo3qzi2C/N61joWpM/HqxXr6ABm5eFhwB+Braps21HAC8Dn6tkPwGZ5X28OnAqc0c/xPQM4tWIZ83JsHcBq4D15/HbAk3lfDQd+CxxR+fkG9gLmApsAY0glz3cCncCDwChgU2BBzzqBW4E9Kj4LJ7bwuzsVOKfK+L6OaeU+2bZin7wFuIs136czKrZxDvCBPPxN4PuV3w9gJOnzt0se/3PS1YSe8T2fmUuB6Xn468DJefgQ4KpW7bf1+dsgSgbZfRHRHRGrSR/+jirTvAfYHbhL0oPAFOAvKt6/rNf0V+f/D1QsbwTwH5LmAlfk5dXjRuBg4ONV1nMQ8MMc03XAZpI2Bd4P/CdARFwPPF9luQcBP4uIZXm652qsv9q2HAKcntd7G+kLMLHO7ekxKs8/k/TlvID0o3SQUsnqfRHxYqRvzsXA30kaC+wDzCAdkzsi4okq8V8fEa9HxDPA06x7CUHAv0iaA/yG1Pd2rcsM91WsYxHwrKR35n0wOyKeXWvB0vbAV4Fdgf8j6cg8fo6kzaqtICJeIv2Y9C5l1Dq+fflTRNyTh99FupyyNCLeAC4hfTYq/S1wTUS8GhGvkI73+/L4ayNieUS8DPyqYp7zgeOULp0dQ0paZevrmFbuk3ezZp+sYN3vFJI2B8ZGxO151DTW3W+7Ak9ExGO9ptkNeLznM0NKBj0uBHrqJD4D/KzxzWy90vozKMHrFcOrqL7tAm6OiI/XWMarNZZZubwvAUuAd5DOKl+rJ7iIWJGL4f8AvI10VtxjGLBPRCxfK1gJqvQd3YvqmAaqb4uAIyPiD3XMX8vyiNij17jHJO1FKlH9q6SbIuKbpC/Nr0j77IpInSD1FX9/x/QTpDPDvSJipaRFpIRWTe9jez6pdLYN6cvd277AQxGxRNKHgFskbQ0syj/6tXyfdBmj8gei1vF9g7Uv5VbGXhlvtT7Fe6s1TV/zXkU6y/0t8EDvhFiwh0ln5r31dUx7H8Nm3Dff8H6LiD9LWiLpAFJS+kQT4ijchlQyqOVlUvEY4B5gX0mTACSNVuN3L2wOLM4lkE+SenKr19nAaVW+dDcBX+x5Iannx/UO8gdN0mRgneuded7PKN/FI2nLBuL5NXBi/kEmnymvN0nbAcsi4j9Jl8b2hHTdndQX9j+RiumQLul9QNKOA4h/c+Dp/KOxP2tKeZXHvJZrgA+Szrp/XeX9OcD+kraLiCWkk4Af0c/Zcy7ZXA4cXzG61vFdRN43kvYEdqyx2HtJ+2hcPov/OHB7r2nuAI7In+lNgI+S6nB+B3xY0kil+rEPVcT6Wt72n9D6s9vfAhtL+mzPCEnvIh3Dase0t3uB/ZTq1UaQbspYS0S8CDyvXN9H+r723m+PAh09vwkV0zwK/KWkjjz+mF7znU8qtV8eEav629jBYEMqGdRyHjBD0uKI2F/Sp4FLeyqJSD9Mj9Wce10/Bq6SdDTpmmvvs5WaInX9We0uoqnAj3LReDjpi/054Bs51lmkD+iTVZZ5Y/5xmSlpBal3ua/UGdK3SGeyc3JCWMTadz0N1F8D35W0GlhJqkfocQmp3mB+jn+ppBOAq5XuEHmadDmtHpcAv5I0k3Rp8NG8zGcl3aV0q+8M4PreM+aS2q3AC9W+zBHxqKSvAr+WtJJUGjwWOFPSrIrLCtWcTcWPP7WP71XAp/Llo/up8TmMiMWS/pH0eRNwQ0Rc22uaWZIuAu7Lo86PiNkAkq4DHiK1CjyTVL/W4xLgY6SE1TIREZI+Cnxf0umk0uIi0vX+rt7HtMr8i5Vu3b0bWEwqjVU7MZsC/DSfLD0OHNdrOa9JOg64QtJw0nH4aUS8rnR78o2SnmHNfu1xHSmBtsUlIvATyDbIKN0LPjsiLig5jmGkH5CjI2JBmbEUTdKYiHgl/yDeAZwQEbPye6cCm0fEP5ca5CBUsd9EKhUuiIhz8nudpArw9/W5kEHEJQMbNHKdyaukepMy49gdmE6qcB3SiSA7L2/zSGBaRSK4BtgJOKDM4Aaxz0qaQrpbaTbw7wC5JPN52qSuoIdLBmZm5gpkMzNzMjAzM5wMzMwMJwMzM8PJwGwtSs0kj+1nmkX5Aa+mNIWdl3mb1jRUaNZyTgZmFSLi0Ih4oc7JazaFbdZunAys7ah6E9iLtKZJ8fsqmhSp1Tz0GEk/U2qWeI7WNDRX2UHQLyU9IOnh/BR0b72bwr5YFc18KzWV/pEa2zBKqeOWOZIuI7Ua2vNerWbUz1Tq5GWOpO/lcVsrNb38UP6rp8l0s3WV3Wyq//zX6B/Vm8BeBHw1v/4Ua5oTrtU89Fnk5orz6y3y/0WsaQa8p7nsUaTmo7eqnIZ1m8L+APDLipieAIbX2IZTgAvz8N+Q+jvopEYz6sCWwB9Y82zQ2Pz/MtY0l7wR6Wnh0o+R/9rvz08gWzuaC3xPqROX6RFxZ2oR4M1mhC8FzsnDB5E6wemZt6d56INIbQkBEBHVmv+emtvHAdiB1D9FzZY7I+J2ST+S9FZSez5XRWpSupr3k/qjICLm5HaJYO1m1CE93Xo38BKpfZ7zJV1PekIa0tPBn8rLWcXa7QqZ1c3JwNpORKzTBHbPW5WT5f+1mofus2lvSfuREsY+EbFM0m3Ubv660sWkZgiOJbVl3+emVFs1NZpRl7Q3cGBe9hdxMxHWRK4zsLajGk1gs6YZ4WNIZ9NQu3no3uN7N/+9OfB8TgS7kc7Ye6vWFPZFpJ6wiNQKbS2VzY+/nXSpCGo0o57rDTaPiBvy8nu24xZyq6+SNlKNjnXM+uNkYO3or4H7ctPOXwW+ncdvLOle4CTW9DU8FejMla7zSU1Dk+fZIldAP0Tqc7fSjcDwfPnmW6Qf6bVE6nfirryM7+ZxS4BH6L/p4p8AY/Lyv0xuAjkilpI61bk0v3cPqVetTYHpedztFdt3EqlfhbmkXure1s96zapyQ3U2JCj1eNUZqQvMMuMYTarT2DNS5ylmbcElA7MmkXQQqbOVc50IrN24ZGBWIEn/i3Qba6UnIuKj1aY3K4uTgZmZ+TKRmZk5GZiZGU4GZmaGk4GZmeFkYGZmwP8H0WkWEeFMjmcAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sb.boxplot(x=\"specialty_desc\", y=\"drug_count\", \n",
    "           data=prescriber_specialty_drugs_df[(prescriber_specialty_drugs_df[\"specialty_desc\"] == \"Cardiology\") |\n",
    "           (prescriber_specialty_drugs_df[\"specialty_desc\"] == \"Internal Medicine\") |\n",
    "           (prescriber_specialty_drugs_df[\"specialty_desc\"] == \"Psychiatry & Neurology\")]\n",
    "          )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will build the dataframes and corresponding matrices to compute the euclidean distance between two Medicare prescribers in DC. Rather than creating a crosstab by `\"drug_name\"` we will use the `\"generic_name\"`. The branded drug Abilify has the generic name of ARIPiprazole. A great tool for understanding the relationship between active ingredients, generic name and branded product is [RxNav](https://rxnav.nlm.nih.gov/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "npi_generic_cross_df = pd.crosstab(prescriber_df[\"npi\"], prescriber_df[\"generic_name\"], values=prescriber_df[\"total_claim_count\"], \n",
    "                                  aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "npi_generic_cross_df = npi_generic_cross_df.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>generic_name</th>\n",
       "      <th>ACETAMINOPHEN WITH CODEINE</th>\n",
       "      <th>ACETAZOLAMIDE</th>\n",
       "      <th>ACYCLOVIR</th>\n",
       "      <th>ADALIMUMAB</th>\n",
       "      <th>ALBUTEROL SULFATE</th>\n",
       "      <th>ALENDRONATE SODIUM</th>\n",
       "      <th>ALLOPURINOL</th>\n",
       "      <th>ALPRAZOLAM</th>\n",
       "      <th>AMIODARONE HCL</th>\n",
       "      <th>AMITRIPTYLINE HCL</th>\n",
       "      <th>...</th>\n",
       "      <th>VALACYCLOVIR HCL</th>\n",
       "      <th>VALPROIC ACID</th>\n",
       "      <th>VALSARTAN</th>\n",
       "      <th>VALSARTAN/HYDROCHLOROTHIAZIDE</th>\n",
       "      <th>VENLAFAXINE HCL</th>\n",
       "      <th>VERAPAMIL HCL</th>\n",
       "      <th>WARFARIN SODIUM</th>\n",
       "      <th>ZAFIRLUKAST</th>\n",
       "      <th>ZOLPIDEM TARTRATE</th>\n",
       "      <th>ZOSTER VACCINE LIVE/PF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npi</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1003850462</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1023081304</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1033264072</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1043403447</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1063517639</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 251 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "generic_name  ACETAMINOPHEN WITH CODEINE  ACETAZOLAMIDE  ACYCLOVIR  \\\n",
       "npi                                                                  \n",
       "1003850462                           0.0            0.0        0.0   \n",
       "1023081304                           0.0            0.0        0.0   \n",
       "1033264072                           0.0            0.0        0.0   \n",
       "1043403447                           0.0            0.0        0.0   \n",
       "1063517639                           0.0            0.0        0.0   \n",
       "\n",
       "generic_name  ADALIMUMAB  ALBUTEROL SULFATE  ALENDRONATE SODIUM  ALLOPURINOL  \\\n",
       "npi                                                                            \n",
       "1003850462           0.0                0.0                17.0          0.0   \n",
       "1023081304           0.0                0.0                13.0          0.0   \n",
       "1033264072           0.0                0.0                 0.0         29.0   \n",
       "1043403447           0.0                0.0                 0.0          0.0   \n",
       "1063517639           0.0               21.0                 0.0         87.0   \n",
       "\n",
       "generic_name  ALPRAZOLAM  AMIODARONE HCL  AMITRIPTYLINE HCL  ...  \\\n",
       "npi                                                          ...   \n",
       "1003850462           0.0             0.0                0.0  ...   \n",
       "1023081304           0.0             0.0                0.0  ...   \n",
       "1033264072           0.0             0.0                0.0  ...   \n",
       "1043403447           0.0             0.0                0.0  ...   \n",
       "1063517639           0.0             0.0               16.0  ...   \n",
       "\n",
       "generic_name  VALACYCLOVIR HCL  VALPROIC ACID  VALSARTAN  \\\n",
       "npi                                                        \n",
       "1003850462                 0.0            0.0       40.0   \n",
       "1023081304                 0.0            0.0       46.0   \n",
       "1033264072                 0.0            0.0       33.0   \n",
       "1043403447                 0.0            0.0        0.0   \n",
       "1063517639                 0.0            0.0      148.0   \n",
       "\n",
       "generic_name  VALSARTAN/HYDROCHLOROTHIAZIDE  VENLAFAXINE HCL  VERAPAMIL HCL  \\\n",
       "npi                                                                           \n",
       "1003850462                              0.0              0.0            0.0   \n",
       "1023081304                              0.0              0.0            0.0   \n",
       "1033264072                              0.0              0.0            0.0   \n",
       "1043403447                              0.0              0.0            0.0   \n",
       "1063517639                              0.0              0.0           18.0   \n",
       "\n",
       "generic_name  WARFARIN SODIUM  ZAFIRLUKAST  ZOLPIDEM TARTRATE  \\\n",
       "npi                                                             \n",
       "1003850462               13.0          0.0               13.0   \n",
       "1023081304               11.0          0.0                0.0   \n",
       "1033264072                0.0          0.0                0.0   \n",
       "1043403447                0.0          0.0                0.0   \n",
       "1063517639               23.0          0.0                0.0   \n",
       "\n",
       "generic_name  ZOSTER VACCINE LIVE/PF  \n",
       "npi                                   \n",
       "1003850462                       0.0  \n",
       "1023081304                       0.0  \n",
       "1033264072                       0.0  \n",
       "1043403447                       0.0  \n",
       "1063517639                       0.0  \n",
       "\n",
       "[5 rows x 251 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "npi_generic_cross_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ACETAMINOPHEN WITH CODEINE', 'ACETAZOLAMIDE', 'ACYCLOVIR',\n",
       "       'ADALIMUMAB', 'ALBUTEROL SULFATE', 'ALENDRONATE SODIUM', 'ALLOPURINOL',\n",
       "       'ALPRAZOLAM', 'AMIODARONE HCL', 'AMITRIPTYLINE HCL',\n",
       "       ...\n",
       "       'VALACYCLOVIR HCL', 'VALPROIC ACID', 'VALSARTAN',\n",
       "       'VALSARTAN/HYDROCHLOROTHIAZIDE', 'VENLAFAXINE HCL', 'VERAPAMIL HCL',\n",
       "       'WARFARIN SODIUM', 'ZAFIRLUKAST', 'ZOLPIDEM TARTRATE',\n",
       "       'ZOSTER VACCINE LIVE/PF'],\n",
       "      dtype='object', name='generic_name', length=251)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "npi_generic_cross_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_specialty_generic_df = pd.merge(prescriber_specialty_df, npi_generic_cross_df.reset_index(level=[\"npi\"]), on=\"npi\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>npi</th>\n",
       "      <th>specialty_desc</th>\n",
       "      <th>total_claim_count</th>\n",
       "      <th>drug_count</th>\n",
       "      <th>ACETAMINOPHEN WITH CODEINE</th>\n",
       "      <th>ACETAZOLAMIDE</th>\n",
       "      <th>ACYCLOVIR</th>\n",
       "      <th>ADALIMUMAB</th>\n",
       "      <th>ALBUTEROL SULFATE</th>\n",
       "      <th>ALENDRONATE SODIUM</th>\n",
       "      <th>...</th>\n",
       "      <th>VALACYCLOVIR HCL</th>\n",
       "      <th>VALPROIC ACID</th>\n",
       "      <th>VALSARTAN</th>\n",
       "      <th>VALSARTAN/HYDROCHLOROTHIAZIDE</th>\n",
       "      <th>VENLAFAXINE HCL</th>\n",
       "      <th>VERAPAMIL HCL</th>\n",
       "      <th>WARFARIN SODIUM</th>\n",
       "      <th>ZAFIRLUKAST</th>\n",
       "      <th>ZOLPIDEM TARTRATE</th>\n",
       "      <th>ZOSTER VACCINE LIVE/PF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1003850462</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>409</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1023081304</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>733</td>\n",
       "      <td>27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1033264072</td>\n",
       "      <td>Family Practice</td>\n",
       "      <td>611</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1043403447</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>229</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1063517639</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>3795</td>\n",
       "      <td>71</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 255 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          npi     specialty_desc  total_claim_count  drug_count  \\\n",
       "0  1003850462    Family Practice                409          16   \n",
       "1  1023081304  Internal Medicine                733          27   \n",
       "2  1033264072    Family Practice                611          25   \n",
       "3  1043403447  Internal Medicine                229          10   \n",
       "4  1063517639  Internal Medicine               3795          71   \n",
       "\n",
       "   ACETAMINOPHEN WITH CODEINE  ACETAZOLAMIDE  ACYCLOVIR  ADALIMUMAB  \\\n",
       "0                         0.0            0.0        0.0         0.0   \n",
       "1                         0.0            0.0        0.0         0.0   \n",
       "2                         0.0            0.0        0.0         0.0   \n",
       "3                         0.0            0.0        0.0         0.0   \n",
       "4                         0.0            0.0        0.0         0.0   \n",
       "\n",
       "   ALBUTEROL SULFATE  ALENDRONATE SODIUM  ...  VALACYCLOVIR HCL  \\\n",
       "0                0.0                17.0  ...               0.0   \n",
       "1                0.0                13.0  ...               0.0   \n",
       "2                0.0                 0.0  ...               0.0   \n",
       "3                0.0                 0.0  ...               0.0   \n",
       "4               21.0                 0.0  ...               0.0   \n",
       "\n",
       "   VALPROIC ACID  VALSARTAN  VALSARTAN/HYDROCHLOROTHIAZIDE  VENLAFAXINE HCL  \\\n",
       "0            0.0       40.0                            0.0              0.0   \n",
       "1            0.0       46.0                            0.0              0.0   \n",
       "2            0.0       33.0                            0.0              0.0   \n",
       "3            0.0        0.0                            0.0              0.0   \n",
       "4            0.0      148.0                            0.0              0.0   \n",
       "\n",
       "   VERAPAMIL HCL  WARFARIN SODIUM  ZAFIRLUKAST  ZOLPIDEM TARTRATE  \\\n",
       "0            0.0             13.0          0.0               13.0   \n",
       "1            0.0             11.0          0.0                0.0   \n",
       "2            0.0              0.0          0.0                0.0   \n",
       "3            0.0              0.0          0.0                0.0   \n",
       "4           18.0             23.0          0.0                0.0   \n",
       "\n",
       "   ZOSTER VACCINE LIVE/PF  \n",
       "0                     0.0  \n",
       "1                     0.0  \n",
       "2                     0.0  \n",
       "3                     0.0  \n",
       "4                     0.0  \n",
       "\n",
       "[5 rows x 255 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prescriber_specialty_generic_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_specialty_generic_df = prescriber_specialty_generic_df.sort_values(by=[\"specialty_desc\", \"npi\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will shift from using the Pandas dataframe object to the `numpy.array`. We need to slice off the columns that are not related to the number of filled prescriptions and convert this to a matrix. The `iloc()` method slices a dataframe by the column and the method `as_matrix()` converts the dataframe into a numpy array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: FutureWarning: Method .as_matrix will be removed in a future version. Use .values instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "pf_matrix = prescriber_specialty_generic_df.iloc[: , 4: ].as_matrix()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(89, 251)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pf_matrix.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We add an arrays for the identifiers an the generic name of the drugs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: FutureWarning: Method .as_matrix will be removed in a future version. Use .values instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "identifiers_matrix = prescriber_specialty_df.iloc[:,0:2].as_matrix()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "generic_array = prescriber_specialty_generic_df.columns[4:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to compute the distance between a vector representing the drugs that a prescriber prescribes we need to import the `scipy.spatial` library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "import scipy.spatial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We normalize the claim count of prescriptions by the total number of claims for the prescriber. Each entry now represent the percentage of total claims that are associated with the prescriber."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "normalized_pf_matrix = pf_matrix / np.reshape(np.sum(pf_matrix,1), (pf_matrix.shape[0],1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(89, 251)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normalized_pf_matrix.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use the euclidean distance to compute the distance between two prescribers. The results of the `pdist()` function need to be placed into square form. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'prescriber_dist1 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"cityblock\"))\\nprescriber_dist2 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"cosine\"))\\nprescriber_dist3 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"sqeuclidean\"))\\nprescriber_dist4 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"seuclidean\"))'"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"prescriber_dist1 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"cityblock\"))\n",
    "prescriber_dist2 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"cosine\"))\n",
    "prescriber_dist3 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"sqeuclidean\"))\n",
    "prescriber_dist4 = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, \"seuclidean\"))\"\"\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we have sorted the prescribers by their specialty then we expect to see some spatial patterns in the distance matrix. A matrix can be visualized using the `matplotlib` library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "from matplotlib import cm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "fig, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2)\n",
    "fig.suptitle('Prescriber Distances')\n",
    "ax1.matshow(prescriber_dist1)\n",
    "ax2.matshow(prescriber_dist2)\n",
    "ax3.matshow(prescriber_dist3)\n",
    "ax4.matshow(prescriber_dist4)\n",
    "plt.rcParams['figure.figsize']=[1, 1]\n",
    "for ax in fig.get_axes():\n",
    "    ax.label_outer()\n",
    "\"\"\" \n",
    "\n",
    "choose = ['braycurtis', 'canberra', 'chebyshev', 'cityblock', 'correlation', 'cosine', 'dice', 'euclidean',\n",
    "         'hamming', 'jaccard', 'jensenshannon', 'kulsinski', 'mahalanobis', 'matching', 'minkowski', 'rogerstanimoto',\n",
    "         'russellrao', 'seuclidean', 'sokalmichener', 'sokalsneath', 'sqeuclidean', 'yule']\n",
    "\n",
    "def compare_dist(choose):\n",
    "    dist = {}\n",
    "    counter = 0\n",
    "    while counter < len(choose):\n",
    "        print(\"type done to finish or type restart to start over\")\n",
    "        userInput = input(\"Distance Metrics: \\nBraycurtis \\tCanberra \\tChebyshev \\nCityblock \\tCorrelation \\tCosine \\nDice \\t\\tEuclidean \\tHamming \\nJaccard \\tJensenshannon \\tKulsinski \\nMalhalanobis \\tMatching \\tMinkowski \\nRogerstanimoto \\tRussellrao \\tSeuclidean \\nSokalmichener \\tSokalsneath \\tSqeuclidean \\nYule\\n\") \n",
    "        userInput = userInput.lower()\n",
    "        if userInput == \"restart\":\n",
    "            dist = {}\n",
    "            counter = 0\n",
    "            print(\"Distance Metrics Cleared\")\n",
    "        elif userInput == \"done\":\n",
    "            return(dist)\n",
    "        elif userInput not in choose:\n",
    "            print(\"Invalid\")\n",
    "        elif userInput in dist:\n",
    "            print(\"Duplicate Distance Metric\")\n",
    "        else:\n",
    "            dist[userInput] = scipy.spatial.distance.squareform(scipy.spatial.distance.pdist(normalized_pf_matrix, userInput))\n",
    "            #plt.matshow(dist[userInput])\n",
    "            print(userInput + \"generated\")\n",
    "            counter += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type done to finish or type restart to start over\n",
      "Distance Metrics: \n",
      "Braycurtis \tCanberra \tChebyshev \n",
      "Cityblock \tCorrelation \tCosine \n",
      "Dice \t\tEuclidean \tHamming \n",
      "Jaccard \tJensenshannon \tKulsinski \n",
      "Malhalanobis \tMatching \tMinkowski \n",
      "Rogerstanimoto \tRussellrao \tSeuclidean \n",
      "Sokalmichener \tSokalsneath \tSqeuclidean \n",
      "Yule\n",
      "Yule\n",
      "yulegenerated\n",
      "type done to finish or type restart to start over\n",
      "Distance Metrics: \n",
      "Braycurtis \tCanberra \tChebyshev \n",
      "Cityblock \tCorrelation \tCosine \n",
      "Dice \t\tEuclidean \tHamming \n",
      "Jaccard \tJensenshannon \tKulsinski \n",
      "Malhalanobis \tMatching \tMinkowski \n",
      "Rogerstanimoto \tRussellrao \tSeuclidean \n",
      "Sokalmichener \tSokalsneath \tSqeuclidean \n",
      "Yule\n",
      "Matching\n",
      "matchinggenerated\n",
      "type done to finish or type restart to start over\n",
      "Distance Metrics: \n",
      "Braycurtis \tCanberra \tChebyshev \n",
      "Cityblock \tCorrelation \tCosine \n",
      "Dice \t\tEuclidean \tHamming \n",
      "Jaccard \tJensenshannon \tKulsinski \n",
      "Malhalanobis \tMatching \tMinkowski \n",
      "Rogerstanimoto \tRussellrao \tSeuclidean \n",
      "Sokalmichener \tSokalsneath \tSqeuclidean \n",
      "Yule\n",
      "Euclidean\n",
      "euclideangenerated\n",
      "type done to finish or type restart to start over\n",
      "Distance Metrics: \n",
      "Braycurtis \tCanberra \tChebyshev \n",
      "Cityblock \tCorrelation \tCosine \n",
      "Dice \t\tEuclidean \tHamming \n",
      "Jaccard \tJensenshannon \tKulsinski \n",
      "Malhalanobis \tMatching \tMinkowski \n",
      "Rogerstanimoto \tRussellrao \tSeuclidean \n",
      "Sokalmichener \tSokalsneath \tSqeuclidean \n",
      "Yule\n",
      "Dice\n",
      "dicegenerated\n",
      "type done to finish or type restart to start over\n",
      "Distance Metrics: \n",
      "Braycurtis \tCanberra \tChebyshev \n",
      "Cityblock \tCorrelation \tCosine \n",
      "Dice \t\tEuclidean \tHamming \n",
      "Jaccard \tJensenshannon \tKulsinski \n",
      "Malhalanobis \tMatching \tMinkowski \n",
      "Rogerstanimoto \tRussellrao \tSeuclidean \n",
      "Sokalmichener \tSokalsneath \tSqeuclidean \n",
      "Yule\n",
      "Jaccard\n",
      "jaccardgenerated\n",
      "type done to finish or type restart to start over\n",
      "Distance Metrics: \n",
      "Braycurtis \tCanberra \tChebyshev \n",
      "Cityblock \tCorrelation \tCosine \n",
      "Dice \t\tEuclidean \tHamming \n",
      "Jaccard \tJensenshannon \tKulsinski \n",
      "Malhalanobis \tMatching \tMinkowski \n",
      "Rogerstanimoto \tRussellrao \tSeuclidean \n",
      "Sokalmichener \tSokalsneath \tSqeuclidean \n",
      "Yule\n",
      "Done\n"
     ]
    }
   ],
   "source": [
    "distanceGraphs = compare_dist(choose)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'float' object cannot be interpreted as an integer",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-62-204b41b549cc>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      9\u001b[0m \u001b[0mrows\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m \u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 11\u001b[1;33m \u001b[0mfig\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxes\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mplt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msubplots\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnumRows\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;36m5\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     12\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mkeys\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdistanceGraphs\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     13\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mcolumns\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;36m5\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\matplotlib\\pyplot.py\u001b[0m in \u001b[0;36msubplots\u001b[1;34m(nrows, ncols, sharex, sharey, squeeze, subplot_kw, gridspec_kw, **fig_kw)\u001b[0m\n\u001b[0;32m   1218\u001b[0m     axs = fig.subplots(nrows=nrows, ncols=ncols, sharex=sharex, sharey=sharey,\n\u001b[0;32m   1219\u001b[0m                        \u001b[0msqueeze\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msqueeze\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msubplot_kw\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msubplot_kw\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1220\u001b[1;33m                        gridspec_kw=gridspec_kw)\n\u001b[0m\u001b[0;32m   1221\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mfig\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxs\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1222\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\matplotlib\\figure.py\u001b[0m in \u001b[0;36msubplots\u001b[1;34m(self, nrows, ncols, sharex, sharey, squeeze, subplot_kw, gridspec_kw)\u001b[0m\n\u001b[0;32m   1520\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1521\u001b[0m         \u001b[1;31m# Create array to hold all axes.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1522\u001b[1;33m         \u001b[0maxarr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mempty\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mncols\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mobject\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1523\u001b[0m         \u001b[1;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1524\u001b[0m             \u001b[1;32mfor\u001b[0m \u001b[0mcol\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mncols\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: 'float' object cannot be interpreted as an integer"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<Figure size 1800x360 with 0 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "if (len(distanceGraphs) % 5 == 0):\n",
    "    numRows = len(distanceGraphs) / 5\n",
    "else:\n",
    "    numRows = len(distanceGraphs) \n",
    "\n",
    "plt.rcParams['figure.figsize'] = [25, 5 * numRows]\n",
    "plt.rcParams['font.size'] = 13\n",
    "\n",
    "rows = 0\n",
    "columns = 0\n",
    "fig, axes = plt.subplots(numRows, 5)\n",
    "for keys in distanceGraphs:\n",
    "    if columns == 5:\n",
    "        columns = 0\n",
    "        rows += 1\n",
    "    elif numRows == 1:\n",
    "        axes[columns].matshow(distanceGraphs[keys])\n",
    "        axes[columns].set_title(keys, fontsize = 20, pad = 20)\n",
    "    else:\n",
    "        axes[rows, columns].matshow(distanceGraphs[keys])\n",
    "        axes[rows, columns].set_title(keys, fontsize = 20, pad = 20)\n",
    "    columns += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_dist[70,:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By plotting the distances we can see that prescribers most like are probably also psychiatrist. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.scatter(np.arange(prescriber_dist.shape[0]),prescriber_dist[70,:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.scatter(np.arange(prescriber_dist.shape[0]),np.sort(prescriber_dist[70,:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_specialty_generic_df.iloc[70,0:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "providers_sorted = np.lexsort((prescriber_dist[:,70].tolist(),))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_specialty_generic_df.iloc[:,0:2].as_matrix()[providers_sorted[0:40],:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.lexsort(((-1 * pf_matrix[70,:]).tolist(),))[0:17]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "generic_array[np.lexsort(((-1 * pf_matrix[70,:]).tolist(),))][0:16]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prescriber_df[prescriber_df[\"npi\"]==1003850462].sort_values(\"total_claim_count\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#prescriber_specialty_drugs_df.to_csv(\"./data/medicare_dc_providers_generic_prescribing_2013.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We take a break from building the algorithm for calculating the euclidean distance between two prescribers. To show how to store the results of your analysis in a format that can be used in other environments. (HDF5)[https://www.hdfgroup.org/HDF5/] is a format for storing scientific data. It can be used by a range of tools include Matlab, Mathematica, R, Python, C and Java. The `h5py` library provides a high level interface for writing and reading HDF5. HDF5 supports compression and reading subsets of really large matrices. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import h5py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f5 = h5py.File(\"./data/medicare_dc_prescriber_matrix_2013.hdf5\", \"w\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "ds_raw_count = f5.create_dataset(\"/generic/raw_counts/core_array/\", shape=pf_matrix.shape, dtype=pf_matrix.dtype)\n",
    "ds_pdist = f5.create_dataset(\"/prescriber/generic/distance/\", shape=prescriber_dist.shape, dtype=prescriber_dist.dtype)\n",
    "ds_normalized = f5.create_dataset(\"/generic/normalized/core_array/\", shape=normalized_pf_matrix.shape, dtype=normalized_pf_matrix.dtype)\n",
    "ds_identifiers = f5.create_dataset(\"/prescriber/identifiers/\", shape=identifiers_matrix.shape, dtype=\"S128\")\n",
    "ds_generic_name = f5.create_dataset(\"/generic/names\", shape=generic_array.shape, dtype=\"S128\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_raw_count[...] = pf_matrix\n",
    "ds_normalized[...] = normalized_pf_matrix\n",
    "ds_pdist[...] = prescriber_dist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_identifiers[...] = np.array(identifiers_matrix.tolist(), dtype=\"S128\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_generic_name[...] = np.array(generic_array.tolist(), dtype=\"S128\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f5.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will show how the process of creating a data frame for "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.array(generic_array.tolist(), dtype=\"S128\")[0:20]"
   ]
  }
 ],
 "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": 1
}