<jupyter_start><jupyter_text>Data Science Test - <NAME>This is the results of analyzing the CMS patients data. DirectionsThis dataset contains a provider-level summary of inpatient discharges, average charges, and average Medicare payments for the top 100 Diagnosis-Related Groups (DRGs).1. Which types of DRGs ("DRG.Definition") account for most of the patient population across all facilities?2. For each facility ("Provider.Name"), which DRGs form the largest portion of the patient population?3. Calculate the average Medicare payments per DRG per facility.4. Is there a way to group facilities in a logical way?--- The first thing to do is to load the modules, read in the data Loading Modules<jupyter_code># Importing modules
%matplotlib inline
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from tqdm import tqdm
import time
from sklearn.preprocessing import OneHotEncoder

# Sklearn imports
from sklearn.decomposition import PCA
from sklearn.manifold import TSNE


plt.rc('text', usetex=False)<jupyter_output><empty_output><jupyter_text>Reading in the dataI turned the dataset into a zipped pickle file because it was too large to host on Github.<jupyter_code># Path to data
filepath = '../data/CMS_Medicare_OpenSource_Data.zip'

# Reading in the data
df = pd.read_pickle(filepath, compression='zip')<jupyter_output><empty_output><jupyter_text>Now we can visualize the data in order to better understand it<jupyter_code>df.head(10)<jupyter_output><empty_output><jupyter_text>The columns of the dataset are the following:<jupyter_code>df.columns.tolist()
print('>>> There are `{0}` rows and `{1}` columns'.format(*df.shape))<jupyter_output>>>> There are `163065` rows and `12` columns<jupyter_text>Cleaning data Whitespaces in columnsBy taking a look at the columns, I'll remove any whitespaces from the column names and make the names lowercase.<jupyter_code>df.rename(columns=dict([(x, '_'.join(x.split()).lower()) for x in df.columns.values]), inplace=True)
df.head()<jupyter_output><empty_output><jupyter_text>Odd symbols in columnsRemoving `$` sign from or strings to make it easier to handle<jupyter_code>for col_ii in ['average_covered_charges', 'average_total_payments', 'average_medicare_payments']:
    df.loc[:, col_ii] = df[col_ii].replace( '[\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)<jupyter_output><empty_output><jupyter_text>MiscellaneousThroughout this analysis, I will be using only a few of the columns in the analysis.<jupyter_code>drg_col      = 'drg_definition'
prov_col     = 'provider_name'
disch_col    = 'total_discharges'
medicare_col = 'average_medicare_payments'<jupyter_output><empty_output><jupyter_text>--- 1st Question__Which types of DRGs ("DRG.Definition") account for most of the patient population across all facilities?__To answer this question, one has to look at the `Total Discharges` for each type of DRGs.<jupyter_code>print('There are `{0}` unique types of definitions'.format(df['drg_definition'].unique().shape[0]))
# Number of top DRGs to choose
ntop = 15

## Computing the counts for DRGs in terms of total patient discharges and sorting them from highest to lowest
## I'm only showing the top 10 DRGs
drg_patient_top = df.loc[:, [drg_col, disch_col]].groupby(drg_col).count().sort_values(disch_col, ascending=False)
drg_patient_top.head(10)<jupyter_output><empty_output><jupyter_text>Now I'll be plotting the number of total patient discharges per DRGs.<jupyter_code># Determining the top DRGs
ax_drg = drg_patient_top.head(ntop).sort_values(disch_col).plot(
    kind='barh', title='Top {0} DRG Definitions in terms of patient discharge'.format(ntop),
    legend=False, figsize=(8,5))

# Labels
ax_drg.set_xlabel('Total Discharges')<jupyter_output><empty_output><jupyter_text>Another way to visualize this is looking at the `percentages` of the top DRGs<jupyter_code># Total percentage of the patient discharges for each DRG
drg_perc_col = 'perc_discharges'
drg_patient_top.loc[:, drg_perc_col] = 100 * (drg_patient_top[disch_col]/drg_patient_top[disch_col].sum())

ax_perc_drg = drg_patient_top[drg_perc_col].head(ntop).to_frame().sort_values(drg_perc_col).plot(
    kind='barh', title='Top 15 DRG Definitions in terms of patient discharge'.format(ntop),
    legend=False, figsize=(8,5))

# Labels
ax_perc_drg.set_xlabel('Percentage of total Discharges [%]')<jupyter_output><empty_output><jupyter_text>---  2nd Question__For each facility ("Provider.Name"), which DRGs form the largest portion of the patient population?__<jupyter_code># Selecting only certain column names
prov_main_df = df.loc[:, [prov_col, drg_col, disch_col]]
prov_main_df.head()<jupyter_output><empty_output><jupyter_text>First, we need to figure out how many facilities there are<jupyter_code>print('There are `{0}` unique facilities'.format(prov_main_df['provider_name'].unique().shape[0]))<jupyter_output>There are `3201` unique facilities<jupyter_text>There are __two__ ways to do this. The first one is to create a dictionary for each facility and determine the DRGs that contribute the most to the total discharges- First, I'll save the data into a dictionary that can be easily accessed.- Each entry will contain the DataFrames for each facility, sorted by most important DRG.<jupyter_code># Unique facilities
prov_unq = prov_main_df[prov_col].unique()

# Creating dictionary
prov_dict = {}

# Looping over facilities
for prov_ii in tqdm(prov_unq):
    prov_dict[prov_ii] = (prov_main_df.loc[prov_main_df[prov_col] == prov_ii]
                         .sort_values(by=disch_col, ascending=False)
                         .reset_index(drop=True)
                         .drop(prov_col, axis=1))<jupyter_output>100%|██████████| 3201/3201 [00:39<00:00, 93.89it/s]<jupyter_text>For example, `ABBEVILLE GENERAL HOSPITAL` suffers the most from `292 - HEART FAILURE & SHOCK W CC`<jupyter_code>prov_dict['ABBEVILLE GENERAL HOSPITAL']<jupyter_output><empty_output><jupyter_text>2nd wayThe 2nd way to look at these data is to choose the __top__ DRG for each facility.<jupyter_code># Initializing list to contain DataFrames
prov_grouped = [[] for x in range(prov_unq.shape[0])]

# Looping over all unique facilities
for ii, prov_ii in enumerate(tqdm(prov_unq)):
    prov_grouped[ii] = prov_dict[prov_ii].sort_values(disch_col, ascending=False).head(1)
    
# Concatenating list
prov_max_grouped = pd.concat(prov_grouped).reset_index(drop=True)

# Adding column of facilities
prov_max_grouped.loc[:, prov_col] = prov_unq

# Rearranging column order
prov_max_grouped = (prov_max_grouped[[prov_col, drg_col, disch_col]]
                   .sort_values(prov_col).reset_index(drop=True))

# Rename DRG column
prov_max_grouped.rename(columns={drg_col: 'Top_{0}'.format(drg_col)}, inplace=True)<jupyter_output>100%|██████████| 3201/3201 [00:02<00:00, 1429.64it/s]<jupyter_text>Now, the __TOP__ DRGs for each facility can be easily viewed:<jupyter_code>prov_max_grouped<jupyter_output><empty_output><jupyter_text>--- 3rd Question__Calculate the average Medicare payments per DRG per facility.__To determine the average Medicare payments per DRG per facility, we need to determine<jupyter_code># Main Medicare DataFrame
medicare_main_df = df.loc[:, [prov_col, drg_col, medicare_col, disch_col]]
medicare_main_df.head(10)<jupyter_output><empty_output><jupyter_text>We can now group the dataset based on the `provider_name` and `drg_definition` in order to know the `average_medicare_payments`:<jupyter_code># Grouping by DRG and facility
medicare_pd = (medicare_main_df.groupby([drg_col, prov_col])
              .mean()).drop(disch_col, axis=1)<jupyter_output><empty_output><jupyter_text>Now, `medicare_pd` shows the average Medicare payment for each DRG and facility.<jupyter_code>medicare_pd<jupyter_output><empty_output><jupyter_text>--- 4th Question__Is there a way to group facilities in a logical way?__There could be multiple answers to this question.These are just a few different ways to group the different facitilies:- By weighted DRGs at each facility- Cost gap among facilities, i.e. facilities that charge signifcantly more for certain DRG than other facilities.- Percentage of total revenue per DRG at each facilityI will try some of these approaches in the following sections  4.1 - Weighted DRGs at each facilityThe first step is determine the the weights of the different DRGs for each facility<jupyter_code># Selecting only certain column names
weighted_drgs_df = df.loc[:, [prov_col, drg_col, disch_col, medicare_col]]
weighted_drgs_df.head()<jupyter_output><empty_output><jupyter_text>Now we can construct the DataFrame containing the `OneHotEncoder` columns:<jupyter_code># Creating encoded DataFrame
encoded_pd = pd.get_dummies(weighted_drgs_df, columns=[drg_col], prefix='enc', prefix_sep='_')
encoded_pd.head()<jupyter_output><empty_output><jupyter_text>Now I have to create a function that determines the weight of each DRG at each facility:<jupyter_code># Function to determine the ranking and weights of each DRG at each facility
def drg_prov_calc(prov_group, weight_type='patient'):
    """
    Determines the ranking and weights of each DRG at each facility.
    
    Parameters
    -----------
    prov_group : `pandas.core.groups`
        Group for a given facility. It includes information about the types of DRGs performed at
        the facility, the total number of discharges, and the average medicare payments.
    
    weight_type : {`patient`, `medi`} `str`, optional
        Determines the type of weight to apply to the DataFrame. This variable is set
        to `patient` by default.
        
        Options:
            - `patient` : It uses the patients to obtain the weights of each DRG.
            - `medi` : It uses the total revenue from medicare to determine the weights of DRGs.
    
    Returns
    ---------
    wgh_drg_pd : `pandas.DataFrame`
        DataFrame containing information about the weighted DRGs for the given facility.
    """
    # Shortcuts for column names
    drg_col      = 'drg_definition'
    prov_col     = 'provider_name'
    disch_col    = 'total_discharges'
    medicare_col = 'average_medicare_payments'
    
    # Calculating weights for each DRG
    n_patients = prov_group[disch_col].sum()
    
    # Total amount of revenue generated
    n_medicare_drg_cost = prov_group[disch_col] * prov_group[medicare_col]
    n_medicare_total    = n_medicare_drg_cost.sum()
    
    # Selecting DRG columns
    drg_cols_enc = prov_group.columns[prov_group.columns.str.contains('enc_')]
    
    if (weight_type == 'patient'):
        # Computing the weighted DRG by total number of patients in facility
        wgh_drg_pd = (100.*((prov_group.loc[:, drg_cols_enc].multiply(prov_group[disch_col], axis=0)) / n_patients)
                      .sum(axis=0)
                      .to_frame()
                      .T)
    elif (weight_type == 'medi'):
        # Weighted DRG in terms of total cost
        wgh_drg_pd = (100.*((prov_group.loc[:, drg_cols_enc].multiply(n_medicare_drg_cost, axis=0)) / n_medicare_total)
                          .sum(axis=0)
                          .to_frame()
                          .T)
    
    return wgh_drg_pd<jupyter_output><empty_output><jupyter_text>And we can now apply `drg_prov_calc` to each facility from `encoded_pd`:<jupyter_code># Computed weighted DRG for each facility
drg_prov_weight = encoded_pd.groupby(prov_col).apply(drg_prov_calc, ('patient'))
# Resetting indices
drg_prov_weight.reset_index(inplace=True)

# Dropping extra columns
drg_prov_weight.drop('level_1', axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>We can look at some __clustering of facitilies__ in terms of DRGs.One way to visualize it is via a __TSNE__:<jupyter_code>start_time = time.time()

# Input data
input_data_cols = drg_prov_weight.columns[drg_prov_weight.columns.str.contains('enc_')]
tsne_input_data = drg_prov_weight.loc[:, input_data_cols].values

# Initializing TSNE element
tsne = TSNE(n_components=2, verbose=1, perplexity=40, n_iter=1000, random_state=0)

# Preparing input data
tsne_results = tsne.fit_transform(tsne_input_data)

print('t-SNE done! Time elapsed: {} seconds'.format(time.time() - start_time))
drg_prov_weight['TSNE_1'] = tsne_results[:,0]
drg_prov_weight['TSN_2'] = tsne_results[:,1]

plt.figure(figsize=(8,8))
sns.scatterplot(
    x="TSNE_1", y="TSN_2",
    palette=sns.color_palette("hls", 10),
    data=drg_prov_weight,
    legend="full",
    alpha=0.3
)<jupyter_output><empty_output><jupyter_text>From this figure, one can see that there _might_ be different clusters of facilities when clusteringbased on DRGs.Or we can also use a __PCA__ for it:<jupyter_code>pca = PCA(n_components=2)
pca_result = pca.fit_transform(tsne_input_data)
drg_prov_weight['pca-one'] = pca_result[:,0]
drg_prov_weight['pca-two'] = pca_result[:,1]
plt.figure(figsize=(8,8))
sns.scatterplot(
    x="pca-one", y="pca-two",
    palette=sns.color_palette("hls", 10),
    data=drg_prov_weight,
    legend="full",
    alpha=0.3
)<jupyter_output><empty_output><jupyter_text>PCA __doesn't__ show any type of clusters for the different facilities though.We can now test it based on the total Medicare cost per DRG at each facility.<jupyter_code># Computed weighted DRG for each facility
drg_prov_medi_weight = encoded_pd.groupby(prov_col).apply(drg_prov_calc, ('medi'))

# Resetting indices
drg_prov_medi_weight.reset_index(inplace=True)

# Dropping extra columns
drg_prov_medi_weight.drop('level_1', axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>We can create the TSNE for this scenario:<jupyter_code>start_time = time.time()

# Input data
input_data_cols = drg_prov_medi_weight.columns[drg_prov_medi_weight.columns.str.contains('enc_')]
tsne_input_medi_data = drg_prov_medi_weight.loc[:, input_data_cols].values

# Initializing TSNE element
tsne_medi = TSNE(n_components=2, verbose=1, perplexity=40, n_iter=1000, random_state=0)

# Preparing input data
tsne_medi_results = tsne_medi.fit_transform(tsne_input_medi_data)

print('t-SNE done! Time elapsed: {} seconds'.format(time.time() - start_time))
drg_prov_medi_weight['TSNE_1'] = tsne_medi_results[:,0]
drg_prov_medi_weight['TSN_2'] = tsne_medi_results[:,1]

plt.figure(figsize=(8,8))
sns.scatterplot(
    x="TSNE_1", y="TSN_2",
    palette=sns.color_palette("hls", 10),
    data=drg_prov_medi_weight,
    legend="full",
    alpha=0.3
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10125268184649457, 'fraction_numerical': 0.012042355872378712, 'mean_word_length': 5.2662619254119685, 'pattern_counts': {'":': 0, '<': 108, '<?xml version=': 0, '>': 112, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '249919', 'license': 'MIT', 'n_tokens_mistral': 5033, 'n_tokens_neox': 4804, 'n_words': 1289, 'path': 'notebooks/1.0-Victor_Calderon-DS_Test.ipynb', 'repo_name': 'vcalderon2009/DS_Test', 'size': '14458'}