<jupyter_start><jupyter_text>------ **Dementia Patients -- Analysis and Prediction** ***Author : <NAME>*** ****Date : August, 2019****  ***Result Plots*** - 0. Setup     - 0.1. Load libraries     - 0.2. Define paths - 1. Data Preparation       - 1.1. Read Data      - 1.2. Prepare data      - 1.3. Prepare target     - 1.4. Removing Unwanted Features     - 2. Data Analysis     - 2.1. Feature      - 2.2. Target      - 3. Data Preparation and Vector Transformation- 4. Analysis and Imputing Missing Values - 5. Feature Analysis     - 5.1. Correlation Matrix    - 5.2. Feature and target     - 5.3. Feature Selection Models     - 6.Machine Learning -Classification Model   0. Setup   0.1 Load libraries  Loading Libraries<jupyter_code>import sys
sys.path.insert(1, '../preprocessing/')
import numpy as np
import pickle
import scipy.stats as spstats
import matplotlib.pyplot as plt
import seaborn as sns
import pandas_profiling
from sklearn.datasets.base import Bunch
#from data_transformation_cls import FeatureTransform
from ast import literal_eval
import plotly.figure_factory as ff
import plotly.offline as py
import plotly.graph_objects as go

import pandas as pd
pd.set_option('display.max_columns', None)  
pd.set_option('display.max_rows', None)
pd.set_option('display.max_colwidth', -1)

from ordered_set import OrderedSet

%matplotlib inline<jupyter_output><empty_output><jupyter_text>0.2 Define paths<jupyter_code># data_path
data_path = '../../../datalcdem/data/optima/dementia_18July/data_notasked/'
result_path = '../../../datalcdem/data/optima/dementia_18July/data_notasked/results/'
optima_path = '../../../datalcdem/data/optima/optima_excel/'<jupyter_output><empty_output><jupyter_text>1. Data Preparation    1.1. Read Data<jupyter_code>#Preparation Features from Raw data

# Patient Comorbidities data
'''patient_com_raw_df = pd.read_csv(data_path + 'optima_patients_comorbidities.csv').groupby(by=['patient_id', 'EPISODE_DATE'], as_index=False).agg(lambda x: x.tolist())[['patient_id', 'EPISODE_DATE', 'Comorbidity_cui']]
display(patient_com_raw_df.head(5))
patient_com_raw_df['EPISODE_DATE'] = pd.to_datetime(patient_com_raw_df['EPISODE_DATE'])


# Patient Treatment data
patient_treat_raw_df = pd.read_csv(data_path + 'optima_patients_treatments.csv').groupby(by=['patient_id', 'EPISODE_DATE'], as_index=False).agg(lambda x: x.tolist())[['patient_id', 'EPISODE_DATE', 'Medication_cui']]
display(patient_treat_raw_df.head(5))
patient_treat_raw_df['EPISODE_DATE'] = pd.to_datetime(patient_treat_raw_df['EPISODE_DATE'])

# Join Patient Treatment and Comorbidities data
patient_com_treat_raw_df = pd.merge(patient_com_raw_df, patient_treat_raw_df,on=['patient_id', 'EPISODE_DATE'], how='outer')
patient_com_treat_raw_df.sort_values(by=['patient_id', 'EPISODE_DATE'],axis=0, inplace=True, ascending=True)
patient_com_treat_raw_df.reset_index(drop=True, inplace=True)
patient_com_treat_raw_df.head(5)


#Saving data
patient_com_treat_raw_df.to_csv(data_path + 'patient_com_treat_episode_df.csv', index=False)'''

# Extracting selected features from Raw data
def rename_columns(col_list):
    d = {}
    for i in col_list:
        if i=='GLOBAL_PATIENT_DB_ID':
            d[i]='patient_id'
        elif 'CAMDEX SCORES: ' in i:
            d[i]=i.replace('CAMDEX SCORES: ', '').replace(' ', '_')
        elif 'CAMDEX ADMINISTRATION 1-12: ' in i:
            d[i]=i.replace('CAMDEX ADMINISTRATION 1-12: ', '').replace(' ', '_')
        elif 'DIAGNOSIS 334-351: ' in i:
            d[i]=i.replace('DIAGNOSIS 334-351: ', '').replace(' ', '_')
        elif 'OPTIMA DIAGNOSES V 2010: ' in i:
            d[i]=i.replace('OPTIMA DIAGNOSES V 2010: ', '').replace(' ', '_')
        elif 'PM INFORMATION: ' in i:
            d[i]=i.replace('PM INFORMATION: ', '').replace(' ', '_')
        else:
            d[i]=i.replace(' ', '_')
    return d

