<jupyter_start><jupyter_text>EDA and Data Cleaning<jupyter_code>from sqlalchemy import create_engine
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
engine = create_engine('postgresql://willnobles:localhost@localhost:5432/diabetes')

# Read the data from CSV files
ids_mapping = pd.read_csv('./dataset_diabetes/dataset_diabetes/IDs_mapping.csv')
diabetic_data = pd.read_csv('./dataset_diabetes/dataset_diabetes/diabetic_data.csv')

# Name the tables
ids_mapping.to_sql('ids_mapping', engine, index=False, if_exists='replace')
diabetic_data.to_sql('diabetes', engine, index=False, if_exists='replace')
# Check for access to the IDs_mapping table
query = 'SELECT * FROM ids_mapping;'
df_id = pd.read_sql(query, engine)

df_id.head()
# Check for access to the Diabetes table
query = 'SELECT * FROM diabetes;'
df_diabetes = pd.read_sql(query, engine)

df_diabetes.head()
query = '''
  SELECT diabetes.*, ids_mapping.*
  FROM diabetes
  LEFT JOIN ids_mapping
  ON CAST(diabetes.admission_type_id AS text) = ids_mapping.admission_type_id;
'''

pd.read_sql(query, engine)
query = '''
  SELECT encounter_id, age, time_in_hospital
  FROM diabetes
  WHERE readmitted = '<30'
  GROUP BY encounter_id, age, time_in_hospital
  ORDER BY age
'''

df_age = pd.read_sql(query, engine)
df_age.head()
# Look at all the columns
df_diabetes.columns
# Observe how many rows and features there are
df_diabetes.shape
# Get an idea of the various data types
df_diabetes.dtypes<jupyter_output><empty_output><jupyter_text>Look at a histogram of the continous variables to get an idea of the various distributions.<jupyter_code>cont_df = df_diabetes[['encounter_id', 'patient_nbr', 'admission_type_id', 'discharge_disposition_id',
              'admission_source_id', 'time_in_hospital', 'num_procedures', 'num_medications',
              'number_outpatient', 'number_emergency', 'number_inpatient', 'number_diagnoses',]].copy()
fig = plt.figure(figsize = (8,8))
ax = fig.gca()
cont_df.hist(ax=ax)
plt.show()<jupyter_output><ipython-input-15-1b3697838340>:3: UserWarning: To output multiple subplots, the figure containing the passed axes is being cleared
  cont_df.hist(ax=ax)<jupyter_text>Look at bar plots to visualize categorical variables.<jupyter_code>cat_df = df_diabetes[['race', 'gender', 'age', 'weight', 'diag_1',
             'diag_2', 'diag_3', 'max_glu_serum', 'A1Cresult', 'metformin',
             'repaglinide', 'nateglinide', 'chlorpropamide', 'glimepiride', 'acetohexamide',
             'glipizide', 'glyburide', 'tolbutamide', 'pioglitazone', 'rosiglitazone',
             'acarbose', 'miglitol', 'troglitazone', 'tolazamide', 'examide',
             'citoglipton', 'insulin', 'glyburide-metformin', 'glipizide-metformin', 'glimepiride-pioglitazone',
             'metformin-rosiglitazone', 'metformin-pioglitazone', 'change', 'diabetesMed', 'readmitted']].copy()
g = sns.catplot(x="diabetesMed", hue="readmitted", col="readmitted",
                data=cat_df, kind="count",
                height=4, aspect=1);
