claim_lines_per_claim.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4e7e0085",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-15T13:13:13.262794Z",
     "start_time": "2022-08-15T13:13:13.251794Z"
    }
   },
   "source": [
    "# Overview\n",
    "The purpose of this analysis is to compare counts of claim lines per claim by service type. <br>\n",
    "Visit Data.gov to download the data used for this analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03595864",
   "metadata": {},
   "source": [
    "# Load libararies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2bf60b36",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-04-05T15:33:21.100907Z",
     "start_time": "2023-04-05T15:33:17.874854Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:90% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "from pathlib import Path\n",
    "import plotly.express as px\n",
    "import plotly.figure_factory as ff\n",
    "import plotly.io as pio\n",
    "pio.renderers.default='iframe'\n",
    "from plotly.subplots import make_subplots\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "plt.style.use('ggplot')\n",
    "from matplotlib.pyplot import figure\n",
    "from matplotlib import rcParams\n",
    "matplotlib.rcParams['figure.figsize'] = (12,8)\n",
    "\n",
    "from IPython.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:90% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4807f85f",
   "metadata": {},
   "source": [
    "# Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "76ef1873",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-04-05T15:33:30.104517Z",
     "start_time": "2023-04-05T15:33:21.102898Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "synthetic beneficiary (8671, 1)\n",
      "synthetic inpatient (58066, 3)\n",
      "synthetic outpatient (575092, 3)\n",
      "synthetic carrier (1121004, 3)\n",
      "synthetic dme (103828, 3)\n",
      "synthetic hha (6215, 3)\n",
      "synthetic hospice (12107, 3)\n",
      "synthetic snf (12548, 3)\n"
     ]
    }
   ],
   "source": [
    "# load data\n",
    "synthetic_bene_df = pd.read_csv('data/beneficiary_2022.csv', sep=\"|\", usecols=['BENE_ID'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'}, low_memory=False)\n",
    "synthetic_inpatient_df = pd.read_csv('data/inpatient.csv', sep=\"|\", usecols=['CLM_ID','BENE_ID', 'CLM_FROM_DT'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'}, low_memory=False)\n",
    "synthetic_outpatient_df = pd.read_csv('data/outpatient.csv', sep=\"|\", usecols=['CLM_ID','BENE_ID', 'CLM_FROM_DT'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'}, low_memory=False)\n",
    "synthetic_carrier_df = pd.read_csv('data/carrier.csv', sep=\"|\", usecols=['CLM_ID','BENE_ID', 'CLM_FROM_DT'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'}, low_memory=False)\n",
    "synthetic_dme_df = pd.read_csv('data/dme.csv', sep=\"|\", usecols=['CLM_ID','BENE_ID', 'CLM_FROM_DT'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'}, low_memory=False)\n",
    "synthetic_hha_df = pd.read_csv('data/hha.csv', sep=\"|\", usecols=['CLM_ID','BENE_ID', 'CLM_FROM_DT'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'},low_memory=False)\n",
    "synthetic_hospice_df = pd.read_csv('data/hospice.csv', sep=\"|\", usecols=['CLM_ID','BENE_ID','CLM_FROM_DT'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'}, low_memory=False)\n",
    "synthetic_snf_df = pd.read_csv('data/snf.csv', sep=\"|\", usecols=['CLM_ID','BENE_ID', 'CLM_FROM_DT'], dtype={'CLM_ID':'str','BENE_ID':'str','CLM_FROM_DT':'str'}, low_memory=False)\n",
    "\n",
    "print(f'synthetic beneficiary {synthetic_bene_df.shape}')\n",
    "print(f'synthetic inpatient {synthetic_inpatient_df.shape}')\n",
    "print(f'synthetic outpatient {synthetic_outpatient_df.shape}')\n",
    "print(f'synthetic carrier {synthetic_carrier_df.shape}')\n",
    "print(f'synthetic dme {synthetic_dme_df.shape}')\n",
    "print(f'synthetic hha {synthetic_hha_df.shape}')\n",
    "print(f'synthetic hospice {synthetic_hospice_df.shape}')\n",
    "print(f'synthetic snf {synthetic_snf_df.shape}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4d9c89a",
   "metadata": {},
   "source": [
    "# Synthetic number of claim lines per claim"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d69be077",
   "metadata": {},
   "source": [
    "## filter synthetic data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "961dc6ca",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-04-05T15:33:31.697776Z",
     "start_time": "2023-04-05T15:33:30.105516Z"
    }
   },
   "outputs": [],
   "source": [
    "# CARRIER\n",
    "values = [2022]\n",
    "synthetic_carrier_df['CLM_FROM_DT'] = pd.to_datetime(synthetic_carrier_df['CLM_FROM_DT'])\n",
    "synthetic_carrier_df['CLM_FROM_YR'] = synthetic_carrier_df['CLM_FROM_DT'].dt.year\n",
    "synthetic_carrier_df = synthetic_carrier_df[synthetic_carrier_df.CLM_FROM_YR.isin(values)]\n",
    "synthetic_carrier_df = pd.merge(synthetic_bene_df, synthetic_carrier_df, on ='BENE_ID', how =\"left\")\n",
    "synthetic_carrier_df = synthetic_carrier_df.groupby('CLM_ID').size().to_frame().reset_index().rename(columns={0: \"tot_claim_lines\"}) \n",
    "synthetic_carrier_df = synthetic_carrier_df[synthetic_carrier_df.tot_claim_lines < synthetic_carrier_df.tot_claim_lines.quantile(.999)]\n",
    "\n",
    "# INPATIENT\n",
    "values = [2022]\n",
    "synthetic_inpatient_df['CLM_FROM_DT'] = pd.to_datetime(synthetic_inpatient_df['CLM_FROM_DT'])\n",
    "synthetic_inpatient_df['CLM_FROM_YR'] = synthetic_inpatient_df['CLM_FROM_DT'].dt.year\n",
    "synthetic_inpatient_df = synthetic_inpatient_df[synthetic_inpatient_df.CLM_FROM_YR.isin(values)]\n",
    "synthetic_inpatient_df = pd.merge(synthetic_bene_df, synthetic_inpatient_df, on ='BENE_ID', how =\"left\")\n",
    "synthetic_inpatient_df = synthetic_inpatient_df.groupby('CLM_ID').size().to_frame().reset_index().rename(columns={0: \"tot_claim_lines\"}) \n",
    "synthetic_inpatient_df = synthetic_inpatient_df[synthetic_inpatient_df.tot_claim_lines < synthetic_inpatient_df.tot_claim_lines.quantile(.999)]\n",
    "\n",
    "# OUTPATIENT\n",
    "values = [2022]\n",
    "synthetic_outpatient_df['CLM_FROM_DT'] = pd.to_datetime(synthetic_outpatient_df['CLM_FROM_DT'])\n",
    "synthetic_outpatient_df['CLM_FROM_YR'] = synthetic_outpatient_df['CLM_FROM_DT'].dt.year\n",
    "synthetic_outpatient_df = synthetic_outpatient_df[synthetic_outpatient_df.CLM_FROM_YR.isin(values)]\n",
    "synthetic_outpatient_df = pd.merge(synthetic_bene_df, synthetic_outpatient_df, on ='BENE_ID', how =\"left\")\n",
    "synthetic_outpatient_df = synthetic_outpatient_df.groupby('CLM_ID').size().to_frame().reset_index().rename(columns={0: \"tot_claim_lines\"}) \n",
    "synthetic_outpatient_df = synthetic_outpatient_df[synthetic_outpatient_df.tot_claim_lines < synthetic_outpatient_df.tot_claim_lines.quantile(.999)]\n",
    "\n",
    "# DME\n",
    "values = [2022]\n",
    "synthetic_dme_df['CLM_FROM_DT'] = pd.to_datetime(synthetic_dme_df['CLM_FROM_DT'])\n",
    "synthetic_dme_df['CLM_FROM_YR'] = synthetic_dme_df['CLM_FROM_DT'].dt.year\n",
    "synthetic_dme_df = synthetic_dme_df[synthetic_dme_df.CLM_FROM_YR.isin(values)]\n",
    "synthetic_dme_df = pd.merge(synthetic_bene_df, synthetic_dme_df, on ='BENE_ID', how =\"left\")\n",
    "synthetic_dme_df = synthetic_dme_df.groupby('CLM_ID').size().to_frame().reset_index().rename(columns={0: \"tot_claim_lines\"}) \n",
    "# synthetic_dme_df = synthetic_dme_df[synthetic_dme_df.tot_claim_lines < synthetic_dme_df.tot_claim_lines.quantile(.999)]\n",
    "\n",
    "# SNF\n",
    "values = [2022]\n",
    "synthetic_snf_df['CLM_FROM_DT'] = pd.to_datetime(synthetic_snf_df['CLM_FROM_DT'])\n",
    "synthetic_snf_df['CLM_FROM_YR'] = synthetic_snf_df['CLM_FROM_DT'].dt.year\n",
    "synthetic_snf_df = synthetic_snf_df[synthetic_snf_df.CLM_FROM_YR.isin(values)]\n",
    "synthetic_snf_df = pd.merge(synthetic_bene_df, synthetic_snf_df, on ='BENE_ID', how =\"left\")\n",
    "synthetic_snf_df = synthetic_snf_df.groupby('CLM_ID').size().to_frame().reset_index().rename(columns={0: \"tot_claim_lines\"}) \n",
    "synthetic_snf_df = synthetic_snf_df[synthetic_snf_df.tot_claim_lines < synthetic_snf_df.tot_claim_lines.quantile(.999)]\n",
    "\n",
    "# HOSPICE\n",
    "values = [2022]\n",
    "synthetic_hospice_df['CLM_FROM_DT'] = pd.to_datetime(synthetic_hospice_df['CLM_FROM_DT'])\n",
    "synthetic_hospice_df['CLM_FROM_YR'] = synthetic_hospice_df['CLM_FROM_DT'].dt.year\n",
    "synthetic_hospice_df = synthetic_hospice_df[synthetic_hospice_df.CLM_FROM_YR.isin(values)]\n",
    "synthetic_hospice_df = pd.merge(synthetic_bene_df, synthetic_hospice_df, on ='BENE_ID', how =\"left\")\n",
    "synthetic_hospice_df = synthetic_hospice_df.groupby('CLM_ID').size().to_frame().reset_index().rename(columns={0: \"tot_claim_lines\"}) \n",
    "synthetic_hospice_df = synthetic_hospice_df[synthetic_hospice_df.tot_claim_lines < synthetic_hospice_df.tot_claim_lines.quantile(.999)]\n",
    "\n",
    "# HHA\n",
    "values = [2022]\n",
    "synthetic_hha_df['CLM_FROM_DT'] = pd.to_datetime(synthetic_hha_df['CLM_FROM_DT'])\n",
    "synthetic_hha_df['CLM_FROM_YR'] = synthetic_hha_df['CLM_FROM_DT'].dt.year\n",
    "synthetic_hha_df = synthetic_hha_df[synthetic_hha_df.CLM_FROM_YR.isin(values)]\n",
    "synthetic_hha_df = pd.merge(synthetic_bene_df, synthetic_hha_df, on ='BENE_ID', how =\"left\")\n",
    "synthetic_hha_df = synthetic_hha_df.groupby('CLM_ID').size().to_frame().reset_index().rename(columns={0: \"tot_claim_lines\"}) \n",
    "# synthetic_hha_df = synthetic_hha_df[synthetic_hha_df.tot_claim_lines < synthetic_hha_df.tot_claim_lines.quantile(.999)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8307bb92",
   "metadata": {},
   "source": [
    "## Create plots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "18847e2e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-04-05T15:33:32.029748Z",
     "start_time": "2023-04-05T15:33:31.699742Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe\n",
       "    scrolling=\"no\"\n",
       "    width=\"1120px\"\n",
       "    height=\"820\"\n",
       "    src=\"iframe_figures/figure_4.html\"\n",
       "    frameborder=\"0\"\n",
       "    allowfullscreen\n",
       "></iframe>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# establish subplot axes\n",
    "fig = make_subplots(rows=2, cols=4, horizontal_spacing = 0.07, vertical_spacing = 0.2, subplot_titles=(\"Carrier\",\n",
    "                                                    \"Inpatient\",\n",
    "                                                    \"Outpatient\",\n",
    "                                                    \"DME\",\n",
    "                                                    \"SNF\",\n",
    "                                                    \"Hospice\",\n",
    "                                                    \"HHA\"))\n",
    "\n",
    "\n",
    "# CARRIER \n",
    "# plot histogram\n",
    "fig.append_trace(go.Histogram(\n",
    "    histnorm='percent',\n",
    "    hoverlabel = dict(namelength=0),\n",
    "    marker_color='mediumseagreen',\n",
    "    x=synthetic_carrier_df['tot_claim_lines'],\n",
    "), row=1, col=1)\n",
    "\n",
    "# axes\n",
    "fig.update_xaxes(title_text=\"Total Claim Lines\", row=1, col=1)\n",
    "fig.update_yaxes(title_text=\"Claims (%)\", row=1, col=1)\n",
    "fig.update_yaxes(range = [0,100])\n",
    "\n",
    "\n",
    "# INPATIENT\n",
    "fig.append_trace(go.Histogram(\n",
    "    histnorm='percent',\n",
    "    hoverlabel = dict(namelength=0),\n",
    "    marker_color='mediumseagreen',\n",
    "    x=synthetic_inpatient_df['tot_claim_lines'],\n",
    "), row=1, col=2)\n",
    "\n",
    "fig.update_xaxes(title_text=\"Total Claim Lines\", row=1, col=2)\n",
    "fig.update_yaxes(title_text=\"Claims (%)\", row=1, col=2)\n",
    "fig.update_yaxes(range = [0,100])\n",
    "\n",
    "\n",
    "# OUTPATIENT\n",
    "fig.append_trace(go.Histogram(\n",
    "    histnorm='percent',\n",
    "    hoverlabel = dict(namelength=0),\n",
    "    marker_color='mediumseagreen',\n",
    "    x=synthetic_outpatient_df['tot_claim_lines'],\n",
    "), row=1, col=3)\n",
    "\n",
    "fig.update_xaxes(title_text=\"Total Claim Lines\", row=1, col=3)\n",
    "fig.update_yaxes(title_text=\"Claims (%)\", row=1, col=3)\n",
    "fig.update_yaxes(range = [0,100])\n",
    "\n",
    "\n",
    "# DME\n",
    "fig.append_trace(go.Histogram(\n",
    "    histnorm='percent',\n",
    "    hoverlabel = dict(namelength=0),\n",
    "    marker_color='mediumseagreen',\n",
    "    x=synthetic_dme_df['tot_claim_lines'],\n",
    "), row=1, col=4)\n",
    "\n",
    "fig.update_xaxes(title_text=\"Total Claim Lines\", row=1, col=4)\n",
    "fig.update_yaxes(title_text=\"Claims (%)\", row=1, col=4)\n",
    "fig.update_yaxes(range = [0,100])\n",
    "\n",
    "\n",
    "# SNF\n",
    "fig.append_trace(go.Histogram(\n",
    "    histnorm='percent',\n",
    "    hoverlabel = dict(namelength=0),\n",
    "    marker_color='mediumseagreen',\n",
    "    x=synthetic_snf_df['tot_claim_lines'],\n",
    "), row=2, col=1)\n",
    "\n",
    "fig.update_xaxes(title_text=\"Total Claim Lines\", row=2, col=1)\n",
    "fig.update_yaxes(title_text=\"Claims (%)\", row=2, col=1)\n",
    "fig.update_yaxes(range = [0,100])\n",
    "\n",
    "\n",
    "# HOSPICE\n",
    "fig.append_trace(go.Histogram(\n",
    "    histnorm='percent',\n",
    "    hoverlabel = dict(namelength=0),\n",
    "    marker_color='mediumseagreen',\n",
    "    x=synthetic_hospice_df['tot_claim_lines'],\n",
    "), row=2, col=2)\n",
    "\n",
    "fig.update_xaxes(title_text=\"Total Claim Lines\", row=2, col=2)\n",
    "fig.update_yaxes(title_text=\"Claims (%)\", row=2, col=2)\n",
    "fig.update_yaxes(range = [0,100])\n",
    "\n",
    "\n",
    "# HHA\n",
    "fig.append_trace(go.Histogram(\n",
    "    histnorm='percent',\n",
    "    hoverlabel = dict(namelength=0),\n",
    "    marker_color='mediumseagreen',\n",
    "    x=synthetic_hha_df['tot_claim_lines'],\n",
    "), row=2, col=3)\n",
    "\n",
    "fig.update_xaxes(title_text=\"Total Claim Lines\", row=2, col=3)\n",
    "fig.update_yaxes(title_text=\"Claims (%)\", row=2, col=3)\n",
    "fig.update_yaxes(range = [0,100])\n",
    "\n",
    "\n",
    "\n",
    "# update plot layout\n",
    "fig.update_layout(title_text=\"2022 Percentage of Synthetic Claim Lines per Claim\", bargap=0.30, width=1100, height=800, showlegend=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8f0ae2b6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-04-05T15:33:32.045751Z",
     "start_time": "2023-04-05T15:33:32.030749Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean synthetic carrier claim lines: 12.182962652119178\n",
      "Mean synthetic inpatient claim lines: 3.0274645851402138\n",
      "Mean synthetic outpatient claim lines: 1.3996940923477654\n",
      "Mean synthetic dme claim lines: 2.695109078114004\n",
      "Mean synthetic snf claim lines: 7.713675213675214\n",
      "Mean synthetic hospice claim lines: 11.161073825503356\n",
      "Mean synthetic hha claim lines: 12.65625\n"
     ]
    }
   ],
   "source": [
    "print(f\"Mean synthetic carrier claim lines: {synthetic_carrier_df['tot_claim_lines'].mean()}\")\n",
    "print(f\"Mean synthetic inpatient claim lines: {synthetic_inpatient_df['tot_claim_lines'].mean()}\")\n",
    "print(f\"Mean synthetic outpatient claim lines: {synthetic_outpatient_df['tot_claim_lines'].mean()}\")\n",
    "print(f\"Mean synthetic dme claim lines: {synthetic_dme_df['tot_claim_lines'].mean()}\")\n",
    "print(f\"Mean synthetic snf claim lines: {synthetic_snf_df['tot_claim_lines'].mean()}\")\n",
    "print(f\"Mean synthetic hospice claim lines: {synthetic_hospice_df['tot_claim_lines'].mean()}\")\n",
    "print(f\"Mean synthetic hha claim lines: {synthetic_hha_df['tot_claim_lines'].mean()}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}