sel_col_df = pd.read_excel(data_path+'Variable_Guide_Highlighted_Fields_.xlsx')
display(sel_col_df.head(5))
sel_cols = [i+j.replace('+', ':')for i,j in zip(sel_col_df['Sub Category '].tolist(), sel_col_df['Variable Label'].tolist())]
rem_cols= ['OPTIMA DIAGNOSES V 2010: OTHER SYSTEMIC ILLNESS: COMMENT'] # Missing column in the dataset
sel_cols = sorted(list(set(sel_cols)-set(rem_cols)))
print (sel_cols)

columns_selected = list(OrderedSet(['GLOBAL_PATIENT_DB_ID', 'EPISODE_DATE'] + sel_cols))

df_datarequest = pd.read_excel(optima_path+'Data_Request_Jan_2019_final.xlsx')
display(df_datarequest.head(1))
df_datarequest_features = df_datarequest[columns_selected]
display(df_datarequest_features.columns)

columns_renamed = rename_columns(df_datarequest_features.columns.tolist())
df_datarequest_features.rename(columns=columns_renamed, inplace=True)

display(df_datarequest_features.head(5))
# df_datarequest_features.drop(columns=['Age_At_Episode', 'PETERSEN_MCI_TYPE'], inplace=True)
display(df_datarequest_features.head(5))

# drop columns having out of range MMSE value
#df_datarequest_features = df_datarequest_features[(df_datarequest_features['MINI_MENTAL_SCORE']<=30) & (df_datarequest_features['MINI_MENTAL_SCORE']>=0)]

# Merging Join Patient Treatment, Comorbidities and selected features from raw data
#patient_com_treat_raw_df['EPISODE_DATE'] = pd.to_datetime(patient_com_treat_raw_df['EPISODE_DATE'])
#patient_com_treat_fea_raw_df = pd.merge(patient_com_treat_raw_df,df_datarequest_features,on=['patient_id', 'EPISODE_DATE'], how='left')
#patient_com_treat_fea_raw_df.sort_values(by=['patient_id', 'EPISODE_DATE'],axis=0, inplace=True, ascending=True)
#patient_com_treat_fea_raw_df.reset_index(inplace=True, drop=True)
#display(patient_com_treat_fea_raw_df.head(5))
patient_com_treat_fea_raw_df = df_datarequest_features # Need to be changed ------------------------

# Filling misssing MMSE value with patient group Average

#patient_com_treat_fea_raw_df['MINI_MENTAL_SCORE']\
#                                        = patient_com_treat_fea_raw_df.groupby(by=['patient_id'])['MINI_MENTAL_SCORE'].transform(lambda x: x.fillna(x.mean()))
display(patient_com_treat_fea_raw_df.head(5))

#  19<=Mild<=24 , 14<=Moderate<=18 , Severe<=13 
#patient_com_treat_fea_raw_df['MINI_MENTAL_SCORE_CATEGORY']=np.nan

def change_minimentalscore_to_category(df):
    df.loc[(df['MINI_MENTAL_SCORE']<=30) & (df['MINI_MENTAL_SCORE']>24),'MINI_MENTAL_SCORE_CATEGORY'] = 'Normal'
    df.loc[(df['MINI_MENTAL_SCORE']<=24) & (df['MINI_MENTAL_SCORE']>=19),
           'MINI_MENTAL_SCORE_CATEGORY'] = 'Mild'
    df.loc[(df['MINI_MENTAL_SCORE']<=18) & (df['MINI_MENTAL_SCORE']>=14),
           'MINI_MENTAL_SCORE_CATEGORY'] = 'Moderate'
    df.loc[(df['MINI_MENTAL_SCORE']<=13) & (df['MINI_MENTAL_SCORE']>=0),'MINI_MENTAL_SCORE_CATEGORY'] = 'Severe'
    
    return df

#patient_com_treat_fea_raw_df = change_minimentalscore_to_category(patient_com_treat_fea_raw_df)

# saving file
patient_com_treat_fea_raw_df.to_csv(data_path + 'patient_com_treat_fea_episode_raw_without_expand_df.csv', index=False)

# Set line number for treatment line
def setLineNumber(lst):
    lst_dict = {ide:0 for ide in lst}
    lineNumber_list = []
    
    for idx in lst:
        if idx in lst_dict:
           lst_dict[idx] = lst_dict[idx] + 1 
           lineNumber_list.append(lst_dict[idx])
            
    return lineNumber_list

patient_com_treat_fea_raw_df['lineNumber'] = setLineNumber(patient_com_treat_fea_raw_df['patient_id'].tolist())
display(patient_com_treat_fea_raw_df.head(5))