plt.figure(figsize=(8, 8))
ax = sns.countplot(x="A1Cresult", hue="readmitted", data=df_diabetes)
ax.axes.set_title("Hospital Readmission Based on A1C Result",fontsize=25)
ax.set_xlabel("A1C Result", fontsize=15)
ax.set_ylabel("Count", fontsize=15)
ax.tick_params(labelsize=5)
sns.plt.show()<jupyter_output><empty_output><jupyter_text>Let's also look at a heatmap to study the relationships between numeric features.  Missing ValuesWe can see that there are a number of missing values for features like `race`, `weight`, and `payer_code` that are categorized with a "?". Missing values for columns like `race` and `payer_code` can be removed without affecting the data very much, but removing them for `weight` will have a big impact since that would involve removing almost 97% of the data.<jupyter_code># Replace all unknown values with NaN
df_diabetes.replace('?', np.nan, inplace=True)
df_diabetes['race'].value_counts()
df_diabetes['weight'].value_counts()
df_diabetes['payer_code'].value_counts()<jupyter_output><empty_output><jupyter_text>Other important features that may be useful for a regression model later on are related to the diagnosis. Let's check how many null values are in each of the diagnosis columns.<jupyter_code>df_diabetes['diag_1'].isnull().sum()
df_diabetes['diag_2'].isnull().sum()
df_diabetes['diag_3'].isnull().sum()<jupyter_output><empty_output><jupyter_text>The largest number of null values comes from the `diag_3` column, but, like the `race` and `payer_code` columns, there are so few of these values relative to the size of the dataset that removing them will likely not affect the data very much. We can remove rows with null values for the diagnosis columns, as well as `payer_code` and `race` columns.<jupyter_code>df_diabetes.dropna(subset=['payer_code', 'race', 'diag_1', 'diag_2', 'diag_3'], inplace=True)
# Sanity check that there are no null/unknown values
df_diabetes['payer_code'].isnull().sum()
df_diabetes['race'].isnull().sum()
df_diabetes['diag_1'].isnull().sum()
df_diabetes['diag_2'].isnull().sum()
df_diabetes['diag_3'].isnull().sum()
df_diabetes.shape<jupyter_output><empty_output><jupyter_text>Duplicate ValuesNote that each patient has a unique identifier called `patient_nbr`, but there are multiple encounters for each patient. We should ensure that our data includes only one encounter per patient, specifically the first encounter.<jupyter_code>df_diabetes.duplicated(subset=['patient_nbr'])<jupyter_output><empty_output><jupyter_text>We'll first sort by `encounter_id` in descending order so that the duplicates we remove will be encounters with a higher identification number since they're likely a subsequent encounter.<jupyter_code>df_diabetes.sort_values(['encounter_id'], ascending=False).groupby('patient_nbr').head()
# Drop duplicates based on patient number, keeping only the last value since that will be the first encounter
df_diabetes.drop_duplicates(subset='patient_nbr', keep='last', inplace=True)<jupyter_output><empty_output><jupyter_text>Let's now check that we've removed all duplicate patient records by comparing the number of unique patient values with the number of rows in the dataframe to make sure they're the same.<jupyter_code>print(f"Unique patient values: {df_diabetes['patient_nbr'].nunique()}, rows: {len(df_diabetes.index)}")<jupyter_output><empty_output><jupyter_text>Formatting and Healthcare-Specific InformationNext, let's look at the `discharge_disposition_id` column. There are 28 unique codes that the [Centers for Medicare & Medicaid Services]('https://www.cms.gov/medicare/medicare-contracting/contractorlearningresources/downloads/ja0801.pdf') (CMS) uses to classify how a patient was discharged.<jupyter_code>df_diabetes.discharge_disposition_id.sort_values().unique()<jupyter_output><empty_output><jupyter_text>Since we want to predict whether a patient will be readmitted within 30 days after their first encounter, we should remove rows with a `discharge_disposition_id` equal to 20 since that code is used when a patient dies or is classified as "Expired."<jupyter_code># Get indices in discharge_disposition_id column where the code is 20 (i.e. "Expired")
expired_indices = df_diabetes[(df_diabetes['discharge_disposition_id'] == 20)].index

# Delete these row indexes from DataFrame
df_diabetes.drop(expired_indices, inplace=True)
# Check that "20" has been removed
df_diabetes.discharge_disposition_id.sort_values().unique()<jupyter_output><empty_output><jupyter_text>We should also look again at the diagnosis columns. The meaning of the codes comes from the World Health Organization and are part of The International Classification of Diseases. Since this dataset comes from hospital records from the years 1999-2008, we want to look at the [Ninth Revision, Clinical Modification (ICD-9-CM)]('https://www.cdc.gov/nchs/icd/icd9cm.htm') codes.<jupyter_code>df_diabetes['diag_1'].unique()<jupyter_output><empty_output><jupyter_text>Note that there are a few codes with a float value of 250 followed by two digits (e.g. 250.52). These are codes specifically to [diabetes-related diagnoses]('https://www.ncbi.nlm.nih.gov/books/NBK368403/table/sb203.t5/'). We'll map the codes to their definitions below, making sure to categorize all float values as "diabetes-related."<jupyter_code>def icd9_code_to_definition(row_val):
    '''
    This function maps ICD-9 codes to their definition.
    '''
    
    # Check edge cases
    if 'E' in str(row_val) or 'V' in str(row_val):
        return "external causes of injury and supplemental classification"
    if '250' in str(row_val):
        return "diabetes-related"
    
    if int(row_val) >= 0 and int(row_val) <= 139:
        return "infectious and parasitic diseases"
    elif int(row_val) >= 140 and int(row_val) <= 239:
        return "neoplasms"
    elif int(row_val) >= 240 and int(row_val) <= 279:
        return "endocrine, nutritional and metabolic diseases, and immunity disorders"
    elif int(row_val) >= 280 and int(row_val) <= 289:
        return "diseases of the blood and blood-forming organs"
    elif int(row_val) >= 290 and int(row_val) <= 319:
        return "mental disorders"
    elif int(row_val) >= 320 and int(row_val) <= 389:
        return "diseases of the nervous system and sense organs"
    elif int(row_val) >= 390 and int(row_val) <= 459:
        return "diseases of the circulatory system"
    elif int(row_val) >= 460 and int(row_val) <= 519:
        return "diseases of the respiratory system"
    elif int(row_val) >= 520 and int(row_val) <= 579:
        return "diseases of the digestive system"
    elif int(row_val) >= 580 and int(row_val) <= 629:
        return "diseases of the genitourinary system"
    elif int(row_val) >= 630 and int(row_val) <= 679:
        return "complications of pregnancy, childbirth, and the puerperium"
    elif int(row_val) >= 680 and int(row_val) <= 709:
        return "diseases of the skin and subcutaneous tissue"
    elif int(row_val) >= 710 and int(row_val) <= 739:
        return "diseases of the musculoskeletal system and connective tissue"
    elif int(row_val) >= 740 and int(row_val) <= 759:
        return "congenital anomalies"
    elif int(row_val) >= 760 and int(row_val) <= 779:
        return "certain conditions originating in the perinatal period"
    elif int(row_val) >= 780 and int(row_val) <= 799:
        return "symptoms, signs, and ill-defined conditions"
    elif int(row_val) >= 800 and int(row_val) <= 999:
        return "injury and poisoning"
    else:
        return "other"
