<jupyter_start><jupyter_text>Takes a sample of patients, extracts  and runs the stored POC model on them, then writes results to hdfs.<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import scipy as sp
import datetime as datetime
from sklearn.ensemble import GradientBoostingClassifier
from impala.util import as_pandas
from sklearn.externals import joblib
import cPickle as pickle
%matplotlib notebook
plt.style.use('ggplot')
from impala.dbapi import connect
conn = connect(host="mycluster.domain.com", port=my_impala_port_number)
cur = conn.cursor()
cur.execute("use my_db")
def pull_and_writedata_2query(masterdf, i, timeoffset):
    '''
    Input: masterdf to write,  index, timeoffset in hours
    returns: masterdf with row values filled out
    '''
    enc = masterdf.encntr_id.iloc[i]
    timestart = masterdf.encntr_starttime.iloc[i] + timeoffset*3600*1000
    timeend = timestart + 12*3600*1000
    age = masterdf.age.iloc[i]
    
    query = '''
    SELECT ce.encntr_id, ce.event_cd 
    ,cv_event_cd.description AS event_description
    ,ce.event_end_dt_tm AS unix_event_end_tm 
    , ce.result_val, ce.result_units_cd
    ,tc.checkin_dt_tm AS checkin_time
    FROM clinical_event ce 
    JOIN encounter enc ON enc.encntr_id = ce.encntr_id
    JOIN tracking_item ti ON enc.encntr_id = ti.encntr_id
    JOIN tracking_checkin tc ON tc.tracking_id = ti.tracking_id
    LEFT OUTER JOIN code_value   cv_event_cd 
    ON   ce.event_cd           = cv_event_cd.code_value     
    WHERE ce.encntr_id = '{0}' 
    AND ce.event_end_dt_tm < {1} 
    AND ce.result_status_cd NOT IN ('31', '36')
    AND ce.event_class_cd NOT IN ('654645')
    AND ce.valid_until_dt_tm > 4e12
    AND ce.event_cd IN ('679984', '2797130','2798305', '703306', '703501', '703511', '703516', 
    '703540', '703558', '2700653',
    '3623994', '4674677', '4686698', '679984', '2797130','2798305', '2797129', '75144985',
    '54411998', '2700653', '4674677', '3618608', '186470117') 
    ORDER BY ce.encntr_id, ce.performed_dt_tm;
    '''.format(enc, timeend)
    
    cur.execute(query)
    df = as_pandas(cur)
    df['result_val'] = pd.to_numeric(df.result_val, errors = 'coerce')

    df_timebox = df[df['unix_event_end_tm']>timestart]
    
    pairs = [('DBP', '703516'), ('SBP', '703501'),
          ('MAP', '703306'), ('temp', '703558'), ('RR', '703540'),
          ('SPO2', '3623994'), ('pulse', '703511') ]

    for pair in pairs:       
        if (df_timebox[df_timebox['event_cd']==pair[1]]).empty: # df with this event code is empty
            masterdf.ix[i, pair[0]+"_mean"] = np.nan
            masterdf.ix[i, pair[0]+"_recent"] = np.nan
        else:
            masterdf.ix[i, pair[0]+"_mean"] = df_timebox[df_timebox['event_cd']==pair[1]]['result_val'].mean()
            masterdf.ix[i, pair[0]+"_recent"] = df_timebox[df_timebox['event_cd']==pair[1]
                                                  ].sort_values(by='unix_event_end_tm', ascending=False).iloc[0]['result_val']
    
    if (df[df['event_cd']=='679984']).empty: 
        masterdf.ix[i, 'on_iv'] = 0
    else:
        masterdf.ix[i, 'on_iv'] = 1
        
    if ( (df[df['event_cd']=='2797130']).empty &
         (df[df['event_cd']=='2798305']).empty &
         (df[df['event_cd']=='2797129']).empty ):
        masterdf.ix[i, 'bu-nal'] = 0
    else:
        masterdf.ix[i, 'bu-nal'] = 1
        
    if df[df['event_cd']=='186470117'].empty:
        masterdf.ix[i, 'dialysis'] = 0
    else:
        masterdf.ix[i, 'dialysis'] = 1
        
    # smoking status - binary, 0 for nonsmoker/former smoker /unknown, 1 for smoker
    # assuming if more than 1 of these smoking lines exist, they will say the same thing.
    if (df[df.event_cd=='75144985']).empty:
        masterdf.ix[i, 'smoker'] = 0
    elif ( (df[df.event_cd=='75144985'].result_val.get_values()[0] == 'Heavy tobacco smoker') | 
     (df[df.event_cd=='75144985'].result_val.get_values()[0] == 'Light tobacco smoker') |
     (df[df.event_cd=='75144985'].result_val.get_values()[0] == 'Current every day smoker') |
     (df[df.event_cd=='75144985'].result_val.get_values()[0] == 'Current some day smoker') |
     (df[df.event_cd=='75144985'].result_val.get_values()[0] == 'Smoker, current status unknown')  ):
        masterdf.ix[i, 'smoker'] = 1
    else: 
        masterdf.ix[i, 'smoker'] = 0
        
    # previous RRT event
    if (df[df.event_cd=='54411998']).empty:
        masterdf.ix[i, 'prev_rrt'] = 0
    else:
        masterdf.ix[i, 'prev_rrt'] = 1
        
    # Obesity status
    if (df[df['event_cd']=='2700653']).empty:
        masterdf.ix[i, 'obese'] = np.nan
    elif (df[df['event_cd']=='4674677']).empty: 
        masterdf.ix[i, 'obese'] = np.nan
    else:
        # assuming there won't be much variation -- grab first value from both of height & weight
        height = pd.to_numeric(df[df['event_cd']=='2700653']['result_val'].get_values())[0]
        if df[df['event_cd']=='2700653']['result_units_cd'].get_values()[0] == '267':
            # convert inch -> cm
            height = height * 2.54
        height = height/100.0  # convert to get height in m
        weight = pd.to_numeric(df[df['event_cd']=='4674677']['result_val'].get_values())[0]
        bmi = weight / (height*height)
        if (bmi>30) & (age>19):
            masterdf.ix[i, 'obese'] = 1
        else:
            masterdf.ix[i, 'obese'] = 0
          
    
    # querying orders for medications
    query_ords = '''SELECT ords.encntr_id, mdx.multum_category_id, orig_order_dt_tm
    FROM (SELECT encntr_id, cki, substr(cki,9) as cki_id, order_id, orig_order_dt_tm FROM orders) ords 
    LEFT OUTER JOIN mltm_category_drug_xref mdx ON ords.cki_id = mdx.drug_identifier 
    LEFT OUTER JOIN mltm_drug_categories mdc ON mdc.multum_category_id = mdx.multum_category_id 
    WHERE mdx.multum_category_id IN ('261', '262','285', '283', '60', '191', '77', '210', '251', '341', '20', '21', 
                                   '22', '23', '24', '25', '26') 
    AND ords.encntr_id = '{0}'
    AND ords.orig_order_dt_tm < {1} 
    ;'''.format(enc, timeend)
    
    cur.execute(query_ords)
    df_ords = as_pandas(cur)
    
    if df_ords[(df_ords['multum_category_id']=='261') | (df_ords['multum_category_id']=='262') 
        | (df_ords['multum_category_id']=='283') | (df_ords['multum_category_id']=='285') ].empty:
        masterdf.ix[i, 'anticoagulants'] = 0
    else:
        masterdf.ix[i, 'anticoagulants'] = 1

    if (df_ords[df_ords['multum_category_id']=='60']).empty: # df with this event code is empty
        masterdf.ix[i, 'narcotics'] = 0
    else:
        masterdf.ix[i, 'narcotics'] = 1

    if (df_ords[df_ords['multum_category_id']=='191']).empty: # df with this event code is empty
        masterdf.ix[i, 'narc-ans'] = 0
    else:
        masterdf.ix[i, 'narc-ans'] = 1

    if df_ords[ (df_ords['multum_category_id']=='77') | (df_ords['multum_category_id']=='210') 
        | (df_ords['multum_category_id']=='251') | (df_ords['multum_category_id']=='341') ].empty:
        masterdf.ix[i, 'antipsychotics'] = 0
    else:
        masterdf.ix[i, 'antipsychotics'] = 1

    if df_ords[ (df_ords['multum_category_id']=='20') | (df_ords['multum_category_id']=='21') 
        | (df_ords['multum_category_id']=='22') | (df_ords['multum_category_id']=='23') 
        | (df_ords['multum_category_id']=='24') | (df_ords['multum_category_id']=='25')
        | (df_ords['multum_category_id']=='26') ].empty:
        masterdf.ix[i, 'chemo'] = 0
    else:
        masterdf.ix[i, 'chemo'] = 1
        
    return masterdf<jupyter_output><empty_output><jupyter_text>Patient selection<jupyter_code># pull encounter ids, encounter start times, patient info for the right kind of patients from the right hospital who have stays > 36 hours