# Extend episode data into columns
def extend_episode_data(df):
    id_dict = {i:0 for i in df['patient_id'].tolist()}
    for x in df['patient_id'].tolist():
        if x in id_dict:
            id_dict[x]=id_dict[x]+1
    
    line_updated = [int(j) for i in id_dict.values() for j in range(1,i+1)]
    # print (line_updated[0:10])
    df.update(pd.Series(line_updated, name='lineNumber'),errors='ignore')
    print ('\n----------------After creating line-number for each patients------------------')
    display(df.head(20))
    
    # merging episodes based on id and creating new columns for each episode
    r = df['lineNumber'].max()
    print ('Max line:',r)
    l = [df[df['lineNumber']==i] for i in range(1, int(r+1))]
    print('Number of Dfs to merge: ',len(l))
    df_new = pd.DataFrame()
    tmp_id = []
    for i, df_l in enumerate(l):
        df_l = df_l[~df_l['patient_id'].isin(tmp_id)]
        for j, df_ll in enumerate(l[i+1:]):
            #df_l = df_l.merge(df_ll, on='id', how='left', suffix=(str(j), str(j+1))) #suffixe is not working
            #print (j)
            df_l = df_l.join(df_ll.set_index('patient_id'), on='patient_id', rsuffix='_'+str(j+1))
        tmp_id = tmp_id + df_l['patient_id'].tolist()
        #display(df_l)
        df_new = df_new.append(df_l, ignore_index=True, sort=False)
        return df_new
    


patient_com_treat_fea_raw_df['lineNumber'] = setLineNumber(patient_com_treat_fea_raw_df['patient_id'].tolist())
# drop rows with duplicated episode for a patient
patient_com_treat_fea_raw_df = patient_com_treat_fea_raw_df.drop_duplicates(subset=['patient_id', 'EPISODE_DATE'])
patient_com_treat_fea_raw_df.sort_values(by=['patient_id', 'EPISODE_DATE'], inplace=True)
columns = patient_com_treat_fea_raw_df.columns.tolist()
patient_com_treat_fea_raw_df = patient_com_treat_fea_raw_df[columns[0:2]+columns[-1:]
                                                            +columns[2:4]+columns[-2:-1]
                                                            +columns[4:-2]]
# Expand patient 
#patient_com_treat_fea_raw_df = extend_episode_data(patient_com_treat_fea_raw_df)
display(patient_com_treat_fea_raw_df.head(2))


#Saving extended episode of each patients
#patient_com_treat_fea_raw_df.to_csv(data_path + 'patient_com_treat_fea_episode_raw_df.csv', index=False)


patient_com_treat_fea_raw_df.shape
display(patient_com_treat_fea_raw_df.describe(include='all'))
display(patient_com_treat_fea_raw_df.info())

tmp_l = []
for i in range(len(patient_com_treat_fea_raw_df.index)) :
    # print("Nan in row ", i , " : " ,  patient_com_treat_fea_raw_df.iloc[i].isnull().sum())
    tmp_l.append(patient_com_treat_fea_raw_df.iloc[i].isnull().sum())
    
plt.hist(tmp_l)
plt.show()
# find NAN and Notasked after filled value
def findnotasked(v):
    #print(v)
    c = 0.0
    flag = False
    try:
        for i in v:
                if  float(i)<9.0 and float(i)>=0.0 and flag==False:                       #float(i)<9.0 and float(i)>=0.0:
                    flag = True
                elif (float(i)==9.0 and flag==True):
                    c = c+1
    except:
        pass
    '''try:
        for i in v:
            if  i!=9.0 or i!=i:                       #float(i)<9.0 and float(i)>=0.0:
                flag = True
            elif (float(i)==9.0 and flag==True):
                c = c+1
    except:
        pass'''
    return c

def findnan(v):
    #print(v)
    c = 0.0
    flag = False
    try:
        for i in v:
                if float(i)<9.0 and float(i)>=0.0 and flag==False: #float(i)<9.0 and float(i)>=0.0:
                    flag = True
                elif (float(i)!=float(i) and flag==True):
                    c = c+1
    except:
        pass
        '''try:
        for i in v:
            if i!=9.0 or i!=i: #float(i)<9.0 and float(i)>=0.0:
                flag = True
            elif (float(i)!=float(i) and flag==True):
                c = c+1
    except:
        pass'''
    return c
    
df = patient_com_treat_fea_raw_df[list(
                                 set([col for col in patient_com_treat_fea_raw_df.columns.tolist()])
                                -set(['EPISODE_DATE']))]
