Quest_Analytics_DA_Take-Home.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assignment:\n",
    "\n",
    "## Find all inconsistencies in client_providers.json dataset.\n",
    "\n",
    "(Goal is to understand and verify data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>[md]</td>\n",
       "      <td>[rheumatologist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>[pt, md]</td>\n",
       "      <td>[family-practitioner, physical-therapist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>[pt, md]</td>\n",
       "      <td>[family-practitioner, physical-therapist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>[pt]</td>\n",
       "      <td>[physical-therapist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>230dc410045f63c44f43e05ed799e2f4</td>\n",
       "      <td>1710010830</td>\n",
       "      <td>grisele</td>\n",
       "      <td>pastrana rodriguez</td>\n",
       "      <td>[md]</td>\n",
       "      <td>[family-practitioner]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi      f_name  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "4  230dc410045f63c44f43e05ed799e2f4  1710010830     grisele   \n",
       "\n",
       "               l_name    titles                                specialties  \n",
       "0              harris      [md]                           [rheumatologist]  \n",
       "1                sabo  [pt, md]  [family-practitioner, physical-therapist]  \n",
       "2              sabour  [pt, md]  [family-practitioner, physical-therapist]  \n",
       "3                sabo      [pt]                       [physical-therapist]  \n",
       "4  pastrana rodriguez      [md]                      [family-practitioner]  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Customer provided dataset\n",
    "client_providers = pd.read_json('client_providers.json', lines = True)\n",
    "client_providers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10050, 6)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client_providers.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>date_of_birth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>1979-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1104806892</td>\n",
       "      <td>raymond</td>\n",
       "      <td>pumarejo</td>\n",
       "      <td>1961-01-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1225324825</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>yagoda</td>\n",
       "      <td>1976-12-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1285892877</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>crosby</td>\n",
       "      <td>1978-11-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1710956974</td>\n",
       "      <td>richard</td>\n",
       "      <td>mccurdy</td>\n",
       "      <td>1946-10-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          npi      f_name        l_name date_of_birth\n",
       "0  1356659510  maryangely  moreno campa    1979-12-12\n",
       "1  1104806892     raymond      pumarejo    1961-01-19\n",
       "2  1225324825    nicholas        yagoda    1976-12-24\n",
       "3  1285892877    nicholas        crosby    1978-11-07\n",
       "4  1710956974     richard       mccurdy    1946-10-25"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#American Board of Medical Specialties (ABMS)/External Dataset\n",
    "abms = pd.read_json('abms.json', lines = True)\n",
    "abms.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9945, 4)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abms.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2b44486e322366dd27f1a62acdbae01e</td>\n",
       "      <td>1497936900</td>\n",
       "      <td>andrew</td>\n",
       "      <td>barleben</td>\n",
       "      <td>[md]</td>\n",
       "      <td>[vascular-surgeon]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>f54129a903ec1260e9139cb99b752360</td>\n",
       "      <td>1538484167</td>\n",
       "      <td>howard</td>\n",
       "      <td>bowers</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>e4f18a4d71b81f7c32b0ee1200377554</td>\n",
       "      <td>1851813695</td>\n",
       "      <td>katherine</td>\n",
       "      <td>nataro</td>\n",
       "      <td>[arnp]</td>\n",
       "      <td>[nurse-practitioner]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0f4be2efb6051b60a3e784b52448b0b7</td>\n",
       "      <td>1730277054</td>\n",
       "      <td>kamalinee</td>\n",
       "      <td>deshpande</td>\n",
       "      <td>[md]</td>\n",
       "      <td>[general-practitioner, family-practitioner]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>aaf4aa01c834714643d83e55b8e63cd2</td>\n",
       "      <td>1578759023</td>\n",
       "      <td>diogenes</td>\n",
       "      <td>duarte</td>\n",
       "      <td>[md]</td>\n",
       "      <td>[sleep-medicine-doctor, pulmonologist]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi     f_name     l_name  titles  \\\n",
       "0  2b44486e322366dd27f1a62acdbae01e  1497936900     andrew   barleben    [md]   \n",
       "1  f54129a903ec1260e9139cb99b752360  1538484167     howard     bowers     NaN   \n",
       "2  e4f18a4d71b81f7c32b0ee1200377554  1851813695  katherine     nataro  [arnp]   \n",
       "3  0f4be2efb6051b60a3e784b52448b0b7  1730277054  kamalinee  deshpande    [md]   \n",
       "4  aaf4aa01c834714643d83e55b8e63cd2  1578759023   diogenes     duarte    [md]   \n",
       "\n",
       "                                   specialties  \n",
       "0                           [vascular-surgeon]  \n",
       "1                                          NaN  \n",
       "2                         [nurse-practitioner]  \n",
       "3  [general-practitioner, family-practitioner]  \n",
       "4       [sleep-medicine-doctor, pulmonologist]  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Attestation Campaign/Internal Data (after calling MD's directly to confirm/update data)\n",
    "attested_providers = pd.read_json('attested_providers.json', lines = True)\n",
    "attested_providers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9482, 6)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Checking for NaN's"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "druid           0\n",
       "npi             0\n",
       "f_name          0\n",
       "l_name          0\n",
       "titles         34\n",
       "specialties     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client_providers.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "npi              0\n",
       "f_name           0\n",
       "l_name           0\n",
       "date_of_birth    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abms.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "druid             0\n",
       "npi               0\n",
       "f_name            0\n",
       "l_name            0\n",
       "titles         1353\n",
       "specialties    1241\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The null's from client_providers and attested_providers will be dropped."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Checking for duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abms.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There needs to be a little more work for client_providers and attested_providers since they have lists, which are not accepted by duplicated()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>[md]</td>\n",
       "      <td>[rheumatologist]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi f_name  l_name titles  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886    max  harris   [md]   \n",
       "\n",
       "        specialties  \n",
       "0  [rheumatologist]  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client_providers.loc[client_providers['npi'] == 1053349886]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#As a reminder, what we are handling\n",
    "client_providers['titles'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70      [md]\n",
       "71       NaN\n",
       "72    [lmft]\n",
       "73      [md]\n",
       "Name: titles, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#zooming in:\n",
    "client_providers['titles'][70:74]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Decision made to dropna since 34/10050 rows is only .3% of the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "client_providers = client_providers.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70      [md]\n",
       "72    [lmft]\n",
       "73      [md]\n",
       "74      [md]\n",
       "Name: titles, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Verified that NaN's were dropped.\n",
    "client_providers['titles'][70:74]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now, onto changing lists into string to check df's for duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "client_providers['titles'] = client_providers['titles'].apply(', '.join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>[rheumatologist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>[family-practitioner, physical-therapist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>[family-practitioner, physical-therapist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>[physical-therapist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>230dc410045f63c44f43e05ed799e2f4</td>\n",
       "      <td>1710010830</td>\n",
       "      <td>grisele</td>\n",
       "      <td>pastrana rodriguez</td>\n",
       "      <td>md</td>\n",
       "      <td>[family-practitioner]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi      f_name  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "4  230dc410045f63c44f43e05ed799e2f4  1710010830     grisele   \n",
       "\n",
       "               l_name  titles                                specialties  \n",
       "0              harris      md                           [rheumatologist]  \n",
       "1                sabo  pt, md  [family-practitioner, physical-therapist]  \n",
       "2              sabour  pt, md  [family-practitioner, physical-therapist]  \n",
       "3                sabo      pt                       [physical-therapist]  \n",
       "4  pastrana rodriguez      md                      [family-practitioner]  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client_providers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "client_providers['specialties'] = client_providers['specialties'].apply(', '.join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>230dc410045f63c44f43e05ed799e2f4</td>\n",
       "      <td>1710010830</td>\n",
       "      <td>grisele</td>\n",
       "      <td>pastrana rodriguez</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi      f_name  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "4  230dc410045f63c44f43e05ed799e2f4  1710010830     grisele   \n",
       "\n",
       "               l_name  titles                              specialties  \n",
       "0              harris      md                           rheumatologist  \n",
       "1                sabo  pt, md  family-practitioner, physical-therapist  \n",
       "2              sabour  pt, md  family-practitioner, physical-therapist  \n",
       "3                sabo      pt                       physical-therapist  \n",
       "4  pastrana rodriguez      md                      family-practitioner  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client_providers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Keep in mind, this output is after 3% of the data is taken out. So within the 3% there could have been duplicates as well.\n",
    "#However, due to the limitation of duplicated() function, had to eliminate NaN's before determining duplicates in (remaining) dataset. \n",
    "client_providers.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, on to the last table attested_providers. There is a greater percentage of nulls. As a reminder, there are 1353 nulls (14% of dataset) in titles column and 1241 (13% of dataset) nulls in specialties column.\n",
    "\n",
    "This is a huge number compared to 3% of dataset that was eliminated from client_providers. However, at least for 86% of the data, we can see how many duplicates there are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1353, 1241)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#As a reminder, what we are handling\n",
    "attested_providers['titles'].isnull().sum(), attested_providers['specialties'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      [md]\n",
       "1       NaN\n",
       "2    [arnp]\n",
       "3      [md]\n",
       "4      [md]\n",
       "5      [md]\n",
       "6      [md]\n",
       "7      [md]\n",
       "8       NaN\n",
       "9      [md]\n",
       "Name: titles, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers['titles'][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                             [vascular-surgeon]\n",
       "1                                            NaN\n",
       "2                           [nurse-practitioner]\n",
       "3    [general-practitioner, family-practitioner]\n",
       "4         [sleep-medicine-doctor, pulmonologist]\n",
       "5    [interventional-cardiologist, cardiologist]\n",
       "6                    [infectious-disease-doctor]\n",
       "7                          [family-practitioner]\n",
       "8                                            NaN\n",
       "9                                    [internist]\n",
       "Name: specialties, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers['specialties'][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "attested_providers = attested_providers.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         [md]\n",
       "2       [arnp]\n",
       "3         [md]\n",
       "4         [md]\n",
       "5         [md]\n",
       "6         [md]\n",
       "7         [md]\n",
       "9         [md]\n",
       "10        [md]\n",
       "11    [md, od]\n",
       "Name: titles, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check to see if NaN's dropped from titles column\n",
    "attested_providers['titles'][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                              [vascular-surgeon]\n",
       "2                            [nurse-practitioner]\n",
       "3     [general-practitioner, family-practitioner]\n",
       "4          [sleep-medicine-doctor, pulmonologist]\n",
       "5     [interventional-cardiologist, cardiologist]\n",
       "6                     [infectious-disease-doctor]\n",
       "7                           [family-practitioner]\n",
       "9                                     [internist]\n",
       "10                                 [psychiatrist]\n",
       "11                              [ophthalmologist]\n",
       "Name: specialties, dtype: object"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check to see if NaN's dropped from specialties column\n",
    "attested_providers['specialties'][:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes! In the clear =)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "attested_providers['titles'] = attested_providers['titles'].apply(', '.join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2b44486e322366dd27f1a62acdbae01e</td>\n",
       "      <td>1497936900</td>\n",
       "      <td>andrew</td>\n",
       "      <td>barleben</td>\n",
       "      <td>md</td>\n",
       "      <td>[vascular-surgeon]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>e4f18a4d71b81f7c32b0ee1200377554</td>\n",
       "      <td>1851813695</td>\n",
       "      <td>katherine</td>\n",
       "      <td>nataro</td>\n",
       "      <td>arnp</td>\n",
       "      <td>[nurse-practitioner]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0f4be2efb6051b60a3e784b52448b0b7</td>\n",
       "      <td>1730277054</td>\n",
       "      <td>kamalinee</td>\n",
       "      <td>deshpande</td>\n",
       "      <td>md</td>\n",
       "      <td>[general-practitioner, family-practitioner]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>aaf4aa01c834714643d83e55b8e63cd2</td>\n",
       "      <td>1578759023</td>\n",
       "      <td>diogenes</td>\n",
       "      <td>duarte</td>\n",
       "      <td>md</td>\n",
       "      <td>[sleep-medicine-doctor, pulmonologist]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>699760b1ba7cd7db7f27d0e2c70e1e62</td>\n",
       "      <td>1316992175</td>\n",
       "      <td>thiruvallur</td>\n",
       "      <td>vallabhan</td>\n",
       "      <td>md</td>\n",
       "      <td>[interventional-cardiologist, cardiologist]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi       f_name     l_name  \\\n",
       "0  2b44486e322366dd27f1a62acdbae01e  1497936900       andrew   barleben   \n",
       "2  e4f18a4d71b81f7c32b0ee1200377554  1851813695    katherine     nataro   \n",
       "3  0f4be2efb6051b60a3e784b52448b0b7  1730277054    kamalinee  deshpande   \n",
       "4  aaf4aa01c834714643d83e55b8e63cd2  1578759023     diogenes     duarte   \n",
       "5  699760b1ba7cd7db7f27d0e2c70e1e62  1316992175  thiruvallur  vallabhan   \n",
       "\n",
       "  titles                                  specialties  \n",
       "0     md                           [vascular-surgeon]  \n",
       "2   arnp                         [nurse-practitioner]  \n",
       "3     md  [general-practitioner, family-practitioner]  \n",
       "4     md       [sleep-medicine-doctor, pulmonologist]  \n",
       "5     md  [interventional-cardiologist, cardiologist]  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "attested_providers['specialties'] = attested_providers['specialties'].apply(', '.join)"
   ]
  },
  {
   "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></th>\n",
       "      <th>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2b44486e322366dd27f1a62acdbae01e</td>\n",
       "      <td>1497936900</td>\n",
       "      <td>andrew</td>\n",
       "      <td>barleben</td>\n",
       "      <td>md</td>\n",
       "      <td>vascular-surgeon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>e4f18a4d71b81f7c32b0ee1200377554</td>\n",
       "      <td>1851813695</td>\n",
       "      <td>katherine</td>\n",
       "      <td>nataro</td>\n",
       "      <td>arnp</td>\n",
       "      <td>nurse-practitioner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0f4be2efb6051b60a3e784b52448b0b7</td>\n",
       "      <td>1730277054</td>\n",
       "      <td>kamalinee</td>\n",
       "      <td>deshpande</td>\n",
       "      <td>md</td>\n",
       "      <td>general-practitioner, family-practitioner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>aaf4aa01c834714643d83e55b8e63cd2</td>\n",
       "      <td>1578759023</td>\n",
       "      <td>diogenes</td>\n",
       "      <td>duarte</td>\n",
       "      <td>md</td>\n",
       "      <td>sleep-medicine-doctor, pulmonologist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>699760b1ba7cd7db7f27d0e2c70e1e62</td>\n",
       "      <td>1316992175</td>\n",
       "      <td>thiruvallur</td>\n",
       "      <td>vallabhan</td>\n",
       "      <td>md</td>\n",
       "      <td>interventional-cardiologist, cardiologist</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi       f_name     l_name  \\\n",
       "0  2b44486e322366dd27f1a62acdbae01e  1497936900       andrew   barleben   \n",
       "2  e4f18a4d71b81f7c32b0ee1200377554  1851813695    katherine     nataro   \n",
       "3  0f4be2efb6051b60a3e784b52448b0b7  1730277054    kamalinee  deshpande   \n",
       "4  aaf4aa01c834714643d83e55b8e63cd2  1578759023     diogenes     duarte   \n",
       "5  699760b1ba7cd7db7f27d0e2c70e1e62  1316992175  thiruvallur  vallabhan   \n",
       "\n",
       "  titles                                specialties  \n",
       "0     md                           vascular-surgeon  \n",
       "2   arnp                         nurse-practitioner  \n",
       "3     md  general-practitioner, family-practitioner  \n",
       "4     md       sleep-medicine-doctor, pulmonologist  \n",
       "5     md  interventional-cardiologist, cardiologist  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Need to keep in mind that this is the case after 14% of the data was dropped. Still could have duplicates in the 14%. However, due to limitation of duplicates() function with NaN's, only dataset without null's could be examined for duplicates. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# To clarify, not working with full data (NaN's not included) to see if abms or attested_providers does provide validation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More EDA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testing to see how MD info between diff DF's look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1602</td>\n",
       "      <td>f14621d86f2c6960232f4aee3033191a</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>md</td>\n",
       "      <td>internist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1603</td>\n",
       "      <td>f14621d86f2c6960232f4aee3033191a</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>marangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>md</td>\n",
       "      <td>internist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1604</td>\n",
       "      <td>f14621d86f2c6960232f4aee3033191a</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>md</td>\n",
       "      <td>internist, oncologist</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 druid         npi      f_name        l_name  \\\n",
       "1602  f14621d86f2c6960232f4aee3033191a  1356659510  maryangely  moreno campa   \n",
       "1603  f14621d86f2c6960232f4aee3033191a  1356659510   marangely  moreno campa   \n",
       "1604  f14621d86f2c6960232f4aee3033191a  1356659510  maryangely  moreno campa   \n",
       "\n",
       "     titles            specialties  \n",
       "1602     md              internist  \n",
       "1603     md              internist  \n",
       "1604     md  internist, oncologist  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client_providers.loc[client_providers['npi'] == 1356659510]"
   ]
  },
  {
   "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>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>date_of_birth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>1979-12-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          npi      f_name        l_name date_of_birth\n",
       "0  1356659510  maryangely  moreno campa    1979-12-12"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abms.loc[abms['npi'] == 1356659510]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [druid, npi, f_name, l_name, titles, specialties]\n",
       "Index: []"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers.loc[attested_providers['npi'] == 1356659510]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Search for maryangely by druid if npi not present just to make sure this MD is not listed (druid assigned if npi not assigned)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name</th>\n",
       "      <th>l_name</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [druid, npi, f_name, l_name, titles, specialties]\n",
       "Index: []"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attested_providers.loc[attested_providers['druid'] == 'f14621d86f2c6960232f4aee3033191a']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see from this initial comparison that MD maryangely moreno campa is listed 3x in client_providers, 1x in abms and 0 times in attested_providers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With this small sample comparison can already see that there is data integrity issues: maryangely is not spelled like others ( line 1603 in client_providers df)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reminder that the goal is to find **inconsistencies**, which come in the following forms:\n",
    "    a. duplicates (same MD per row though recorded in multiple forms due to misspellings/various titles, specialities)\n",
    "    b. incomplete (NaN's in client_providers and attested_providers)\n",
    "    c. more current information (abms and attested_providers has more current info than client_providrs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Start with client_providers compared to abms."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>1952-02-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>230dc410045f63c44f43e05ed799e2f4</td>\n",
       "      <td>1710010830</td>\n",
       "      <td>grisele</td>\n",
       "      <td>pastrana rodriguez</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "      <td>griselle</td>\n",
       "      <td>pastrana</td>\n",
       "      <td>1969-04-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi    f_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "4  230dc410045f63c44f43e05ed799e2f4  1710010830     grisele   \n",
       "\n",
       "             l_name_x  titles                              specialties  \\\n",
       "0              harris      md                           rheumatologist   \n",
       "1                sabo  pt, md  family-practitioner, physical-therapist   \n",
       "2              sabour  pt, md  family-practitioner, physical-therapist   \n",
       "3                sabo      pt                       physical-therapist   \n",
       "4  pastrana rodriguez      md                      family-practitioner   \n",
       "\n",
       "     f_name_y  l_name_y date_of_birth  \n",
       "0         max    harris    1952-02-10  \n",
       "1  abdelmajid    sabour    1964-05-04  \n",
       "2  abdelmajid    sabour    1964-05-04  \n",
       "3  abdelmajid    sabour    1964-05-04  \n",
       "4    griselle  pastrana    1969-04-30  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner = pd.merge(client_providers, abms, on='npi', how='inner')\n",
    "c_a_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6863, 9)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only **6863** rows in common between client_providers and abms. This means that there will be this many rows from client_providers to check against abms. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us take a look at how MD maryangely info changed after this table transformation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1122</td>\n",
       "      <td>f14621d86f2c6960232f4aee3033191a</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>md</td>\n",
       "      <td>internist</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>1979-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1123</td>\n",
       "      <td>f14621d86f2c6960232f4aee3033191a</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>marangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>md</td>\n",
       "      <td>internist</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>1979-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1124</td>\n",
       "      <td>f14621d86f2c6960232f4aee3033191a</td>\n",
       "      <td>1356659510</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>md</td>\n",
       "      <td>internist, oncologist</td>\n",
       "      <td>maryangely</td>\n",
       "      <td>moreno campa</td>\n",
       "      <td>1979-12-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 druid         npi    f_name_x      l_name_x  \\\n",
       "1122  f14621d86f2c6960232f4aee3033191a  1356659510  maryangely  moreno campa   \n",
       "1123  f14621d86f2c6960232f4aee3033191a  1356659510   marangely  moreno campa   \n",
       "1124  f14621d86f2c6960232f4aee3033191a  1356659510  maryangely  moreno campa   \n",
       "\n",
       "     titles            specialties    f_name_y      l_name_y date_of_birth  \n",
       "1122     md              internist  maryangely  moreno campa    1979-12-12  \n",
       "1123     md              internist  maryangely  moreno campa    1979-12-12  \n",
       "1124     md  internist, oncologist  maryangely  moreno campa    1979-12-12  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.loc[c_a_inner['npi'] == 1356659510]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we see that the same MD maryangely has 3 separate entries due to mispelled first name and inconsistent specialty entry (internist and internist/oncologist). However, given that abms is a reference table, lines 1122 and 1124 are the most current. However, that is as far as the verification with abms goes since it does not provide titles/specialties data. (This is where attested_providers table will come in.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check a few more to get a glimpse at how pervasive this issue may be."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>6367</td>\n",
       "      <td>96fb3432ddc5408ec68a627b258cc871</td>\n",
       "      <td>1104806892</td>\n",
       "      <td>raymond</td>\n",
       "      <td>pumarejo</td>\n",
       "      <td>md</td>\n",
       "      <td>infectious-disease-doctor, pulmonologist</td>\n",
       "      <td>raymond</td>\n",
       "      <td>pumarejo</td>\n",
       "      <td>1961-01-19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 druid         npi f_name_x  l_name_x titles  \\\n",
       "6367  96fb3432ddc5408ec68a627b258cc871  1104806892  raymond  pumarejo     md   \n",
       "\n",
       "                                   specialties f_name_y  l_name_y  \\\n",
       "6367  infectious-disease-doctor, pulmonologist  raymond  pumarejo   \n",
       "\n",
       "     date_of_birth  \n",
       "6367    1961-01-19  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.loc[c_a_inner['npi'] == 1104806892]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is no problem here. Let us try one more."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2003</td>\n",
       "      <td>c1a0d0ef0d46394d8a5a2260fb7cebf0</td>\n",
       "      <td>1225324825</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>yagoda</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>yagoda</td>\n",
       "      <td>1976-12-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2004</td>\n",
       "      <td>c1a0d0ef0d46394d8a5a2260fb7cebf0</td>\n",
       "      <td>1225324825</td>\n",
       "      <td>padma</td>\n",
       "      <td>reddy</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>yagoda</td>\n",
       "      <td>1976-12-24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 druid         npi  f_name_x l_name_x titles  \\\n",
       "2003  c1a0d0ef0d46394d8a5a2260fb7cebf0  1225324825  nicholas   yagoda     md   \n",
       "2004  c1a0d0ef0d46394d8a5a2260fb7cebf0  1225324825     padma    reddy     md   \n",
       "\n",
       "              specialties  f_name_y l_name_y date_of_birth  \n",
       "2003  family-practitioner  nicholas   yagoda    1976-12-24  \n",
       "2004  family-practitioner  nicholas   yagoda    1976-12-24  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.loc[c_a_inner['npi'] == 1225324825] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interesting. The same npi is assigned to 2 different MD's unless nicholas yagoda has an alias."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get a peak at how pervasive the problem is will do a groupby npi to see how many multiple entries there are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>druid</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1427223114</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1629331772</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1942200407</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1568758027</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1285901140</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1497749022</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1033436704</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1801834171</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1407965668</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1558300269</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            druid  f_name_x  l_name_x  titles  specialties  f_name_y  \\\n",
       "npi                                                                    \n",
       "1427223114      5         5         5       5            5         5   \n",
       "1629331772      4         4         4       4            4         4   \n",
       "1942200407      4         4         4       4            4         4   \n",
       "1568758027      4         4         4       4            4         4   \n",
       "1285901140      4         4         4       4            4         4   \n",
       "1497749022      4         4         4       4            4         4   \n",
       "1033436704      4         4         4       4            4         4   \n",
       "1801834171      4         4         4       4            4         4   \n",
       "1407965668      4         4         4       4            4         4   \n",
       "1558300269      4         4         4       4            4         4   \n",
       "\n",
       "            l_name_y  date_of_birth  \n",
       "npi                                  \n",
       "1427223114         5              5  \n",
       "1629331772         4              4  \n",
       "1942200407         4              4  \n",
       "1568758027         4              4  \n",
       "1285901140         4              4  \n",
       "1497749022         4              4  \n",
       "1033436704         4              4  \n",
       "1801834171         4              4  \n",
       "1407965668         4              4  \n",
       "1558300269         4              4  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups1_count = c_a_inner.groupby(['npi']).count()\n",
    "groups1_count.sort_values(['f_name_x'],ascending = False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>druid</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1366531329</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366528838</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366517880</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366496226</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366494155</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366493439</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366491821</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366486359</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366483356</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1992979397</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            druid  f_name_x  l_name_x  titles  specialties  f_name_y  \\\n",
       "npi                                                                    \n",
       "1366531329      1         1         1       1            1         1   \n",
       "1366528838      1         1         1       1            1         1   \n",
       "1366517880      1         1         1       1            1         1   \n",
       "1366496226      1         1         1       1            1         1   \n",
       "1366494155      1         1         1       1            1         1   \n",
       "1366493439      1         1         1       1            1         1   \n",
       "1366491821      1         1         1       1            1         1   \n",
       "1366486359      1         1         1       1            1         1   \n",
       "1366483356      1         1         1       1            1         1   \n",
       "1992979397      1         1         1       1            1         1   \n",
       "\n",
       "            l_name_y  date_of_birth  \n",
       "npi                                  \n",
       "1366531329         1              1  \n",
       "1366528838         1              1  \n",
       "1366517880         1              1  \n",
       "1366496226         1              1  \n",
       "1366494155         1              1  \n",
       "1366493439         1              1  \n",
       "1366491821         1              1  \n",
       "1366486359         1              1  \n",
       "1366483356         1              1  \n",
       "1992979397         1              1  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups1_count.sort_values(['f_name_x'],ascending = False).tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we see that the most duplicated record is the MD with npi = 1427223114 while there are numerous repeats that are in the 4 range and less. At the same time, there are records that are not repeated such as evident with tail output right above."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Putting numbers to inconsistencies.   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For this join, need to check if client_providers' first_name and last_name are the same in abms. \n",
    "\n",
    "### There can be two different outcomes:\n",
    "\n",
    "    a. match/equal (T)\n",
    "    b. more current/updated info in abms table (F)\n",
    "\n",
    "Percentage of T and F will be determined. Goal is to have more T than F occurrences. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_a_inner['f_names_eq'] = c_a_inner['f_name_x'] == c_a_inner['f_name_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>f_names_eq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>1952-02-10</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>230dc410045f63c44f43e05ed799e2f4</td>\n",
       "      <td>1710010830</td>\n",
       "      <td>grisele</td>\n",
       "      <td>pastrana rodriguez</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "      <td>griselle</td>\n",
       "      <td>pastrana</td>\n",
       "      <td>1969-04-30</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi    f_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "4  230dc410045f63c44f43e05ed799e2f4  1710010830     grisele   \n",
       "\n",
       "             l_name_x  titles                              specialties  \\\n",
       "0              harris      md                           rheumatologist   \n",
       "1                sabo  pt, md  family-practitioner, physical-therapist   \n",
       "2              sabour  pt, md  family-practitioner, physical-therapist   \n",
       "3                sabo      pt                       physical-therapist   \n",
       "4  pastrana rodriguez      md                      family-practitioner   \n",
       "\n",
       "     f_name_y  l_name_y date_of_birth  f_names_eq  \n",
       "0         max    harris    1952-02-10        True  \n",
       "1  abdelmajid    sabour    1964-05-04       False  \n",
       "2  abdelmajid    sabour    1964-05-04        True  \n",
       "3  abdelmajid    sabour    1964-05-04       False  \n",
       "4    griselle  pastrana    1969-04-30       False  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     5701\n",
       "False    1162\n",
       "Name: f_names_eq, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.f_names_eq.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of f_name_eq True and Percentage of False:\n",
    "**83%** for True and 17% for False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_a_inner['l_names_eq'] = c_a_inner['l_name_x'] == c_a_inner['l_name_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>druid</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles</th>\n",
       "      <th>specialties</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>f_names_eq</th>\n",
       "      <th>l_names_eq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>1952-02-10</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>1964-05-04</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>230dc410045f63c44f43e05ed799e2f4</td>\n",
       "      <td>1710010830</td>\n",
       "      <td>grisele</td>\n",
       "      <td>pastrana rodriguez</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "      <td>griselle</td>\n",
       "      <td>pastrana</td>\n",
       "      <td>1969-04-30</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              druid         npi    f_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista   \n",
       "4  230dc410045f63c44f43e05ed799e2f4  1710010830     grisele   \n",
       "\n",
       "             l_name_x  titles                              specialties  \\\n",
       "0              harris      md                           rheumatologist   \n",
       "1                sabo  pt, md  family-practitioner, physical-therapist   \n",
       "2              sabour  pt, md  family-practitioner, physical-therapist   \n",
       "3                sabo      pt                       physical-therapist   \n",
       "4  pastrana rodriguez      md                      family-practitioner   \n",
       "\n",
       "     f_name_y  l_name_y date_of_birth  f_names_eq  l_names_eq  \n",
       "0         max    harris    1952-02-10        True        True  \n",
       "1  abdelmajid    sabour    1964-05-04       False       False  \n",
       "2  abdelmajid    sabour    1964-05-04        True        True  \n",
       "3  abdelmajid    sabour    1964-05-04       False       False  \n",
       "4    griselle  pastrana    1969-04-30       False       False  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     5668\n",
       "False    1195\n",
       "Name: l_names_eq, dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_a_inner.l_names_eq.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of l_name_eq True and Percentage of False:\n",
    "**83%** for True and 17% for False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Let us now compare client_providers to attested_providers table and see if there are any better results given that this table has exact columns to client_providers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>1790784189</td>\n",
       "      <td>joan</td>\n",
       "      <td>fambrough</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>joan</td>\n",
       "      <td>lee</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist, certif...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            druid_x         npi    f_name_x   l_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max     harris   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid     sabour   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b  1790784189        joan  fambrough   \n",
       "\n",
       "  titles_x                            specialties_x  \\\n",
       "0       md                           rheumatologist   \n",
       "1   pt, md  family-practitioner, physical-therapist   \n",
       "2   pt, md  family-practitioner, physical-therapist   \n",
       "3       pt                       physical-therapist   \n",
       "4     crna   certified-registered-nurse-anesthetist   \n",
       "\n",
       "                            druid_y f_name_y l_name_y titles_y  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa      max   harris       md   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b     joan      lee     crna   \n",
       "\n",
       "                                       specialties_y  \n",
       "0                                     rheumatologist  \n",
       "1                                 physical-therapist  \n",
       "2                                 physical-therapist  \n",
       "3                                 physical-therapist  \n",
       "4  certified-registered-nurse-anesthetist, certif...  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner = pd.merge(client_providers, attested_providers, on='npi', how='inner')\n",
    "c_att_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6709, 11)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6709** rows in common between client_providers and attested_providers. This is less than result from client_providers joined to abms."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see how the same npi's turn out in this new combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [druid_x, npi, f_name_x, l_name_x, titles_x, specialties_x, druid_y, f_name_y, l_name_y, titles_y, specialties_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.loc[c_att_inner['npi'] == 1356659510]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This empty result means that this npi was not attested. Let us try the other two npi's. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>6198</td>\n",
       "      <td>96fb3432ddc5408ec68a627b258cc871</td>\n",
       "      <td>1104806892</td>\n",
       "      <td>raymond</td>\n",
       "      <td>pumarejo</td>\n",
       "      <td>md</td>\n",
       "      <td>infectious-disease-doctor, pulmonologist</td>\n",
       "      <td>96fb3432ddc5408ec68a627b258cc871</td>\n",
       "      <td>raymond</td>\n",
       "      <td>pumarejo</td>\n",
       "      <td>md</td>\n",
       "      <td>infectious-disease-doctor, critical-care-docto...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               druid_x         npi f_name_x  l_name_x  \\\n",
       "6198  96fb3432ddc5408ec68a627b258cc871  1104806892  raymond  pumarejo   \n",
       "\n",
       "     titles_x                             specialties_x  \\\n",
       "6198       md  infectious-disease-doctor, pulmonologist   \n",
       "\n",
       "                               druid_y f_name_y  l_name_y titles_y  \\\n",
       "6198  96fb3432ddc5408ec68a627b258cc871  raymond  pumarejo       md   \n",
       "\n",
       "                                          specialties_y  \n",
       "6198  infectious-disease-doctor, critical-care-docto...  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.loc[c_att_inner['npi'] == 1104806892]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yay! This npi existed after join between client_providers and attested_providers. Here, we can see once again close, but not exact specialties description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1956</td>\n",
       "      <td>c1a0d0ef0d46394d8a5a2260fb7cebf0</td>\n",
       "      <td>1225324825</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>yagoda</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "      <td>c1a0d0ef0d46394d8a5a2260fb7cebf0</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>yagoda</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1957</td>\n",
       "      <td>c1a0d0ef0d46394d8a5a2260fb7cebf0</td>\n",
       "      <td>1225324825</td>\n",
       "      <td>padma</td>\n",
       "      <td>reddy</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "      <td>c1a0d0ef0d46394d8a5a2260fb7cebf0</td>\n",
       "      <td>nicholas</td>\n",
       "      <td>yagoda</td>\n",
       "      <td>md</td>\n",
       "      <td>family-practitioner</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               druid_x         npi  f_name_x l_name_x  \\\n",
       "1956  c1a0d0ef0d46394d8a5a2260fb7cebf0  1225324825  nicholas   yagoda   \n",
       "1957  c1a0d0ef0d46394d8a5a2260fb7cebf0  1225324825     padma    reddy   \n",
       "\n",
       "     titles_x        specialties_x                           druid_y  \\\n",
       "1956       md  family-practitioner  c1a0d0ef0d46394d8a5a2260fb7cebf0   \n",
       "1957       md  family-practitioner  c1a0d0ef0d46394d8a5a2260fb7cebf0   \n",
       "\n",
       "      f_name_y l_name_y titles_y        specialties_y  \n",
       "1956  nicholas   yagoda       md  family-practitioner  \n",
       "1957  nicholas   yagoda       md  family-practitioner  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.loc[c_att_inner['npi'] == 1225324825] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like the first comparison, two different names show up for this npi search. Though everything else is consistent (titles and specialties), which is good."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get a peak at how pervasive the problem is will do a groupby npi to see how many multiple entries there are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>druid_x</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1558300269</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1407965668</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1619155546</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1497749022</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1629004957</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1285901140</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1033436704</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1467594184</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1629331772</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1952565301</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            druid_x  f_name_x  l_name_x  titles_x  specialties_x  druid_y  \\\n",
       "npi                                                                         \n",
       "1558300269        4         4         4         4              4        4   \n",
       "1407965668        4         4         4         4              4        4   \n",
       "1619155546        4         4         4         4              4        4   \n",
       "1497749022        4         4         4         4              4        4   \n",
       "1629004957        4         4         4         4              4        4   \n",
       "1285901140        4         4         4         4              4        4   \n",
       "1033436704        4         4         4         4              4        4   \n",
       "1467594184        4         4         4         4              4        4   \n",
       "1629331772        4         4         4         4              4        4   \n",
       "1952565301        4         4         4         4              4        4   \n",
       "\n",
       "            f_name_y  l_name_y  titles_y  specialties_y  \n",
       "npi                                                      \n",
       "1558300269         4         4         4              4  \n",
       "1407965668         4         4         4              4  \n",
       "1619155546         4         4         4              4  \n",
       "1497749022         4         4         4              4  \n",
       "1629004957         4         4         4              4  \n",
       "1285901140         4         4         4              4  \n",
       "1033436704         4         4         4              4  \n",
       "1467594184         4         4         4              4  \n",
       "1629331772         4         4         4              4  \n",
       "1952565301         4         4         4              4  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups2_count = c_att_inner.groupby(['npi']).count()\n",
    "groups2_count.sort_values(['f_name_x'],ascending = False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>druid_x</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1366760027</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366580631</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366717605</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366717159</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366659260</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366622466</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366605495</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366593576</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1366584674</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1992979397</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            druid_x  f_name_x  l_name_x  titles_x  specialties_x  druid_y  \\\n",
       "npi                                                                         \n",
       "1366760027        1         1         1         1              1        1   \n",
       "1366580631        1         1         1         1              1        1   \n",
       "1366717605        1         1         1         1              1        1   \n",
       "1366717159        1         1         1         1              1        1   \n",
       "1366659260        1         1         1         1              1        1   \n",
       "1366622466        1         1         1         1              1        1   \n",
       "1366605495        1         1         1         1              1        1   \n",
       "1366593576        1         1         1         1              1        1   \n",
       "1366584674        1         1         1         1              1        1   \n",
       "1992979397        1         1         1         1              1        1   \n",
       "\n",
       "            f_name_y  l_name_y  titles_y  specialties_y  \n",
       "npi                                                      \n",
       "1366760027         1         1         1              1  \n",
       "1366580631         1         1         1              1  \n",
       "1366717605         1         1         1              1  \n",
       "1366717159         1         1         1              1  \n",
       "1366659260         1         1         1              1  \n",
       "1366622466         1         1         1              1  \n",
       "1366605495         1         1         1              1  \n",
       "1366593576         1         1         1              1  \n",
       "1366584674         1         1         1              1  \n",
       "1992979397         1         1         1              1  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups2_count.sort_values(['f_name_x'],ascending = False).tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we see that many npi records are in the 4 range and less. At the same time, there are records that are not repeated such as evident with tail output right above."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Putting numbers to inconsistencies again.   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For this join, need to check if client_providers' first_name, last_name, titles and specialties are the same in attested_providers. \n",
    "\n",
    "There can be two different outcomes:\n",
    "\n",
    "    a. match/equal (T)\n",
    "    b. more current/updated info in attested_providers table (F)\n",
    "\n",
    "Percentage of T and F will be determined. Goal is to have more T than F occurrences."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_att_inner['f_names_eq'] = c_att_inner['f_name_x'] == c_att_inner['f_name_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "      <th>f_names_eq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>1790784189</td>\n",
       "      <td>joan</td>\n",
       "      <td>fambrough</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>joan</td>\n",
       "      <td>lee</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist, certif...</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            druid_x         npi    f_name_x   l_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max     harris   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid     sabour   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b  1790784189        joan  fambrough   \n",
       "\n",
       "  titles_x                            specialties_x  \\\n",
       "0       md                           rheumatologist   \n",
       "1   pt, md  family-practitioner, physical-therapist   \n",
       "2   pt, md  family-practitioner, physical-therapist   \n",
       "3       pt                       physical-therapist   \n",
       "4     crna   certified-registered-nurse-anesthetist   \n",
       "\n",
       "                            druid_y f_name_y l_name_y titles_y  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa      max   harris       md   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b     joan      lee     crna   \n",
       "\n",
       "                                       specialties_y  f_names_eq  \n",
       "0                                     rheumatologist        True  \n",
       "1                                 physical-therapist        True  \n",
       "2                                 physical-therapist       False  \n",
       "3                                 physical-therapist        True  \n",
       "4  certified-registered-nurse-anesthetist, certif...        True  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     6249\n",
       "False     460\n",
       "Name: f_names_eq, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.f_names_eq.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of f_name_eq True and Percentage of False:\n",
    "**93%** for True and 7% for False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_att_inner['l_names_eq'] = c_att_inner['l_name_x'] == c_att_inner['l_name_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "      <th>f_names_eq</th>\n",
       "      <th>l_names_eq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>1790784189</td>\n",
       "      <td>joan</td>\n",
       "      <td>fambrough</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>joan</td>\n",
       "      <td>lee</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist, certif...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            druid_x         npi    f_name_x   l_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max     harris   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid     sabour   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b  1790784189        joan  fambrough   \n",
       "\n",
       "  titles_x                            specialties_x  \\\n",
       "0       md                           rheumatologist   \n",
       "1   pt, md  family-practitioner, physical-therapist   \n",
       "2   pt, md  family-practitioner, physical-therapist   \n",
       "3       pt                       physical-therapist   \n",
       "4     crna   certified-registered-nurse-anesthetist   \n",
       "\n",
       "                            druid_y f_name_y l_name_y titles_y  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa      max   harris       md   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b     joan      lee     crna   \n",
       "\n",
       "                                       specialties_y  f_names_eq  l_names_eq  \n",
       "0                                     rheumatologist        True        True  \n",
       "1                                 physical-therapist        True        True  \n",
       "2                                 physical-therapist       False       False  \n",
       "3                                 physical-therapist        True        True  \n",
       "4  certified-registered-nurse-anesthetist, certif...        True       False  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     6150\n",
       "False     559\n",
       "Name: l_names_eq, dtype: int64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.l_names_eq.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of l_name_eq True and Percentage of False:\n",
    "**92%** for True and 8% for False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_att_inner['titles_eq'] = c_att_inner['titles_x'] == c_att_inner['titles_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "      <th>f_names_eq</th>\n",
       "      <th>l_names_eq</th>\n",
       "      <th>titles_eq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>1790784189</td>\n",
       "      <td>joan</td>\n",
       "      <td>fambrough</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>joan</td>\n",
       "      <td>lee</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist, certif...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            druid_x         npi    f_name_x   l_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max     harris   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid     sabour   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b  1790784189        joan  fambrough   \n",
       "\n",
       "  titles_x                            specialties_x  \\\n",
       "0       md                           rheumatologist   \n",
       "1   pt, md  family-practitioner, physical-therapist   \n",
       "2   pt, md  family-practitioner, physical-therapist   \n",
       "3       pt                       physical-therapist   \n",
       "4     crna   certified-registered-nurse-anesthetist   \n",
       "\n",
       "                            druid_y f_name_y l_name_y titles_y  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa      max   harris       md   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b     joan      lee     crna   \n",
       "\n",
       "                                       specialties_y  f_names_eq  l_names_eq  \\\n",
       "0                                     rheumatologist        True        True   \n",
       "1                                 physical-therapist        True        True   \n",
       "2                                 physical-therapist       False       False   \n",
       "3                                 physical-therapist        True        True   \n",
       "4  certified-registered-nurse-anesthetist, certif...        True       False   \n",
       "\n",
       "   titles_eq  \n",
       "0       True  \n",
       "1      False  \n",
       "2      False  \n",
       "3       True  \n",
       "4       True  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     6163\n",
       "False     546\n",
       "Name: titles_eq, dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.titles_eq.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of titles_eq True and Percentage of False:\n",
    "**92%** for True and 8% for False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_att_inner['specialties_eq'] = c_att_inner['specialties_x'] == c_att_inner['specialties_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>druid_x</th>\n",
       "      <th>npi</th>\n",
       "      <th>f_name_x</th>\n",
       "      <th>l_name_x</th>\n",
       "      <th>titles_x</th>\n",
       "      <th>specialties_x</th>\n",
       "      <th>druid_y</th>\n",
       "      <th>f_name_y</th>\n",
       "      <th>l_name_y</th>\n",
       "      <th>titles_y</th>\n",
       "      <th>specialties_y</th>\n",
       "      <th>f_names_eq</th>\n",
       "      <th>l_names_eq</th>\n",
       "      <th>titles_eq</th>\n",
       "      <th>specialties_eq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>1053349886</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>09a3d8f53bd041d5a244a37c37566efa</td>\n",
       "      <td>max</td>\n",
       "      <td>harris</td>\n",
       "      <td>md</td>\n",
       "      <td>rheumatologist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>abdelmajid</td>\n",
       "      <td>sabour</td>\n",
       "      <td>pt, md</td>\n",
       "      <td>family-practitioner, physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>1942283189</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>19a73029b597b48fa7d7d6631a1a5c9e</td>\n",
       "      <td>krista</td>\n",
       "      <td>sabo</td>\n",
       "      <td>pt</td>\n",
       "      <td>physical-therapist</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>1790784189</td>\n",
       "      <td>joan</td>\n",
       "      <td>fambrough</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist</td>\n",
       "      <td>496edcfabc3ffda82d83ad27f948cd2b</td>\n",
       "      <td>joan</td>\n",
       "      <td>lee</td>\n",
       "      <td>crna</td>\n",
       "      <td>certified-registered-nurse-anesthetist, certif...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            druid_x         npi    f_name_x   l_name_x  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa  1053349886         max     harris   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e  1942283189  abdelmajid     sabour   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e  1942283189      krista       sabo   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b  1790784189        joan  fambrough   \n",
       "\n",
       "  titles_x                            specialties_x  \\\n",
       "0       md                           rheumatologist   \n",
       "1   pt, md  family-practitioner, physical-therapist   \n",
       "2   pt, md  family-practitioner, physical-therapist   \n",
       "3       pt                       physical-therapist   \n",
       "4     crna   certified-registered-nurse-anesthetist   \n",
       "\n",
       "                            druid_y f_name_y l_name_y titles_y  \\\n",
       "0  09a3d8f53bd041d5a244a37c37566efa      max   harris       md   \n",
       "1  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "2  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "3  19a73029b597b48fa7d7d6631a1a5c9e   krista     sabo       pt   \n",
       "4  496edcfabc3ffda82d83ad27f948cd2b     joan      lee     crna   \n",
       "\n",
       "                                       specialties_y  f_names_eq  l_names_eq  \\\n",
       "0                                     rheumatologist        True        True   \n",
       "1                                 physical-therapist        True        True   \n",
       "2                                 physical-therapist       False       False   \n",
       "3                                 physical-therapist        True        True   \n",
       "4  certified-registered-nurse-anesthetist, certif...        True       False   \n",
       "\n",
       "   titles_eq  specialties_eq  \n",
       "0       True            True  \n",
       "1      False           False  \n",
       "2      False           False  \n",
       "3       True            True  \n",
       "4       True           False  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     4501\n",
       "False    2208\n",
       "Name: specialties_eq, dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_att_inner.specialties_eq.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of specialties_eq True and Percentage of False:\n",
    "**67%** for True and 33% for False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusions:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inconsistencies found per npi / (MD) were:\n",
    "\n",
    "    a. misspelled name\n",
    "    b. varying titles\n",
    "    c. varying specialties\n",
    "    d. npi assigned to multiple MD's\n",
    "    e. NaN's/missing information\n",
    "    f. more current info in abms/attested providers\n",
    "\n",
    "For the most part, True was between 80% and 90%. Only when it came to specialties there was not a match between client_providers dataset and attested_providers. This explains the percentage drop to lower than 80%. Given NaN's were dropped prior to join to attested_providers, client_providers had inaccurate information. \n",
    "\n",
    "In the end, client_providers table had mostly consistent data except for when it came to npi specialties data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This leads to a more developed method for records accuracy checking to report to clients when given their dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BONUS:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " _(planned for multiple truth sources though assignment asks to just plan for one - attested_providers)_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Methodology: \n",
    "\n",
    "1. Internal analysis: Are there npi duplicates? If so, what does sampling some of them reveal? (E.g. various first names/last names/titles/specialties/some of these cases/all of the above?)\n",
    "\n",
    "2. Comparison between one truth source, column-by-column.\n",
    "\n",
    "3. Comparison with another/multiple truth source(s), column-by-column to get average truth.\n",
    "\n",
    "Metrics: \n",
    "\n",
    "1. Duplicates: find % of whole dataset\n",
    "\n",
    "2. One Verification: for each column, find percent True, report overall True\n",
    "\n",
    "3. Multiple Verification: for each column re-verified, average columns of True, get overall True\n",
    "\n",
    "Justification for My Approach:\n",
    "In order to get most current data to provide Quest Analytics customers most current MD availability, need to check if their own data has integrity issues such as duplicate npi/MD's. On top of that, need to check to see if this data is correct by verifying across truth source(s)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Results from provided data that could be used by Quest Analytics' clients:\n",
    "\n",
    "### 1. client_providers Duplicates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_duplicates = client_providers.groupby(['npi']).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "druid          1369\n",
       "f_name         1369\n",
       "l_name         1369\n",
       "titles         1369\n",
       "specialties    1369\n",
       "dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_duplicates[c_duplicates > 1.0].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Out of the original dataframe minus NaN's (10016 entries), **1369** have more than 1 copy of itself (duplicates). This is 14% of the data, which is a huge amount. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. One Verification (including duplicates):\n",
    "-Against abms only-\n",
    "\n",
    "83% for both f_name and l_name. So overall True average is: 83%\n",
    "\n",
    "-Against attested_providers only-\n",
    "93% for f_name, 92% for l_name, 92% for titles, 67% for specialties. So overall True average is: 86%"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Multiple Verification (including duplicates): \n",
    "    only columns that are reverified are: f_name and l_name\n",
    "        Average of f_name: (83+93)/2 = 88%\n",
    "        Average of l_name: (83+92)/2 = 87.5%\n",
    "        Overall average: 87.75%"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the client who provided client_providers table, this would be the estimate of how accurate the original data is, compared to multiple sources of truth (abms and attested_providers)."
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}