# using encounter arrive time rather than tc.checkin_dt_tm -- tc.checkin_dt_tm may have multiple values per checkin time.

query = '''
SELECT enc.encntr_id
, enc.arrive_dt_tm as encntr_starttime
, (enc.depart_dt_tm - enc.arrive_dt_tm)/3600000 as encntr_duration_hrs
, enc.depart_dt_tm as encntr_endtime
, from_unixtime(CAST(enc.encntr_complete_dt_tm/1000 as bigint)) AS encntr_complete_time
, year(now()) - year(from_unixtime(CAST(p.birth_dt_tm/1000 as bigint))) AS age 
, CASE p.sex_cd WHEN '362' then 'F' ELSE 'M' END as sex
, cvr.description as race
FROM encounter enc
INNER JOIN person p on p.person_id = enc.person_id
LEFT OUTER JOIN code_value cvr ON cvr.code_value = p.race_cd
WHERE enc.depart_dt_tm - enc.arrive_dt_tm > 3600*1000*36
AND enc.admit_type_cd != '0'
AND enc.encntr_type_class_cd = '391'
AND enc.loc_facility_cd='633867'
AND enc.encntr_complete_dt_tm < 4e12
ORDER BY enc.depart_dt_tm DESC
LIMIT 10
'''

cur.execute(query)
dfshell = as_pandas(cur)

