<jupyter_start><jupyter_text>Exploratory Data Analysis<jupyter_code># Standard Imports
import warnings
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import missingno as msno
sns.set(style = 'whitegrid')
plt.rcParams['figure.figsize'] = (10, 7)
warnings.filterwarnings(action="ignore")
# Read in the data
data = pd.read_pickle('./data/patients.pkl')
# Inspect the first 5 rows
data.head()
# Display all the names of the columns
data.columns
# Check the number of rows and columns
data.shape
# Visualize the missing data
msno.bar(data, fontsize=25, figsize=(30,12), color='darkblue')
plt.title('Visualization of the Missing Data', fontsize=50)
# Check count of missing number
data.isna().sum()
# Check the data types
data.dtypes<jupyter_output><empty_output><jupyter_text>Its clear that there are a lot of missing values especially in columns that describe the Claim Diagnosis code. Will handle the missing values later, but first have to deep dive in the Exploratory Data Analysis.  1. The distribution of people who are potentially fraud<jupyter_code># Create Some features for conveniece of calculations
inpatient = data[data['IsOutpatient'] == 0]
outpatient = data[data['IsOutpatient'] == 1]
diag_code = data.columns[data.columns.str.contains('DiagnosisCode')].tolist()
proc_code = data.columns[data.columns.str.contains('ProcedureCode')].tolist()
chronic = data.columns[data.columns.str.contains("Chronic")].tolist()
data["ClaimDuration"] = data["ClaimEndDt"] - data["ClaimStartDt"]
data["ClaimDuration"] = data["ClaimDuration"].dt.days
data["NoPhy"] = data[['AttendingPhysician','OperatingPhysician']].isna().all(axis =1)
data['AllPhy'] = data[['AttendingPhysician','OperatingPhysician']].notnull().all(axis =1)
data['SameAttOper'] = data['AttendingPhysician'] == data['OperatingPhysician']
data["AdmisDuration"] = data["DischargeDt"] - data["AdmissionDt"]
data["AdmisDuration"] = data["AdmisDuration"].dt.days
data["AgeAtClm"] = round((data["ClaimStartDt"] - data["DOB"]).dt.days/365,0).astype(int)
data["TotalRev"] = data['InscClaimAmtReimbursed'] + data['DeductibleAmtPaid']
data['ClmYear'] = data.ClaimStartDt.dt.year.rename('Year')
data['ClmMonth'] = data.ClaimStartDt.dt.month.rename('Month')
data['ClmWeek'] = data.ClaimStartDt.dt.week.rename('Week')
data['InsCovRatio'] = data['InscClaimAmtReimbursed']/(data['InscClaimAmtReimbursed'] + data["DeductibleAmtPaid"])
data['RevPerDay'] = data["TotalRev"]/(data['ClaimDuration']+1)
data['Chronic_Sum'] = data[chronic].sum(axis = 1)
# Function for transforming dataframe, which will be used for plotting
def transform_df(agg_col, agg_func, new_colname):
    new_dfname = data.groupby(['PotentialFraud','IsOutpatient','Provider'])[[agg_col]].agg(agg_func)
    new_dfname.columns.values[0] = new_colname
    new_dfname = new_dfname.reset_index()
    return new_dfname
# Function for creating barplots grouped by in/out patients and fraudulent providers
def create_boxplot(df, column, title1, title2):
    fig, ax = plt.subplots(1, 2, figsize=(12, 6))
    sns.boxplot(data = df[(df['IsOutpatient'] == 0)], 
                x = column, y = 'PotentialFraud', ax = ax[0], fliersize= 1);
    sns.boxplot(data = df[(df['IsOutpatient'] == 1)], 
                x = column, y = 'PotentialFraud', ax = ax[1], fliersize= 1);
    ax[0].set_title(title1);
    ax[1].set_title(title2);
