<jupyter_start><jupyter_text>Querying DataFor the first run, need to download data from the database.  For subsequent runs, just load from pickle file.<jupyter_code>@lD.log(logBase + '.getData')
def getData(logger, query, cols=[], saveData=True, savePath='temp'):

    dbName = projConfig['inputs']['dbName']
    dbVersion = projConfig['inputs']['dbVersion']
    
    data = pgIO.getAllData(query, dbName=dbName)
    df = pd.DataFrame(data)
    if cols != []: df.columns = cols
    if saveData and not os.path.exists(f'../data/{savePath}.pkl'):
        df.to_pickle(f'../data/{savePath}.pkl')
    return df<jupyter_output><empty_output><jupyter_text>Filter for Patient CohortGet PatientID for cohort which meets the selection criteria:1. the same medication was prescribed for at least 120 days; 2. same medication for at least 120 days, an upper bound on the time; 3. to exclude patients with wide gaps between visits, we only include patients with at least three visits of same medication during the 120-day time period.4. Diagnosed with a specific **target disease**.     - This will reduce the size of the dataset and simplify the query.<jupyter_code>disorders_dsmno = pd.read_csv('../data/raw_data/disorders_dsmno.csv')
# from plotly import graph_objs as go
# ## testing with plotly
# fig = go.Figure([go.Bar(x=disorders_dsmno['disorders'], y=disorders_dsmno['counts'], orientation='h')])
# plotly.offline.plot(fig)

fig, ax = plt.subplots()
plt.barh(disorders_dsmno['disorders'], disorders_dsmno['counts'])
plt.title('Count of patient records by Mental Health Disorder Category')
# # plt.yticks(labels=[f'{disorder} ({round(pct,1)}%)' for disorder, pct in zip(disorders_dsmno['disorders'], (disorders_dsmno['counts']/disorders_dsmno['counts'].sum())*100)])
# for disorder, pct in zip(disorders_dsmno['disorders'], (disorders_dsmno['counts']/disorders_dsmno['counts'].sum())*100):
#     ax.text(f'{round(pct, 1)}%')
plt.show()
# cohort_dsm = disorders_dsmno.loc[disorders_dsmno['disorders']=='major depressive disorder']['dsmno'].item()
## Issues with the string missing the '', like 'F33.0'
cohort_dsm = ('296.20', '296.21',     '296.22',    '296.23',    '296.24',    '296.25',
                '296.26',    'F32.0',    'F32.1',    'F32.2',    'F32.4',
                'F32.5',    'F32.81',    'F32.89',    '296.30',    '296.31',
                '296.32',    '296.33',    '296.34', '296.35',    '296.36',
                'F33.0',    'F33.1',    'F33.2',    'F33.40',    'F33.41',
                'F33.42',    'F33.8',    '311',    'F32.9',    'F33.9',
                '300.4',    'F34.1')
cohort_query = f"""select
        distinct pd.patientid, pd.typepatientid, tp.days
    from
        rwe_version1_2.pdiagnose pd, rwe_version1_2.typepatient as tp
    where pd.typepatientid = tp.typepatientid
    and pd.patientid in (
        select
            tp.patientid
        from
            rwe_version1_2.typepatient as tp,
            rwe_version1_2.meds as meds
        where
            tp.typepatientid = meds.typepatientid
        group by
            tp.patientid,
            meds.medication
        having
            max(tp.days) - min(tp.days) >= 120
            and count(distinct tp.typepatientid) >= 3 )
    and
        cast(pd.dsmno as text) in {cohort_dsm}
        and pd.primary_dx = true
        """
all_pts_data = getData(cohort_query, 
                       savePath='intermediate/cohort_visitsdays')