tmpdf = pd.DataFrame(data=df['patient_id'].unique(), columns=['patient_id'])
display(tmpdf.head(5))
for col in df.columns.tolist():
    #print (col)
    tmp_df1 = df.groupby(by=['patient_id'])[col].apply(lambda x : findnotasked(x) 
                                                       ).reset_index(name='Count(notAsked)_'+col )
    tmp_df2 = df.groupby(by=['patient_id'])[col].apply(lambda x : findnan(x) 
                                                       ).reset_index(name='Count(nan)_'+col )
    #print (tmp_df1.isnull().sum().sum(), tmp_df2.isnull().sum().sum())
    tmpdf = tmpdf.merge(tmp_df1, on=['patient_id'], how='inner')
    tmpdf = tmpdf.merge(tmp_df2, on=['patient_id'], how='inner')
    #print (tmpdf.columns.tolist()[-2])
    
#display(tmpdf)
#display(tmpdf.agg(lambda x: x.sum(), axis=1))
col_notasked = [col for col in tmpdf.columns if 'Count(notAsked)_' in col]
col_nan = [col for col in tmpdf.columns.tolist() if 'Count(nan)_' in col]
tmpdf['count_Total(notasked)']=tmpdf[col_notasked].agg(lambda x: x.sum(),axis=1)
tmpdf['count_Total(nan)']=tmpdf[col_nan].agg(lambda x: x.sum(),axis=1)
display(tmpdf.head(5))

profile = tmpdf.profile_report(title='Dementia Profiling Report')
profile.to_file(output_file= result_path + "dementia_data_profiling_report_output_all_patients_notasked_nan.html")

# find NAN and Notasked after filled value
def findnotasked_full(v):
    #print(v)
    c = 0.0
    try:
        for i in v:
                if float(i)==9.0:
                    c = c+1
    except:
        pass
    return c

def findnan_full(v):
    c = 0.0
    try:
        for i in v:
                if float(i)!=i:
                    c = c+1
    except:
        pass
    return c

    
df = patient_com_treat_fea_raw_df[list(
                                 set([col for col in patient_com_treat_fea_raw_df.columns.tolist()])
                                -set(['EPISODE_DATE']))]
tmpdf_full = pd.DataFrame(data=df['patient_id'].unique(), columns=['patient_id'])
display(tmpdf_full.head(5))
for col in df.columns.tolist():
    #print (col)
    tmp_df1_full = df.groupby(by=['patient_id'])[col].apply(lambda x : findnotasked_full(x) 
                                                       ).reset_index(name='Count(notAsked)_'+col )
    tmp_df2_full = df.groupby(by=['patient_id'])[col].apply(lambda x : findnan_full(x) 
                                                       ).reset_index(name='Count(nan)_'+col )
    #print (tmp_df1.isnull().sum().sum(), tmp_df2.isnull().sum().sum())
    tmpdf_full = tmpdf_full.merge(tmp_df1_full, on=['patient_id'], how='inner')
    tmpdf_full = tmpdf_full.merge(tmp_df2_full, on=['patient_id'], how='inner')
    #print (tmpdf.columns.tolist()[-2])
    
#display(tmpdf)
#display(tmpdf.agg(lambda x: x.sum(), axis=1))
col_notasked_full = [col for col in tmpdf_full.columns if 'Count(notAsked)_' in col]
col_nan_full = [col for col in tmpdf_full.columns.tolist() if 'Count(nan)_' in col]
tmpdf_full['count_Total(notasked)']=tmpdf_full[col_notasked].agg(lambda x: x.sum(),axis=1)
tmpdf_full['count_Total(nan)']=tmpdf_full[col_nan].agg(lambda x: x.sum(),axis=1)
display(tmpdf_full.head(5))

profile = tmpdf_full.profile_report(title='Dementia Profiling Report')
profile.to_file(output_file= result_path + "dementia_data_profiling_report_output_all_patients_notasked_nan_full.html")

# profile = patient_com_treat_fea_raw_df.profile_report(title='Dementia Profiling Report', style={'full_width':True})
profile = patient_com_treat_fea_raw_df.profile_report(title='Dementia Profiling Report')
profile.to_file(output_file= result_path + "dementia_data_profiling_report_output_all_patients_notasked.html")
#columnswise sum
total_notasked_nan = tmpdf.sum(axis = 0, skipna = True)
total_notasked_nan.to_csv(data_path+'total_notasked_nan.csv', index=True)
total_notasked_nan_com = tmpdf_full.sum(axis = 0, skipna = True)
total_notasked_nan_com.to_csv(data_path+'total_notasked_nan_com.csv', index=True)

patient_com_treat_fea_raw_df.describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14029583146571045, 'fraction_numerical': 0.014151245437664084, 'mean_word_length': 3.7427877315517764, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 17, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '7474', 'license': 'MIT', 'n_tokens_mistral': 5881, 'n_tokens_neox': 5617, 'n_words': 961, 'path': 'dementia_optima/models/misc/data_kernel_sl_nr_ft_newfeaturevariable_maya_notAskedandnull.ipynb', 'repo_name': 'SDM-TIB/dementia_mmse', 'size': '15624'}