# Plotting the Potential Fraud 
fraud = data['PotentialFraud'].value_counts()
plt.figure(figsize=(12,6))
sns.barplot(fraud.index, fraud.values, alpha=0.8)
plt.title('Potential Fraud Distribution', fontsize=20)
plt.xlabel('Potential Fraud', fontsize=12)
plt.ylabel('Number of Occurences', fontsize=12)
plt.show();<jupyter_output><empty_output><jupyter_text>From the barplot above potential fraud occurs more than 200k times compared to cases with no potential fraud that have about 350k cases.  2. What age of people are most likely to be affected by Potential fraud<jupyter_code>patient_age = data.groupby(['PotentialFraud','AgeAtClm'])[['ClaimID']].count()
patient_age.columns.values[0] = 'Count'
patient_age = patient_age.reset_index()
sns.lineplot(data = patient_age,x = "AgeAtClm", y = 'Count', hue='PotentialFraud');<jupyter_output><empty_output><jupyter_text>From the line chart above, the age of people most likely to be affected by fraud is 65. This is significant because this is the age for Medicare. According to the economist (May, 31st 2014 edition) Medicare doles out about 272 billion  USD, approximately 60 billion USD is lost annually.   3. Does this differ for patients with Chronic conditions<jupyter_code>plt.figure(figsize=(20,10))
data.groupby('AgeAtClm')[chronic].sum().plot(kind = 'line');
data
data.groupby(['AttendingPhysician'])['PotentialFraud']
# Average amount of claims
np.sum(data['InscClaimAmtReimbursed'])/len(data)
# Which months have the highest claims
data['ClmMonth'].value_counts().plot(kind='barh')
plt.xlabel('Amount')
plt.ylabel('Month of the year')
yticks = ({1:'January', 2:'February', 3:'March', 4:'April', 5:'May', 6:'June', 7:'July', 8:'August', 9:'September', 10:'October', 11:'November', 12:'December'})<jupyter_output><empty_output><jupyter_text>From my observation, it is clear that fraud is usually high ath the beginning of the year, especially in the third month denoted by 3 from the diagram above. The fraudulent activities seem to drop in the second month after an initial increase in the beginning, but pick right back up in the third month.<jupyter_code># Distribution of all the numerical datatypes
data_num = data.select_dtypes(include=['float', 'int64'])
data_num.hist(figsize=(16, 20), bins=50, xlabelsize=8, ylabelsize=8);<jupyter_output><empty_output><jupyter_text>3. How many unique beneficiaries did each orovider have?<jupyter_code># Boxplot for each unique beneficiary
num_bene = transform_df('BeneID', 'nunique', 'NuniqueBene')
create_boxplot(num_bene, "NuniqueBene",
              "Number of Unique Inpatient per Provider",
              "Number of Unique outpatient per Provider")<jupyter_output><empty_output><jupyter_text>4. How many claims did each provider make?<jupyter_code>num_clm = transform_df('ClaimID', 'count', 'Claim_Count')
fig, ax = plt.subplots(1, 2, figsize=(12, 6))
sns.distplot(num_clm.loc[(num_clm['IsOutpatient'] == 0) & (num_clm['PotentialFraud'] == "No"),
                         "Claim_Count"], hist = False, label = "Not Fraud", ax = ax[0]);
sns.distplot(num_clm.loc[(num_clm['IsOutpatient'] == 0) & (num_clm['PotentialFraud'] == "Yes"),
                         "Claim_Count"], hist = False, label = "Potential Fraud", ax = ax[0]);
sns.distplot(num_clm.loc[(num_clm['IsOutpatient'] == 1) & (num_clm['PotentialFraud'] == "No"),
                         "Claim_Count"], hist = False, label = "Not Fraud", ax = ax[1]);
sns.distplot(num_clm.loc[(num_clm['IsOutpatient'] == 1) & (num_clm['PotentialFraud'] == "Yes"),
                         "Claim_Count"], hist = False, label = "Potential Fraud", ax = ax[1]);
ax[0].set_title("Number of Inpatient Claims per Provider");
ax[1].set_title("Number of Outpatient Claims per Provider");<jupyter_output><empty_output><jupyter_text>5. What is the mean claim duration?<jupyter_code># Calculate the mean claim duration
clm_dur = transform_df('ClaimDuration', 'mean', 'MeanClmDuration')
create_boxplot(clm_dur, "MeanClmDuration",
              "Mean Claim Duration for Inpatients per Provider",
              "Mean Claim Duration for Outpatients per Provider")<jupyter_output><empty_output><jupyter_text>6. How many claims did not have any physician involved for providers?<jupyter_code>no_phy = transform_df('NoPhy', 'sum', 'ClmWithNoPhy')
create_boxplot(no_phy, 'ClmWithNoPhy',
              "Claims with no physicians for Inpatients per Provider",
              "Claims with no physicians for Outpatients per provider ")<jupyter_output><empty_output><jupyter_text>7. What is the percentage of claims that have the same attending and operating physician?<jupyter_code>same_att_oper = transform_df('SameAttOper', 'mean', 'SameAttendOperatePhy')
create_boxplot(same_att_oper, 'SameAttendOperatePhy',
              "Claims Ratio with the Same Att. and Oper. Physician for Inpatients per Provider",
              "Claims Ratio with the Same Att. and Oper. Physician out Inpatients per Provider")<jupyter_output><empty_output><jupyter_text>8. How many days do inpatients stay in hospitals on average for providers?<jupyter_code>adm_dur = transform_df('AdmisDuration', 'mean', 'MeanAdmisDuration')