col_list = [ 'encntr_id', 'encntr_starttime', 'encntr_duration_hrs', 'encntr_endtime', 'encntr_complete_time',
            'age', 'sex', 'race', 'obese', 'smoker', 'prev_rrt', 'on_iv', 'bu-nal',
           'DBP_mean', 'DBP_recent', 'SBP_mean', 'SBP_recent', 
            'MAP_mean', 'MAP_recent', 
             'temp_mean', 'temp_recent', 'SPO2_mean', 'SPO2_recent',
            'RR_mean', 'RR_recent', 'pulse_mean', 'pulse_recent',
            'anticoagulants', 'narcotics', 'narc-ans',
            'antipsychotics', 'chemo', 'dialysis']

dfshell = dfshell.reindex(columns=col_list)
dfshell['age'] = pd.to_numeric(dfshell.age, errors = 'coerce')
dfshell
modeltable1 = dfshell.copy() # hours 0 - 12
modeltable2 = dfshell.copy() # hours 12 - 24
modeltable3 = dfshell.copy() # hours 24 - 36
count = 0
for i in xrange(len(modeltable1)):
# for i in xrange(1):
    count += 1
    print "On loop: {0} of {1}".format(count, len(modeltable1))
    modeltable1 = pull_and_writedata_2query(modeltable1, i, 0)
count = 0
for i in xrange(len(modeltable2)):
# for i in xrange(1):
    count += 1
    print "On loop: {0} of {1}".format(count, len(modeltable2))
    modeltable2 = pull_and_writedata_2query(modeltable2, i, 12)
count = 0
for i in xrange(len(modeltable3)):
# for i in xrange(1):
    count += 1
    print "On loop: {0} of {1}".format(count, len(modeltable3))
    modeltable3 = pull_and_writedata_2query(modeltable3, i, 24)
modeltable1
modeltable2
modeltable3<jupyter_output><empty_output><jupyter_text>Subset columns & predict for each modelingtable<jupyter_code># binarize sex
modeltable1['is_male'] = modeltable1['sex'].map({'M': 1, 'F': 0})
modeltable1.pop('sex')
modeltable2['is_male'] = modeltable2['sex'].map({'M': 1, 'F': 0})
modeltable2.pop('sex')
modeltable3['is_male'] = modeltable3['sex'].map({'M': 1, 'F': 0})
modeltable3.pop('sex')
col_use = [ 'age', 'is_male', 'obese', 'smoker', 'prev_rrt', 'on_iv', 'bu-nal',
           'DBP_mean', 'DBP_recent', 'SBP_mean', 'SBP_recent', 
            'MAP_mean', 'MAP_recent', 
             'temp_mean', 'temp_recent', 'SPO2_mean', 'SPO2_recent',
            'RR_mean', 'RR_recent', 'pulse_mean', 'pulse_recent',
            'anticoagulants', 'narcotics', 'narc-ans',
            'antipsychotics', 'chemo', 'dialysis']
X1 = modeltable1[col_use]
X2 = modeltable2[col_use]
X3 = modeltable3[col_use]
# let's look at getting rid of the data rows where vitals signs are all nans
vitals_cols = ['DBP_mean', 'DBP_recent', # take the mean of all the measurements & the most recently observed point
            'SBP_mean', 'SBP_recent',
            'MAP_mean', 'MAP_recent', # mean arterial pressure
             'temp_mean', 'temp_recent',# temperature
             'SPO2_mean', 'SPO2_recent',
            'RR_mean', 'RR_recent', # respiratory rate
            'pulse_mean', 'pulse_recent']

# Which rows are all nans?