# Change values in each column from their code to their definition
df_diabetes['diag_1'] = df_diabetes['diag_1'].apply(icd9_code_to_definition)
df_diabetes['diag_2'] = df_diabetes['diag_2'].apply(icd9_code_to_definition)
df_diabetes['diag_3'] = df_diabetes['diag_3'].apply(icd9_code_to_definition)<jupyter_output><empty_output><jupyter_text>Let's just check that our data is transformed correctly.<jupyter_code>df_diabetes['diag_1'].unique()
df_diabetes['diag_2'].unique()
df_diabetes['diag_3'].unique()
df_diabetes.shape<jupyter_output><empty_output><jupyter_text>Let's now take a look at the `age` column.<jupyter_code>df_diabetes['age'].unique()<jupyter_output><empty_output><jupyter_text>We can see that the column is an object type with a range of values from the number at the lower end of the range inclusive to the end-range number exclusive. Let's change these to be integer types with the mean value replacing each age range.<jupyter_code>df_diabetes.replace({'[0-10)':5, '[10-20)':15, '[20-30)':25, '[30-40)':35, '[40-50)':45,
                     '[50-60)':55, '[60-70)':65, '[70-80)':75, '[80-90)':85, '[90-100)':95}, inplace=True)<jupyter_output><empty_output><jupyter_text>Lastly, let's choose columns in the dataframe that we want to keep for further analysis and make a smaller dataframe as a result.<jupyter_code>df_diabetes = df_diabetes[['encounter_id', 'patient_nbr', 'race',
                          'gender', 'age', 'weight',
                          'admission_type_id', 'discharge_disposition_id', 'time_in_hospital',
                          'num_lab_procedures', 'num_procedures', 'num_medications',
                          'diag_1', 'diag_2', 'diag_3',
                          'number_diagnoses', 'change', 'readmitted']].copy(deep=True)
df_diabetes.head()
df_cat1 = df_diabetes[['race', 'gender', 'diag_1', 'diag_2', 'diag_3', 'change', 'readmitted']].copy()

df_cont1 = df_diabetes[['age', 'weight',
                          'admission_type_id', 'discharge_disposition_id', 'time_in_hospital',
                          'num_lab_procedures', 'num_procedures', 'num_medications', 'number_diagnoses']].copy(deep=True)

df_cont1.dtypes
df_diabetes['diag_1'].nunique()
sns.catplot(y="diag_1", hue="readmitted", kind="count",
            palette="pastel", edgecolor=".6",
            data=df_diabetes)
plt.title("Value Counts for Various Diabetes Diagnoses", fontsize=20)<jupyter_output><empty_output><jupyter_text>We'll save the cleaned data to a file so that it can be opened in another notebook for data visualization and regression analysis.<jupyter_code>#df_diabetes.to_csv('diabetes.csv')
df_diabetes['num_lab_procedures'].describe()
df_diabetes['num_medications'].describe()
df_diabetes['time_in_hospital'].describe()
df_diabetes['age'].describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11485931785954176, 'fraction_numerical': 0.021419508918576014, 'mean_word_length': 5.223873664653971, 'pattern_counts': {'":': 0, '<': 91, '<?xml version=': 0, '>': 90, 'https://': 3, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '142301', 'license': 'MIT', 'n_tokens_mistral': 4641, 'n_tokens_neox': 4225, 'n_words': 1201, 'path': 'diabetes_eda.ipynb', 'repo_name': 'wnobles/metis-project3-classification', 'size': '13399'}