primary_care_workforce_pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataset Generation # \n",
    "\n",
    "## Obtaining base data from APCD ##\n",
    "### Data from APCD must be obtained by logging following these steps:\n",
    "\n",
    "<ul>\n",
    "<li>Log into Stronghold (Brown University Secure Computing Environment) from APCD data can be accessed</li>\n",
    "<li>Run dbeaver from command line to access the DBeaver SQL studio UI</li>\n",
    "<li>Run the code in /apcd_sql_files/APCD_Data_Extract.sql and save the result in a file called apcd_data_extract.csv in the input_files folder </li>\n",
    "</ul>\n",
    "\n",
    "### Python Libraries / Constants Necessary For Subsequent Logic ###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pc_utilities import *\n",
    "from pc_constants import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a single dataframe from APCD Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "apcd_file_name = 'apcd_data_extract.csv'\n",
    "apcd_provider_data = import_csv_gracefully(INPUT_FILES_DIRECTORY, apcd_file_name)\n",
    "print(\"Importing data from APCD yielded:\", len(apcd_provider_data[APCD_NPI_COL_NAME].unique()), \"unique NPIs.\")\n",
    "\n",
    "apcd_provider_data = apcd_provider_data[~apcd_provider_data[APCD_NPI_COL_NAME].isin(list(KNOWN_ORGANIZATIONAL_NPIS.keys()))]\n",
    "unique_APCD_npis = apcd_provider_data[APCD_NPI_COL_NAME].unique()\n",
    "print(\"Post dropping known organizational NPIs:\", len(unique_APCD_npis), \"unique NPIs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Generating RIDOH Files\n",
    "\n",
    "##### Sanity Check: Reconciling NPPES with RIDOH License Database\n",
    "\n",
    "As a sanity check to ensure providers aren't missed, the NPPES database should be reconciled with the RIDOH License Database.\n",
    "\n",
    "##### Instructions for Downloading Relevant Files:\n",
    "\n",
    "1. Visit [Rhode Island Department of Health - Licensee Lists](https://health.ri.gov/lists/licensees/).\n",
    "2. Download the appropriate files by **Profession or Facility**, selecting:\n",
    "   - \"Physician\"\n",
    "   - \"Physician Assistant\"\n",
    "   - \"Nursing\"\n",
    "   - \"Midwifery\"\n",
    "3. For each file, place it in the input_files folder!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update the below! \n",
    "ridoh_physician_licensee_extract_file_name = 'Physician-licensee-extract-2024-10-07.csv'\n",
    "ridoh_physician_assistant_licensee_extract_file_name = 'Physician-Assistant-licensee-extract-2024-10-07.csv'\n",
    "ridoh_nursing_licensee_extract_file_name = 'Nursing-licensee-extract-2024-10-07.csv'\n",
    "ridoh_midwife_licensee_extract_file_name = 'Midwifery-licensee-extract-2024-10-07.csv'\n",
    "\n",
    "\n",
    "# Nothing to update below! \n",
    "RIDOH_FIRST_NAME_COL_NAME = add_source_db_prefix('First', RIDOH_PREFIX)\n",
    "RIDOH_LAST_NAME_COL_NAME = add_source_db_prefix('Last', RIDOH_PREFIX)\n",
    "RIDOH_LICENSE_NO_COL_NAME = add_source_db_prefix('License No', RIDOH_PREFIX) \n",
    "RIDOH_CREDENTIAL_COLUMN_NAME = add_source_db_prefix('Credential', RIDOH_PREFIX) \n",
    "SPECIALTY_COLUMN_NAME = add_source_db_prefix('Specialty', RIDOH_PREFIX) \n",
    "\n",
    "RIDOH_NAME_COL_NAME = add_source_db_prefix('Name', RIDOH_PREFIX)\n",
    "RIDOH_MIDDLE_COL_NAME = add_source_db_prefix('Middle', RIDOH_PREFIX)\n",
    "RIDOH_LIC_TYPE_COL_NAME = add_source_db_prefix('License Type', RIDOH_PREFIX)\n",
    "RIDOH_STATUS_COL_NAME = add_source_db_prefix('Status', RIDOH_PREFIX)\n",
    "RIDOH_ISSUE_DATE_COL_NAME = add_source_db_prefix('Issue Date', RIDOH_PREFIX)\n",
    "RIDOH_EXP_DATE_COL_NAME = add_source_db_prefix('Expiration Date', RIDOH_PREFIX)\n",
    "RIDOH_ADDRESS_ONE_COL_NAME = add_source_db_prefix('Address Line 1', RIDOH_PREFIX)\n",
    "RIDOH_ADDRESS_TWO_COL_NAME = add_source_db_prefix('Address Line 2', RIDOH_PREFIX)\n",
    "RIDOH_ADDRESS_THREE_COL_NAME = add_source_db_prefix('Address Line 3', RIDOH_PREFIX)\n",
    "RIDOH_CITY_COL_NAME = add_source_db_prefix('City', RIDOH_PREFIX)\n",
    "RIDOH_STATE_COL_NAME = add_source_db_prefix('State', RIDOH_PREFIX)\n",
    "RIDOH_ZIP_COL_NAME = add_source_db_prefix('Zip', RIDOH_PREFIX)\n",
    "RIDOH_EMAIL_COL_NAME = add_source_db_prefix('Email', RIDOH_PREFIX)\n",
    "RIDOH_PHONE_COL_NAME = add_source_db_prefix('Phone', RIDOH_PREFIX)\n",
    "RIDOH_FAX_COL_NAME = add_source_db_prefix('Fax', RIDOH_PREFIX)\n",
    "RIDOH_PROF_COL_NAME = add_source_db_prefix('Profession', RIDOH_PREFIX)\n",
    "\n",
    "ridoh_physicians = import_csv_gracefully(INPUT_FILES_DIRECTORY, ridoh_physician_licensee_extract_file_name)\n",
    "ridoh_physicians[RIDOH_CREDENTIAL_COLUMN_NAME] = ROLE_MD_DO\n",
    "\n",
    "ridoh_physicians_assistant = import_csv_gracefully(INPUT_FILES_DIRECTORY,ridoh_physician_assistant_licensee_extract_file_name)\n",
    "ridoh_physicians_assistant[RIDOH_CREDENTIAL_COLUMN_NAME] = ROLE_PA\n",
    "\n",
    "ridoh_midwifery = import_csv_gracefully(INPUT_FILES_DIRECTORY, ridoh_midwife_licensee_extract_file_name)\n",
    "ridoh_midwifery[RIDOH_CREDENTIAL_COLUMN_NAME] = ROLE_CERT_NURSE_MIDWIFE\n",
    "\n",
    "# Includes both nurses and NPs so won't set credential for now (may use license type later for this analysis)\n",
    "ridoh_nursing = import_csv_gracefully(INPUT_FILES_DIRECTORY, ridoh_nursing_licensee_extract_file_name)\n",
    "\n",
    "ridoh_clinicians = pd.concat([ridoh_physicians, ridoh_physicians_assistant, ridoh_nursing, ridoh_midwifery], ignore_index=True)\n",
    "excluded_column = ridoh_clinicians[[RIDOH_CREDENTIAL_COLUMN_NAME]]\n",
    "remaining_columns = ridoh_clinicians.drop(columns=[RIDOH_CREDENTIAL_COLUMN_NAME]).add_prefix(RIDOH_PREFIX)\n",
    "ridoh_clinicians = pd.concat([excluded_column, remaining_columns], axis=1)\n",
    "\n",
    "ridoh_names_of_interest = list(ridoh_clinicians[[RIDOH_FIRST_NAME_COL_NAME, RIDOH_LAST_NAME_COL_NAME]].itertuples(index=False, name=None))\n",
    "ridoh_licenses = ridoh_clinicians[RIDOH_LICENSE_NO_COL_NAME]\n",
    "\n",
    "ridoh_first_names = [pair[0] for pair in ridoh_names_of_interest]\n",
    "ridoh_last_names = [pair[1] for pair in ridoh_names_of_interest]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generating NPPES Files\n",
    "\n",
    "#### Accessing the NPPES Database\n",
    "\n",
    "- The NPPES database can be found on the CMS [webpage](https://www.cms.gov/medicare/regulations-guidance/administrative-simplification/data-dissemination).\n",
    "- The actual file you need is the **Data Dissemination** file, which can be downloaded [here](https://download.cms.gov/nppes/NPI_Files.html).\n",
    "- Look for the relevant CSV file that starts with `npidata_pfile` (e.g., `npidata_pfile_20050523-20240107`) and place it in the input_files folder!\n",
    "\n",
    "### Filtering NPPES\n",
    "\n",
    "NPPES was the source of truth on provider metadata. We went through the entire database and then:\n",
    "- Only included those NPIs that we had seen in our APCD queries - NPIs that didn't show up in APCD are presumed to not have billed RI payors and therefore not be under consideration\n",
    "- For those NPIs that were seen in the APCD queries, we still need to confirm that the providers are Rhode Island based. We did this by checking against three sources:\n",
    "    - Did the provider have an address associated with RI in terms of personal or business address?\n",
    "    - Did the provider have a license number that matched the RIDOH license database?\n",
    "    - Did the provider have a name that matched the RIDOH license database? \n",
    "\n",
    "Of note, the public-facing RIDOH database did not include NPI numbers so this version of the logic checked against name and license number only. Future versions should consider obtaining the NPI number.\n",
    "\n",
    "Current runtime for this step is approximately 10 minutes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update the below! \n",
    "nppes_provider_data_file = 'npidata_pfile_20050523-20240107.csv'\n",
    "output_ri_nppes_clinicians_file_suffix = '_ri_clinicians.csv'\n",
    "# This flag indicates whether the NPPES data set should explicitly exclude any NPI associated with entity type=2 or organization\n",
    "# Default is to not exclude to be conservative! \n",
    "exclude_organizations = False\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Nothing to update below! \n",
    "# chunk_size can be modified depending on the memory/performance of the machine on which the code is run!\n",
    "\n",
    "NPPES_PREFIX = 'NPPES_'\n",
    "\n",
    "# Looking for providers in NPPES with a RI connection based on any noted state being RI\n",
    "address_columns_to_check = [\n",
    "    add_source_db_prefix('Provider Business Mailing Address State Name', NPPES_PREFIX),\n",
    "    add_source_db_prefix('Provider Business Practice Location Address State Name', NPPES_PREFIX)\n",
    "]\n",
    "\n",
    "license_columns = [f'{NPPES_PREFIX}Provider License Number State Code_{i}' for i in range(1, 16)]\n",
    "address_columns_to_check.extend(license_columns)\n",
    "\n",
    "identifier_columns = [f'{NPPES_PREFIX}Other Provider Identifier State_{i}' for i in range(1, 51)]\n",
    "address_columns_to_check.extend(identifier_columns)\n",
    "\n",
    "provider_license_number_columns = [f'{NPPES_PREFIX}Provider License Number_{i}' for i in range(1, 16)]\n",
    "taxonomy_columns_to_check = [f'{NPPES_PREFIX}Healthcare Provider Taxonomy Code_{i}' for i in range(1, 16)]\n",
    "\n",
    "NPPES_FIRST_NAME_COL_NAME = add_source_db_prefix('Provider First Name', NPPES_PREFIX)\n",
    "NPPES_MIDDLE_NAME_COL_NAME = add_source_db_prefix('Provider Middle Name', NPPES_PREFIX) \n",
    "NPPES_LAST_NAME_COL_NAME = add_source_db_prefix('Provider Last Name (Legal Name)', NPPES_PREFIX) \n",
    "NPPES_ENTITY_TYPE_CODE = add_source_db_prefix('Entity Type Code', NPPES_PREFIX)\n",
    "NPPES_NPI_COL_NAME = add_source_db_prefix('NPI', NPPES_PREFIX)\n",
    "NPPES_ENTITY_TYPE_ORG_CODE = 2\n",
    "\n",
    "NPPES_IN_RI_COL_NAME = add_source_db_prefix('Is In RI?', NPPES_PREFIX + 'CALC_')\n",
    "NPPES_MATCH_RIDOH_NAME_COL_NAME = add_source_db_prefix('Matched RIDOH On Name', NPPES_PREFIX + 'CALC_')\n",
    "NPPES_MATCH_RIDOH_LIC_COL_NAME = add_source_db_prefix('Matched RIDOH On License', NPPES_PREFIX + 'CALC_')\n",
    "\n",
    "chunk_size = 200000 \n",
    "base_path = '.'\n",
    "nppes_file_name = os.path.join(INPUT_FILES_DIRECTORY, nppes_provider_data_file)\n",
    "output_ri_providers_file_name = current_date() + output_ri_nppes_clinicians_file_suffix\n",
    "output_ri_providers_file_path = os.path.join(base_path, output_ri_providers_file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nppes_total_rows = sum(1 for _ in open(nppes_file_name))\n",
    "num_chunks = nppes_total_rows // chunk_size + (nppes_total_rows % chunk_size > 0)\n",
    "print(f\"The file will be read in {num_chunks} chunks. The target number of NPI numbers to find is: \", len(unique_APCD_npis))\n",
    "\n",
    "nppes_aggregated = pd.DataFrame()\n",
    "for chunk_index, current_npi_batch in enumerate(import_csv_gracefully(INPUT_FILES_DIRECTORY, nppes_provider_data_file, chunk_size, False)):\n",
    "\tcurrent_npi_batch = current_npi_batch.add_prefix(NPPES_PREFIX)\n",
    "\t# Filter on only those NPI numbers that were returned from the APCD query - all other NPI numbers will be disregarded!  \n",
    "\tcurrent_npi_batch = current_npi_batch[current_npi_batch[NPPES_NPI_COL_NAME].isin(unique_APCD_npis)]\n",
    "\n",
    "\tprint(\"Current Time:\", current_date_time(), \" Chunk number: \", chunk_index + 1, \" Filtered Chunk length:\", len(current_npi_batch))\n",
    "\tif exclude_organizations:\n",
    "\t\tcurrent_npi_batch = current_npi_batch[current_npi_batch[NPPES_ENTITY_TYPE_CODE] != NPPES_ENTITY_TYPE_ORG_CODE]\n",
    "\t\n",
    "\t# We nevertheless do additional filtering to actually make sure that the providers we got back from NPPES are actually \n",
    "\t# Rhode Island Providers! (hence filtering on state, RIDOH name, and RIDOH license number - looking for proof of residency!)\n",
    "\tin_ri = current_npi_batch[current_npi_batch[address_columns_to_check].apply(lambda x: (x == RHODE_ISLAND_STATE_CODE).any(), axis=1)].copy()\n",
    "\tin_ri[NPPES_IN_RI_COL_NAME] = True\n",
    "\tin_ri.dropna(how='all', axis=1, inplace=True)\n",
    "\n",
    "\tnppes_aggregated = pd.concat([nppes_aggregated, in_ri], ignore_index=True)\n",
    "\n",
    "\tnot_in_ri = current_npi_batch[~current_npi_batch[address_columns_to_check].apply(lambda x: (x == RHODE_ISLAND_STATE_CODE).any(), axis=1)].copy()\n",
    "\tnot_in_ri[NPPES_IN_RI_COL_NAME] = False\n",
    "\tprint(\"RI analysis complete. There were the following number of providers not in RI:\" , len(not_in_ri), \" \", current_date_time())\n",
    "\t\n",
    "\tif (len(not_in_ri) > 0):\n",
    "\t\tfinal_mask_names = (\n",
    "\t\t\tnot_in_ri[NPPES_FIRST_NAME_COL_NAME].isin(ridoh_first_names) & \n",
    "\t\t\tnot_in_ri[NPPES_LAST_NAME_COL_NAME].isin(ridoh_last_names)\n",
    "\t\t)\n",
    "\n",
    "\t\tfiltered_name_rows = not_in_ri.loc[final_mask_names].copy()\n",
    "\t\tfiltered_name_rows[NPPES_MATCH_RIDOH_NAME_COL_NAME] = True\n",
    "\t\tprint(\"Name matching analysis complete.\", len(filtered_name_rows[filtered_name_rows[NPPES_MATCH_RIDOH_NAME_COL_NAME] == True]), \" providers matched on name. \", current_date_time())\n",
    "\n",
    "\t\tfinal_mask_licenses = pd.DataFrame({col: not_in_ri[col].isin(ridoh_licenses) for col in provider_license_number_columns}).any(axis=1)\n",
    "\t\tfiltered_license_rows = not_in_ri.loc[final_mask_licenses].copy()\n",
    "\t\tfiltered_license_rows[NPPES_MATCH_RIDOH_LIC_COL_NAME] = True\n",
    "\t\tprint(\"License matching analysis complete. \", len(filtered_license_rows[filtered_license_rows[NPPES_MATCH_RIDOH_LIC_COL_NAME] == True]), \" providers matched on license, \", current_date_time())\n",
    "\n",
    "\t\tfiltered_rows = pd.concat([filtered_name_rows,filtered_license_rows])\n",
    "\t\tfiltered_rows.dropna(how='all', axis=1, inplace=True)\n",
    "\n",
    "\t\tnppes_aggregated = pd.concat([nppes_aggregated, filtered_rows], ignore_index=True)\n",
    "\tprint(\"****** Total providers are now: \", len(nppes_aggregated))\n",
    "\t# Improved memory usage on local machine but may be unnecessary\n",
    "\tfor var in ['current_npi_batch', 'in_ri', 'not_in_ri', 'filtered_name_rows', 'filtered_license_rows', 'filtered_rows', 'final_mask_names', 'final_mask_licenses']:\n",
    "\t\tif var in locals():\n",
    "\t\t\tdel locals()[var]\n",
    "\n",
    "\n",
    "nppes_aggregated.to_csv(output_ri_providers_file_path, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge APCD to NPPES\n",
    "- This is where the logic of actually creating one single large dataset takes place\n",
    "- Of note, we consider all NPIs who EITHER billed for our core prevention codes OR who billed only for vaccination but did not have an internal medicine subspecialty of exclusion \n",
    "- We also save down those APCD NPIs that were not found in NPPES for further evaluation and analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nppes_data = import_csv_gracefully('.', output_ri_providers_file_name)\n",
    "merged_df = pd.merge(apcd_provider_data, nppes_data, left_on=APCD_NPI_COL_NAME, right_on=NPPES_NPI_COL_NAME)\n",
    "print(\"After merging with NPPEs, only\", len(merged_df) , \"NPIs remain.\")\n",
    "\n",
    "\n",
    "npis_not_in_nppes = apcd_provider_data[~apcd_provider_data[APCD_NPI_COL_NAME].isin(nppes_data[NPPES_NPI_COL_NAME])]\n",
    "print(\"This means that\", len(npis_not_in_nppes), \"NPIs from APCD are not found in NPPES. (likely because they are not RI providers)\")\n",
    "print(\"Of these,\", len(npis_not_in_nppes[npis_not_in_nppes[APCD_TOTAL_CLAIMS_ALL_COL_NAME] <= 100]), \"billed for 100 or fewer claims of ANY kind.\")\n",
    "npis_not_in_nppes.to_csv(\"npis_not_in_nppes_\" + current_date() + \".csv\")\n",
    "\n",
    "\n",
    "providers_with_core_prevention = merged_df[merged_df[APCD_PC_CODES_PRESENT_COL_NAME] == True]\n",
    "print(\"The\", len(merged_df),\"NPIs included\", len(providers_with_core_prevention), \"providers who did bill for one of our core prevention codes at least once.\")\n",
    "providers_without_core_prevention = merged_df[merged_df[APCD_PC_CODES_PRESENT_COL_NAME] == False]\n",
    "print(\"And it included\", len(providers_without_core_prevention), \"providers who did NOT bill for one of our core prevention codes at least once.\")\n",
    "\n",
    "df_nppes_all_taxonomies = import_csv_gracefully(INPUT_FILES_DIRECTORY, 'nppes_all_taxonomies.csv')\n",
    "internal_medicine_subspecialties_to_exclude = df_nppes_all_taxonomies[df_nppes_all_taxonomies['Internal_Medicine_Subspecialty_To_Exclude'] == 'Yes'][NPPES_CODE].tolist()\n",
    "\n",
    "valid_columns = [col for col in taxonomy_columns_to_check if col in providers_without_core_prevention.columns]\n",
    "\n",
    "if valid_columns:\n",
    "    mask = providers_without_core_prevention[valid_columns].isin(internal_medicine_subspecialties_to_exclude).any(axis=1)\n",
    "else:\n",
    "    mask = pd.Series([False] * len(providers_without_core_prevention))\n",
    "\n",
    "providers_without_core_prevention_specialty = providers_without_core_prevention[mask]\n",
    "print(\"Of the providers who billed only for immunizations,\",len(providers_without_core_prevention_specialty), \"providers had a specialty of exclusion.\")\n",
    "providers_without_core_prevention_no_specialty = providers_without_core_prevention[~mask]\n",
    "print(\"Of the providers who billed only for immunizations,\",len(providers_without_core_prevention_no_specialty), \"providers did not have a specialty of exclusion.\")\n",
    "\n",
    "final_provider_list = pd.concat([providers_with_core_prevention, providers_without_core_prevention_no_specialty], ignore_index=True)\n",
    "print(\"In total then, there were: \", len(final_provider_list), \"primary care NPIs that could be matched between NPPES, RIDOH and APCD.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merged Dataset Cleaning\n",
    "\n",
    "### Taxonomy Mark-up\n",
    "- This section of code deals with using the taxonomy codes included in NPPES to identify what types of clinicians the merged file includes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "physician_taxonomies = df_nppes_all_taxonomies[df_nppes_all_taxonomies['Grouping'] == 'Allopathic & Osteopathic Physicians']\n",
    "# We are excluding those taxonomies that we do not expect are primary care taxonomies\n",
    "df_nppes_pc_taxonomies = df_nppes_all_taxonomies[df_nppes_all_taxonomies['Exclude?'] != 'Yes']\n",
    "\n",
    "# Create dictionary mapping of physician specialty to taxonomy codes\n",
    "taxoncmy_code_specialties = {}\n",
    "# Though EM should not generally be primary care, due to some data quality issues, we include it here\n",
    "taxoncmy_code_specialties[SPECIALTY_EM] = physician_taxonomies[(physician_taxonomies[NPPES_CLASSIFICATION] == SPECIALTY_EM)][NPPES_CODE].tolist()\n",
    "# anyone with one of these specialties must be a physician by defintion\n",
    "primary_care_adjacent_specialties = [SPECIALTY_INTEG_MEDICINE, SPECIALTY_PREVENT_MEDICINE, SPECIALTY_INTERNAL_MEDICINE, SPECIALTY_FAMILY_MEDICINE, SPECIALTY_GEN_PRACTICE, SPECIALTY_OBGYN, SPECIALTY_PEDS]\n",
    "for specialty in primary_care_adjacent_specialties:\n",
    "    taxoncmy_code_specialties[specialty] = df_nppes_pc_taxonomies[(df_nppes_pc_taxonomies[NPPES_CLASSIFICATION] == specialty)][NPPES_CODE].tolist()\n",
    "\n",
    "def get_codes(dataframe, classification_conditions=None):\n",
    "    \"\"\"Get taxonomy codes based on classification conditions.\n",
    "    Args:\n",
    "        dataframe: DataFrame containing taxonomy codes\n",
    "        classification_conditions: Single classification string or list of tuples with (classification, operator)\n",
    "            where operator is '==' or '|' for OR condition\n",
    "    \"\"\"\n",
    "    if isinstance(classification_conditions, str):\n",
    "        return dataframe[dataframe[NPPES_CLASSIFICATION] == classification_conditions][NPPES_CODE].tolist()\n",
    "    elif classification_conditions:\n",
    "        mask = None\n",
    "        for classification in classification_conditions:\n",
    "            condition = (dataframe[NPPES_CLASSIFICATION] == classification)\n",
    "            if mask is None:\n",
    "                mask = condition\n",
    "            else:\n",
    "                mask |= condition  # Bitwise OR for OR condition\n",
    "        return dataframe[mask][NPPES_CODE].tolist()\n",
    "    return dataframe[NPPES_CODE].tolist()\n",
    "\n",
    "valid_columns = [col for col in taxonomy_columns_to_check if col in final_provider_list.columns]\n",
    "\n",
    "# Of note, the logic below will take the last role as the accurate one - this is why we set up the boolean\n",
    "# is_role column to track those clinicians who meet the criteria for more than one role (this logic is also replicated for specialty) \n",
    "taxonomy_code_roles = {\n",
    "    ROLE_MISC_OTHER : get_codes(df_nppes_all_taxonomies,['Legal Medicine', 'Specialist']),\n",
    "    ROLE_PODIATRY : get_codes(df_nppes_all_taxonomies, ROLE_PODIATRY),\n",
    "    ROLE_OPTOMETRY : get_codes(df_nppes_all_taxonomies, ROLE_OPTOMETRY),\n",
    "    ROLE_CASE_MGMT : get_codes(df_nppes_all_taxonomies, ROLE_CASE_MGMT),\n",
    "    ROLE_PSYCHOLOGIST : get_codes(df_nppes_all_taxonomies, ROLE_PSYCHOLOGIST),\n",
    "    ROLE_ORGANIZATION : get_codes(df_nppes_pc_taxonomies, ['Clinic/Center','General Acute Care Hospital', 'Nursing Facility/Intermediate Care Facility', 'Hospice Care, Community Based']),\n",
    "    ROLE_CLIN_NURSE_SPECIALIST : get_codes(df_nppes_pc_taxonomies, ROLE_CLIN_NURSE_SPECIALIST),\n",
    "    ROLE_CERT_NURSE_MIDWIFE : get_codes(df_nppes_pc_taxonomies, ['Advanced Practice Midwife', 'Midwife']),\n",
    "    ROLE_NURSE: get_codes(df_nppes_pc_taxonomies, 'Registered Nurse'), \n",
    "    ROLE_STUDENT : get_codes(df_nppes_pc_taxonomies, 'Student in an Organized Health Care Education/Training Program'), \n",
    "    ROLE_NP: get_codes(df_nppes_pc_taxonomies, ROLE_NP),\n",
    "    ROLE_PA: get_codes(df_nppes_pc_taxonomies, ROLE_PA),\n",
    "    ROLE_MD_DO: get_codes(physician_taxonomies),\n",
    "}\n",
    "\n",
    "def update_roles_specialties(final_provider_list, taxonomy_dictionary, valid_columns, col_to_update):\n",
    "    for role_or_specialty, codes_to_check in taxonomy_dictionary.items():\n",
    "        if valid_columns:\n",
    "            mask = final_provider_list[valid_columns].apply(lambda row: row.isin(codes_to_check).any(), axis=1)\n",
    "            final_provider_list.loc[mask, col_to_update] = role_or_specialty\n",
    "            taxonomy_code_tracking_col_name = ''\n",
    "            if col_to_update == SPECIALTY_COLUMN_NAME:\n",
    "                taxonomy_code_tracking_col_name = 'Included_taxonomy_values_specialty'\n",
    "            elif col_to_update == RIDOH_CREDENTIAL_COLUMN_NAME:\n",
    "                taxonomy_code_tracking_col_name = 'Included_taxonomy_values_role'\n",
    "            final_provider_list.loc[mask, taxonomy_code_tracking_col_name] = final_provider_list.loc[mask, valid_columns].apply(\n",
    "                lambda row: [val for val in row if val in codes_to_check], axis=1\n",
    "            )\n",
    "            column_name = f'is_{role_or_specialty}'\n",
    "            final_provider_list[column_name] = False  \n",
    "            final_provider_list.loc[mask, column_name] = True \n",
    "\n",
    "update_roles_specialties(final_provider_list, taxonomy_code_roles, valid_columns, RIDOH_CREDENTIAL_COLUMN_NAME)\n",
    "update_roles_specialties(final_provider_list, taxoncmy_code_specialties, valid_columns, SPECIALTY_COLUMN_NAME)\n",
    "\n",
    "\n",
    "# Original list of column names\n",
    "full_specialty_list = primary_care_adjacent_specialties + [SPECIALTY_EM]\n",
    "cols = [f'is_{col}' for col in full_specialty_list]\n",
    "\n",
    "# Create the 'Count Specialties' column by summing up True values in the modified columns\n",
    "final_provider_list['Count Specialties'] = final_provider_list[cols].sum(axis=1)\n",
    "final_provider_list['Derived Specialty'] = 'Unknown'\n",
    "\n",
    "# Function to determine the derived specialty based on Count Specialties and specific rules\n",
    "def get_derived_specialty(row):\n",
    "    if row['Count Specialties'] == 1:\n",
    "        # Find the column where the value is True and remove the 'is_' prefix\n",
    "        true_column = next(col for col in cols if row[col] == True)\n",
    "        return true_column.replace('is_', '')\n",
    "    elif row['Count Specialties'] == 2:\n",
    "        # Get the list of columns where value is True\n",
    "        true_columns = [col for col in cols if row[col] == True]\n",
    "        if set(true_columns) == {'is_Internal Medicine', 'is_Pediatrics'}:\n",
    "            return 'Med-Peds'\n",
    "        elif set(true_columns) == {'is_Internal Medicine', 'is_Family Medicine'}:\n",
    "            return 'IM-FM'\n",
    "        elif set(true_columns) == {'is_Emergency Medicine', 'is_Internal Medicine'}:\n",
    "            return 'EM-IM'\n",
    "        elif set(true_columns) == {'is_Family Medicine', 'is_Obstetrics & Gynecology'}:\n",
    "            return 'FM-OBGYN'\n",
    "        elif set(true_columns) == {'is_Family Medicine', 'is_Pediatrics'}:\n",
    "            return 'FM-Peds'\n",
    "        elif set(true_columns) == {'is_Family Medicine', 'is_Emergency Medicine'}:\n",
    "            return 'FM-EM'\n",
    "        else:\n",
    "            return 'Multiple specialties'\n",
    "    elif row['Count Specialties'] > 2:\n",
    "        return 'Multiple specialties'\n",
    "    else:\n",
    "        return 'Unknown'\n",
    "\n",
    "final_provider_list['Derived Specialty'] = final_provider_list.apply(get_derived_specialty, axis=1)\n",
    "\n",
    "final_provider_list.to_csv('final_provider_list.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### RIDOH License Number Triangulation\n",
    "The goal of this code is to try to triangulate the exact license number and specialty type of every clinician found above. \n",
    "The license number is particularly valuable as it can be directly looked up for information on year of graduation as well as school"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "def clean_license_minimal(license_no):\n",
    "    if pd.isna(license_no) or license_no.strip() == '':\n",
    "        return ''\n",
    "    pattern = rf'^({MD_PREFIX}|{DO_PREFIX}|{LP_PREFIX})'\n",
    "    return re.sub(pattern, '', license_no).strip()\n",
    "\n",
    "def clean_license(license_no):\n",
    "    if pd.isna(license_no) or license_no.strip() == '':\n",
    "        return ''\n",
    "    pattern = rf'^({MD_PREFIX}0?|{DO_PREFIX}0?|{LP_PREFIX}0?)'\n",
    "    return re.sub(pattern, '', license_no).strip()\n",
    "\n",
    "ridoh_clinicians[SPECIALTY_COLUMN_NAME] = ridoh_clinicians[SPECIALTY_COLUMN_NAME].astype(str).replace(NAN_STRING, '')\n",
    "\n",
    "grouped_ridoh = ridoh_clinicians.groupby(\n",
    "        [RIDOH_CREDENTIAL_COLUMN_NAME, RIDOH_NAME_COL_NAME, RIDOH_FIRST_NAME_COL_NAME, \n",
    "         RIDOH_MIDDLE_COL_NAME, RIDOH_LAST_NAME_COL_NAME, RIDOH_LICENSE_NO_COL_NAME, \n",
    "         RIDOH_LIC_TYPE_COL_NAME, RIDOH_STATUS_COL_NAME, RIDOH_ISSUE_DATE_COL_NAME, RIDOH_EXP_DATE_COL_NAME, \n",
    "         RIDOH_ADDRESS_ONE_COL_NAME, RIDOH_ADDRESS_TWO_COL_NAME, RIDOH_ADDRESS_THREE_COL_NAME, \n",
    "         RIDOH_CITY_COL_NAME, RIDOH_STATE_COL_NAME, RIDOH_ZIP_COL_NAME, \n",
    "         RIDOH_EMAIL_COL_NAME,RIDOH_PHONE_COL_NAME, RIDOH_FAX_COL_NAME, RIDOH_PROF_COL_NAME], dropna=False\n",
    "    )[SPECIALTY_COLUMN_NAME].apply(','.join).reset_index()\n",
    "\n",
    "grouped_ridoh['License Cleaned'] = grouped_ridoh[RIDOH_LICENSE_NO_COL_NAME].apply(clean_license)\n",
    "grouped_ridoh['License Cleaned Minimal'] = grouped_ridoh[RIDOH_LICENSE_NO_COL_NAME].apply(clean_license_minimal)\n",
    "\n",
    "\n",
    "grouped_ridoh['full_name_concatenated'] = (\n",
    "    grouped_ridoh[RIDOH_FIRST_NAME_COL_NAME].fillna('') + ' ' +\n",
    "    grouped_ridoh[RIDOH_MIDDLE_COL_NAME].fillna('') + ' ' +\n",
    "    grouped_ridoh[RIDOH_LAST_NAME_COL_NAME].fillna('')\n",
    ").str.strip().str.strip().str.lower().str.replace(' ', '')\n",
    "\n",
    "def clean_provider_licenses(row):\n",
    "    licenses = [\n",
    "        clean_license(row[add_source_db_prefix('Provider License Number_1', NPPES_PREFIX)]), \n",
    "        clean_license(row[add_source_db_prefix('Provider License Number_2', NPPES_PREFIX)]),\n",
    "        clean_license(row[add_source_db_prefix('Provider License Number_3', NPPES_PREFIX)]),\n",
    "        clean_license(row[add_source_db_prefix('Provider License Number_4', NPPES_PREFIX)]),\n",
    "        clean_license(row[add_source_db_prefix('Provider License Number_5', NPPES_PREFIX)])\n",
    "    ]\n",
    "    return ', '.join(filter(None, licenses))\n",
    "\n",
    "final_provider_list['License Cleaned'] = final_provider_list.apply(clean_provider_licenses, axis=1)\n",
    "\n",
    "def confirm_license_specialty(row, ridoh_clinicians):\n",
    "    matching_row = ridoh_clinicians[\n",
    "        (ridoh_clinicians[RIDOH_FIRST_NAME_COL_NAME].str.strip().str.lower() == str(row[NPPES_FIRST_NAME_COL_NAME]).strip().lower()) &\n",
    "        (ridoh_clinicians[RIDOH_LAST_NAME_COL_NAME].str.strip().str.lower() == str(row[NPPES_LAST_NAME_COL_NAME]).strip().lower()) &\n",
    "        (ridoh_clinicians[RIDOH_CREDENTIAL_COLUMN_NAME].str.strip().str.lower() == str(row[RIDOH_CREDENTIAL_COLUMN_NAME]).strip().lower())\n",
    "    ]\n",
    "    \n",
    "    # Usually first/last name is uniquely identify - however, in some cases, we need to \n",
    "    if (len(matching_row)) > 1:\n",
    "        if pd.notna(row[NPPES_MIDDLE_NAME_COL_NAME]) and row[NPPES_MIDDLE_NAME_COL_NAME].strip() != '':\n",
    "            matching_row = matching_row[matching_row[RIDOH_MIDDLE_COL_NAME].str.strip().str.lower().str[0] == str(row[NPPES_MIDDLE_NAME_COL_NAME]).strip().lower()[0]]\n",
    "    \n",
    "    if (len(matching_row) == 0):\n",
    "        row_full_name = (\n",
    "                str(row[NPPES_FIRST_NAME_COL_NAME] if pd.notna(row[NPPES_FIRST_NAME_COL_NAME]) else '').strip().lower() +\n",
    "                str(row[NPPES_MIDDLE_NAME_COL_NAME] if pd.notna(row[NPPES_MIDDLE_NAME_COL_NAME]) else '').strip().lower() +\n",
    "                str(row[NPPES_LAST_NAME_COL_NAME] if pd.notna(row[NPPES_LAST_NAME_COL_NAME]) else '').strip().lower()\n",
    "            )\n",
    "        matching_row = ridoh_clinicians[\n",
    "            (ridoh_clinicians['full_name_concatenated'].str.strip().str.lower().str.replace(' ', '') == row_full_name.replace(' ', '')) &\n",
    "            (ridoh_clinicians[RIDOH_CREDENTIAL_COLUMN_NAME].str.strip().str.lower() == str(row[RIDOH_CREDENTIAL_COLUMN_NAME]).strip().lower())\n",
    "        ]\n",
    "\n",
    "    if not matching_row.empty:\n",
    "        for license_column in ['License Cleaned', 'License Cleaned Minimal']:\n",
    "            ridoh_license_cleaned = matching_row[license_column].values[0]\n",
    "            final_provider_license_cleaned = row['License Cleaned']\n",
    "\n",
    "            if ridoh_license_cleaned in [item.strip() for item in final_provider_license_cleaned.split(',')]:\n",
    "                return pd.Series({\n",
    "                    'Confirmed License': matching_row[RIDOH_LICENSE_NO_COL_NAME].values[0],\n",
    "                    'Confirmed RIDOH Specialty': matching_row[SPECIALTY_COLUMN_NAME].values[0]\n",
    "                })\n",
    "        \n",
    "    return pd.Series({\n",
    "        'Confirmed License': UNCONFIRMED_STRING,\n",
    "        'Confirmed RIDOH Specialty': UNCONFIRMED_STRING\n",
    "    })\n",
    "\n",
    "final_provider_list[['Confirmed License', 'Confirmed RIDOH Specialty']] = final_provider_list.apply(\n",
    "    confirm_license_specialty, axis=1, ridoh_clinicians=grouped_ridoh\n",
    ")\n",
    "\n",
    "final_provider_list.to_csv('final_provider_list.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.common.keys import Keys\n",
    "from selenium.webdriver.support.ui import Select\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.common.exceptions import StaleElementReferenceException\n",
    "from selenium.webdriver.chrome.options import Options\n",
    "from selenium.common.exceptions import TimeoutException\n",
    "\n",
    "MAX_WAIT_TIME_IN_SECS = 3\n",
    "\n",
    "METHOD_LIC = 'License Look-up'\n",
    "METHOD_NAME = 'First and Last Name Look-up'\n",
    "\n",
    "HLTHRI_ISSUE_DATE_COL_NAME = 'RIDOH Issue Date'\n",
    "HLTHRI_EXP_DATE_COL_NAME = 'RIDOH Expiration Date'\n",
    "HLTHRI_SCHOOL_NAME_COL_NAME = 'RIDOH School Name'\n",
    "HLTHRI_GRAD_DATE_COL_NAME = 'RIDOH Graduation Date'\n",
    "HLTHRI_SPEC_INFO_COL_NAME = 'RIDOH Specialty Info'\n",
    "HLTHRI_METHD_COL_NAME = 'RIDOH Methodology'\n",
    "HLTHRI_LIC_NO_COL_NAME = 'RIDOH Discovered License No'\n",
    "HLTHRI_NAME_COL_NAME = 'RIDOH Discovered Name'\n",
    "HLTHRI_PROF_COL_NAME = 'RIDOH Discovered Profession'\n",
    "HLTHRI_LIC_TYPE_COL_NAME = 'RIDOH Discovered License Type'\n",
    "HLTHRI_LIC_STATUS_COL_NAME = 'RIDOH Discovered License Status'\n",
    "HLTHRI_CITY_COL_NAME = 'RIDOH Discovered City'\n",
    "HLTHRI_STATE_COL_NAME = 'RIDOH Discovered State'\n",
    "\n",
    "MAX_RETRIES_PER_CLINICIAN = 2\n",
    "\n",
    "final_provider_list_2 = final_provider_list.copy()\n",
    "final_provider_list_2[HLTHRI_ISSUE_DATE_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_EXP_DATE_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_SCHOOL_NAME_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_GRAD_DATE_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_SPEC_INFO_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_METHD_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_LIC_NO_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_NAME_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_PROF_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_LIC_TYPE_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_LIC_STATUS_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_CITY_COL_NAME] = ''\n",
    "final_provider_list_2[HLTHRI_STATE_COL_NAME] = ''\n",
    "\n",
    "ridoh_online_verification_complaint_submission_site = 'https://healthri.mylicense.com/verification/Search.aspx?facility=N&SubmitComplaint=Y'\n",
    "\n",
    "def get_chrome_driver():\n",
    "    chrome_options = Options()\n",
    "    chrome_options.add_argument(\"--incognito\")\n",
    "    chrome_options.add_argument(\"--headless\")  # Enable headless mode\n",
    "    chrome_options.add_argument(\"--no-sandbox\")  # Bypass OS security model (Linux only)\n",
    "    chrome_options.add_argument(\"--disable-dev-shm-usage\")  # Overcome limited resource problems\n",
    "    chrome_options.add_argument(\"--log-level=3\")  # Suppress logs\n",
    "    driver = webdriver.Chrome(options=chrome_options)\n",
    "    return driver\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def get_element_text(wait, element_name, primary_id, secondary_id=None, verbose=False):\n",
    "    try:\n",
    "        return wait.until(EC.presence_of_element_located((By.ID, primary_id))).text\n",
    "    except TimeoutException:\n",
    "        try:\n",
    "            if secondary_id is not None:\n",
    "                return wait.until(EC.presence_of_element_located((By.ID, secondary_id))).text\n",
    "            else:\n",
    "                return None\n",
    "        except TimeoutException:\n",
    "            if verbose:\n",
    "                print(f\"{element_name} element not found.\")\n",
    "            return None\n",
    "\n",
    "def get_nppes_license_if_available(row):\n",
    "    license_to_search = None\n",
    "    if (row['Confirmed License'] != UNCONFIRMED_STRING):\n",
    "        license_to_search = row['Confirmed License']\n",
    "    else:\n",
    "        for i in range(1, 6):\n",
    "            license_number_col = f'{NPPES_PREFIX}Provider License Number_{i}'\n",
    "            state_code_col = f'{NPPES_PREFIX}Provider License Number State Code_{i}'\n",
    "            if row[state_code_col] == RHODE_ISLAND_STATE_CODE:\n",
    "                license_to_search = row[license_number_col]\n",
    "                # Thought process / hope is first license is more likely to be accurate\n",
    "                # Could revisit searching against all possible licenses but currently will just fall back\n",
    "                # to name search - it also turns out licenses CAN have dashes and be valid but CAN'T have spaces!\n",
    "                return license_to_search.replace(\" \",\"\")\n",
    "\n",
    "def clean_name(name):\n",
    "    return name.replace(\"-\",\" \").replace(\" \", \"\")\n",
    "\n",
    "\n",
    "\n",
    "for index, row in final_provider_list_2.iterrows():\n",
    "    role = row[RIDOH_CREDENTIAL_COLUMN_NAME]\n",
    "    first_name = row[NPPES_FIRST_NAME_COL_NAME]\n",
    "    last_name = row[NPPES_LAST_NAME_COL_NAME]\n",
    "    entity_type = row[NPPES_ENTITY_TYPE_CODE]\n",
    "    if entity_type == NPPES_ENTITY_TYPE_ORG_CODE:\n",
    "        print(f\"Index: {index} - No license look-up for organizations\")\n",
    "        continue\n",
    "\n",
    "    # If there is no first or last name, then there is a good chance this is an organization and there is no need to search\n",
    "    if not first_name or not last_name or str(first_name).lower() == NAN_STRING or str(last_name).lower() == NAN_STRING:\n",
    "        print(f\"Index: {index} - Skipping no name clinician.\")\n",
    "        continue\n",
    "    skip_license_search = False\n",
    "    for attempt in range(MAX_RETRIES_PER_CLINICIAN):\n",
    "        # resetting those variables that can change per attempt\n",
    "        license_to_search = None\n",
    "        credential = None\n",
    "        prefix = None \n",
    "        try:\n",
    "            # Currently only attempting license look-ups for specific credentials \n",
    "            if (role in [ROLE_MD_DO, ROLE_PA, ROLE_CLIN_NURSE_SPECIALIST, ROLE_NURSE, ROLE_NP, ROLE_CERT_NURSE_MIDWIFE]):\n",
    "                driver = get_chrome_driver()\n",
    "                driver.get(ridoh_online_verification_complaint_submission_site)\n",
    "\n",
    "                # the skip_license_search flag was developed in the case that the result of the search based on the license number\n",
    "                # yields a physician whose name doesn't match our records - in this case, we want to re-try to search based on name alone\n",
    "                if not skip_license_search:\n",
    "                    license_to_search = get_nppes_license_if_available(row)\n",
    "\n",
    "                \n",
    "                # Of note, RI is not considered a valid prefix by the RIDOH website - however, there are no \n",
    "                # guarantees on if this belongs to an MD, DO, NP so for now, falling back on name searches\n",
    "                if license_to_search is not None:\n",
    "                    invalid_license_pattern = rf'^({RHODE_ISLAND_STATE_CODE})'\n",
    "                    invalid_match = re.match(invalid_license_pattern, license_to_search)\n",
    "                    if invalid_match:\n",
    "                        skip_license_search = True\n",
    "                        raise Exception(f\"License begins with {RHODE_ISLAND_STATE_CODE} identifer - will search on name instead!\")\n",
    "\n",
    "                    # Consider better handling for: F03170623, LP03291, MW00016\n",
    "                    pattern = rf'^({DO_PREFIX}|{MD_PREFIX}|{CNM_PREFIX}|{PA_PREFIX}|{APRN_PREFIX}|{RN_PREFIX}|{ETL_PREFIX}|{NPP_PREFIX}|{CAPRN_PREFIX})'\n",
    "                    match = re.match(pattern, license_to_search)\n",
    "\n",
    "                    if match:\n",
    "                        prefix = match.group(0)\n",
    "\n",
    "                    if prefix == PA_PREFIX:\n",
    "                        credential = ROLE_PA\n",
    "                    elif prefix == MD_PREFIX or prefix == DO_PREFIX:\n",
    "                        credential = ROLE_MD_DO\n",
    "                    elif prefix in [APRN_PREFIX,RN_PREFIX,ETL_PREFIX,NPP_PREFIX,CAPRN_PREFIX]:\n",
    "                        credential = 'Nursing'\n",
    "                    elif prefix == CNM_PREFIX:\n",
    "                        credential = 'Midwifery'\n",
    "\n",
    "                    print(f\"Index: {index} - searching {license_to_search}, against the license type of: {credential},{current_date_time()}\")\n",
    "\n",
    "                    if credential is not None:\n",
    "                        dropdown_element = driver.find_element(By.ID, 't_web_lookup__profession_name')\n",
    "                        select = Select(dropdown_element)\n",
    "                        select.select_by_visible_text(credential)\n",
    "\n",
    "                    search_input = driver.find_element(By.NAME, \"t_web_lookup__license_no\")\n",
    "                    search_input.send_keys(license_to_search)  \n",
    "                    methodology = METHOD_LIC\n",
    "                else:\n",
    "                    if role == ROLE_MD_DO:\n",
    "                        credential = ROLE_MD_DO\n",
    "                    elif role == ROLE_PA:\n",
    "                        credential = ROLE_PA\n",
    "                    elif role == ROLE_NP or role == ROLE_NURSE or role == ROLE_CLIN_NURSE_SPECIALIST:\n",
    "                        credential = 'Nursing'\n",
    "                    elif role == ROLE_CERT_NURSE_MIDWIFE:\n",
    "                        credential = 'Midwifery'\n",
    "                    else:\n",
    "                        break\n",
    "\n",
    "                    dropdown_element = driver.find_element(By.ID, 't_web_lookup__profession_name')\n",
    "                    select = Select(dropdown_element)\n",
    "                    select.select_by_visible_text(credential)\n",
    "\n",
    "                    search_input = driver.find_element(By.NAME, \"t_web_lookup__first_name\")\n",
    "                    search_input.send_keys(first_name)  \n",
    "\n",
    "                    search_input = driver.find_element(By.NAME, \"t_web_lookup__last_name\")\n",
    "                    search_input.send_keys(last_name) \n",
    "                    print(f\"Index: {index} - searching {first_name} and {last_name}, against the license type of: {credential}, {current_date_time()}\")\n",
    "                    methodology = METHOD_NAME\n",
    "                \n",
    "                search_input.send_keys(Keys.RETURN)\n",
    "                wait = WebDriverWait(driver, MAX_WAIT_TIME_IN_SECS)\n",
    "\n",
    "                wait.until(EC.presence_of_element_located((By.ID, 'datagrid_results')))\n",
    "                link_elements = driver.find_elements(By.CSS_SELECTOR, 'a[id^=\"datagrid_results__ctl\"]')\n",
    "\n",
    "                for link in link_elements:\n",
    "                    license_number = None\n",
    "                    try:\n",
    "                        link_license_number = link.find_element(By.XPATH, '../following-sibling::td[1]/span').text\n",
    "                        # This logic is necessary as the search automatically inserts a wildcard at beginning and thus\n",
    "                        # includes other associated licenses which capture slightly different info (e.g. lack specialty for physicians)\n",
    "                        # here, we confirm that we got the exact license we searched on if license was in the query\n",
    "                        if license_to_search is not None and license_to_search != link_license_number:\n",
    "                            continue\n",
    "                        \n",
    "                        ridoh_name = link.text\n",
    "                        print(f'Name: {ridoh_name} and License Number: {link_license_number} found based on methodology: {methodology}')\n",
    "\n",
    "                        # This logic is necessary because there are individuals who an incorrect license number listed and whose\n",
    "                        # name on license look-up doesn't match - for these people, we want to revert to a manual name search\n",
    "                        if (clean_name(first_name) not in clean_name(ridoh_name) or clean_name(last_name) not in clean_name(ridoh_name)):\n",
    "                            skip_license_search = True\n",
    "                            raise Exception(\"Name mismatch based on license search!\")\n",
    "                        \n",
    "                        ridoh_profession = link.find_element(By.XPATH, '../following-sibling::td[3]/span').text\n",
    "                        ridoh_license_type = link.find_element(By.XPATH, '../following-sibling::td[4]/span').text\n",
    "                        ridoh_license_status = link.find_element(By.XPATH, '../following-sibling::td[5]/span').text\n",
    "                        ridoh_city = link.find_element(By.XPATH, '../following-sibling::td[6]/span').text\n",
    "                        ridoh_state = link.find_element(By.XPATH, '../following-sibling::td[7]/span').text\n",
    "\n",
    "                        final_provider_list_2.at[index, HLTHRI_NAME_COL_NAME] = ridoh_name\n",
    "                        final_provider_list_2.at[index, HLTHRI_LIC_NO_COL_NAME] = link_license_number\n",
    "                        final_provider_list_2.at[index, HLTHRI_PROF_COL_NAME] = ridoh_profession\n",
    "                        final_provider_list_2.at[index, HLTHRI_LIC_TYPE_COL_NAME] = ridoh_license_type\n",
    "                        final_provider_list_2.at[index, HLTHRI_LIC_STATUS_COL_NAME] = ridoh_license_status\n",
    "                        final_provider_list_2.at[index, HLTHRI_CITY_COL_NAME] = ridoh_city\n",
    "                        final_provider_list_2.at[index, HLTHRI_STATE_COL_NAME] = ridoh_state\n",
    "\n",
    "                        driver.execute_script(\"arguments[0].removeAttribute('target');\", link)\n",
    "                        link.click()\n",
    "                        break\n",
    "                    except StaleElementReferenceException:\n",
    "                        print(\"Stale element reference, re-fetching the links.\")\n",
    "\n",
    "                issue_date = get_element_text(wait, \"Issue date\", \"_ctl15__ctl1_issue_date\", \"_ctl17__ctl1_issue_date\")\n",
    "                expiration_date = get_element_text(wait, \"Expiration date\", \"_ctl15__ctl1_expiration_date\", \"_ctl17__ctl1_expiration_date\")\n",
    "                school_name = get_element_text(wait, \"School name\", \"_ctl25__ctl1_schl_name\", \"_ctl27__ctl1_schl_name\")\n",
    "                graduated_date = get_element_text(wait, \"Graduation date\", \"_ctl25__ctl1_date_to\", \"_ctl27__ctl1_date_to\")\n",
    "                specialty_info = get_element_text(wait, \"Specialty Information\", \"_ctl33__ctl1_authority_code\")\n",
    "\n",
    "                if all(x is None for x in [issue_date, expiration_date, school_name, graduated_date, specialty_info]) and methodology == METHOD_LIC:\n",
    "                    skip_license_search = True\n",
    "                    raise Exception(\"Nothing was found based on a license search - retrying based on name\")\n",
    "\n",
    "\n",
    "                final_provider_list_2.at[index, HLTHRI_ISSUE_DATE_COL_NAME] = issue_date\n",
    "                final_provider_list_2.at[index, HLTHRI_EXP_DATE_COL_NAME] = expiration_date\n",
    "                # Of note, school name is particularly complicated for non-physicians - it seems to capture more school info \n",
    "                final_provider_list_2.at[index, HLTHRI_SCHOOL_NAME_COL_NAME] = school_name\n",
    "                final_provider_list_2.at[index, HLTHRI_GRAD_DATE_COL_NAME] = graduated_date\n",
    "                # Of note, physicians can in fact have multiple specialties list - current logic doesn't handle this\n",
    "                final_provider_list_2.at[index, HLTHRI_SPEC_INFO_COL_NAME] = specialty_info\n",
    "                final_provider_list_2.at[index, HLTHRI_METHD_COL_NAME] = methodology\n",
    "                driver.quit()\n",
    "                break\n",
    "        except Exception as e:\n",
    "            print(f\"Attempt {attempt + 1}/{MAX_RETRIES_PER_CLINICIAN} - Error processing row {index}: {e}\")\n",
    "            if attempt == MAX_RETRIES_PER_CLINICIAN - 1:\n",
    "                print(\"Max retries reached. Skipping to the next row.\")\n",
    "                driver.quit()\n",
    "            else:\n",
    "                continue\n",
    "\n",
    "final_provider_list_2.to_csv('final_modified_dataframe.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Geographic Analysis and Breakdown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_provider_list_3 = import_csv_gracefully('.', 'final_modified_dataframe.csv')\n",
    "school_data = import_csv_gracefully(INPUT_FILES_DIRECTORY, 'educational_institutional_lookup.csv')\n",
    "\n",
    "combined = pd.merge(final_provider_list_3, school_data, left_on= HLTHRI_SCHOOL_NAME_COL_NAME, right_on='Institution', how='left')\n",
    "# combined.to_csv('final_modified_dataframe_with_state.csv', index=False)\n",
    "import plotly.graph_objects as go\n",
    "import ipywidgets as widgets\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "ALL_VALUE = 'ALL'\n",
    "\n",
    "decade_color_mapping = {\n",
    "    '2000.0': '#1f77b4',  # Blue\n",
    "    '2010.0': '#ff7f0e',  # Orange\n",
    "    '2020.0': '#2ca02c',  # Green\n",
    "    '1990.0': '#d62728',  # Red\n",
    "    '1980.0': '#9467bd',  # Purple\n",
    "    '1970.0': '#8c564b',  # Brown\n",
    "    '1960.0': '#e377c2',  # Pink\n",
    "    '1950.0': '#7f7f7f',  # Gray\n",
    "    '1940.0': '#bcbd22',  # Olive\n",
    "    '1930.0': '#17becf',  # Cyan\n",
    "    'OTHER' : '#f0eded'\n",
    "}\n",
    "\n",
    "state_color_mapping = {\n",
    "    'OTHER' : '#f0eded',\n",
    "    'Massachusetts': '#1f77b4',  # Blue\n",
    "    'Rhode Island': '#ff7f0e',  # Orange\n",
    "    'New York': '#2ca02c',  # Green\n",
    "    'Pennsylvania': '#d62728',  # Red\n",
    "    'Maine': '#9467bd',  # Purple\n",
    "    'Connecticut': '#8c564b',  # Brown\n",
    "    'North Carolina': '#e377c2',  # Pink\n",
    "    'California': '#7f7f7f',  # Gray\n",
    "    'Missouri': '#bcbd22',  # Olive\n",
    "    'Texas': '#17becf',  # Cyan\n",
    "    'Ohio': '#ffbb78',  # Light Orange\n",
    "    'District of Columbia': '#98df8a',  # Light Green\n",
    "    'New Jersey': '#ff9896',  # Light Red\n",
    "    'Kentucky': '#c5b0d5',  # Lavender\n",
    "    'Colorado': '#f7b6d2',  # Light Pink\n",
    "    'Florida': '#c49c94',  # Light Brown\n",
    "    'Georgia': '#f4c542',  # Light Olive\n",
    "    'Illinois': '#17b3c2',  # Teal\n",
    "    'Maryland': '#7f7f7f',  # Dark Gray\n",
    "    'Michigan': '#bd9d29',  # Mustard Yellow\n",
    "\n",
    "}\n",
    "\n",
    "def get_colors(items, color_mapping):\n",
    "    return [color_mapping.get(item, '#000000') for item in items]  # Default to black if no mapping exists\n",
    "\n",
    "\n",
    "combined.fillna('Unknown', inplace=True)\n",
    "combined[HLTHRI_GRAD_DATE_COL_NAME] = pd.to_datetime(combined[HLTHRI_GRAD_DATE_COL_NAME], errors='coerce')\n",
    "combined['Graduation_Decade'] = (combined[HLTHRI_GRAD_DATE_COL_NAME].dt.year // 10 * 10).astype(str)\n",
    "\n",
    "excluded_credentials = [ROLE_STUDENT, ROLE_ORGANIZATION, ROLE_MISC_OTHER, ROLE_CASE_MGMT, ROLE_PSYCHOLOGIST, ROLE_PODIATRY, ROLE_OPTOMETRY]\n",
    "filtered_credentials = [cred for cred in combined[RIDOH_CREDENTIAL_COLUMN_NAME].unique() if cred not in excluded_credentials]\n",
    "\n",
    "credential_dropdown = widgets.Dropdown(\n",
    "    options=filtered_credentials,\n",
    "    description=RIDOH_CREDENTIAL_COLUMN_NAME + ':',\n",
    "    value=ROLE_MD_DO\n",
    ")\n",
    "\n",
    "country_dropdown = widgets.Dropdown(\n",
    "    options=[ALL_VALUE] + combined['Institution_Country'].unique().tolist(),  # Add 'All' option\n",
    "    description='Country:',\n",
    "    value=ALL_VALUE  # Default value is 'All'\n",
    ")\n",
    "\n",
    "specialty_dropdown = widgets.Dropdown(\n",
    "    options=[ALL_VALUE] + combined[SPECIALTY_COLUMN_NAME].unique().tolist(),  # Add 'All' option\n",
    "    description= SPECIALTY_COLUMN_NAME + ':',\n",
    "    value=ALL_VALUE  # Default value is 'All'\n",
    ")\n",
    "\n",
    "# Create a dropdown for number of slices\n",
    "num_slices_dropdown = widgets.Dropdown(\n",
    "    options=[3, 4, 5, 6, 7, 8, 9, 10],  # Options for number of slices\n",
    "    description='Num Slices:',\n",
    "    value=8  # Default value\n",
    ")\n",
    "\n",
    "# Output widget to hold pie charts\n",
    "output_pie_charts = widgets.Output()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def aggregate_slices(df, column, num_slices=8):\n",
    "    counts = df[column].value_counts()\n",
    "    top_counts = counts.nlargest(num_slices - 1)\n",
    "    other_count = counts[counts.index.isin(top_counts.index) == False].sum()\n",
    "    \n",
    "    # Create a new Series to hold the final counts, including \"OTHER\" if applicable\n",
    "    aggregated_counts = top_counts.copy()\n",
    "    if other_count > 0:\n",
    "        aggregated_counts['OTHER'] = other_count\n",
    "    \n",
    "    # Sort the aggregated counts by value in descending order\n",
    "    sorted_aggregated_counts = aggregated_counts.sort_values(ascending=False)\n",
    "    \n",
    "    return sorted_aggregated_counts.index, sorted_aggregated_counts.values\n",
    "\n",
    "# Function to update pie charts based on selected credential, country, specialty, and num_slices\n",
    "def update_pie_charts(selected_credential, selected_country, selected_specialty, num_slices):\n",
    "    with output_pie_charts:\n",
    "        output_pie_charts.clear_output()  # Clear the current output\n",
    "\n",
    "        # Filter dataframe based on selected credential\n",
    "        filtered_df = combined[combined[RIDOH_CREDENTIAL_COLUMN_NAME] == selected_credential]\n",
    "\n",
    "        # Further filter by selected country if it's not 'All'\n",
    "        if selected_country != ALL_VALUE:\n",
    "            filtered_df = filtered_df[filtered_df['Institution_Country'] == selected_country]\n",
    "        \n",
    "        # Further filter by selected specialty if it's not 'All'\n",
    "        if selected_specialty != ALL_VALUE:\n",
    "            filtered_df = filtered_df[filtered_df[SPECIALTY_COLUMN_NAME] == selected_specialty]\n",
    "\n",
    "        # Create pie charts while excluding \"Unknown\" for each column independently\n",
    "        country_filtered = filtered_df[filtered_df['Institution_Country'] != \"Unknown\"]\n",
    "        state_filtered = filtered_df[filtered_df['Institution_State'] != \"Unknown\"]\n",
    "        region_filtered = filtered_df[filtered_df['Institution_Region_Census'] != \"Unknown\"]\n",
    "        division_filtered = filtered_df[filtered_df['Institution_Division_Census'] != \"Unknown\"]\n",
    "        decade_filtered = filtered_df[filtered_df['Graduation_Decade'] != \"Unknown\"]  # Exclude \"Unknown\"\n",
    "        decade_filtered = decade_filtered[decade_filtered['Graduation_Decade'] != 'nan']\n",
    "\n",
    "        # Aggregate slices and create pie charts\n",
    "        country_names, country_values = aggregate_slices(country_filtered, 'Institution_Country', num_slices)\n",
    "        state_names, state_values = aggregate_slices(state_filtered, 'Institution_State', num_slices)\n",
    "        region_names, region_values = aggregate_slices(region_filtered, 'Institution_Region_Census', num_slices)\n",
    "        division_names, division_values = aggregate_slices(division_filtered, 'Institution_Division_Census', num_slices)\n",
    "        decade_names, decade_values = aggregate_slices(decade_filtered, 'Graduation_Decade', num_slices)  # New decade data\n",
    "\n",
    "        # Create pie charts\n",
    "        fig_country = go.FigureWidget(px.pie(names=country_names, values=country_values, title='Institution Country Breakdown', width=400, height=300))\n",
    "        fig_state = go.FigureWidget(px.pie(\n",
    "            names=state_names, \n",
    "            values=state_values, \n",
    "            title='Institution State Breakdown (if USA)', \n",
    "            width=400, \n",
    "            height=300,\n",
    "            color_discrete_sequence=get_colors(state_names, state_color_mapping) # ['#1f77b4', '#ff7f0e', '#2ca02c', '#d62728', '#9467bd', '#8c564b', '#e377c2', '#7f7f7f', '#bcbd22', '#17becf', '#f0eded']\n",
    "        ))\n",
    "        fig_region = go.FigureWidget(px.pie(names=region_names, values=region_values, title='Institution Region Breakdown (if USA)', width=400, height=300))\n",
    "        fig_division = go.FigureWidget(px.pie(names=division_names, values=division_values, title='Institution Division Breakdown (if USA)', width=400, height=300))\n",
    "        fig_decade = go.FigureWidget(px.pie(\n",
    "            names=decade_names,\n",
    "            values=decade_values,\n",
    "            title='Graduation Decade Breakdown',\n",
    "            width=400,\n",
    "            height=300,\n",
    "            color_discrete_sequence=get_colors(decade_names, decade_color_mapping)\n",
    "        ))\n",
    "\n",
    "        # Display charts inline using HBox\n",
    "        display(widgets.HBox([fig_country, fig_decade]))\n",
    "        display(widgets.HBox([fig_state, fig_region, fig_division, ]))\n",
    "\n",
    "# Callback function when a dropdown value changes\n",
    "def on_dropdown_change(change):\n",
    "    update_pie_charts(credential_dropdown.value, country_dropdown.value, specialty_dropdown.value, num_slices_dropdown.value)\n",
    "\n",
    "# Observe changes in all dropdowns\n",
    "credential_dropdown.observe(on_dropdown_change, names='value')\n",
    "country_dropdown.observe(on_dropdown_change, names='value')\n",
    "specialty_dropdown.observe(on_dropdown_change, names='value')\n",
    "num_slices_dropdown.observe(on_dropdown_change, names='value')\n",
    "\n",
    "# Display the dropdowns and output widget\n",
    "display(widgets.HBox([credential_dropdown, country_dropdown, specialty_dropdown, num_slices_dropdown]))\n",
    "display(output_pie_charts)\n",
    "\n",
    "# Initial pie charts for the default dropdown values\n",
    "update_pie_charts(credential_dropdown.value, country_dropdown.value, specialty_dropdown.value, num_slices_dropdown.value)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analysis of Clinician Productivity including bins/distributions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_provider_list.loc[:, 'Total_Distinct_Medical_Claim_Count_PC_EQ'] = round(final_provider_list['APCD_CORE_PC_CLAIMS_COUNT'] / 900,1)\n",
    "final_provider_list.loc[:, 'APCD_TOTAL_CLAIMS_ALL_EQ'] = round(final_provider_list[APCD_TOTAL_CLAIMS_ALL_COL_NAME] / 3360,1)\n",
    "\n",
    "final_provider_list[SPECIALTY_COLUMN_NAME] = final_provider_list[SPECIALTY_COLUMN_NAME].replace('', 'Undetermined')  \n",
    "final_provider_list[SPECIALTY_COLUMN_NAME] = final_provider_list[SPECIALTY_COLUMN_NAME].fillna('Undetermined')  \n",
    "\n",
    "\n",
    "final_provider_list['Provider Gender Code'] = final_provider_list[NPPES_PREFIX + 'Provider Gender Code'].replace('', 'Unknown')  \n",
    "final_provider_list['Provider Gender Code'] = final_provider_list[NPPES_PREFIX + 'Provider Gender Code'].fillna('Unknown')  \n",
    "\n",
    "physicians = final_provider_list[final_provider_list[RIDOH_CREDENTIAL_COLUMN_NAME] == 'Physician']\n",
    "\n",
    "group_by_dropdown = widgets.Dropdown(\n",
    "    options=['Specialty', 'Specialty and Gender'],\n",
    "    description='Group by:',\n",
    "    value='Specialty and Gender',  # Default value\n",
    ")\n",
    "\n",
    "pc_to_fte_division_factor = widgets.IntText(\n",
    "    value=900,  # Default value\n",
    "    step=10,    # Step size\n",
    "    layout=widgets.Layout(width='100px')\n",
    ")\n",
    "\n",
    "pc_to_fte_division_factor_label = widgets.Label(\n",
    "    value='# PC Claims For 1 FTE:',\n",
    "    layout=widgets.Layout(width='150px', height='30px', display='flex', align_items='center')\n",
    ")\n",
    "\n",
    "all_to_fte_division_factor = widgets.IntText(\n",
    "    value=3360,  # Default value\n",
    "    step=50,    # Step size\n",
    "    layout=widgets.Layout(width='100px')\n",
    ")\n",
    "\n",
    "all_to_fte_division_factor_label = widgets.Label(\n",
    "    value='# All Claims For 1 FTE:',\n",
    "    layout=widgets.Layout(width='150px', height='30px', display='flex', align_items='center')\n",
    ")\n",
    "\n",
    "controls = widgets.HBox([group_by_dropdown, pc_to_fte_division_factor_label, pc_to_fte_division_factor, all_to_fte_division_factor_label, all_to_fte_division_factor])\n",
    "\n",
    "\n",
    "def display_grouped(group_by, pc_to_fte_division_factor, all_to_fte_division_factor):\n",
    "    grouping_keys = [SPECIALTY_COLUMN_NAME]\n",
    "    if group_by == 'Specialty and Gender':\n",
    "        grouping_keys.append('Provider Gender Code')\n",
    "\n",
    "    grouped_df = physicians.groupby(grouping_keys).agg(\n",
    "            Total_PC_Claims=('APCD_CORE_PC_CLAIMS_COUNT', 'sum'),\n",
    "            Total_All_Claims=(APCD_TOTAL_CLAIMS_ALL_COL_NAME, 'sum'),\n",
    "            Number_Providers=(SPECIALTY_COLUMN_NAME, 'size'),\n",
    "        ).reset_index()\n",
    "    \n",
    "    grouped_df = grouped_df.rename(columns={\n",
    "        'Provider Gender Code': 'Gender',\n",
    "        'Total_PC_Claims': 'Total Primary Care Claims',\n",
    "        'Total_All_Claims': 'Total Claims',\n",
    "        'Number_Providers': 'Number of Providers'\n",
    "    })\n",
    "\n",
    "    total_row_values = grouped_df[['Total Primary Care Claims', 'Total Claims', 'Number of Providers']].sum().tolist()\n",
    "    if group_by == 'Specialty and Gender':\n",
    "        total_row_values.insert(0, '') \n",
    "\n",
    "    total_row = pd.DataFrame([['Total'] + total_row_values], columns=grouped_df.columns)\n",
    "    grouped_df = pd.concat([grouped_df, total_row], ignore_index=True)\n",
    "\n",
    "    if pc_to_fte_division_factor > 0:  # Ensure division factor is greater than zero to avoid division by zero\n",
    "        grouped_df['Full Equiv. by Primary Care Claims'] = grouped_df['Total Primary Care Claims'] / pc_to_fte_division_factor\n",
    "\n",
    "    if all_to_fte_division_factor > 0:\n",
    "        grouped_df['Full Equiv. by All Claims'] = grouped_df['Total Claims'] / all_to_fte_division_factor\n",
    "\n",
    "\n",
    "    def highlight_total(s):\n",
    "        is_total_row = s.iloc[0] == 'Total'\n",
    "        return ['font-weight: bold; border: 2px solid black;background-color: lightblue' if is_total_row else '' for _ in s]\n",
    "    \n",
    "    styled_df = grouped_df.style.apply(highlight_total, axis=1) \\\n",
    "        .format(\"{:.1f}\", subset=['Total Primary Care Claims', 'Total Claims', 'Number of Providers', 'Full Equiv. by Primary Care Claims','Full Equiv. by All Claims']) \n",
    "    \n",
    "    display(styled_df)\n",
    "\n",
    "output = widgets.interactive_output(display_grouped, {\n",
    "    'group_by': group_by_dropdown,\n",
    "    'pc_to_fte_division_factor': pc_to_fte_division_factor,\n",
    "    'all_to_fte_division_factor':all_to_fte_division_factor,\n",
    "})\n",
    "\n",
    "display(controls, output)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"There are many ways to think about the patient panel size for these physicians.\\n\")\n",
    "\n",
    "print(\"By looking at primary care claims:\\n\")\n",
    "print(\"When considering 1 year number of members by just primary care claims: \", physicians['APCD_MEMBER_ID_CORE_PC_ONE_YEAR_COUNT'].mean())\n",
    "print(\"When considering 1 year number of members by just primary care claims (looking at internal id): \", physicians['APCD_INTERNAL_MEMBER_ID_CORE_PC_ONE_YEAR_COUNT'].mean())\n",
    "print(\"When considering 2 year number of members by just primary care claims: \", physicians['APCD_MEMBER_ID_CORE_PC_TWO_YEAR_COUNT'].mean())\n",
    "print(\"When considering 2 year number of members by just primary care claims(looking at internal id): \", physicians['APCD_INTERNAL_MEMBER_ID_CORE_PC_TWO_YEAR_COUNT'].mean())\n",
    "print(\"For context, the average number of primary care claims over 1 year was: \", physicians['APCD_CORE_PC_CLAIMS_COUNT'].mean())\n",
    "print(\"-------------------------------------------------------------\")\n",
    "\n",
    "print(\"By looking at all claims:\\n\")\n",
    "print(\"When considering 1 year number of members by all claims: \", physicians['APCD_MEMBER_ID_ALL_COUNT'].mean())\n",
    "print(\"When considering 1 year number of members by all claims (looking at internal id): \", physicians['APCD_INTERNAL_MEMBER_ID_ALL_COUNT'].mean())\n",
    "\n",
    "print(\"When considering 2 year number of members by all claims: \", physicians['APCD_MEMBER_ID_ALL_TWO_YEAR_COUNT'].mean())\n",
    "print(\"When considering 2 year number of members by all claims (looking at internal id): \", physicians['APCD_INTERNAL_MEMBER_ID_ALL_TWO_YEAR_COUNT'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from ipywidgets import interact, SelectMultiple\n",
    "\n",
    "bin_edges = [0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, np.inf]\n",
    "bin_labels = ['0-0.1', '0.1-0.2', '0.2-0.3', '0.3-0.4', '0.4-0.5', '0.5-0.6', '0.6-0.7', '0.7-0.8', '0.8-0.9', '0.9+']\n",
    "physicians.loc[:, 'Bin'] = pd.cut(physicians['Total_Distinct_Medical_Claim_Count_PC_EQ'], bins=bin_edges, labels=bin_labels, right=False)\n",
    "\n",
    "def plot_histogram(specialties=None):\n",
    "    # Filter by selected specialties if any are provided\n",
    "    if 'None' not in specialties:\n",
    "        df_filtered = physicians[physicians[SPECIALTY_COLUMN_NAME].isin(specialties)]\n",
    "    else:\n",
    "        df_filtered = physicians  # No specialty selected, plot entire dataset\n",
    "\n",
    "    bin_counts = df_filtered['Bin'].value_counts(sort=False)\n",
    "    \n",
    "    # Plotting\n",
    "    plt.figure(figsize=(10, 6))\n",
    "    bin_counts.plot(kind='bar', edgecolor='black')\n",
    "    \n",
    "    # Customizing the plot\n",
    "    plt.title('Distribution of Total Distinct Medical Claim Count by Bin for Specialty: ' + str(specialties[0]))\n",
    "    plt.xlabel('Bins')\n",
    "    plt.ylabel('Count')\n",
    "    plt.xticks(rotation=45)\n",
    "    \n",
    "    plt.show()\n",
    "\n",
    "specialties = ['None'] + physicians[SPECIALTY_COLUMN_NAME].unique().tolist()\n",
    "interact(plot_histogram, specialties=SelectMultiple(options=specialties, description='Specialties', value=('None',)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"To summarize:\")\n",
    "print(\"------------------------------------\")\n",
    "print(final_provider_list[RIDOH_CREDENTIAL_COLUMN_NAME].value_counts())\n",
    "print(\"------------------------------------\")\n",
    "print(final_provider_list[SPECIALTY_COLUMN_NAME].value_counts())\n",
    "print(\"Physician FTE equivalents (by PC claims):\", final_provider_list[final_provider_list[RIDOH_CREDENTIAL_COLUMN_NAME] == 'Physician']['APCD_CORE_PC_CLAIMS_COUNT'].sum() / (900))\n",
    "print(\"Physician FTE equivalents (by PC claims):\", final_provider_list[final_provider_list[RIDOH_CREDENTIAL_COLUMN_NAME] == 'Physician'][APCD_TOTAL_CLAIMS_ALL_COL_NAME].sum() / (3360))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Additional Datasources to consider integrating:\n",
    "\n",
    "https://data.cms.gov/tools/medicare-revalidation-list/provider/O20040203000479 \n",
    "https://data.cms.gov/api-docs "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scratch Sanity Check Analysis\n",
    "``` sql\n",
    "SELECT Rendering_Provider_NPI, COUNT(*) FROM medical_claim mc\n",
    "WHERE Rendering_Provider_NPI IN (\n",
    "    '1306147111', -- Concentra Urgent Care\n",
    "    '1649511841', -- Minute Clinic \n",
    "    '1316402746', -- Minute Clinic\n",
    "    '1134191836', -- Minute Clinic\n",
    "    '1457470858', -- MinuteClinic\n",
    "    '1376891416', -- Carewell Urgent Care\n",
    "    '1528221470', -- NORTH PROVIDENCE URGENT CARE \n",
    "    '1568555183', -- SCMG EXPRESS CARE EAST GREENWICH\n",
    "    '1437119005' -- Lincoln Urgent Care Center\n",
    ")\n",
    "AND mc.first_service_dt >= '2022-06-01'\n",
    "AND mc.procedure_code IN ('G0402', 'G0438', 'G0439', '99381', '99382', '99383', '99384', '99385', '99386', '99387', '99391', '99392', '99393', '99394', '99395', '99396', '99397', '90471')\n",
    "GROUP BY Rendering_Provider_NPI\n",
    "\n",
    "\n",
    "-- Attending_Internal_Provider_Id, Billing_Internal_Provider_Id, Ordering_Internal_Provider_Id, Operating_Internal_Provider_Id, Other_Internal_Provider_Id, Referring_Internal_Provier_Id \n",
    "SELECT Attending_Internal_Provider_Id, Rendering_Provider_NPI, COUNT(*) FROM medical_claim mc\n",
    "WHERE Attending_Internal_Provider_Id IN (\n",
    "    SELECT Attending_Internal_Provider_Id FROM Provider_Master where NPI IN (\n",
    "        '1306147111', -- Concentra Urgent Care -- 10484848 \n",
    "        '1649511841', -- Minute Clinic -- 12509669\n",
    "        '1316402746', -- Minute Clinic -- 12509707\n",
    "        '1134191836', -- Minute Clinic -- 7950009\n",
    "        '1457470858', -- MinuteClinic -- 1018675\n",
    "        '1376891416', -- Carewell Urgent Care -- 1069741\n",
    "        '1528221470', -- NORTH PROVIDENCE URGENT CARE -- 9149369\n",
    "        '1568555183', -- SCMG EXPRESS CARE EAST GREENWICH -- 11531596\n",
    "        '1437119005' -- Lincoln Urgent Care Center -- 11531596\n",
    "    ) \n",
    ") \n",
    "AND Attending_Internal_Provider_Id <> '-1'\n",
    "AND mc.first_service_dt >= '2022-06-01'\n",
    "AND mc.procedure_code IN ('G0402', 'G0438', 'G0439', '99381', '99382', '99383', '99384', '99385', '99386', '99387', '99391',\n",
    " '99392', '99393', '99394', '99395', '99396', '99397', '90471')\n",
    "GROUP BY Attending_Internal_Provider_Id, Rendering_Provider_NPI\n",
    "```\n",
    "    \n"
   ]
  }
 ],
 "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}