all_pts_data.columns = ['PatientID', 'VisitID', 'Days']
len(all_pts_data)
all_pts_data.PatientID.unique()
all_pts_data.to_pickle('../data/intermediate/cohort_visitsdays.pkl')<jupyter_output><empty_output><jupyter_text>Subsequent Loads:<jupyter_code>all_pts_data = pickle.load(open('../data/intermediate/cohort_visitsdays.pkl','rb'))
all_pts_list = tuple([pt for pt in all_pts_data['PatientID']]) #for sql query
# Some EDA about the frequency of patient visits
days_between_visits = all_pts_data.groupby('PatientID').agg({'Days':['count', 'max','min','mean','std']}) #.apply(list)
days_between_visits.head(10)#['Days']['count']
all_pts_data.groupby('PatientID')['VisitID'].nunique().describe()<jupyter_output><empty_output><jupyter_text>Get Data of Patient Cohort1. Separate tables2. Full join  Checking for DuplicatesUsing distinct in the SQL queries to ensure that for each table, there's unique values.<jupyter_code>cgi_query = f"""
    select tp.patientid, tp.typepatientid, cgi.severity
    from rwe_version1_2.typepatient tp, rwe_version1_2.cgi cgi
    where tp.typepatientid = cgi.typepatientid
    and tp.typepatientid in {visits_list}
"""
cgi_cols = ['PatientID', 'VisitID','CGI']
cgi_data = getData(cgi_query, cols=cgi_cols, saveData=False)
grp = cgi_data.groupby('VisitID')
grp['CGI'].count()[grp['CGI'].count() > 1]
cgi_data.head()
meds_query = f"""
    select 
        distinct on (tp.patientid, tp.typepatientid, meds.medication)
        tp.patientid, tp.typepatientid, meds.medication, meds.dose, meds.regimen
    from rwe_version1_2.typepatient tp, rwe_version1_2.meds meds
    where tp.typepatientid = meds.typepatientid
    and tp.typepatientid in {visits_list}
"""
meds_cols = ['PatientID', 'VisitID','Medication','Dose', 'Regimen']
meds_data = getData(meds_query, cols=meds_cols, saveData=False)
meds_data.head()
meds_data.Regimen = meds_data.Regimen.astype('float')
meds = meds_data.groupby(['VisitID','Medication']).agg({'Dose':['mean','max','min','count'], 'Regimen':['mean','max','min','count']})
comorbid_query = f"""
    select distinct on (tp.patientid, tp.typepatientid, pd.diagnosis, pd.dsmno)
        tp.patientid, tp.typepatientid, pd.diagnosis, pd.dsmno
    from rwe_version1_2.typepatient tp, rwe_version1_2.pdiagnose pd
    where tp.typepatientid = pd.typepatientid
    and tp.typepatientid in {visits_list}