sns.boxplot(data = adm_dur, x = "MeanAdmisDuration", y = 'PotentialFraud', fliersize= 1);
plt.title("Mean Admission Duration for inpatients per Provider");<jupyter_output><empty_output><jupyter_text>How many medical doctors are there in the train outpatient/inpatient dataset?<jupyter_code>all_inphy = pd.concat([inpatient["AttendingPhysician"],inpatient["OperatingPhysician"],inpatient["OtherPhysician"]])
print("Unique doctors for inpatients:", all_inphy.nunique())
all_outphy = pd.concat([outpatient["AttendingPhysician"],outpatient["OperatingPhysician"],outpatient["OtherPhysician"]])
print("Unique doctors for outpatients:", all_outphy.nunique())<jupyter_output>Unique doctors for outpatients: 89770<jupyter_text>Do inpatients show worse health conditions (in terms of chronic diseases) than outpatients?<jupyter_code>print("Percentage of inpatients with certain chronic disease")
inpatient[chronic].sum()/(inpatient.shape[0])
print("Percentage of outpatients with certain chronic disease")
outpatient[chronic].sum()/(outpatient.shape[0])<jupyter_output>Percentage of outpatients with certain chronic disease<jupyter_text>Do patients who visit the providers more often have worse health conditions?<jupyter_code>num_clm = data.groupby('BeneID').count().ClaimID.reset_index()
# Patients who visited the hospital more than once
bene_more = num_clm[num_clm.ClaimID > 1].BeneID.tolist()
print("Percentage of patients who visited the hospital more than once by chronic disease type")
data[data["BeneID"].isin(bene_more)][chronic].sum()/(data[data["BeneID"].isin(bene_more)].shape[0])
print("Percentage of patients who visited the hospital only once by chronic disease type")
data[~data["BeneID"].isin(bene_more)][chronic].sum()/(data[~data["BeneID"].isin(bene_more)].shape[0])<jupyter_output>Percentage of patients who visited the hospital only once by chronic disease type<jupyter_text>How many distinct patients are there in the in/out-patient datasets?<jupyter_code>print("Unique inpatient beneficiaries:", data[data["IsOutpatient"] == 0]["BeneID"].nunique())
print("Unique outpatient beneficiaries:", data[data["IsOutpatient"] == 1]["BeneID"].nunique())<jupyter_output>Unique inpatient beneficiaries: 31289
Unique outpatient beneficiaries: 133980<jupyter_text>Do patients go to different hospitals?<jupyter_code># Finding patterns of beneficiaries who move around to more than 1 hospitals
nuniq_prov = data.groupby('BeneID')["Provider"].nunique().reset_index()
bene_more = nuniq_prov[nuniq_prov.Provider > 1].BeneID.tolist()
data.loc[data["BeneID"].isin(bene_more),"Bene_Mult"] = 1
data.loc[~data["BeneID"].isin(bene_more),"Bene_Mult"] = 0
bene_mult_prov = transform_df('Bene_Mult', 'mean', 'NumBeneMult')
create_boxplot(bene_mult_prov, "NumBeneMult",
              "Percentage of inpatients visiting multiple providers per Provider",
              "Percentage of outpatients visiting multiple providers per Provider")<jupyter_output><empty_output><jupyter_text>Is the age range different for different providers?<jupyter_code>age_range = data.groupby(['PotentialFraud','IsOutpatient','Provider'])[['AgeAtClm']].max() -\
                            data.groupby(['PotentialFraud','IsOutpatient','Provider'])[['AgeAtClm']].min()
age_range.columns.values[0] = 'Age_Range'
age_range = age_range.reset_index()
create_boxplot(age_range, "Age_Range",
              "Age Range of Inpatients per Provider",
              "Age Range of Outpatients per Provider")<jupyter_output><empty_output><jupyter_text>Patients with what chronic disease would make more in/outpatient visits?<jupyter_code>data.groupby(['PotentialFraud','IsOutpatient'])[chronic].mean().plot(kind = 'barh');
plt.legend(fontsize = 12, bbox_to_anchor=(1,0.8));<jupyter_output><empty_output><jupyter_text>What are the top 5 most frequent ClmAdmitDiagnosisCodes from PotentialFraud providers?<jupyter_code># Top 5 most frequent code from Potential Fraud claims
data[data['PotentialFraud']=='Yes'].groupby('ClmAdmitDiagnosisCode')['ClaimID'].count().sort_values(ascending=False).head(5)
# # Save file as csv with new features
# data.to_csv('./data/claims.csv') 
# Save file as pickle with new features
data.to_pickle('./data/claims.pkl', )<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13400361322755477, 'fraction_numerical': 0.01272484486686042, 'mean_word_length': 6.436915887850467, 'pattern_counts': {'":': 0, '<': 73, '<?xml version=': 0, '>': 75, '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': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '129087', 'license': 'CC0-1.0', 'n_tokens_mistral': 4318, 'n_tokens_neox': 4012, 'n_words': 1005, 'path': '2_EDA_and_Feature_Engineering.ipynb', 'repo_name': 'oaagboro/Healthcare_Insurance_Fraud', 'size': '12731'}