print np.where(X1.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0]
print np.where(X2.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0]
print np.where(X3.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0]
# Oh good, they're all the same samples. let's get rid of those rows.
# Write out rows that are not all 0/NaNs across. (if all nans, remove this sample)
X1 = X1.loc[np.where(X1.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0]]; X1 = X1.reset_index(drop=True)
X2 = X2.loc[np.where(X2.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0]]; X2 = X2.reset_index(drop=True)
X3 = X3.loc[np.where(X3.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0]]; X3 = X3.reset_index(drop=True)

# if 'obese' is Nan, then set the patient to be not obese.
X1.loc[np.where(pd.isnull(X1['obese']))[0], 'obese'] = 0
X2.loc[np.where(pd.isnull(X2['obese']))[0], 'obese'] = 0
X3.loc[np.where(pd.isnull(X3['obese']))[0], 'obese'] = 0
X1.T
X2.T
X3.T
## some entries for temperature & pulse are nan. Let's fill those values, using the mean from the column.
# Fill nans with mean of columns
X1 = X1.fillna(X1.mean())
X2 = X2.fillna(X2.mean())
X3 = X3.fillna(X3.mean())
X1
### Load saved model
# from sklearn.externals import joblib
gbc = joblib.load('my_trained_model.compressed')
gbc
ypred1 = gbc.predict_proba(X1)
ypred2 = gbc.predict_proba(X2)
ypred3 = gbc.predict_proba(X3)
ypred1[:,1]
ypred2
ypred3
pred_probs1 = pd.DataFrame(data=ypred1[:,1], columns =["model_probability_of_rrt"])
pred_probs1['model_probability_of_rrt'] = pd.to_numeric(pred_probs1.model_probability_of_rrt)
pred_probs1['score'] = pred_probs1['model_probability_of_rrt'].apply(lambda x: int(round(x*10.0, 0)))
pred_probs2 = pd.DataFrame(data=ypred2[:,1], columns =["model_probability_of_rrt"])
pred_probs2['model_probability_of_rrt'] = pd.to_numeric(pred_probs2.model_probability_of_rrt)
pred_probs2['score'] = pred_probs2['model_probability_of_rrt'].apply(lambda x: int(round(x*10.0, 0)))
pred_probs3 = pd.DataFrame(data=ypred3[:,1], columns =["model_probability_of_rrt"])
pred_probs3['model_probability_of_rrt'] = pd.to_numeric(pred_probs3.model_probability_of_rrt)
pred_probs3['score'] = pred_probs3['model_probability_of_rrt'].apply(lambda x: int(round(x*10.0, 0)))
enc1 = modeltable1.iloc[np.where(modeltable1.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0],:]['encntr_id']
enc1 = enc1.reset_index(drop=True)
enc2 = modeltable2.iloc[np.where(modeltable2.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0],:]['encntr_id']
enc2 = enc2.reset_index(drop=True)
enc3 = modeltable3.iloc[np.where(modeltable3.ix[:, vitals_cols].sum(axis=1, skipna=True)!=0)[0],:]['encntr_id']
enc3 = enc3.reset_index(drop=True)
enc1
df1 = pd.concat([enc1, X1, pred_probs1],axis=1)
df2 = pd.concat([enc2, X2, pred_probs2],axis=1)
df3 = pd.concat([enc3, X3, pred_probs3],axis=1)
df1
# May need to rename columns to get rid of dash in name...
df1.rename(columns={'bu-nal': 'bu_nal', 'narc-ans': 'narc_ans'}, inplace=True)
df2.rename(columns={'bu-nal': 'bu_nal', 'narc-ans': 'narc_ans'}, inplace=True)
df3.rename(columns={'bu-nal': 'bu_nal', 'narc-ans': 'narc_ans'}, inplace=True)
# Close previous impyla connection, reconnect on ibis...
conn.close()
# Use ibis to save dfs to HDFS
import ibis
hdfs = ibis.hdfs_connect(host="mycluster.domain.com")
con = ibis.impala.connect(host="mycluster.domain.com", port=my_impala_port_number, hdfs_client=hdfs)
db = con.database('my_db')
db.create_table('sxamplePatientsScores_0_12hrs', df1)
t = db.examplePatientsScores_0_12hrs
t.execute()
db.create_table('examplePatientsScores_12_24hrs', df2)
t = db.examplePatientsScores_12_24hrs
t.execute()
db.create_table('examplePatientsScores_24_36hrs', df3)
t = db.examplePatientsScores_24_36hrs
t.execute()
db.list_tables()
con.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1606853137776624, 'fraction_numerical': 0.05211240184307872, 'mean_word_length': 4.10772290354657, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '528745', 'license': 'Apache-2.0', 'n_tokens_mistral': 6495, 'n_tokens_neox': 5932, 'n_words': 1181, 'path': 'notebooks/modeling/RunModelOnExamplePatients.ipynb', 'repo_name': 'bartleyintel/model-for-predicting-RRT-events', 'size': '15409'}