"""
comorbid_cols = ['PatientID', 'VisitID','Diagnosis','DSMNo']
comorbid_data = getData(comorbid_query, cols=comorbid_cols, saveData=False)
comorbid_data.head()
for i, d in enumerate([comorbid_data, meds_data, cgi_data]):
    d.to_pickle(f'../data/separate/df{i}.pkl')<jupyter_output><empty_output><jupyter_text>merging the queries<jupyter_code>cgi_visits_query = f"""select tp.patientid, tp.typepatientid, tp.days, tp.visit_type, cgi.severity, meds.medication, meds.dose, meds.regimen
                    from 
                        rwe_version1_2.typepatient tp, 
                        ({cgi_query}) as cgi, 
                        ({meds_query}) as meds
                    where tp.typepatientid = cgi.typepatientid
                    and tp.typepatientid = meds.typepatientid
                    and tp.typepatientid in {visits_list}
                    """
cols = ['PatientID', 'VisitID', 'Days','VisitType','CGI', 'Medication', 'Dose', 'Regimen']
cgimeds_data = getData(cgi_visits_query, cols=cols, savePath='../data/intermediate/all_cgimeds')
cgimeds_data.head()
len(cgimeds_data)<jupyter_output><empty_output><jupyter_text>full join<jupyter_code>full_data_query = f"""
select tp.patientid, tp.typepatientid, tp.visit_name, tp.visit_type, tp.age, tp.days, pd.diagnosis, pd.dsmno, meds.medication, meds.dose, cgi.severity
from rwe_version1_2.typepatient tp, rwe_version1_2.pdiagnose pd, rwe_version1_2.meds meds, rwe_version1_2.cgi cgi
where tp.patientid in {cohort_list}
and tp.typepatientid = pd.typepatientid
and tp.typepatientid = cgi.typepatientid
and tp.typepatientid = meds.typepatientid
"""
cols = ['PatientID', 'VisitID', 'VisitName','VisitType','Age','Days','Diagnosis','DSMNo','Medication','Dose','CGI']
data = getData(full_data_query, cols=cols,savePath='intermediate/mdd_pts_cgi')
# data = pickle.load(open('../data/intermediate/full_dataset2.pkl','rb'))
print(len(data))
data.head()
data[data.VisitID==1515]<jupyter_output><empty_output><jupyter_text>--- Change in CGIUse this as a feature? Or just as part of the visuals, to say that 'this period of time, the patient is not improving or their condition is worsening.'<jupyter_code>temp = cgimeds_data[cgimeds_data.PatientID ==458347].sort_values('Days')
temp['CGI-Change'] = temp['CGI'] - temp['CGI'].shift(1)
temp
plt.plot(temp.Days, temp['CGI-Change'])
# Negative = Improve
# Positive = Deprove (Increase in CGI)<jupyter_output><empty_output><jupyter_text>---  2. Loading from PickleFor subsequent uses, without having to query all over again.<jupyter_code>mdd_filter = pd.read_csv('../data/raw_data/Filters/Mdd_Filter.csv', header=None)[0]
#mdd_patients = pickle.load(open('/home/gerpang/VDL/data/intermediate/mdd_patients.pkl','rb'))
# data = pickle.load(open('/home/gerpang/VDL/data/intermediate/full_dataset.pkl','rb'))
data = pickle.load(open('../data/temp/cgi_visits.pkl','rb'))<jupyter_output><empty_output><jupyter_text>Current Patient<jupyter_code>ptX = 458700 # current patient's ID
ptX_data = data.loc[data.PatientID==ptX]

# print(f'''---Current Patient---
# ID: {ptX}
# Age: {ptX_data.Age.max()}
# Sex: {ptX_data.Sex.max()}
# Race: {ptX_data.Sex.max()}
# No. Records: {len(ptX_data)}
# ''')<jupyter_output><empty_output><jupyter_text>1. CGI over Time<jupyter_code># Patient X's CGI Improvement over time
plt.plot(ptX_data.groupby('Days')['CGI'].mean(), label='Severity', marker='x', color='blue')

ptX_daysmax = ptX_data.Days.max()
for yr in range(ptX_daysmax//365): plt.axvline(x=(yr+1)*365,linestyle='--',c='grey')
plt.axis([-10, ptX_daysmax+10, -0.5,7])
plt.title(f'CGI Severity of Patient {ptX} over time')
plt.savefig('../data/intermediate/cgi_time.png', bbox_inches='tight')
plt.show()<jupyter_output><empty_output><jupyter_text>Comorbidities1. List the DSM numbers of the patient (and comparative pop) which are not inside the filter list. 2. Use a separate mapping table to bin the data into the comorbidities list (need to create this)<jupyter_code>#Original list of diagnoses for ptX
# pd.DataFrame(ptX_data.Diagnosis.unique(), columns=['Comorbidities'])

## Exclude diagnoses in the mdd_filter
ptX_dsm = pd.DataFrame(ptX_data.DSMNo.unique(), columns=['dsm'])
ptX_dsm[~ptX_dsm.dsm.isin(mdd_filter)]

# Comorbid diseases and number of visits associated with each
ptX_data.groupby('Diagnosis').VisitType.count()
# Comorbid diseases and days of visits associated with each 
## Tells you when the cormorbidity was diagnosed relative to the disease of interest
ptX_data.groupby('Diagnosis').Days.unique()<jupyter_output><empty_output><jupyter_text>Comparative PopulationDefined as similar primary diagnosis.<jupyter_code>pop = data.loc[data.PatientID!=ptX] #[:100000]
pop_mdd = pop.loc[pop.DSMNo.isin(mdd_filter)]
print(f'''
We need to filter for patient records related to MDD-diseases for the medications.
That will narrow the population to {len(pop_mdd)} records.

For comorbidities, we still look at all the records of similar medical profiles,
which means {len(pop)} records.''')<jupyter_output>We need to filter for patient records related to MDD-diseases for the medications.
That will narrow the population to 471169 records.

For comorbidities, we still look at all the records of similar medical profiles,
which means 1775628 records.<jupyter_text>CPop % ImprovedUsing the `pop_mdd` dataset of patients' MDD reocrds:<jupyter_code>cpop_meds = pop_mdd.groupby('Medication').agg({'PatientID':'nunique', 'CGI':'mean'})

cpop_improved = pop_mdd.loc[pop['CGI']<=2].groupby('Medication').agg({'PatientID':'nunique', 'CGI':['mean','median','var']})

cpop_all = cpop_meds.merge(cpop_improved, how='left', on='Medication')
cpop_all.columns = ['Count_All','CGI_All','Count_Improved','CGI_Mean','CGI_Median','CGI_Var']
cpop_all['Pct_Improved'] = cpop_all.Count_Improved / cpop_all.Count_All * 100 
cpop_all.sort_values(['Count_All'], ascending=False)<jupyter_output>/home/gerpang/.local/lib/python3.6/site-packages/pandas/core/reshape/merge.py:618: UserWarning: merging between different levels can give an unintended result (1 levels on the left, 2 on the right)
  warnings.warn(msg, UserWarning)<jupyter_text>[Plotting the Confidence Intervals of Improved Population](https://matplotlib.org/3.1.1/gallery/lines_bars_and_markers/horizontal_barchart_distribution.htmlsphx-glr-gallery-lines-bars-and-markers-horizontal-barchart-distribution-py)Using Top 10 Medications (by patient count) first: - Remove NA- For each Medication- plot a boxplot<jupyter_code>top10meds = cpop_all.sort_values(['Count_All'], ascending=False).index[:10]
top10meds_cgi = pop_mdd.loc[pop_mdd['Medication'].isin(top10meds)].groupby(['Medication'])['CGI-I'].apply(list)

for i, med in enumerate(top10meds_cgi): 
    top10meds_cgi[i] = [m for m in top10meds_cgi[i] if ~np.isnan(m)] #list(filter(None,top10meds_cgi[i]))
top10meds_cgi
import statistics
for ix, med in enumerate(top10meds_cgi):
    print(statistics.mean(top10meds_cgi[ix]))
# Extract percentage improved from the cpop_all dataframe
pct_imp = cpop_all[cpop_all.index.isin(top10meds_cgi.index)]['Pct_Improved']
fig, ax = plt.subplots()
bp_data = top10meds_cgi
plt.boxplot(bp_data, vert=False)

ax.set_yticklabels([f'{med_name} ({round(pct,1)}%)' for med_name, pct in zip(top10meds_cgi.index, pct_imp)])
ax.set_title('Treatment Response C.I. of Top 10 Prescribed Medications')
ax.set_ylabel('Medication & % Patients Improved')
ax.set_xlabel('CGI Score')
plt.savefig('../data/intermediate/cgi_improvement.png', bbox_inches='tight')
plt.show()<jupyter_output><empty_output><jupyter_text>CPop Comorbidities<jupyter_code>pop_comorbid = pop[~pop.DSMNo.isin(mdd_filter)].groupby(['DSMNo','Diagnosis']).PatientID.nunique()
pd.DataFrame(pop_comorbid).sort_values('PatientID',ascending=False)[:10]<jupyter_output><empty_output><jupyter_text>CPop CGI Against Time- From Comparative Population (excl PtX) - MDD-related records- For each medication prescribed- Median +CI CGI scores of patient group over time<jupyter_code>pop_mdd.head(10)<jupyter_output><empty_output><jupyter_text>For a particular medicatione.g. buproprion<jupyter_code>timeperiod = 120 # days
med_of_interest='bupropion'

meds_within_time = pop_mdd.groupby(['PatientID','Medication']).filter(lambda x:x['Days'].max() - x['Days'].min()<=timeperiod)

# pop_grouped = pop_mdd.groupby(['PatientID','Medication'])
# pop_scaledDays = pop_grouped.Days.apply(lambda x:x-x.min()).filter(lambda x:x['Days'].max() - x['Days'].min()<=timeperiod)
# pop_scaledDays
meds_within_time
meds_within_time['Days_x'] = meds_within_time.groupby(['PatientID','Medication']).Days.apply(lambda x:x-x.min())
meds_within_time = meds_within_time.groupby(['PatientID','Medication']).filter(lambda x:x['Days_x'].max() - x['Days_x'].min()<=timeperiod)
meds_within_time
med_cgi = meds_within_time[meds_within_time.Medication ==med_of_interest].groupby('Days_x').agg({'CGI':['median','var']})
med_cgi
plt.errorbar(med_cgi[('CGI','median')].index,
             med_cgi[('CGI','median')].values, 
             yerr = med_cgi[('CGI','var')],
             color = 'blue',
             marker = 'x',
             ecolor = 'orange', elinewidth=0.5)

for period in np.arange(30, 121, 30): plt.axvline(x=period,linestyle='--',c='grey')
plt.axis([-10, 130, -0.5,7])

plt.title(f'Median CGI Response of Comparative Population to {med_of_interest}')
plt.xlabel('Number of Days Since First Prescribed')
plt.ylabel('CGI Score')
plt.savefig('../data/intermediate/historic_median_cgi.png', bbox_inches='tight')
plt.show()<jupyter_output><empty_output><jupyter_text>Made a whoopsie here. should not be everyday of the 120 days, but a single aggregated value. then repeat for 30,60,90 days.<jupyter_code>median_vals = []
rng = np.arange(30, 121, 30)
for period in rng:
    print(period)
    cgis_inperiod = meds_within_time[(meds_within_time.Medication ==med_of_interest) &(meds_within_time.Days_x<period)]['CGI']
    results = [cgis_inperiod.mean(), cgis_inperiod.median(), cgis_inperiod.std()]
    median_vals.append(results)

median_df = pd.DataFrame(median_vals, columns=['Mean','Median','Std'])

median_df
median_df['Std']
plt.errorbar(rng, median_df['Median'],
             yerr = median_df['Std'] *2,
             color = 'blue',
             marker = 'x',
             ecolor = 'orange', elinewidth=0.5)

plt.axis([-10, 130, -0.5,7])
plt.title(f'Median CGI Response of Comparative Population to {med_of_interest}')
plt.xlabel('Number of Days Since First Prescribed')
plt.ylabel('CGI Score')
plt.savefig('../data/intermediate/historic_median_cgi_binned.png', bbox_inches='tight')
plt.show()<jupyter_output><empty_output><jupyter_text>This graph looks super useless. but it is the described way inthe paper.  Interactive<jupyter_code># import ipywidgets as widgets
from ipywidgets import interact, interact_manual
meds_list = pop_mdd['Medication'].unique()[:10]
@interact
def show_meds_CGIbyDays(med=meds_list, x=365):

    temp = pop_mdd.loc[pop_mdd.Medication == med].groupby('Days').agg({'CGI':'median'})
    return temp.loc[temp.index<x]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1474882307371432, 'fraction_numerical': 0.021154877540075084, 'mean_word_length': 5.165319617927994, 'pattern_counts': {'":': 0, '<': 81, '<?xml version=': 0, '>': 78, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, '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': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '175838', 'license': 'MIT', 'n_tokens_mistral': 6452, 'n_tokens_neox': 6001, 'n_words': 1163, 'path': 'src/MDD_Querying_Temp.ipynb', 'repo_name': 'geraldinepyh/VDL', 'size': '16781'}