<jupyter_start><jupyter_text>NOW PULL TOGETHER THE HCA INVENTORY    -from REDCAP HCPA (the main source of subject listings and data expectation determinations        after having dropped all the withdrawn, excluded, CC, or otherwise flagged records)   -from SSAGA (link by hcpa_id and event)   -from Penn (link by subid and assessment)   -from Toolbox (link by subject and event)   -from Q (link by subjectid and visit)keep essential vars and flesh out expectations for other datatypesmerge in curated indicators.already dropped the unusables, and the CCs from 2ndary sources.  Shouldnt be duplications per subject event but check counts as you go<jupyter_code>#REDCAPs need extensive event, demographic, and visit summary harmonization
#vars for all events
#tempH='/home/petra/UbWinSharedSpace1/boxtemp/HCPA_DATA_2021-11-03_1551.csv'
#HCAdf=pd.read_csv(tempH, low_memory=False)

HCAdf=getreport(data=datahcpa)
allevents=HCAdf.loc[HCAdf.redcap_event_name=='visit_1_arm_1'][['dob','id','psuedo_guid','subject_id','gender','site','racial','ethnic']].replace({'site':
                                      {'1': 'MGH/Harvard', '2':'UCLA', '3':'UMinn', '4':'WashU'}})
#races are not coded the same across databases
allevents['race']=allevents.replace({'racial':
                                       {'1':'American Indian/Alaska Native',
                                        '2':'Asian',
                                        '3':'Black or African American',
                                        '4':'Hawaiian or Pacific Islander',
                                        '5':'White',
                                        '6':'More than one race',
                                        '99':'Unknown or not reported'}})['racial']

#ethnicities are not coded the same across databases
allevents['ethnic_group']=allevents.replace({'ethnic':
                                           {'1':'Hispanic or Latino',
                                            '2':'Not Hispanic or Latino',
                                            '3':'unknown or not reported'}})['ethnic']

allevents['M/F']=allevents.replace({'gender':
                                           {'1':'M',
                                            '2':'F'}})['gender']


#cpature the flags
new = allevents['subject_id'].str.split("_", 1, expand=True)
allevents['subject'] = new[0].str.strip()
allevents['flagged'] = new[1].str.strip()

print(HCAdf.shape)
#propagate all events fields throughout rest of events
HCAdf1=pd.merge(allevents,HCAdf.drop(columns=['subject_id','dob','psuedo_guid','gender','site','racial','ethnic']),how='right',on='id')
print(HCAdf1.shape)

#removed flagged individuals
HCAdf1=HCAdf1.loc[HCAdf1.flagged.isnull()==True]
print(HCAdf1.shape)  #the fact that there are two more from a right merge vs a left merge in the above merge 
#suggests to me that there are two subjects without v1 data...yay inventories.  who are they, though?    

#recode event names
HCAdf1['redcap_event']=HCAdf1.replace({'redcap_event_name':
                                           {'visit_1_arm_1':'V1',
                                            'follow_up_1_arm_1':'F1',
                                            'visit_2_arm_1':'V2',
                                             'follow_up_2_arm_1':'F2',
                                            'covid_arm_1':'Covid',
                                            'follow_up_3_arm_1':'F3',
                                            'covid_remote_arm_1':'CR',
                                            'actigraphy_arm_1':'A',
                                           }})['redcap_event_name']

HCAdf1[['site','redcap_event','id','psuedo_guid','subject','M/F','race','ethnic_group','v1_date','age','v2_date','age_v2','v3_date','age_v3',
        'covid_dt', 'bt_covid_dt', 'rt_covid_dt']].head(5)
#print(HCAdf1.shape)<jupyter_output><empty_output><jupyter_text>HCA Event Harmonization<jupyter_code>list(HCAdf1.columns)
#map visits,ages, and event registrations.
HCAdf1['sub_event']=HCAdf1['redcap_event']

HCAdf1['sub_event_date']=''
HCAdf1['sub_event_age']=''
HCAdf1['sub_event_register']=''

HCAdf1.loc[HCAdf1.redcap_event=='V1','event_date']=HCAdf1.v1_date
HCAdf1.loc[HCAdf1.redcap_event=='V1','event_age']=HCAdf1.age
HCAdf1.loc[HCAdf1.redcap_event=='V1','event_register']=HCAdf1.visit
HCAdf1.loc[HCAdf1.redcap_event=='V1','sub_event']="1.V1"

HCAdf1.loc[HCAdf1.redcap_event=='F1','event_date']=HCAdf1.v3_date
HCAdf1.loc[HCAdf1.redcap_event=='F1','event_age']=HCAdf1.age_v3
HCAdf1.loc[HCAdf1.redcap_event=='F1','event_register']=HCAdf1.visit3
HCAdf1.loc[HCAdf1.redcap_event=='F1','sub_event']="2.F1"

HCAdf1.loc[HCAdf1.redcap_event=='V2','event_date']=HCAdf1.v2_date
HCAdf1.loc[HCAdf1.redcap_event=='V2','event_age']=HCAdf1.age_v2
HCAdf1.loc[HCAdf1.redcap_event=='V2','event_register']=HCAdf1.visit2
HCAdf1.loc[HCAdf1.redcap_event=='V2','sub_event']="3.V2"

HCAdf1.loc[HCAdf1.redcap_event=='F2','event_date']=HCAdf1.v3_date #yes this is right.  FU events are longitudinally tracked
HCAdf1.loc[HCAdf1.redcap_event=='F2','event_age']=HCAdf1.age_v3 #yes this is right.  FU events are longitudinally tracked
HCAdf1.loc[HCAdf1.redcap_event=='F2','event_register']=HCAdf1.visit4 #yes this is right.  Event registers consider F2 to be visit4
HCAdf1.loc[HCAdf1.redcap_event=='F2','sub_event']="4.F2"


HCAdf1.loc[HCAdf1.redcap_event=='F3','event_date']=HCAdf1.v3_date
HCAdf1.loc[HCAdf1.redcap_event=='F3','event_age']=HCAdf1.age_v3
HCAdf1.loc[HCAdf1.redcap_event=='F3','event_register']=HCAdf1.visit5
HCAdf1.loc[HCAdf1.redcap_event=='F3','sub_event']="5.F3"
            
HCAdf1.loc[HCAdf1.redcap_event=='A','event_date']=HCAdf1.v6_startdate
HCAdf1.loc[HCAdf1.redcap_event=='A','event_age']=HCAdf1.age_v6
HCAdf1.loc[HCAdf1.redcap_event=='A','event_register']=HCAdf1.visit6
HCAdf1.loc[HCAdf1.redcap_event=='A','sub_event']="6.A"

#Covid
HCAdf1.loc[HCAdf1.redcap_event=='Covid','event_date']=HCAdf1.covid_dt
HCAdf1.loc[HCAdf1.redcap_event=='Covid','event_register']=HCAdf1.visit7
HCAdf1.loc[HCAdf1.redcap_event=='Covid','sub_event']='7.Covid1'

#Covid2
Covid2=HCAdf1.loc[HCAdf1.visit8.str.contains('8')].copy()
Covid2.event_date=Covid2.bt_covid_dt
Covid2.event_register=Covid2.visit8          
Covid2.sub_event='8.Covid2'
print(Covid2.shape)

#Covid Remote
HCAdf1.loc[HCAdf1.redcap_event=='CR','event_date']=HCAdf1.rt_covid_dt
HCAdf1.loc[HCAdf1.redcap_event=='CR','event_register']=HCAdf1.visit9
HCAdf1.loc[HCAdf1.redcap_event=='CR','sub_event']='9.Covid Remote'

print(HCAdf1.shape)
#add extra rows for covid2 event so that every event with a date has a row
HCAdf2=pd.concat([HCAdf1,Covid2],axis=0)
print(HCAdf2.shape)

#populate ages where not calculated
HCAdf2.loc[HCAdf2['event_date'].str.contains('2929'),'event_date']=''
HCAdf2['alt_age']=(pd.to_datetime(HCAdf2['event_date'])-pd.to_datetime(HCAdf2['dob'])).dt.days/365.2425
HCAdf2.reset_index(inplace=True)

#assign missing ages to Covid Arms
HCAdf2.loc[HCAdf2.redcap_event.isin(['Covid','CR']),'event_age']=HCAdf2.alt_age<jupyter_output><empty_output><jupyter_text>HCA Missing rows<jupyter_code>#remove empty rows (triggered by sending of survey to participant, where form status=0)
#list(HCAdf2.columns)
print(HCAdf2.shape)
HCAdf3=HCAdf2.loc[~(HCAdf2.sub_event.isin(['7.Covid1']) & (HCAdf2.event_date.astype('str')=='') & (HCAdf2.covid_complete=='0'))]
print(HCAdf3.shape)

HCAdf3=HCAdf3.loc[~(HCAdf3.sub_event.isin(['8.Covid2']) & (HCAdf3.event_date.astype('str')=='') & (HCAdf3.covid_2_complete=='0'))]
print(HCAdf3.shape)

HCAdf3=HCAdf3.loc[~(HCAdf3.sub_event.isin(['9.Covid Remote']) & (HCAdf3.event_date.astype('str')=='') & (HCAdf3.covid_remote_complete=='0'))]
print(HCAdf3.shape)

#print(list(HCAdf3.columns))

#remove empty rows for events that were triggered but have no registration
HCAdf3=HCAdf3.loc[~(HCAdf3.redcap_event.isin(['V1']) & (HCAdf3.event_register.astype('str')==''))]
print(HCAdf3.shape)
HCAdf3=HCAdf3.loc[~(HCAdf3.redcap_event.isin(['F1']) & (HCAdf3.event_register.astype('str')==''))]
print(HCAdf3.shape)
HCAdf3=HCAdf3.loc[~(HCAdf3.redcap_event.isin(['V2']) & (HCAdf3.event_register.astype('str')==''))]
print(HCAdf3.shape)
HCAdf3=HCAdf3.loc[~(HCAdf3.redcap_event.isin(['F2']) & (HCAdf3.event_register.astype('str')==''))]
print(HCAdf3.shape)
HCAdf3=HCAdf3.loc[~(HCAdf3.redcap_event.isin(['F3']) & (HCAdf3.event_register.astype('str')==''))]
print(HCAdf3.shape)
HCAdf3=HCAdf3.loc[~(HCAdf3.redcap_event.isin(['A']) & (HCAdf3.event_register.astype('str')==''))]
print(HCAdf3.shape)


HCAdf3=HCAdf3.sort_values(['site','sub_event','subject'])
#HCAdf3[['site','redcap_event','sub_event','id','subject','event_date','M/F','event_age','race','ethnic_group','event_register',
#        'v1_date','age','visit','v3_date','age_v3','visit3','visit4','visit5','v2_date','age_v2','visit2',
#        'v6_startdate','age_v6','visit6','covid_dt','visit7','covid_complete','bt_covid_dt','visit8','covid_2_complete','rt_covid_dt','visit9', 'covid_remote_complete','alt_age']].to_csv('HCAlong.csv',index=False)
#For table : RedCap AutoID	HCA ID	Date Registered	M/F	Age

#now merge in visit summary info

#keep essential vars and flesh out expectations for other datatypes
#merge in curated indicators.
#already dropped the unusables, and the CCs.  Shouldnt be duplications per subject event
print(pennHCA.columns)
print(TLBXA.columns)
print(Qdf.columns)
print(ssaga.columns)

#pennHCA.head()
#Qdf.head()
print("TLBX IDS that dont exist in REDCap: create trello card to investigate and possibly drop from curated")
tlbxwierdos=pd.merge(HCAdf3,TLBXA,how='right',left_on=['subject'],right_on=['subject'],indicator='TLBX_wierdos')
print(tlbxwierdos.loc[tlbxwierdos.TLBX_wierdos=='right_only'][['subject']])
#list(tlbxwierdos.subject.unique())


print("Penn IDS that dont exist in REDCap: create trello card to investigate and set unusable in curated")
pennzos=pd.merge(HCAdf3,pennHCA,left_on=['subject'],right_on=['subid'],how='right',indicator="Pennzos")
print(pennzos.loc[pennzos.Pennzos=='right_only'][['subid']])

print("Ravlt IDs that doen't exist in REDCap: create trello cards to investigate and set unusable in curated")
ravvos=pd.merge(HCAdf3,Qdf,left_on=['subject'],right_on=['subjectid'],how='right',indicator="Ravvos")
print(ravvos.loc[(ravvos.Ravvos=='right_only') & ~(ravvos.subjectid.str.contains('HCD'))][['subjectid']])

print("SSAGA IDs that don't exist in REDCap: create trello cards to investigate and drop from curated")
ssagos=pd.merge(HCAdf3,ssaga,left_on=['subject'],right_on=['hcpa_id'],how='right',indicator="Ssagos")
print(ssagos.loc[(ssagos.Ssagos=='right_only')][['hcpa_id']])

#gold standard for withdrawn status is appendix to name in HCPA database.    
#HCA7297488, HCA8680189, HCA8363682, HCA7361269 HCA9383491 HCA8415675--> all withdrawn or excluded . SSAGA database doesnt say as much but pre-release 
#folders already account for this information because they refer to the HCPA redcap database as<jupyter_output><empty_output><jupyter_text>HCA Curated_* Flags<jupyter_code>#get Toolbox
print(HCAdf3.shape)

HCAdf4=pd.merge(HCAdf3,TLBXA,how='left',left_on=['subject','redcap_event'],right_on=['subject','event'],indicator='Curated_TLBX')
HCAdf4.Curated_TLBX=HCAdf4.Curated_TLBX.str.replace('both','YES')
HCAdf4.Curated_TLBX=HCAdf4.Curated_TLBX.str.replace('left_only','NO')
HCAdf4.loc[~(HCAdf4.redcap_event.isin(['V1','V2','CR'])),'Curated_TLBX']='NE'

HCAdf4.loc[(HCAdf4.Curated_TLBX=='NO') & (HCAdf4.redcap_event.isin(['CR'])) & (HCAdf4.visit9.isin(['12'])),'Curated_TLBX']='SEE V2'
HCAdf4.loc[(HCAdf4.Curated_TLBX=='YES') & (HCAdf4.redcap_event.isin(['CR'])) & (HCAdf4.visit9.isin(['12'])),'Curated_TLBX']='YES BUT'
HCAdf4.loc[(HCAdf4.Curated_TLBX=='NO') & (HCAdf4.redcap_event.isin(['CR'])) & (HCAdf4.visit9.isin(['11'])),'Curated_TLBX']='SEE V1'
HCAdf4.loc[(HCAdf4.Curated_TLBX=='YES') & (HCAdf4.redcap_event.isin(['CR'])) & (HCAdf4.visit9.isin(['11'])),'Curated_TLBX']='YES BUT'


HCAdf4['PIN']=HCAdf4.subject.str.cat(HCAdf4.redcap_event,sep="_")
#CR and visit exceptions to expections from trello
trellolist=['HCA9681499_CR','HCA9447390_CR','HCA8628995_CR','HCA8034158_CR','HCA7985712_CR','HCA7954094_CR',
            'HCA7689304_CR','HCA7670686_V2','HCA7513670_CR','HCA7184576_CR','HCA7024655_CR',
            'HCA6766290_V2','HCA6766290_CR','HCA8867610_CR',
            'HCA9198294_CR','HCA9944304_CR'] #'HCA7423063_CR','HCA6250255_CR','HCA6691992_CR','HCA6400450_CR', removed from this list since all V2 data types collected same day
#trelloseev1
#trelloseev2
#trelloseecr


HCAdf4.loc[(HCAdf4.Curated_TLBX=='NO') & HCAdf4.PIN.isin(trellolist),'Curated_TLBX']='NE TRELLO'
HCAdf4.loc[(HCAdf4.Curated_TLBX=='YES') & HCAdf4.PIN.isin(trellolist),'Curated_TLBX']='YES BUT'

#general notes catches
redlist=['HCA9796921_V2','HCA9735294_V2','HCA8595300 V1','HCA9734999_V2','HCA8163169_V1','HCA7424873_V1',
         'HCA7474181_V2','HCA7296183_V2','HCA9461182_V2','HCA8689410_V1','HCA8249078_V2','HCA8829905_V2',
         'HCA7474181_V1','HCA9093684_V2','HCA9383491_V1','HCA9914193_V3','HCA8595300_V1']

HCAdf4.loc[(HCAdf4.Curated_TLBX=='NO') & (HCAdf4.PIN.isin(redlist)),'Curated_TLBX']='NE PM'
HCAdf4.loc[(HCAdf4.Curated_TLBX=='YES') & (HCAdf4.PIN.isin(redlist)),'Curated_TLBX']='YES BUT'



double=['HCA7581081_V2','HCA8530978_V1']
HCAdf4.loc[(HCAdf4.Curated_TLBX=='NO') & (HCAdf4.PIN.isin(double)),'Curated_TLBX']='SEE DOUBLE'
HCAdf4.loc[(HCAdf4.Curated_TLBX=='YES') & (HCAdf4.PIN.isin(double)),'Curated_TLBX']='YES BUT'



HCAdf4=HCAdf4.drop(columns=['event'])
#HCAdf4.to_csv('HCAexp4.csv',index=False)
print(HCAdf4.shape)

#get double winner ids
HCAdf4=pd.merge(HCAdf4,doubles,left_on='subject',right_on='HCAid',how='left')           
HCAdf4.head()
print(HCAdf4.shape)


##get pennHCA, 
HCAdf5=pd.merge(HCAdf4,pennHCA,left_on=['subject','redcap_event'],right_on=['subid','assessment'],how='left',indicator="Curated_PennCNP")
HCAdf5.Curated_PennCNP=HCAdf5.Curated_PennCNP.str.replace('both','YES')
HCAdf5.Curated_PennCNP=HCAdf5.Curated_PennCNP.str.replace('left_only','NO')
HCAdf5.loc[~(HCAdf5.redcap_event.isin(['V1','V2','CR'])),'Curated_PennCNP']='NE'

#HCAdf5.loc[(HCAdf4.Curated_PennCNP=='NO') & (HCAdf5.redcap_event.isin(['CR'])) & (HCAdf5.visit9.isin(['12','11'])),'Curated_PennCNP']='NE'
#HCAdf5.loc[(HCAdf5.redcap_event.isin(['CR'])) & (HCAdf5.visit9.isin(['12','11'])),'Curated_PennCNP']='NE'

HCAdf5.loc[(HCAdf5.Curated_PennCNP=='NO') & (HCAdf5.redcap_event.isin(['CR'])) & (HCAdf5.visit9.isin(['12'])),'Curated_PennCNP']='SEE V2'
HCAdf5.loc[(HCAdf5.Curated_PennCNP=='YES') & (HCAdf5.redcap_event.isin(['CR'])) & (HCAdf5.visit9.isin(['12'])),'Curated_PennCNP']='YES BUT'
HCAdf5.loc[(HCAdf5.Curated_PennCNP=='NO') & (HCAdf5.redcap_event.isin(['CR'])) & (HCAdf5.visit9.isin(['11'])),'Curated_PennCNP']='SEE V1'
HCAdf5.loc[(HCAdf5.Curated_PennCNP=='YES') & (HCAdf5.redcap_event.isin(['CR'])) & (HCAdf5.visit9.isin(['11'])),'Curated_PennCNP']='YES BUT'



HCAdf5=HCAdf5.drop(columns=['subid','assessment'])
print(HCAdf5.shape)
print('### HI')
#get Q
Qdf['event']=Qdf.visit.astype('str')
Qdf.event=Qdf.event.str.replace('1','V1')
Qdf.event=Qdf.event.str.replace('2','V2')
Qdf2=Qdf.drop(columns=['visit'])

HCAdf6=pd.merge(HCAdf5,Qdf2,left_on=['subject','redcap_event'],right_on=['subjectid', 'event'],how='left',indicator="Curated_Q")
HCAdf6.Curated_Q=HCAdf6.Curated_Q.str.replace('both','YES')
HCAdf6.Curated_Q=HCAdf6.Curated_Q.str.replace('left_only','NO')
HCAdf6.loc[~(HCAdf6.redcap_event.isin(['V1','V2','CR'])),'Curated_Q']='NE'
HCAdf6=HCAdf6.drop(columns=['subjectid', 'event'])


HCAdf6.loc[(HCAdf6.Curated_Q=='NO') & (HCAdf6.redcap_event.isin(['CR'])) & (HCAdf6.visit9.isin(['12'])),'Curated_Q']='SEE V2'
HCAdf6.loc[(HCAdf6.Curated_Q=='YES') & (HCAdf6.redcap_event.isin(['CR'])) & (HCAdf6.visit9.isin(['12'])),'Curated_Q']='YES BUT'
HCAdf6.loc[(HCAdf6.Curated_Q=='NO') & (HCAdf6.redcap_event.isin(['CR'])) & (HCAdf6.visit9.isin(['11'])),'Curated_Q']='SEE V1'
HCAdf6.loc[(HCAdf6.Curated_Q=='YES') & (HCAdf6.redcap_event.isin(['CR'])) & (HCAdf6.visit9.isin(['11'])),'Curated_Q']='YES BUT'

print(HCAdf6.shape)

#and SSAGA
HCAdf7=pd.merge(HCAdf6,ssaga,left_on=['subject','redcap_event'],right_on=['hcpa_id', 'event'],how='left',indicator="Curated_SSAGA")
HCAdf7.Curated_SSAGA=HCAdf7.Curated_SSAGA.str.replace('both','YES')
HCAdf7.Curated_SSAGA=HCAdf7.Curated_SSAGA.str.replace('left_only','NO')
HCAdf7.loc[~(HCAdf7.redcap_event.isin(['V1','V2'])),'Curated_SSAGA']='NE'

HCAdf7=HCAdf7.drop(columns=['hcpa_id', 'event'])
print(HCAdf7.shape)

#misscat for visit summary v1 and v2:  1, Scan | 2, REDCap | 3, Toolbox | 7, UPennCNP | 4, RAVLT | 6, Trail-making | 8, SSAGA   
HCAdf7.loc[(HCAdf7.misscat___7=='1') & (HCAdf7.Curated_PennCNP=='NO'),'Curated_PennCNP']='NE PM'
HCAdf7.loc[(HCAdf7.misscat___4=='1') & (HCAdf7.Curated_Q=='NO'),'Curated_Q']='NE PM'
HCAdf7.loc[(HCAdf7.misscat___8=='1') & (HCAdf7.Curated_SSAGA=='NO'),'Curated_SSAGA']='NE PM'
HCAdf7.loc[(HCAdf7.misscat___7=='1') & (HCAdf7.Curated_PennCNP=='YES'),'Curated_PennCNP']='YES BUT'
HCAdf7.loc[(HCAdf7.misscat___4=='1') & (HCAdf7.Curated_Q=='YES'),'Curated_Q']='YES BUT'
HCAdf7.loc[(HCAdf7.misscat___8=='1') & (HCAdf7.Curated_SSAGA=='YES'),'Curated_SSAGA']='YES BUT'


#HCA6937998_CR, HCA8508480_CR are UMN subjects with 'had V2 data collection' checked, so no CR expected- make sure logic excludes them (UMN)

#OTHER CR subjects with all signs of PENN_CNP data explicitly NOT expected
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.covid_remote_complete=='2') & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_pt___3=='0') & (HCAdf7.covid_summary_rt_pt___4=='0')) & (HCAdf7.covid_summary_rt_test=='1'),'Curated_PennCNP']='NE PM'#[['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_summary_rt_pt___3','covid_summary_rt_pt___4','covid_summary_rt_pt___5','covid_summary_rt_test','covid_summary_notes']] 

#CR subjects with all signs of RAVLT data NOT expected
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.covid_remote_complete=='2') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='0') & (HCAdf7.covid_summary_rt_test=='1'),'Curated_Q']='NE PM'


#1, Covid Surveys | 2, MoCA | 3, Delayed Discounting | 4, Emotion Recognition | 5, RAVLT | 6, Picture Sequence Memory | 7, Picture Vocabulary | 8, List Sorting | 9, Oral Reading
#CR subjects with all signs of RAVLT expected at V1
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.event_register=='9')  & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='1') & (HCAdf7.covid_summary_rt_test=='2'),'Curated_Q']='SEE V1'
#CR subjects with all signs of RAVLT expected at V2
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='1') & (HCAdf7.covid_summary_rt_test=='3'),'Curated_Q']='SEE V2'

#CR subjects with all signs of PENNCNP expected at V1
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.event_register=='9')  & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_pt___3=='1') | (HCAdf7.covid_summary_rt_pt___4=='1')) & (HCAdf7.covid_summary_rt_test=='2'),'Curated_PennCNP']='SEE V1'
#CR subjects with all signs of PENNCNP expected at V2
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_pt___3=='1') | (HCAdf7.covid_summary_rt_pt___4=='1')) & (HCAdf7.covid_summary_rt_test=='3'),'Curated_PennCNP']='SEE V2'

#CR subjects with all signs of TLBX expected at V1
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_TLBX=='NO') & (HCAdf7.event_register=='9')  & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_pt___6=='1') | (HCAdf7.covid_summary_rt_pt___7=='1') | (HCAdf7.covid_summary_rt_pt___8=='1') | (HCAdf7.covid_summary_rt_pt___9=='1')) & (HCAdf7.covid_summary_rt_test=='2'),'Curated_TLBX']='SEE V1'
#CR subjects with all signs of TLBX expected at V2
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_TLBX=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_pt___6=='1') | (HCAdf7.covid_summary_rt_pt___7=='1') | (HCAdf7.covid_summary_rt_pt___8=='1') | (HCAdf7.covid_summary_rt_pt___9=='1')) & (HCAdf7.covid_summary_rt_test=='3'),'Curated_TLBX']='SEE V2'




###special clauses for MGH and UCLA, who didn't complete visit summaries in same way as rest of sites:

HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject=='HCA8379495'), "Curated_PennCNP"]="NE PM"

mgpin=['HCA6476584','HCA6686191']
for i in mgpin:
    HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject==i), "Curated_SSAGA"]="NE PM"
    HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject==i), "Curated_PennCNP"]="NE PM"
    HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject==i), "Curated_Q"]="NE PM"
    HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject==i), "Curated_TLBX"]="NE PM"


mgpin2=['HCA9071068','HCA9190884']
for i in mgpin2:
    HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject==i), "Curated_SSAGA"]="SEE V1"
    HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject==i), "Curated_PennCNP"]="SEE V1"
    HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject==i), "Curated_Q"]="SEE V1"
    HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject==i), "Curated_TLBX"]="SEE V1"

HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA8502872'), "Curated_SSAGA"]="SEE V2"
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA8502872'), "Curated_PennCNP"]="SEE V2"
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA8502872'), "Curated_Q"]="SEE V2"
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA8502872'), "Curated_TLBX"]="SEE V2"

#special WashU cases:
HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject=='HCA7133155'), "Curated_Q"]="SEE CR"

HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject=='HCA7372476'), "Curated_PennCNP"]='SEE CR'

HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject=='HCA7987312'),'Curated_PennCNP']='SEE CR' 
HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject=='HCA7987312'),'Curated_Q']='SEE CR' 


HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA6766290'), "Curated_Q"]="NE PM"
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA6766290'), "Curated_Q"]="NE PM"

 
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA9187693'), "Curated_PennCNP"]="NE PM"
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA9187693'), "Curated_Q"]="NE PM"
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject=='HCA9187693'), "Curated_TLBX"]="NE PM"

    
#CR subject with all signs of TLBX data NOT expected
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_TLBX=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_test=='1') & (HCAdf7.covid_summary_rt_pt___6=='0') & (HCAdf7.covid_summary_rt_pt___7=='0')  & (HCAdf7.covid_summary_rt_pt___8=='0')  & (HCAdf7.covid_summary_rt_pt___9=='0'),'Curated_TLBX']="NE PM"
           #[['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]


#CR subject with all signs of Q data NOT expected
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_test=='1') | (HCAdf7.covid_summary_rt_test=='2') | (HCAdf7.covid_summary_rt_test=='3')) & (HCAdf7.covid_summary_rt_pt___5=='0'),'Curated_Q']="NE PM"#[['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_test=='1') & (HCAdf7.covid_summary_rt_pt___5=='0'),'Curated_Q']="NE PM"#[['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]

#CR subject with all signs of PennCNP data NOT expected
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_test=='1') | (HCAdf7.covid_summary_rt_test=='2') | (HCAdf7.covid_summary_rt_test=='3')) & (HCAdf7.covid_summary_rt_pt___3=='0') & (HCAdf7.covid_summary_rt_pt___4=='0'),'Curated_PennCNP']="NE PM"#,'Curated_Q']#="NE PM"#[['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_test=='1') & (HCAdf7.covid_summary_rt_pt___3=='0') & (HCAdf7.covid_summary_rt_pt___4=='0'),'Curated_PennCNP']="NE PM"#,'Curated_Q']#="NE PM"#[['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]

HCAdf7=HCAdf7.loc[~(HCAdf7.subject.str.upper().str.contains('TEST'))]
HCAdf7=HCAdf7.loc[~(HCAdf7.subject.str.upper().str.contains('TRAINING'))]
print(HCAdf7.shape)
list(HCAdf7.columns)


HCAdf7.head()
#CR subjects with all signs of RAVLT expected at V1
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.event_register=='9')  & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='1') & (HCAdf7.covid_summary_rt_test=='2'),'Curated_Q']#='SEE V1'
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='YES') & (HCAdf7.event_register==9)  & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='1') & (HCAdf7.covid_summary_rt_test=='2'),'Curated_Q']='YES BUT'
##CR subjects with all signs of RAVLT expected at V2
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='1') & (HCAdf7.covid_summary_rt_test=='3'),'Curated_Q']#='SEE V2'
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='YES') & (HCAdf7.event_register==9)  & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='1') & (HCAdf7.covid_summary_rt_test=='3'),'Curated_Q']='YES BUT'
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.event_register=='9')  & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_pt___3=='1') | (HCAdf7.covid_summary_rt_pt___4=='1')) & (HCAdf7.covid_summary_rt_test=='2'),'Curated_PennCNP']
#CR subject with all signs of Q data NOT expected
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.event_register=='9') & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_test=='1') | (HCAdf7.covid_summary_rt_test=='2') | (HCAdf7.covid_summary_rt_test=='3')) & (HCAdf7.covid_summary_rt_pt___3=='0') & (HCAdf7.covid_summary_rt_pt___4=='0')]#,'Curated_Q']#="NE PM"#[['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]
print(HCAdf7.shape)
#missingness for CR, in lieu of being able to trust NOs at this point, since so many visit summaries missing:    
#1, Separate Remote Battery | 2, V1 Data Collection | 3, V2 Data Collection
#covid_summary_rt_pt:1, Covid Surveys | 2, MoCA | 3, Delayed Discounting | 4, Emotion Recognition | 5, RAVLT | 6, Picture Sequence Memory | 7, Picture Vocabulary | 8, List Sorting | 9, Oral Reading												


##subjects who likely just need to have their visit summaries updated or filled out if not the above
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO')][['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___3','covid_summary_rt_pt___4','covid_summary_rt_pt___5','covid_summary_rt_test','covid_summary_notes','event_date']]#.to_csv('testCRsummary.csv')

#special wacky case where subject accidentally had CR and V2 back to back.
HCAdf7.loc[(HCAdf7.redcap_event=='V2') & (HCAdf7.subject_id=='HCA7133155')]#,'Curated_Q']='SEE CR'
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject_id=='HCA7133155'),'Curated_TLBX']='SEE V2'
HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.subject_id=='HCA7133155'),'Curated_PennCNP']='SEE_V2'



#subjects with all signs of data expected (but for whom data has not been located)
a=HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_PennCNP=='NO') & (HCAdf7.covid_remote_complete=='2') & (HCAdf7.covid_remote_summary_complete=='2') & ((HCAdf7.covid_summary_rt_pt___3=='1') | (HCAdf7.covid_summary_rt_pt___4=='1')) & (HCAdf7.covid_summary_rt_test=='1')][['site','subject_id','redcap_event','event_date','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___3','covid_summary_rt_pt___4','covid_summary_rt_pt___5','covid_summary_rt_test','covid_summary_notes']] 
#missingness for CR (Q)
print(a)

#subjects with all signs of data expected (but for whom data has not been located)
b=HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO') & (HCAdf7.covid_remote_complete=='2') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_pt___5=='1') & (HCAdf7.covid_summary_rt_test=='1')][['site','subject_id','redcap_event','event_date','covid_summary_notes']]#.to_csv('missingCR_Ravlt.csv',index=False) 
print(b)

#'covid_summary_rt_pt___3','covid_summary_rt_pt___4','covid_summary_rt_pt___5','covid_remote_summary_complete','event_register','covid_remote_complete','covid_summary_rt_test',
#subjects who likely just need to have their visit summaries updated or filled out if not the above
c=HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_Q=='NO')][['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___5','covid_summary_rt_pt___5','covid_summary_rt_test','covid_summary_notes','event_date']]#.to_csv('testCRsummary.csv')
print(c)

#missingness for CR (TLBX)
#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_TLBX=='NO') & (HCAdf7.covid_remote_complete=='2') & (HCAdf7.covid_remote_summary_complete=='2') & ~(HCAdf7.covid_summary_rt_test=='1')][['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]

#HCAdf7.loc[(HCAdf7.redcap_event=='CR') & (HCAdf7.Curated_TLBX=='NO') & (HCAdf7.covid_remote_complete=='2') & (HCAdf7.covid_remote_summary_complete=='2') & (HCAdf7.covid_summary_rt_test=='1') & (test.covid_summary_rt_pt___6=='0') & (test.covid_summary_rt_pt___7=='0')  & (test.covid_summary_rt_pt___8=='0')  & (test.covid_summary_rt_pt___9=='0')][['site','subject_id','redcap_event','event_register','covid_remote_complete','covid_remote_summary_complete','covid_summary_rt_pt___6','covid_summary_rt_pt___7','covid_summary_rt_pt___8','covid_summary_rt_pt___9','covid_summary_rt_test','covid_summary_notes','event_date']]
#test.loc[(test.covid_summary_rt_pt___6=='0') & (test.covid_summary_rt_pt___7=='0')  & (test.covid_summary_rt_pt___8=='0')  & (test.covid_summary_rt_pt___9=='0')]

#remove duplicates. 
print("BEFORE DE-DUP")
print(HCAdf7.shape)

print(HCAdf7.loc[HCAdf7.duplicated()]) #should be empty

HCAdf7.drop_duplicates(subset=None, keep='first', inplace=True)

print("After DE-DUP")
print(HCAdf7.shape)


HCAdf7.loc[~(HCAdf7.redcap_event.isin(['V1','V2','CR'])),'PIN']=''

HCAdf7.loc[HCAdf7.site=='MGH/Harvard','site']='MGH'




#last minute 'yes but' fixes that were a result of visit summary form shortcomings (as opposed to data that should be 
#examined more closely)
HCAdf7.loc[(HCAdf7.PIN=='HCA7987312_CR'),'Curated_Q']='YES'
HCAdf7.loc[(HCAdf7.PIN=='HCA7987312_CR'),'Curated_PennCNP']='YES'
HCAdf7.loc[(HCAdf7.PIN=='HCA9968823_V1'),'Curated_PennCNP']='NE PM'
Qlist=['HCA6605569_V1','HCA7106657_V1','HCA7723782_V1','HCA8829905_V1']
HCAdf7.loc[HCAdf7.PIN.isin(Qlist),'Curated_Q']='YES'

#CREATE NDA VARS
#nda interview date
HCAdf7['nda_interview_date']=pd.to_datetime(HCAdf7['event_date']) - pd.offsets.QuarterBegin(startingMonth=1)

#nda age in months
HCAdf7['nda_age'] = (
        12 * (pd.to_datetime(HCAdf7['event_date']).dt.year - pd.to_datetime(HCAdf7.dob).dt.year) +
        (pd.to_datetime(HCAdf7['event_date']).dt.month - pd.to_datetime(HCAdf7.dob).dt.month) +
        (pd.to_datetime(HCAdf7['event_date']).dt.day - pd.to_datetime(HCAdf7.dob).dt.day) / 31)
HCAdf7.nda_age=HCAdf7.nda_age.round() 
#HCAdf7['check_age']= HCAdf7.event_age.replace('',np.nan).astype(float)*12
HCAdf7.loc[HCAdf7.nda_age > 1080, 'nda_age'] = 1200
HCAdf7.sort_values(by='nda_age',ascending=False)[['subject','redcap_event','event_age','nda_age','event_date','nda_interview_date']]
#merge in pedfinal by pseudo_guid

pedfinal.head()
HCAdf7=pd.merge(pedfinal,HCAdf7,how='right',on='psuedo_guid')#,indicator='bb')
#HCAdf7.bb.value_counts()  

#create days since first date
HCAdf7.event_date=pd.to_datetime(HCAdf7.event_date)

date1=HCAdf7.sort_values(['subject','event_date'])[['subject','event_date']].drop_duplicates(subset=['subject'],keep='first').rename(columns={'event_date':'first_date'})
print(date1[['subject','first_date']].head())
HCAdf8=pd.merge(HCAdf7,date1,how='left',on='subject')

HCAdf8['daysfromtime0']=(HCAdf8.event_date - HCAdf8.first_date).dt.days
#HCAdf8['daysfromtime0'].days()
print(HCAdf8.daysfromtime0.head())
HCAdf8['DB_Source']='hcpa'

#export inventory
#For both partial and complete COVID Remote responses, cognitive testing is (check one):
#1, Separate Remote Battery | 2, V1 Data Collection | 3, V2 Data Collection
#covid_summary_rt_pt:1, Covid Surveys | 2, MoCA | 3, Delayed Discounting | 4, Emotion Recognition | 5, RAVLT | 6, Picture Sequence Memory | 7, Picture Vocabulary | 8, List Sorting | 9, Oral Reading												

covidcols=[ #'sub_event',
# 'sub_event_date',
# 'sub_event_age',
# 'sub_event_register',
# 'covid_remote_complete',
# 'moca_complete',
# 'covid_protocol_order',
# 'covid_summary1',
# 'covid_summary2',
 'covid_summary_rt',
 'covid_summary_rt_pt___1',
 'covid_summary_rt_pt___2',
 'covid_summary_rt_pt___3',
 'covid_summary_rt_pt___4',
 'covid_summary_rt_pt___5',
 'covid_summary_rt_pt___6',
 'covid_summary_rt_pt___7',
 'covid_summary_rt_pt___8',
 'covid_summary_rt_pt___9',
 'covid_summary_rt_test',
 'covid_remote_summary_complete',          
 'covid_summary_notes']

#ALL columns, unmasked
allcols=['pedid','nda_age', 'nda_interview_date','psuedo_guid', 'DB_Source','HCAid','HCDid','site','redcap_event_name','sub_event','redcap_event',
          'event_register','event_date','daysfromtime0','id','subject','race','ethnic_group','M/F','height','weight',
        'event_age','Curated_SSAGA','Curated_PennCNP', 'Curated_Q','Curated_TLBX',
        'DateFinished','PIN', 'general_note', 'misscat___3','misscat___7','misscat___4','misscat___8','misstoolbox',
        'missupenncnp', 'misstravlt','missssaga']+covidcols #'dob','mstrl2_last','fp_mstrl2_last',

HCAdf8[allcols].rename(columns={'id':'REDCap_id','psuedo_guid':'pseudo_guid'}).to_csv('HCA_AllSources_'+snapshotdate+'.csv',index=False)


#slim is for the pre-release folder (has no dates)
slimcols=['pedid','nda_age', 'nda_interview_date','psuedo_guid', 'DB_Source','HCAid','HCDid','site','sub_event','redcap_event','redcap_event_name',
          'daysfromtime0','id','subject','race','ethnic_group','M/F','height','weight',
        'event_age','Curated_SSAGA','Curated_PennCNP', 'Curated_Q','Curated_TLBX',
        'PIN']


#mask ages above 90
HCAdf8.event_age=pd.to_numeric(HCAdf8.event_age)#.astype(float,errors='ignore')
HCAdf8.loc[HCAdf8.event_age>90,'event_age']=90.0
HCAdf8.sub_event_age=pd.to_numeric(HCAdf8.sub_event_age)#.astype(float,errors='ignore')
HCAdf8.loc[HCAdf8.sub_event_age>90,'sub_event_age']=90.0

HCAdf8[slimcols].rename(columns={'psuedo_guid':'pseudo_guid','id':'REDCap_id'}).to_csv('HCA_AllSourcesSlim_'+snapshotdate+'.csv',index=False)


#1.V1
#2.F1
#3.V2
#4.F2
#5.F3
#6.A
#7.Covid1
#8.Covid2
#9.Covid Remote
#misscat for visit summary v1 and v2:  1, Scan | 2, REDCap | 3, Toolbox | 7, UPennCNP | 4, RAVLT | 6, Trail-making | 8, SSAGA<jupyter_output><empty_output><jupyter_text>NOW FOR THE HCD Inventory - just focus on child/parent combos first...then HCD18 afterNOW PULL TOGETHER THE HCA INVENTORY    -from REDCAP HCPD child (the main source of subject listings and data expectation determinations        after having dropped all the withdrawn, excluded, CC, or otherwise flagged records)   -from REDCap parent (link by child_id and redcap_event)   -from KSADS (subset by patientid into subject and event...two to 4 possible columns w/patienttype and Mood possibilities)   -from Penn (link by subid and assessment)   -from Toolbox (link by subject and event)   -from Q (link by subjectid and visit)   -from eprimekeep essential vars and flesh out expectations for other datatypesmerge in curated indicators.already dropped the unusables, and the CCs.  Shouldnt be duplications per subject event<jupyter_code>#################################################################
#####HCD ########################################################
HCDchilddf=getreport(data=datachild)
HCDparentdf=getreport(data=dataparents)

##HCD child/parent
#HCDchilddf.head()
HCDparentdf.head()



#HCDparentdf.loc[HCDparentdf.parent_id=='HCD4256459']
HCDchilddf.columns
#vars for all events (race and ethnicity from parents) 
#note that parent_id is at V1...different parents came in for different visits
dfnewparent=HCDparentdf.loc[HCDparentdf.redcap_event_name=='visit_1_arm_1'][['id','child_id','parent_id','p_c_race___10','p_c_race___11','p_c_race___12','p_c_race___14','p_c_race___18','p_c_race___25','p_c_race___99','p_c_latino']].copy()

#HCPDchildrens parents can select multiple race options (checkbox)...need to convert those to 'more than one race' category of mutually exclusive
#version of race variable required by NDAR
#12, NATIVE AMERICAN | 18, ASIAN |  11, BLACK/AFRICAN AMERICAN |  14, NATIVE HAWAIIAN or
#OTHER PACIFIC ISLANDER |10, WHITE | 25, MORE THAN ONE RACE |  99, DON'T KNOW

dfnewparent.p_c_race___10=dfnewparent.p_c_race___10.str.strip().replace('','0').astype(int)
dfnewparent.p_c_race___11=dfnewparent.p_c_race___11.str.strip().replace('','0').astype(int)
dfnewparent.p_c_race___12=dfnewparent.p_c_race___12.str.strip().replace('','0').astype(int)
dfnewparent.p_c_race___14=dfnewparent.p_c_race___14.str.strip().replace('','0').astype(int)
dfnewparent.p_c_race___18=dfnewparent.p_c_race___18.str.strip().replace('','0').astype(int)
dfnewparent.p_c_race___25=dfnewparent.p_c_race___25.str.strip().replace('','0').astype(int)
dfnewparent.p_c_race___99=dfnewparent.p_c_race___99.str.strip().replace('','0').astype(int)

dfnewparent['sumcheckedrace']=dfnewparent[['p_c_race___10','p_c_race___11','p_c_race___12','p_c_race___14','p_c_race___18','p_c_race___25']].sum(axis=1)

dfnewparent.loc[(dfnewparent.sumcheckedrace==1) & (dfnewparent.p_c_race___10==1),'racial']=5
dfnewparent.loc[(dfnewparent.sumcheckedrace==1) & (dfnewparent.p_c_race___11==1),'racial']=3
dfnewparent.loc[(dfnewparent.sumcheckedrace==1) & (dfnewparent.p_c_race___12==1),'racial']=1
dfnewparent.loc[(dfnewparent.sumcheckedrace==1) & (dfnewparent.p_c_race___14==1),'racial']=4
dfnewparent.loc[(dfnewparent.sumcheckedrace==1) & (dfnewparent.p_c_race___18==1),'racial']=2
dfnewparent.loc[(dfnewparent.sumcheckedrace==1) & (dfnewparent.p_c_race___25==1),'racial']=6
dfnewparent.loc[(dfnewparent.sumcheckedrace==0) & (dfnewparent.p_c_race___99==1),'racial']=99

dfnewparent.loc[dfnewparent.sumcheckedrace>=2,'racial']=6

dfnewparent['race']=dfnewparent.replace({'racial':
                                       {1:'American Indian/Alaska Native',
                                        2:'Asian',
                                        3:'Black or African American',
                                        4:'Hawaiian or Pacific Islander',
                                        5:'White',
                                        6:'More than one race',
                                        99:'Unknown or not reported'}})['racial']

dfnewparent['ethnic_group']=dfnewparent.replace({'p_c_latino':
                                           {'1':'Hispanic or Latino',
                                            '0':'Not Hispanic or Latino',
                                            '9':'unknown or not reported'}})['p_c_latino']


dfnewparent['redcap_id_parent']=dfnewparent['id']

dfnewparent[['p_c_race___10','p_c_race___11','p_c_race___12','p_c_race___14','p_c_race___18','p_c_race___25','p_c_race___99','sumcheckedrace','race']]

#capture the flags - note that flags in parents might not be same as flags in kids.  Gold standard for flags is child db
new = dfnewparent['child_id'].str.split("_", 1, expand=True)
dfnewparent['subject'] = new[0].str.strip()
dfnewparent['flaggedparent'] = new[1].str.strip()

dfnewparent=dfnewparent.drop(columns='id')
dfnewparent.columns
print(dfnewparent.shape)
dfnewparent=dfnewparent.loc[~((dfnewparent.parent_id=="") & (dfnewparent.child_id==""))]
print(dfnewparent.shape)
dfnewparentslim=dfnewparent.loc[dfnewparent.flaggedparent.isnull()==True]
print(dfnewparentslim.shape)
print("this number should match first argument of previous print statement, indicating no duplicates:")
print(len(dfnewparentslim.child_id.unique()))
#find the duplicate.  remove if just an empty record
dfnewparentslim.loc[dfnewparentslim.duplicated(subset='child_id')]
dfnewparentslim.loc[dfnewparentslim.child_id=='']

#grab vars for all events from child db
dfchild=HCDchilddf.loc[HCDchilddf.redcap_event_name=='visit_1_arm_1'][['dob','id','subject_id','gender','site','psuedo_guid']].replace({'site':{'1': 'MGH/Harvard', '2':'UCLA', '3':'UMinn', '4':'WashU'}})
print(dfchild.shape)

dfchild['M/F']=dfchild.replace({'gender': {'1':'M', '2':'F'}})['gender']
#dfchild=dfchild.rename(columns={'id':'redcap_id_child'})


new = dfchild['subject_id'].str.split("_", 1, expand=True)
dfchild['subject'] = new[0].str.strip()
dfchild['flagged'] = new[1].str.strip()
print(dfchild.shape)

idsflagged=dfchild.loc[dfchild.flagged.isnull()==False][['id']] #grab the id not the subject so you can drop the whole row

dfchildslim=dfchild.loc[dfchild.flagged.isnull()==True]
print(dfchildslim.shape)
print("this number should match first argument of previous print statement, indicating no duplicates:")
print(len(dfchildslim.subject.unique()))

duplicates = dfchildslim[(dfchildslim.duplicated(subset='subject') == True)].sort_values('subject')
dups=list(duplicates.subject)
dfchildslim.loc[dfchildslim.subject.isin(dups)]

#precursor to parent id cleanup issues
list(idsflagged.id)
diffs=pd.merge(dfchildslim[['subject','subject_id']],dfnewparentslim[['subject','child_id']],on='subject',how='outer',indicator=True)
diffs.loc[~(diffs._merge=='both')]
#these all appear to be subjects for whom data collection has not begun in earnest
#if they still exist at the end of data collection, please delete. 
#merge races from parents with child record
#child db is gold standard for subject list
allevents=pd.merge(dfchildslim,dfnewparentslim,how='left',on='subject')

extraparents=pd.merge(dfchildslim,dfnewparentslim,how='outer',on='subject',indicator=True)
extraparents.loc[extraparents._merge=="right_only"]#these are all bogus typo ids with no data
print(allevents.shape)
allevents[['subject','subject_id','child_id','parent_id']].loc[allevents.parent_id.isnull()==True]#,'flagged','flaggedparent']].head()

allevents=allevents.loc[~(allevents.subject=="TEST")]
print(allevents.shape)
#allevents.loc[allevents.id.isin(list(idsflagged.id))]
allevents.loc[(allevents.subject_id.isnull()==True)]

#rename the parent_id to parent_at_V1 to make it obvious that this can change
allevents=allevents[['dob', 'id', 'subject_id','psuedo_guid', 'gender', 'site', 'M/F', 'subject',
       'flagged', 'child_id', 'parent_id', 'race',
       'ethnic_group', 'redcap_id_parent', 'flaggedparent']].rename(columns={'parent_id':'parent_at_V1'})
allevents.head(10)


#propagate all events fields throughout rest of events (child) and then check that flagged entities are excluded (should be case since merging by id)
print(HCDchilddf.shape)
#need to keep the right merge in case there are subjects for whom no v1 data.  
HCDchilddf2=pd.merge(allevents,HCDchilddf.drop(columns=['subject_id','dob','gender','site','psuedo_guid']),how='right',on='id')
print(HCDchilddf2.shape)

HCDchilddf3=HCDchilddf2.loc[~(HCDchilddf2.id.isin(list(idsflagged.id)))].copy()
print(HCDchilddf3.shape)

HCDchilddf3=HCDchilddf3.loc[~(HCDchilddf3.subject_id.isnull()==True)]#[['id','suject','redcap_event_name']]
#HCDchilddf3.columns
print(HCDchilddf3.shape)

HCDchilddf3.columns
#HCDchilddf3.redcap_event_name.unique()
#recode event names for linking with other data sources
#map parent and child events to same variables
HCDchilddf3['redcap_event']=HCDchilddf3.replace({'redcap_event_name':
                                           {'visit_1_arm_1':'V1',
                                            'follow_up_arm_1':'F1',
                                            'visit_2_arm_1':'V2',
                                            'visit_3_arm_1':'V3',
                                            'covid_2_arm_1':'Covid2'
                                           }})['redcap_event_name']<jupyter_output><empty_output><jupyter_text>HCD Child Event Harmonization<jupyter_code>HCDchilddf3['event_date']=''
HCDchilddf3['event_age']=''
HCDchilddf3['event_register']=''

HCDchilddf3.loc[HCDchilddf3.redcap_event.isin(['V1','F1','V2','V3']),'event_date']=HCDchilddf3.intake_date
HCDchilddf3.loc[HCDchilddf3.redcap_event.isin(['V1','F1','V2','V3']),'event_age']=HCDchilddf3.age
HCDchilddf3.loc[HCDchilddf3.redcap_event.isin(['Covid2']),'event_age']=HCDchilddf3.covidy_age
HCDchilddf3.loc[HCDchilddf3.redcap_event.isin(['Covid2']),'event_date']=HCDchilddf3.covidy_dt

#there appears to have been some inconsistent effort at linking event names across REDCap data sources...
HCDchilddf3.loc[HCDchilddf3.redcap_event=='V1','event_register']=HCDchilddf3.visit
HCDchilddf3.loc[HCDchilddf3.redcap_event=='F1','event_register']=HCDchilddf3.visit4
HCDchilddf3.loc[HCDchilddf3.redcap_event=='V2','event_register']=HCDchilddf3.visit2
HCDchilddf3.loc[HCDchilddf3.redcap_event=='V3','event_register']=HCDchilddf3.visit3
HCDchilddf3.loc[HCDchilddf3.redcap_event=='Covid2','event_register']=HCDchilddf3.visit8
print(HCDchilddf3.shape)
HCDchilddf3.columns
#remove empty rows
print(HCDchilddf3.shape)
HCDchilddf4=HCDchilddf3.loc[~(HCDchilddf3.redcap_event.isin(['F1']) & (HCDchilddf3.event_register.astype('str')==''))]
print(HCDchilddf4.shape)
#HCDchilddf4.loc[(HCDchilddf4.event_register=='4')& (HCDchilddf4.event_date=='')]
#print(HCDchilddf4.shape)
HCDchilddf4=HCDchilddf4.loc[~(HCDchilddf4.redcap_event.isin(['V1']) & (HCDchilddf4.event_register.astype('str')==''))]
print(HCDchilddf4.shape)
HCDchilddf4=HCDchilddf4.loc[~(HCDchilddf4.redcap_event.isin(['V2']) & (HCDchilddf4.event_register.astype('str')==''))]
print(HCDchilddf4.shape)
HCDchilddf4=HCDchilddf4.loc[~(HCDchilddf4.redcap_event.isin(['V3']) & (HCDchilddf4.event_register.astype('str')==''))]
print(HCDchilddf4.shape)


#remove more empty rows
print(HCDchilddf4.shape)
#HCDchilddf5=HCDchilddf4.copy()
HCDchilddf5=HCDchilddf4.loc[~(HCDchilddf4.redcap_event.isin(['Covid2']) & (HCDchilddf4.event_date=='') & (HCDchilddf4.covid_19_questionnaire_youth_complete=='0'))]
print(HCDchilddf5.shape)
#print(HCDchilddf3.shape)
HCDchilddf5.to_csv('test.csv',index=False)
#misscat: 1, Scan | 2, REDCap | 3, Toolbox | 9, Cognitive Testing | 5, KSADS
#HCDchilddf5[['id','subject_id','gender','site','M/F','subject','flagged','child_id','parent_at_V1','race','ethnic_group','redcap_id_parent','flaggedparent','redcap_event_name','redcap_event','event_date','event_age','event_register','visit','visit2','visit3','visit4','visit8','visit_type','intake_date','age','covidy_dt','covidy_age','covid_19_questionnaire_youth_complete']].sort_values(['site','redcap_event']).to_csv('hcdtest.csv',index=False)

#HCDchilddf5.loc[HCDchilddf5.subject_id.str.contains('HCD0813243')]
#find second parent IDs (e.g. when a different parent came in for v2 or something)
###PPPPPPPPPP###
HCDparentdf['redcap_event']=HCDparentdf.replace({'redcap_event_name':
                                           {'visit_1_arm_1':'V1',
                                            'follow_up_arm_1':'F1',
                                            'visit_2_arm_1':'V2',
                                            'visit_3_arm_1':'V3',
                                            'covid_arm_1': 'Covid1',
                                            'covid_2_arm_1':'Covid2'
                                           }})['redcap_event_name']


HCDparentdf.groupby('redcap_event').count()
HCDparentdf.loc[HCDparentdf.redcap_event.isnull()==True]
HCDparentdf.columns
#HCDchilddf5.loc[HCDchilddf5['redcap_id_parent'].str.strip()=='14']

print(allevents.shape)
allevents.head()

#There are some EVENTS (covid related) in the parent database that not in the 
#child database...moreover some EVENTS in child but not in parents (children who turned 18 during study)
#List of eligible SUBJECTS are the same (or should be after end of study) within both databases, but need
#to be careful when making a pooled inventory, that you don't end up with blocks of missing information outside the intersection

#want all events from both dbs without reintroducing withdrawns.  

#make sure not to set to empty where events in child but not parent


initvars=['id','parent_id','child_id']#,'site','p_c_race___10','p_c_race___11','p_c_race___12','p_c_race___14','p_c_race___18','p_c_race___25','p_c_race___99','p_c_latino','p_c_sex','dob']
parentinit=HCDparentdf.loc[HCDparentdf.parent_id.str.contains('HCD')][initvars].rename(columns={'parent_id':'parent_at_V1'})
parentinit.head()
#merge with v1 parent initiated for parent_at_visit column, and then overwrite
parentinit['parent_at_visit']=parentinit['parent_at_V1']
print(HCDparentdf.shape)
#HCDparentdf2=pd.merge(parentinit,HCDparentdf.drop(columns=['child_id','site','p_c_sex','dob']),on='id',how='left')
HCDparentdf2=pd.merge(parentinit,HCDparentdf.drop(columns=['child_id']),on='id',how='left')
print(HCDparentdf2.shape)

#subset to records corresponding to children that arent excluded or anything (per redcap id of parent in previous parent and child merge by subject_id and child_id)
#note that we cant use child redcap ids to drop withdrawns in this case because arm in child db doesnt exist for some parent data
listchilluns=list(HCDchilddf5.loc[HCDchilddf5['redcap_id_parent'].isnull()==False,'redcap_id_parent'].unique())
listchilluns
print(HCDparentdf2.shape)
HCDparentdf2=HCDparentdf2.loc[HCDparentdf2['id'].isin(listchilluns)]
print(HCDparentdf2.shape)

HCDparentdf2.loc[~(HCDparentdf2.parent_id_dif==''),'parent_at_visit']=HCDparentdf2.parent_id_dif
#HCDparentdf2[['site','id','parent_at_visit','parent_id','parent_id_dif','child_id','redcap_event_name']]#=HCDparentdf.parent_id_dif
print(HCDparentdf2.columns)
HCDparentdf2[['id', 'parent_at_V1', 'parent_at_visit','child_id']].head()
#print(len(listchilluns))


#NOte that this won't solve all the ID problems because parent at visit isn't necessarily parent at SESSION 
#(e.g. the id on the TOOLBOX data).  These had to be hard coded in from info available in notes sections 
# of visit summaries.  This happens later
print(len(listchilluns))
print('child shape using different methods of making sure withdrawns were excluded:' + str(dfchildslim.shape))
child=pd.DataFrame(HCDchilddf5.subject.unique(),columns=['subject'])
#parent=pd.DataFrame(HCDparentdf2.child_id.unique(),columns=['subject'])
parent=pd.DataFrame(HCDparentdf2.id.unique(),columns=['id'])
child.head()
parent.head()
print("child shape: " + str(child.shape))
print("parent shape: " + str(parent.shape))
print("if the large numbers above all agree, that means we have successfully subset the parent and child databases to subject that should be in both (and are not withdrawn or otherwise excluded)")<jupyter_output><empty_output><jupyter_text>HCD Parent Event Harmonization<jupyter_code>HCDparentdf2.head()
HCDparentdf2.columns
HCDparentdf2['event_date']=''
#HCDparentdf2['event_age']=''
HCDparentdf2['event_register']=''

HCDparentdf2.loc[HCDparentdf2.redcap_event.isin(['V1','F1','V2','V3']),'event_date']=HCDparentdf2.intake_date
HCDparentdf2.loc[HCDparentdf2.redcap_event.isin(['Covid1']),'event_date']=HCDparentdf2.covid_dt
HCDparentdf2.loc[HCDparentdf2.redcap_event.isin(['Covid2']),'event_date']=HCDparentdf2.covidy_dt


HCDparentdf2.loc[HCDparentdf2.redcap_event=='V1','event_register']=HCDparentdf2.visit
HCDparentdf2.loc[HCDparentdf2.redcap_event=='F1','event_register']=HCDparentdf2.visit4
HCDparentdf2.loc[HCDparentdf2.redcap_event=='V2','event_register']=HCDparentdf2.visit2
HCDparentdf2.loc[HCDparentdf2.redcap_event=='V3','event_register']=HCDparentdf2.visit3
HCDparentdf2.loc[HCDparentdf2.redcap_event=='Covid1','event_register']=HCDparentdf2.visit7
HCDparentdf2.loc[HCDparentdf2.redcap_event=='Covid2','event_register']=HCDparentdf2.visit8

#remove empty rows:
print(HCDparentdf2.shape)
HCDparentdf2=HCDparentdf2.loc[~(HCDparentdf2.redcap_event.isin(['Covid2']) & (HCDparentdf2.event_date=='') & (HCDparentdf2.covid_19_questionnaire_parent_complete=='0'))]
print(HCDparentdf2.shape)
HCDparentdf2=HCDparentdf2.loc[~(HCDparentdf2.redcap_event.isin(['Covid1']) & (HCDparentdf2.event_date=='') & (HCDparentdf2.covid_complete=='0'))]
print(HCDparentdf2.shape)
HCDparentdf2=HCDparentdf2.loc[~(HCDparentdf2.redcap_event.isin(['F1','V2','V3']) & (HCDparentdf2.event_register=='') )]


print(HCDparentdf2.shape)
HCDparentdf2.columns
#HCDparentdf2[['id', 'site', 'redcap_event_name','redcap_event', 'event_date', 'event_register' ,'child_id',
#              'parent_id','parent_at_visit', 'visit8', 'visit_type', 'intake_date', 'parent_id_yn',
#       'parent_id_dif', 'covid_dt', 'covidy_dt', 'covid_complete', 'covid_19_questionnaire_parent_complete']].to_csv('hcdparenttest.csv',index=False)
HCDchilddf5.head()
HCDchilddf5.shape
HCDchilddf5.to_csv('test.csv',index=False)
alllist=['dob',
 'id',
 'subject_id',
 'psuedo_guid',
 'gender',
 'site',
 'M/F',
 'subject',
 'flagged',
 'parent_at_V1',
 'race',
 'ethnic_group',
 'redcap_id_parent',
 'flaggedparent']<jupyter_output><empty_output><jupyter_text>HCD parent and child Curated_*<jupyter_code>#Now that all vars in common are mapped, get parents and children into a single inventory
#fill in holes where stuff outside the intersection of subject and event merge resides.  
#rename a few things so its obvious whose data we're discussing


#parents=HCDparentdf2[['parent_at_visit','parent_at_V1','site','gender','child_id','redcap_event','event_date','id','p_c_sex','race','ethnic_group','dob']].rename(columns={'event_date':'p_event_date','id':'redcap_id_parent','dob':'p_c_dob'})
#parents=parents.replace({'site':{'1': 'MGH/Harvard', '2':'UCLA', '3':'UMinn', '4':'WashU'}})
parents=HCDparentdf2[['parent_at_visit','child_id','redcap_event','event_date','event_register']].rename(columns={'event_date':'p_event_date','event_register':'p_event_register'})
childs=HCDchilddf5.copy()
#misscat: 1, Scan | 2, REDCap | 3, Toolbox | 9, Cognitive Testing | 5, KSADS
#misstoolbox: 1, Child | 2, Parent about Child | 3, Parent about Parent

parents.head()
childs.columns #head()

#findchild
#togetherinventory=pd.merge(parents,childs.drop(columns=['child_id','parent_at_V1','redcap_id_parent','site','race','ethnic_group']),how='outer',left_on=['child_id','redcap_event'],right_on=['subject','redcap_event'], indicator=True)
togetherinventory=pd.merge(parents,childs.drop(columns=['child_id']),how='outer',left_on=['child_id','redcap_event'],right_on=['subject','redcap_event'], indicator=True)
togetherinventory._merge=togetherinventory._merge.str.replace('left_only','parent_only')
togetherinventory._merge=togetherinventory._merge.str.replace('both','parentandchild')

togetherinventory._merge=togetherinventory._merge.str.replace('right_only','child_only')
togetherinventory=togetherinventory.rename(columns={'_merge':'DB_Source'})
togetherinventory.reset_index(inplace=True)

#theseones have now lost their 'child_id'] because event was in child only
togetherinventory.loc[togetherinventory.child_id.isnull()==True,'child_id']=togetherinventory.subject


#pull in the allevents vars again for the ones in parent only
togetherinventory2=pd.merge(togetherinventory.drop(columns=alllist),allevents,on='child_id',how='left')
togetherinventory2.loc[togetherinventory2.event_date.isnull()==True,'event_date']=togetherinventory2.p_event_date
togetherinventory2.loc[togetherinventory2.event_register.isnull()==True,'event_register']=togetherinventory2.p_event_register
togetherinventory2.loc[togetherinventory2.event_age.isnull()==True,'event_age']=(pd.to_datetime(togetherinventory2['event_date'])-pd.to_datetime(togetherinventory2['dob'])).dt.days/365.2425



togetherinventory2.to_csv('test2.csv',index=False)

#create basic event expectations for Toolbox
##toolbox
togetherinventory2=pd.merge(togetherinventory2.drop(columns=['subject']),TLBXD,how='left',left_on=['child_id','redcap_event'],right_on=['subject','event'],indicator=True)
togetherinventory2=togetherinventory2.rename(columns={'_merge':'Curated_TLBX_ChildPIN'}).drop(columns=['event','subject'])
togetherinventory2.Curated_TLBX_ChildPIN=togetherinventory2.Curated_TLBX_ChildPIN.str.replace('both','YES')
togetherinventory2.Curated_TLBX_ChildPIN=togetherinventory2.Curated_TLBX_ChildPIN.str.replace('left_only','NO')
togetherinventory2.loc[(togetherinventory2.Curated_TLBX_ChildPIN=='NO') & (togetherinventory2.redcap_event.isin(['F1','Covid1','Covid2'])),'Curated_TLBX_ChildPIN']="NE"
#togetherinventory2.columns

togetherinventory3=pd.merge(togetherinventory2.rename(columns={'DateFinished':'ChildDateFinishedTLBX'}),TLBXD.rename(columns={'DateFinished':'ParentDateFinishedTLBX'}),how='left',left_on=['parent_at_visit','redcap_event'],right_on=['subject','event'],indicator=True)
togetherinventory3=togetherinventory3.rename(columns={'_merge':'Curated_TLBX_ParentPIN'}).drop(columns=['event','subject'])
togetherinventory3.Curated_TLBX_ParentPIN=togetherinventory3.Curated_TLBX_ParentPIN.str.replace('both','YES')
togetherinventory3.Curated_TLBX_ParentPIN=togetherinventory3.Curated_TLBX_ParentPIN.str.replace('left_only','NO')
togetherinventory3.loc[(togetherinventory3.Curated_TLBX_ParentPIN=='NO') & (togetherinventory3.redcap_event.isin(['F1','Covid1','Covid2'])),'Curated_TLBX_ParentPIN']="NE"



#patch missing redcap_event_name variables in parentonly situations
togetherinventory3.loc[(togetherinventory3.redcap_event_name.isnull()==True) & (togetherinventory3.redcap_event=='F1'),'redcap_event_name']='follow_up_arm_1'
togetherinventory3.loc[(togetherinventory3.redcap_event_name.isnull()==True) & (togetherinventory3.redcap_event=='Covid2'),'redcap_event_name']='covid_2_arm_1'
togetherinventory3.loc[(togetherinventory3.redcap_event_name.isnull()==True) & (togetherinventory3.redcap_event=='Covid1'),'redcap_event_name']='covid_arm_1'

childs.head()
list(allevents.columns)
allevents.loc[allevents.flagged.isnull()==False]
print(pennHCD.columns)
print(erows.columns)
print(TLBXD.columns)
print(Qdf.columns)
print(KSADSdf.columns)
print(doubles.columns)

Qdf.head()
togetherinventory3.head()
#now pull in Penn, Eprime Qinteractive,KSADS,for children
togetherinventory3.columns
colms=['redcap_id_parent','id','parent_at_visit','parent_at_V1','child_id','site','event_age','redcap_event','DB_Source','event_date','p_event_date','M/F','p_c_sex','race','ethnic_group','ChildDateFinishedTLBX','ChildPINfound','ParentDateFinishedTLBX','ParentPINfound','visit','visit2','visit3','visit4','visit8','visit_type','intake_date','age','covidy_dt','covidy_age','register_visit_complete','register_covid2_complete','register_follow_up_complete','register_visit3_complete','register_visit2_complete','register_visit1_complete','intake_interview_complete','puberty_complete','subject_data_block_1_complete','subject_data_block_2_complete','participant_satisfaction_survey_complete','achenbach_alert_complete','external_measures_complete','follow_up_complete','covid_19_questionnaire_youth_complete','toolbox_complete','remote_toolbox_emotion_complete','misstoolbox___1','misstoolbox___2','misstoolbox___3','misscat___1','misscat___2','misscat___3','misscat___9','misscat___5','data_status','misstoolboxc','misstoolboxpc','misstoolboxpp','general_note']
togetherinventory4=pd.merge(togetherinventory3,pennHCD,left_on=['child_id','redcap_event'],right_on=['subid','assessment'],how='left',indicator=True) #the ones in Penn but not together are the 18+ yos
togetherinventory4=togetherinventory4.rename(columns={'_merge':'Curated_PennCNP'})
togetherinventory4.Curated_PennCNP=togetherinventory4.Curated_PennCNP.str.replace('both','YES')
togetherinventory4.Curated_PennCNP=togetherinventory4.Curated_PennCNP.str.replace('left_only','NO')
togetherinventory4['agetest']=pd.to_numeric(togetherinventory4['event_age'],errors='coerce')
togetherinventory4.loc[(togetherinventory4.Curated_PennCNP=="NO") & (togetherinventory4.agetest<8),'Curated_PennCNP']='NE AGE'
togetherinventory4.loc[togetherinventory4.redcap_event.isin(['F1','Covid2','Covid1']),'Curated_PennCNP']='NE'
togetherinventory4.loc[(togetherinventory4.Curated_PennCNP=='NO') & (togetherinventory4.misscat___9=='1') & (togetherinventory4.misscog___2=='1'),'Curated_PennCNP']='NE PM'
togetherinventory4.loc[(togetherinventory4.Curated_PennCNP=='YES') & (togetherinventory4.misscat___9=='1') & (togetherinventory4.misscog___2=='1'),'Curated_PennCNP']='YES BUT'
#togetherinventory4.loc[togetherinventory4.event_age.astype(float)<8.0,'Curated_PennCNP']='NE'

# drop PENN ids known to be permanently missing
#perm_misslist = [
#    'HCA6012744',
#    'HCA6605569',
#    'HCA8689410',
#    'HCA8735289',
#    'HCA8917700',
#    'HCA9695713',
#    'HCD1539759',
#    'HCD1714852',
#    'HCD1769978',
#    'HCD2642858',
#    'HCD2913459',
#    'HCD2978386']
#togetherinventory4.loc[(togetherinventory4.Curated_PennCNP=="NO") & (togetherinventory4.child_id.isin(perm_misslist)),'Curated_PennCNP']='NE'

togetherinventory4=togetherinventory4.drop(columns=['subid','assessment'])
togetherinventory4.groupby('redcap_event').count()
#eprime
#misscat: 1, Scan | 2, REDCap | 3, Toolbox | 9, Cognitive Testing | 5, KSADS
#misstoolbox: 1, Child | 2, Parent about Child | 3, Parent about Parent
#misscog: 1, Q-Interactive - matrix reasoning | 2, UPennCNP | 3, Delayed Discounting



togetherinventory5=pd.merge(togetherinventory4,erows.rename(columns={'visit':'evisit'}),left_on=['child_id','redcap_event'],right_on=['subject','evisit'],how='left',indicator=True) #the ones in Penn but not together are the 18+ yos
togetherinventory5=togetherinventory5.rename(columns={'_merge':'Curated_Eprime'})
togetherinventory5.Curated_Eprime=togetherinventory5.Curated_Eprime.str.replace('both','YES')
togetherinventory5.Curated_Eprime=togetherinventory5.Curated_Eprime.str.replace('left_only','NO')
togetherinventory5.loc[(togetherinventory5.Curated_Eprime=="NO") & (togetherinventory5.agetest>8),'Curated_Eprime']="NE AGE"
togetherinventory5.loc[(togetherinventory5.redcap_event.isin(['Covid1','Covid2','F1','V2','V3'])),'Curated_Eprime']='NE'
togetherinventory5.loc[(togetherinventory5.Curated_Eprime=='NO') & (togetherinventory5.misscat___9=='1') & (togetherinventory4.misscog___3=='1'),'Curated_Eprime']='NE PM'
togetherinventory5.loc[(togetherinventory5.Curated_Eprime=='YES') & (togetherinventory5.misscat___9=='1') & (togetherinventory4.misscog___3=='1'),'Curated_Eprime']='YES BUT'

togetherinventory5=togetherinventory5.drop(columns=['subject','evisit'])

print(Qdf.columns)
Qdf.groupby('visit').count()
#get Q
Qdf['event']=Qdf.visit.astype('str')
Qdf.event=Qdf.event.str.replace('1','V1')
Qdf.event=Qdf.event.str.replace('2','V2')
Qdf.event=Qdf.event.str.replace('3','V3')
Qdf2=Qdf.drop(columns=['visit'])
#misscog: 1, Q-Interactive - matrix reasoning | 2, UPennCNP | 3, Delayed Discounting

togetherinventory6=pd.merge(togetherinventory5,Qdf2,left_on=['child_id','redcap_event'],right_on=['subjectid', 'event'],how='left',indicator="Curated_Q")
togetherinventory6.Curated_Q=togetherinventory6.Curated_Q.str.replace('both','YES')
togetherinventory6.Curated_Q=togetherinventory6.Curated_Q.str.replace('left_only','NO')
togetherinventory6.loc[~(togetherinventory6.redcap_event.isin(['V1','V2','V3'])),'Curated_Q']='NE'
togetherinventory6.loc[(togetherinventory6.Curated_Q=='NO') & (togetherinventory6.misscat___9=='1') & (togetherinventory4.misscog___1=='1'),'Curated_Q']='NE PM'
togetherinventory6.loc[(togetherinventory6.Curated_Q=='YES') & (togetherinventory6.misscat___9=='1') & (togetherinventory4.misscog___1=='1'),'Curated_Q']='YES BUT'


togetherinventory6=togetherinventory6.drop(columns=['subjectid', 'event'])

togetherinventory6.columns
KSADSdf.head()

print(togetherinventory6.columns)
#There are four types of KSADS data corresponding to four IDs that need to be merged into inventory
#parent, child, parent MOOD and child MOOD. Sometimes (no indication as to when) 
#Mood and regular info was collected under same battery (e.g. a regular old V1 id).   

print(KSADSdf.columns)
KSADSdf.patientid.head()
new = KSADSdf['patientid'].str.split("_", 1, expand=True)
KSADSdf['subject'] = new[0].str.strip()
KSADSdf['event'] = new[1].str.strip()
KSADSdf['event']=KSADSdf['event'].str.upper().str.replace(' ','_')
#KSADSdf['subjectlength']=KSADSdf.subject.str.len()
#KSADSdf.loc[KSADSdf.subjectlength>10] #none, whew
KSADSdf.groupby('patienttype').count()
KSADSdf.groupby('event').count()

normalKt=KSADSdf.loc[(KSADSdf.event.isin(['V1','V2','V3'])) & (KSADSdf.patienttype=='T')][['subject','event']]
normalKp=KSADSdf.loc[(KSADSdf.event.isin(['V1','V2','V3'])) & (KSADSdf.patienttype=='P')][['subject','event']]

#misscat_5,  missksads___1, missksads___2
#eprime
#misscat: 1, Scan | 2, REDCap | 3, Toolbox | 9, Cognitive Testing | 5, KSADS
#misstoolbox: 1, Child | 2, Parent about Child | 3, Parent about Parent
#misscog: 1, Q-Interactive - matrix reasoning | 2, UPennCNP | 3, Delayed Discounting


togetherinventory7=pd.merge(togetherinventory6,normalKt,left_on=['child_id','redcap_event'],right_on=['subject', 'event'],how='left',indicator="Curated_KSADS_T")
togetherinventory7.Curated_KSADS_T=togetherinventory7.Curated_KSADS_T.str.replace('both','YES')
togetherinventory7.Curated_KSADS_T=togetherinventory7.Curated_KSADS_T.str.replace('left_only','NO')
togetherinventory7.loc[(togetherinventory7.Curated_KSADS_T=='NO') & (togetherinventory7.agetest<12),'Curated_KSADS_T']='NE AGE'
togetherinventory7.loc[~(togetherinventory7.redcap_event.isin(['V1','V2','V3'])),'Curated_KSADS_T']='NE'
togetherinventory7.loc[(togetherinventory7.Curated_KSADS_T=='NO') & (togetherinventory7.misscat___5=='1') & (togetherinventory7.missksads___1=='1'),'Curated_KSADS_T']='NE PM'
togetherinventory7.loc[(togetherinventory7.Curated_KSADS_T=='YES') & (togetherinventory7.misscat___5=='1') & (togetherinventory7.missksads___1=='1'),'Curated_KSADS_T']='YES BUT'
 
   
    
togetherinventory8=pd.merge(togetherinventory7.drop(columns=['event','subject']),normalKp,left_on=['child_id','redcap_event'],right_on=['subject', 'event'],how='left',indicator="Curated_KSADS_P")
togetherinventory8.Curated_KSADS_P=togetherinventory8.Curated_KSADS_P.str.replace('both','YES')
togetherinventory8.Curated_KSADS_P=togetherinventory8.Curated_KSADS_P.str.replace('left_only','NO')
togetherinventory8.loc[~(togetherinventory8.redcap_event.isin(['V1','V2','V3'])),'Curated_KSADS_P']='NE'
togetherinventory8.loc[(togetherinventory8.Curated_KSADS_P=='NO') & (togetherinventory8.misscat___5=='1') & (togetherinventory8.missksads___2=='1'),'Curated_KSADS_P']='NE PM'
togetherinventory8.loc[(togetherinventory8.Curated_KSADS_P=='YES') & (togetherinventory8.misscat___5=='1') & (togetherinventory8.missksads___2=='1'),'Curated_KSADS_P']='YES BUT'

togetherinventory8=togetherinventory8.drop(columns=['subject','event'])

togetherinventory8.columns

specialK=KSADSdf.loc[(KSADSdf.event.isin(['V1_MOOD','V2_MOOD','V3_MOOD']))].copy()
specialK.groupby('event').count()   
new2=specialK['event'].str.split("_", 1, expand=True)
specialK['newevent']=new2[0].str.strip()
specialK.groupby('newevent').count()

specialKt=specialK.loc[(specialK.newevent.isin(['V1','V2','V3'])) & (specialK.patienttype=='T')][['subject','newevent']]
specialKp=specialK.loc[(specialK.newevent.isin(['V1','V2','V3'])) & (specialK.patienttype=='P')][['subject','newevent']]

specialKt.columns
togetherinventory8.columns
togetherinventory8.event_date=pd.to_datetime(togetherinventory8.event_date)

togetherinventory9=pd.merge(togetherinventory8,specialKt,left_on=['child_id','redcap_event'],right_on=['subject', 'newevent'],how='left',indicator="Curated_KSADS_MOOD_T")
togetherinventory9.Curated_KSADS_MOOD_T=togetherinventory9.Curated_KSADS_MOOD_T.str.replace('both','YES')
togetherinventory9.Curated_KSADS_MOOD_T=togetherinventory9.Curated_KSADS_MOOD_T.str.replace('left_only','NO')
togetherinventory9.loc[(togetherinventory9.Curated_KSADS_MOOD_T=='NO') & (togetherinventory9.agetest<12),'Curated_KSADS_MOOD_T']='NE AGE'
togetherinventory9.loc[(togetherinventory9.Curated_KSADS_MOOD_T=='NO') & (togetherinventory9.event_date < '2020-8-1'),'Curated_KSADS_MOOD_T']='NE'
togetherinventory9.loc[~(togetherinventory9.redcap_event.isin(['V1','V2','V3'])),'Curated_KSADS_MOOD_T']='NE'
togetherinventory9.loc[(togetherinventory9.Curated_KSADS_MOOD_T=='NO') & (togetherinventory9.misscat___5=='1') & (togetherinventory9.missksads___1=='1'),'Curated_KSADS_MOOD_T']='NE PM'
togetherinventory9.loc[(togetherinventory9.Curated_KSADS_MOOD_T=='YES') & (togetherinventory9.misscat___5=='1') & (togetherinventory9.missksads___1=='1'),'Curated_KSADS_MOOD_T']='YES BUT'

togetherinventory9.columns

togetherinventory10=pd.merge(togetherinventory9.drop(columns=['newevent','subject']),specialKp,left_on=['child_id','redcap_event'],right_on=['subject', 'newevent'],how='left',indicator="Curated_KSADS_MOOD_P")
togetherinventory10.Curated_KSADS_MOOD_P=togetherinventory10.Curated_KSADS_MOOD_P.str.replace('both','YES')
togetherinventory10.Curated_KSADS_MOOD_P=togetherinventory10.Curated_KSADS_MOOD_P.str.replace('left_only','NO')
togetherinventory10.loc[(togetherinventory10.Curated_KSADS_MOOD_P=='NO') & (togetherinventory10.event_date < '2020-8-1'),'Curated_KSADS_MOOD_P']='NE'
togetherinventory10.loc[~(togetherinventory10.redcap_event.isin(['V1','V2','V3'])),'Curated_KSADS_MOOD_P']='NE'
togetherinventory10.loc[(togetherinventory10.Curated_KSADS_MOOD_P=='NO') & (togetherinventory10.misscat___5=='1') & (togetherinventory10.missksads___1=='1'),'Curated_KSADS_MOOD_P']='NE PM'
togetherinventory10.loc[(togetherinventory10.Curated_KSADS_MOOD_P=='YES') & (togetherinventory10.misscat___5=='1') & (togetherinventory10.missksads___1=='1'),'Curated_KSADS_MOOD_P']='YES BUT'


togetherinventory10.columns
togetherinventory10.drop(columns=['subject','newevent'])
#togetherinventory10.event_date=pd.to_datetime(togetherinventory10.event_date)
#togetherinventory10.loc[togetherinventory10.event_date < '2020-8-1']
#togetherinventory10.event_date
snapshotdate
#Lastly get doubles
print(doubles.columns)
togetherinventory11=pd.merge(doubles,togetherinventory10,how='right',left_on='HCDid',right_on='parent_at_visit')
#to do.  add in pedigree numbers
print(togetherinventory11.columns)

Xlist

#################################PPPPPPPP
#extra PIN work for TOOLBOX parents of multiple children
togetherinventory11['ParentPIN']=togetherinventory11.parent_at_visit.str.cat(togetherinventory11.redcap_event,sep="_")
togetherinventory11['ParentPIN_alt']=togetherinventory11.parent_at_V1.str.cat(togetherinventory11.redcap_event,sep="_")
togetherinventory11['ChildPIN']=togetherinventory11.child_id.str.cat(togetherinventory11.redcap_event,sep="_")

#merge in known X1 PINS
Xlist=Xlist.rename(columns={'PIN':'ParentPIN','DateFinished':'ParentDateFinishedTLBX','subject':'parent_at_visit'})
Xlist.loc[Xlist.ParentPIN.str.contains('X1'),'redcap_event']='V1'
Xlist.loc[Xlist.ParentPIN.str.contains('V2'),'redcap_event']='V2'
Xlist.loc[Xlist.ParentPIN.str.contains('X2'),'redcap_event']='V2'
Xlist.loc[Xlist.ParentPIN.str.contains('X3'),'redcap_event']='V3'

print(Xlist.drop(columns=['subjectlength','event']))

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0117324_V1','ParentPIN']="HCD3559977_X1"
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0117324_V1','ParentDateFinishedTLBX']='1/19/2020 14:03'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0909761_V1','ParentPIN']="HCD3622655_X1"
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0909761_V1','ParentDateFinishedTLBX']='8/24/19 12:53'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2499374_V1','ParentPIN']="HCD3918272_X1"
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2499374_V1','ParentDateFinishedTLBX']='6/25/19 10:02'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1775266_V2','ParentPIN']='HCD4128753_X2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1775266_V2','ParentDateFinishedTLBX']='8/2/19 16:44'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0836356_V1','ParentPIN']='HCD3629467_X1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0836356_V1','ParentDateFinishedTLBX']='1/27/20 10:42'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0501830_V1','ParentPIN']='HCD5465776_X1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0501830_V1','ParentDateFinishedTLBX']='10/18/20 14:30'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2944066_V1','ParentPIN']='HCD4593073_X1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2944066_V1','ParentDateFinishedTLBX']='4/29/17 17:14'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1335339_V1','ParentPIN']='HCD3366461_X1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1335339_V1','ParentDateFinishedTLBX']='7/25/2021 12:22'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2156142_V1','ParentPIN']='HCD3379874_X1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2156142_V1','ParentDateFinishedTLBX']='3/12/19 16:09'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1681358_V1','ParentPIN']='HCD5744376_X1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1681358_V1','ParentDateFinishedTLBX']='8/8/2019 18:01'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0639455_V1','ParentPIN']='HCD5466980_X1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0639455_V1','ParentDateFinishedTLBX']='11/3/2019 12:08'


#print(Xlist)

#NEW INTEL MAKING ITS WAY THROUGH TRELLO (if empty, this code accounts for  known Xs :



#cases where multiple parents came in for a particular visit and messed with the ability to track toolbox PINs
#note that some of these might still be making their way through pipes to curated
#if not in TLBXD, then make sure they get grabbed from endpoint. 

TLBXD.head()
print(TLBXD.loc[TLBXD.subject.isin(['HCD5443261','HCD4982185','HCD5555474','HCD3429358','HCD5555474','HCD3044641','HCD5016242','HCD4351251','HCD4810257','HCD3404039','HCD4082048','HCD5443261'])])

#cases where multiple parents came in for a particular visit or parents 
#came in for multiple children and messed with the ability to track toolbox PINs

#HCD5555474_V1 is parent PIN associated with HCD0977576_V1 (both parents came in)
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0977576_V1','ParentPIN']='HCD5555474_V1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0977576_V1','ParentDateFinishedTLBX']='8/24/2018 18:21'

#HCD0363743_V1 with HCD5443261_V1
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0363743_V1','ParentPIN']='HCD5443261_V1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0363743_V1','ParentDateFinishedTLBX']='12/19/2017 15:54'


#HCD3044641_V1 not HCD5850476_V1 completed parent about self toolbox data due to injury...i.e. v1 split between two parents.
#affected two subjects Toolbox's parent about self as context for child data: HCD2540042_V1 and HCD1893171_V1
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2540042_V1','ParentPIN']='HCD3044641_V1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2540042_V1','ParentDateFinishedTLBX']='8/11/18 14:36'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1893171_V1','ParentPIN']='HCD3044641_V1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1893171_V1','ParentDateFinishedTLBX']='8/11/18 14:36'

#HCD4982185_V1 with HCD2944874_V1
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2944874_V1','ParentPIN']='HCD4982185_V1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2944874_V1','ParentDateFinishedTLBX']='2021-09-26 15:55:31'



#discovered that parents were the same person so commenting this out - will correct HCD4810257_V2 id in curated
##HCD4810257_V2 not HCD5562875_V2 completed parent about self for HCD2239247_V2 (visit split between two parents)
#togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2239247_V2','ParentPIN']='HCD4810257_V2'
#togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2239247_V2','ParentDateFinishedTLBX']='2019-08-26 09:19:53'

#V1 to V2 PIN-mapping nonsense
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0843151_V1','ParentPIN']='HCD4620555_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0843151_V1','ParentDateFinishedTLBX']='6/11/2019  13:47:00'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1780663_V1','ParentPIN']='HCD3404039_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1780663_V1','ParentDateFinishedTLBX']='2019-10-16 15:47:33'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2980575_V1','ParentPIN']='HCD4046448_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2980575_V1','ParentDateFinishedTLBX']='5/5/2019  10:00:00'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2095754_V1','ParentPIN']='HCD3721859_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2095754_V1','ParentDateFinishedTLBX']='6/25/2019  14:25:25'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1872062_V1','ParentPIN']='HCD5314250_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1872062_V1','ParentDateFinishedTLBX']='7/26/2019  14:18:04'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1194751_V1','ParentPIN']='HCD5857793_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1194751_V1','ParentDateFinishedTLBX']='10/17/2019  10:56:00'



togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0030413_V1','ParentPIN']='HCD5027247_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0030413_V1','ParentDateFinishedTLBX']='10/26/2019  08:19:43'


togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0318435_V1','ParentPIN']='HCD5027247_V3'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0318435_V1','ParentDateFinishedTLBX']='12/14/2020  15:50:49'



#this one on endpoint but not in curated yet as of 36 - adding to list to grab
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1328241_V2','ParentPIN']='HCD5016242_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1328241_V2','ParentDateFinishedTLBX']='2020-01-25 09:10:57'

#this one not yet pulled into endpoing as of round 28 - adding to list to grab
#HCD4351251_V1 is parent PIN associated with HCD2373958_V1 even though other parent came in (both parents came in)
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2373958_V1','ParentPIN']='HCD4351251_V1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2373958_V1','ParentDateFinishedTLBX']='2019-12-19 18:47:35'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2888789_V1','ParentPIN']='HCD4082048_V1'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2888789_V1','ParentDateFinishedTLBX']='2018-09-24 09:39:51'

togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0363743_V2','ParentPIN']='HCD5443261_V2'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0363743_V2','ParentDateFinishedTLBX']='2019-03-11 17:58:19'

#2nd parents at a particular visit above get set to yes
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0977576_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2540042_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1893171_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2239247_V2','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1780663_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1194751_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD1328241_V2','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2373958_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2888789_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0363743_V2','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD2944874_V1','Curated_TLBX_ParentPIN']='YES'
togetherinventory11.loc[togetherinventory11.ChildPIN=='HCD0363743_V1','Curated_TLBX_ParentPIN']='YES'

#singletons where  walking test was rescored and got a new date which  makes it look like a 2017 test was administered in 2021 (important to know about if you want to filter by DateDiff variable)
togetherinventory11.loc[togetherinventory11.ParentPIN=='HCD3324849_V1','ParentDateFinishedTLBX']='6/22/17 10:25'

togetherinventory11.loc[togetherinventory11.ParentPIN=='HCD5604259_V1','ParentDateFinishedTLBX']='7/18/17 10:48'

#cases where SHOULD be an X but it didn't happen are handled
#after the HCD child and HCD 18+ merge at end of this notebook with other NEs


#cleanup of PINs where event was FU or something.
togetherinventory11.loc[~(togetherinventory11.redcap_event.isin(['V1','V2','V3'])),'ParentPIN']=''
togetherinventory11.loc[~(togetherinventory11.redcap_event.isin(['V1','V2','V3'])),'ParentPIN_alt']=''
togetherinventory11.loc[~(togetherinventory11.redcap_event.isin(['V1','V2','V3'])),'ChildPIN']=''


misscols=['toolbox_complete',
       'remote_toolbox_emotion_complete', 'misstoolbox___1', 'misstoolbox___2',
       'misstoolbox___3',  'misscat___3',
       'misscat___9', 'misscat___5', 'data_status', 'misstoolboxc',
       'misstoolboxpc', 'misstoolboxpp', 'general_note', 'misscog___1',
       'misscog___2', 'misscog___3', 'misscogq', 'misscogupen', 'misscogdd',
       'missksads___1', 'missksads___2', 'missksads_c', 'missksads_p']

print("BEFORE DE-DUP")
print(togetherinventory11.shape)

togetherinventory11.drop_duplicates(subset=None, keep='first', inplace=True)

print("After DE-DUP")
print(togetherinventory11.shape)

##create days since first date and parent child tlbx date diff
togetherinventory11.event_date=pd.to_datetime(togetherinventory11.event_date)
togetherinventory11.ParentDateFinishedTLBX=pd.to_datetime(togetherinventory11.ParentDateFinishedTLBX)

togetherinventory11.loc[togetherinventory11.ChildDateFinishedTLBX.astype('str').str.contains('####'),'ChildDateFinishedTLBX']=''
togetherinventory11.ChildDateFinishedTLBX=pd.to_datetime(togetherinventory11.ChildDateFinishedTLBX)

togetherinventory11['ParentChildTLBXdaydiff']=(togetherinventory11.ParentDateFinishedTLBX - togetherinventory11.ChildDateFinishedTLBX).dt.days

date1=togetherinventory11.sort_values(['child_id','event_date'])[['child_id','event_date']].drop_duplicates(subset=['child_id'],keep='first').rename(columns={'event_date':'first_date'})
print(date1[['child_id','first_date']].head())
togetherinventory12=pd.merge(togetherinventory11,date1,how='left',on='child_id')

togetherinventory12['daysfromtime0']=(togetherinventory12.event_date - togetherinventory12.first_date).dt.days
#togetherinventory12['daysfromtime0'].dt.days
print(togetherinventory12.daysfromtime0.head())


keeplist=['id','dob', 'redcap_id_parent','site','psuedo_guid','HCAid', 'HCDid', 'parent_at_visit', 'parent_at_V1', 'child_id',
        'race',  'ethnic_group', 'M/F', 'DB_Source','redcap_event',  'event_date', 'daysfromtime0', 'event_age', 'event_register', 
        'height','weight','bpressure',
        'Curated_TLBX_ChildPIN', 'Curated_TLBX_ParentPIN',
       'Curated_PennCNP', 'Curated_Eprime',
       'Curated_Q', 'Curated_KSADS_T',  'Curated_KSADS_P',
       'Curated_KSADS_MOOD_T', 'Curated_KSADS_MOOD_P',   'ChildDateFinishedTLBX','ParentDateFinishedTLBX', 
          'ParentChildTLBXdaydiff','ParentPIN','ChildPIN',      
       'redcap_event_name',]

keepcols=keeplist+misscols #need to keep dob until the very end because of NDA age calculation


parentchild=togetherinventory12.copy()
print(parentchild.columns)<jupyter_output><empty_output><jupyter_text>HCD 18<jupyter_code>#HCD18+
HCD18df=getreport(data=data18)
HCD18df.head()
hcpd18=HCD18df.loc[HCD18df.redcap_event_name=='visit_arm_1'].copy()
hcpd18.head()
hcpd18.columns
hcpd18.sub_race___10=hcpd18.sub_race___10.astype(int)
hcpd18.sub_race___11=hcpd18.sub_race___11.astype(int)
hcpd18.sub_race___12=hcpd18.sub_race___12.astype(int)
hcpd18.sub_race___14=hcpd18.sub_race___14.astype(int)
hcpd18.sub_race___18=hcpd18.sub_race___18.astype(int)
hcpd18.sub_race___25=hcpd18.sub_race___25.astype(int)
hcpd18.sub_race___99=hcpd18.sub_race___99.astype(int)
#.str.strip().replace('','0')

hcpd18['sumcheckedrace']=hcpd18[['sub_race___10','sub_race___11','sub_race___12','sub_race___14','sub_race___18','sub_race___25']].sum(axis=1)

#HCPD18 folks can select multiple race options (checkbox)...need to convert those to 'more than one race' category of mutually exclusive version of race variable required by NDAR
hcpd18.loc[(hcpd18.sumcheckedrace==1) & (hcpd18.sub_race___10==1),'racial']=5
hcpd18.loc[(hcpd18.sumcheckedrace==1) & (hcpd18.sub_race___11==1),'racial']=3
hcpd18.loc[(hcpd18.sumcheckedrace==1) & (hcpd18.sub_race___12==1),'racial']=1
hcpd18.loc[(hcpd18.sumcheckedrace==1) & (hcpd18.sub_race___14==1),'racial']=4
hcpd18.loc[(hcpd18.sumcheckedrace==1) & (hcpd18.sub_race___18==1),'racial']=2
hcpd18.loc[(hcpd18.sumcheckedrace==1) & (hcpd18.sub_race___25==1),'racial']=6
hcpd18.loc[(hcpd18.sumcheckedrace==0) & (hcpd18.sub_race___99==1),'racial']=99
hcpd18.loc[hcpd18.sumcheckedrace>=2,'racial']=6

#hcpd18[['sub_race___10','sub_race___11','sub_race___12','sub_race___14','sub_race___18','sub_race___25','sumcheckedrace','racial']].head(20)
hcpd18.loc[hcpd18.racial==1,'race']='American Indian/Alaska Native'
hcpd18.loc[hcpd18.racial==2,'race']='Asian'
hcpd18.loc[hcpd18.racial==3,'race']='Black or African American'
hcpd18.loc[hcpd18.racial==4,'race']='Hawaiian or Pacific Islander'
hcpd18.loc[hcpd18.racial==5,'race']='White'
hcpd18.loc[hcpd18.racial==6,'race']='More than one race'
hcpd18.loc[hcpd18.racial==99,'race']='Unknown or not reported'


hcpd18.loc[hcpd18.sub_latino=='1','ethnic_group']='Hispanic or Latino'
hcpd18.loc[hcpd18.sub_latino=='0','ethnic_group']='Not Hispanic or Latino'
hcpd18.loc[hcpd18.sub_latino=='9','ethnic_group']='Unknown or not reported'

hcpd18[['sub_race___10','sub_race___11','sub_race___12','sub_race___14','sub_race___18','sub_race___25','sumcheckedrace','racial','race']].head()

#missing because excluded. all good.
hcpd18.loc[hcpd18.race.isnull()==True]
hcpd18.loc[hcpd18.ethnic_group.isnull()==True]
allevents=hcpd18[['dob','id','subject_id','gender','site','race','ethnic_group','psuedo_guid']].replace({'site':
                                      {'1': 'MGH/Harvard', '2':'UCLA', '3':'UMinn', '4':'WashU'}}).copy()
allevents['M/F']=allevents.replace({'gender':
                                           {'1':'M',
                                            '2':'F'}})['gender']

#cpature the flags
new = allevents['subject_id'].str.split("_", 1, expand=True)
allevents['subject'] = new[0].str.strip()
allevents['flagged'] = new[1].str.strip()

#propagate all events fields throughout rest of events
HCD18df2=pd.merge(allevents,HCD18df.drop(columns=['subject_id','dob','gender','psuedo_guid','site','sub_race___12', 'sub_race___18', 'sub_race___11',
       'sub_race___14', 'sub_race___10', 'sub_race___25', 'sub_race___99',
       'sub_latino']),how='right',on='id')
HCD18df2.head()
HCD18df2.flagged.unique()

#removed flagged individuals
print(HCD18df2.shape)
HCD18df3=HCD18df2.loc[(HCD18df2.flagged.isnull()==True)].copy()
print(HCD18df3.shape)

#recode event names
HCD18df3['redcap_event']=HCD18df3.replace({'redcap_event_name':
                                           {'visit_arm_1':'V1',
                                            'follow_up_arm_1':'F1',
                                            'covid_arm_1':'Covid',
                                            'covid_2_arm_1':'Covid2'
                                           }})['redcap_event_name'].copy()


HCD18df3.columns<jupyter_output><empty_output><jupyter_text>HCD 18 Event Harmonization<jupyter_code>#intake_date age v3_date age_v3 visit_type visit visit7 visit8 covid_dt covid18_dt covid18_age
#visit_arm_1
#follow_up_arm_1
#covid_arm_1
#covid_2_arm_1
HCD18df3['event_date']=''
HCD18df3['event_age']=''
HCD18df3['event_register']=''

HCD18df3.loc[HCD18df3.redcap_event.isin(['V1']),'event_date']=HCD18df3.intake_date
HCD18df3.loc[HCD18df3.redcap_event.isin(['V1']),'event_age']=HCD18df3.age
HCD18df3.loc[HCD18df3.redcap_event.isin(['V1']),'event_register']=HCD18df3.visit_type

HCD18df3.loc[HCD18df3.redcap_event.isin(['F1']),'event_date']=HCD18df3.v3_date
HCD18df3.loc[HCD18df3.redcap_event.isin(['F1']),'event_age']=HCD18df3.age_v3
HCD18df3.loc[HCD18df3.redcap_event.isin(['F1']),'event_register']=HCD18df3.visit

HCD18df3.loc[HCD18df3.redcap_event.isin(['Covid']),'event_date']=HCD18df3.covid_dt
#HCD18df3.loc[HCD18df3.redcap_event.isin(['Covid']),'event_age']=HCD18df3.age
HCD18df3.loc[HCD18df3.redcap_event.isin(['Covid']),'event_register']=HCD18df3.visit7

HCD18df3.loc[HCD18df3.redcap_event.isin(['Covid2']),'event_date']=HCD18df3.covid18_dt
HCD18df3.loc[HCD18df3.redcap_event.isin(['Covid2']),'event_age']=HCD18df3.covid18_age
HCD18df3.loc[HCD18df3.redcap_event.isin(['Covid2']),'event_register']=HCD18df3.visit8

#HCAdf2.loc[HCAdf2['event_date'].str.contains('2929'),'event_date']=''
HCD18df3['alt_age']=(pd.to_datetime(HCD18df3['event_date'])-pd.to_datetime(HCD18df3['dob'])).dt.days/365.2425
HCD18df3.reset_index(inplace=True)

#assign missing ages to Covid Arms
HCD18df3.loc[HCD18df3.redcap_event.isin(['Covid']),'event_age']=HCAdf2.alt_age




#remove empty rows
print(HCD18df3.shape)
HCD18df3=HCD18df3.loc[~(HCD18df3.redcap_event.isin(['Covid']) & (HCD18df3.event_date=='') & (HCD18df3.covid_complete=='0'))]
print(HCD18df3.shape)
HCD18df4=HCD18df3.loc[~((HCD18df3.event_register=='8') & (HCD18df3.event_date=='') & (HCD18df3.covid_19_questionnaire_youth_complete=='0'))]
print(HCD18df4.shape)
HCD18df4.columns

#HCD18df4[['site','redcap_event','id','subject','event_date','M/F','event_age','race', 'ethnic_group','intake_date', 'visit_type', 'age', 'visit', 'v3_date', 'age_v3',
#       'visit7', 'visit8', 'covid_dt',  'covid18_dt',
#       'covid18_age', 'alt_age','covid_19_questionnaire_youth_complete', 'covid_complete']].sort_values(['site','redcap_event']).to_csv('hcd18test.csv',index=False)
#        ]].head(10)
#1, Scan | 2, REDCap | 3, Toolbox | 9, Cognitive Testing | 5, KSADS
TLBXD.head()
HCD18df4.head()
HCD18df4.loc[HCD18df4.redcap_event.isin(['V1'])]<jupyter_output><empty_output><jupyter_text>HCD 18 Curated_*<jupyter_code>HCD18df4.groupby('redcap_event').count()
HCD18df4.event_date=pd.to_datetime(HCD18df4.event_date)

togetherinventory=pd.merge(HCD18df4,TLBXD,how='left',left_on=['subject','redcap_event'],right_on=['subject','event'],indicator='Curated_TLBX').copy()
togetherinventory.Curated_TLBX=togetherinventory.Curated_TLBX.str.replace('both','YES')
togetherinventory.Curated_TLBX=togetherinventory.Curated_TLBX.str.replace('left_only','NO')
togetherinventory.loc[~(togetherinventory.redcap_event.isin(['V1'])),'Curated_TLBX']='NE'
togetherinventory=togetherinventory.rename(columns={'DateFinished':'DateFinishedTLBX'}).copy()
togetherinventory=togetherinventory.drop(columns=['event']).copy()

togetherinventory2=pd.merge(togetherinventory,pennHCD,left_on=['subject','redcap_event'],right_on=['subid','assessment'],how='left',indicator='Curated_PennCNP').copy()
togetherinventory2.Curated_PennCNP=togetherinventory2.Curated_PennCNP.str.replace('both','YES')
togetherinventory2.Curated_PennCNP=togetherinventory2.Curated_PennCNP.str.replace('left_only','NO')
togetherinventory2.loc[~(togetherinventory2.redcap_event.isin(['V1'])),'Curated_PennCNP']='NE'
togetherinventory2.loc[(togetherinventory2.Curated_PennCNP=='NO') & (togetherinventory2.misscat___9=='1') & (togetherinventory2.misscog___2=='1'),'Curated_PennCNP']='NE PM'
togetherinventory2.loc[(togetherinventory2.Curated_PennCNP=='YES') & (togetherinventory2.misscat___9=='1') & (togetherinventory2.misscog___2=='1'),'Curated_PennCNP']='YES BUT'
togetherinventory2=togetherinventory2.drop(columns=['subid','assessment']).copy()

togetherinventory3=pd.merge(togetherinventory2,Qdf2,left_on=['subject','redcap_event'],right_on=['subjectid', 'event'],how='left',indicator="Curated_Q").copy()
togetherinventory3.Curated_Q=togetherinventory3.Curated_Q.str.replace('both','YES')
togetherinventory3.Curated_Q=togetherinventory3.Curated_Q.str.replace('left_only','NO')
togetherinventory3.loc[~(togetherinventory3.redcap_event.isin(['V1'])),'Curated_Q']='NE'
togetherinventory3.loc[(togetherinventory3.Curated_Q=='NO') & (togetherinventory3.misscat___9=='1') & (togetherinventory3.misscog___1=='1'),'Curated_Q']='NE PM'
togetherinventory3.loc[(togetherinventory3.Curated_Q=='YES') & (togetherinventory3.misscat___9=='1') & (togetherinventory3.misscog___1=='1'),'Curated_Q']='YES BUT'
togetherinventory3=togetherinventory3.drop(columns=['subjectid', 'event']).copy()


togetherinventory4=pd.merge(togetherinventory3,normalKt,left_on=['subject','redcap_event'],right_on=['subject', 'event'],how='left',indicator="Curated_KSADS_T").copy()
togetherinventory4.Curated_KSADS_T=togetherinventory4.Curated_KSADS_T.str.replace('both','YES')
togetherinventory4.Curated_KSADS_T=togetherinventory4.Curated_KSADS_T.str.replace('left_only','NO')
togetherinventory4.loc[~(togetherinventory4.redcap_event.isin(['V1'])),'Curated_KSADS_T']='NE'
togetherinventory4['Curated_KSADS_P']='NE AGE'
togetherinventory4.loc[(togetherinventory4.Curated_KSADS_T=='NO') & (togetherinventory7.misscat___5=='1'),'Curated_KSADS_T']='NE PM'
togetherinventory4.loc[(togetherinventory4.Curated_KSADS_T=='YES') & (togetherinventory7.misscat___5=='1'),'Curated_KSADS_T']='YES BUT'
togetherinventory4=togetherinventory4.drop(columns=['event']).copy()
 
#PM=['HCD0051825_V1']
togetherinventory4.loc[(togetherinventory4.Curated_KSADS_T=='NO') & (togetherinventory4.subject=='HCD0051825'),'Curated_KSADS_T']='NE PM'

    
togetherinventory5=pd.merge(togetherinventory4,specialKt,left_on=['subject','redcap_event'],right_on=['subject', 'newevent'],how='left',indicator="Curated_KSADS_MOOD_T").copy()
togetherinventory5.Curated_KSADS_MOOD_T=togetherinventory5.Curated_KSADS_MOOD_T.str.replace('both','YES')
togetherinventory5.Curated_KSADS_MOOD_T=togetherinventory5.Curated_KSADS_MOOD_T.str.replace('left_only','NO')
togetherinventory5.loc[(togetherinventory5.Curated_KSADS_MOOD_T=='NO') & (togetherinventory5.event_date < '2020-8-1'),'Curated_KSADS_MOOD_T']='NE'
togetherinventory5.loc[~(togetherinventory5.redcap_event.isin(['V1'])),'Curated_KSADS_MOOD_T']='NE'
togetherinventory5['Curated_KSADS_MOOD_P']='NE AGE'
togetherinventory5.loc[(togetherinventory5.Curated_KSADS_MOOD_T=='NO') & (togetherinventory5.misscat___5=='1') ,'Curated_KSADS_MOOD_T']='NE PM'
togetherinventory5.loc[(togetherinventory5.Curated_KSADS_MOOD_T=='YES') & (togetherinventory5.misscat___5=='1'),'Curated_KSADS_MOOD_T']='YES BUT'

togetherinventory5['Curated_Eprime']='NE AGE'
#[['id','site', 
#'subject', 'flagged','misscat___3','data_status', 'misstoolbox', 'general_note','_merge',
#       'redcap_event', 'event_date', 'event_age', 'event_register']]


togetherinventory5['DB_Source']='teen'

##create days since first date
togetherinventory5.event_date=pd.to_datetime(togetherinventory5.event_date)

date1=togetherinventory5.sort_values(['subject','event_date'])[['subject','event_date']].drop_duplicates(subset=['subject'],keep='first').rename(columns={'event_date':'first_date'})
print(date1[['subject','first_date']].head())
togetherinventory6=pd.merge(togetherinventory5,date1,how='left',on='subject')

togetherinventory6['daysfromtime0']=(togetherinventory6.event_date - togetherinventory6.first_date).dt.days
#togetherinventory6['daysfromtime0'].days()
print(togetherinventory6.daysfromtime0.head())


#togetherinventory.loc[togetherinventory.redcap_event.isin(['V1'])].to_csv('hcd18TLBXinventory.csv',index=False)
togetherinventory6.columns
reorder=['id',  'site', 'psuedo_guid','race',   'ethnic_group', 'M/F', 'subject', 'height', 'weight', 'bpressure',
         'redcap_event', 'event_date', 'dob','event_age','alt_age','daysfromtime0',
       'event_register',  'Curated_TLBX','DateFinishedTLBX','Curated_PennCNP', 'Curated_Q', 'Curated_KSADS_T',
        'Curated_KSADS_MOOD_T', 'Curated_KSADS_P','Curated_Eprime',
        'Curated_KSADS_MOOD_P','DB_Source',
         'redcap_event_name',
        'misscat___3', 'misscat___9',
       'misscat___5', 'misstoolbox', 'general_note',
       'misscog___1', 'misscog___2', 'misscogq', 'misscogupen',
       'missssaga']


teens=togetherinventory6.copy()
#teens[reorder].to_csv('hcd18_Inventory'+snapshotdate+'.csv',index=False)
print(teens[reorder].columns)
print(teens.columns)
print(parentchild[keeplist+misscols].columns)

#teens.subject_id
#teens.loc[teens.subject_id=='HCD0671956']
#teens.loc[teens.subject_id]#.str.contains('HCD0671956')]
#put HCD-child and HCD-18+ together

hcdconcat=pd.concat([teens[reorder],parentchild[keeplist+misscols]],sort=False)


hcdconcat=hcdconcat.reset_index()
hcdconcat.loc[hcdconcat.subject.isnull()==True,'subject']=hcdconcat.child_id
hcdconcat.loc[hcdconcat.Curated_TLBX.isnull()==True,'Curated_TLBX']=hcdconcat.Curated_TLBX_ChildPIN
hcdconcat.loc[hcdconcat.DateFinishedTLBX.isnull()==True,'DateFinishedTLBX']=hcdconcat.ChildDateFinishedTLBX

hcdconcat=hcdconcat.rename(columns={'Curated_TLBX_ParentPIN':'Curated_TLBX_Parent'})
hcdconcat['PIN']=hcdconcat.subject.str.cat(hcdconcat.redcap_event,sep="_")


hcdconcat.loc[~(hcdconcat.redcap_event.isin(['V1','V2','V3'])),'PIN']=''
#hcdconcat.loc[(hcdconcat.DB_Source=='teen') & (hcdconcat.redcap_event=='F1'),'PIN'].head()<jupyter_output><empty_output><jupyter_text>Advanced Curated_* info for Toolbox<jupyter_code>#toolbox 'NE BUTs' (e.g. permanently missing data - needed to pool all HCD sources before this was possible)
hcdconcat.loc[(hcdconcat.subject=='HCD2730249') & (hcdconcat.ParentPIN=='HCD4511146_V3'),'Curated_TLBX_Parent']='NE PM'
hcdconcat.loc[hcdconcat.ParentPIN=='HCD5164156_V1','Curated_TLBX_Parent']='NE PM'
hcdconcat.loc[hcdconcat.PIN=='HCD2731049_V3','Curated_TLBX']='NE PM'
hcdconcat.loc[hcdconcat.ParentPIN=='HCD3883178_V3','Curated_TLBX_Parent']='NE PM'              
hcdconcat.loc[hcdconcat.PIN=='HCD2828569_V3','Curated_TLBX']='NE PM'
hcdconcat.loc[hcdconcat.ParentPIN=='HCD3736771_V3','Curated_TLBX_Parent']='NE PM'
hcdconcat.loc[hcdconcat.PIN=='HCD1769978_V1','Curated_TLBX']='NE PM'


hcdconcat.loc[hcdconcat.PIN.isin(['HCD2199867_V1','HCD0134526_V3','HCD0288250_V3','HCD0259546_V3','HCD0181838_V3','HCD0598366_V3','HCD0024418_V3','HCD0956871_V3','HCD2295762_V3','HCD0113316_V3','HCD1655458_V3','HCD2485666_V3','HCD1227740_V3','HCD0802440_V3']),'Curated_TLBX']='NE PM'
hcdconcat.loc[hcdconcat.ParentPIN.isin(['HCD4819275_V3','HCD3724663_V3','HCD3718163_V3','HCD4248763_V3','HCD4335657_V3','HCD4763880_V3','HCD3404039_V3','HCD3760465_V3','HCD3721859_V3','HCD5952989_V3','HCD5337666_V1']),'Curated_TLBX_Parent']='NE PM'
   
hcdconcat.loc[hcdconcat.PIN.isin(['HCD0869371_V3','HCD0777265_V3','HCD1852056_V1','HCD1880162_V3']),'Curated_TLBX']='NE PM'
hcdconcat.loc[hcdconcat.ParentPIN.isin(['HCD3915771_V1','HCD3663568_V1','HCD5060245_V1','HCD5549176_V2', 'HCD5549176_V2','HCD5549176_V2','HCD4451558_V2','HCD5060245_V1','HCD3644362_V3','HCD5212040_V2','HCD5337666_V1','HCD4815065_V3','HCD5186570_V3','HCD4919784_V2','HCD5944384_V1','HCD4033843_V1','HCD5664984_V1','HCD4719372_V2','HCD3380152_V1']),'Curated_TLBX_Parent']='NE PM'
#Note: HCA7424873_V1 = HCD5337666_V1, however Parent is of child who had failure to complete.  Listed as NE PM above, and NE PM here in case they show up again, but parent isnt in inventory.
               
               
#would be X1s who didn't complete  Toolbox for second child.
hcdconcat.loc[(hcdconcat.subject=='HCD0927056') & (hcdconcat.ParentPIN=='HCD5374369_V1'),'Curated_TLBX_Parent']='NE X'
hcdconcat.loc[(hcdconcat.subject=='HCD0927056') & (hcdconcat.ParentPIN=='HCD5374369_V1'),'ParentDateFinishedTLBX']=''
hcdconcat.loc[(hcdconcat.subject=='HCD0927056') & (hcdconcat.ParentPIN=='HCD5374369_V1'),'ParentChildTLBXdaydiff']=''

hcdconcat.loc[(hcdconcat.subject=='HCD1188756') & (hcdconcat.ParentPIN=='HCD4891990_V1'),'Curated_TLBX_Parent']='NE X'
hcdconcat.loc[(hcdconcat.subject=='HCD1188756') & (hcdconcat.ParentPIN=='HCD4891990_V1'),'ParentDateFinishedTLBX']=''
hcdconcat.loc[(hcdconcat.subject=='HCD1188756') & (hcdconcat.ParentPIN=='HCD4891990_V1'),'ParentChildTLBXdaydiff']=''

hcdconcat.loc[(hcdconcat.subject=='HCD2642858') & (hcdconcat.ParentPIN=='HCD4679689_V1'),'Curated_TLBX_Parent']='NE X'
hcdconcat.loc[(hcdconcat.subject=='HCD2642858') & (hcdconcat.ParentPIN=='HCD4679689_V1'),'ParentDateFinishedTLBX']=''
hcdconcat.loc[(hcdconcat.subject=='HCD2642858') & (hcdconcat.ParentPIN=='HCD4679689_V1'),'ParentChildTLBXdaydiff']=''


#NE Double
hcdconcat.loc[hcdconcat.ParentPIN.isin(['HCD4064248_V1','HCD5896703_V1','HCD5937387_V1','HCD5342154_V1','HCD5375472_V2','HCD5177771_V1']),'Curated_TLBX_Parent']='SEE DOUBLE' # double winner=HCA9268996

#teen parents 
hcdconcat.loc[hcdconcat.DB_Source=='teen','Curated_TLBX_Parent']='NE AGE'


#registered events that didnt happen - consider removing from REDCap altogether
#this one is registered in parent database only...
hcdconcat=hcdconcat.loc[~(hcdconcat.PIN.isin(['HCD0406230_V2']))].copy()

#get list of legit HCD subjects
hcdconcat.columns
a=pd.DataFrame(hcdconcat.loc[hcdconcat.ParentPIN.isnull()==False,'ParentPIN'])
a.head()
b=pd.DataFrame(a.loc[~(a.ParentPIN=="")])
p=list(b.ParentPIN.str[:10].unique()) #.ParentPin#.str[:10]
allsubs=list(hcdconcat.subject.unique())+list(hcdconcat.parent_at_V1.unique())+list(hcdconcat.parent_at_visit.unique())+p
len(allsubs)

subsubs=list(hcdconcat.subject.unique())
#subsubs


#'HCD4810257' in list(hcdconcat.parent_at_visit.unique())
#b.ParentPIN.str[:10].unique()

print("TLBXD IDS that dont exist in REDCap: create trello card to investigate and possibly drop from curated")
for i in TLBXD.subject:
    if i in allsubs:
        pass
    else:
        print(i)


print("Penn IDS that dont exist in REDCap: create trello card to investigate and set unusable in curated")
for i in pennHCD.subid:
    if i in subsubs:
        pass
    else:
        print(i)

print("Q-int IDs that doen't exist in REDCap: create trello cards to investigate and set unusable in curated")
for i in Qdf2.loc[Qdf2.subjectid.str.contains('HCD'),'subjectid']:
    if i in subsubs:
        pass
    else:
        print(i)

#ravvos=pd.merge(HCAdf3,Qdf2,left_on=['subject'],right_on=['subjectid'],how='right',indicator="Ravvos")
#print(ravvos.loc[(ravvos.Ravvos=='right_only') & ~(ravvos.subjectid.str.contains('HCD'))][['subjectid']])

#print("SSAGA IDs that don't exist in REDCap: create trello cards to investigate and drop from curated")
#ssagos=pd.merge(HCAdf3,ssaga,left_on=['subject'],right_on=['hcpa_id'],how='right',indicator="Ssagos")
#print(ssagos.loc[(ssagos.Ssagos=='right_only')][['hcpa_id']])
##note that HCA7297488 and HCA8680189 and HCA8363682 withdrew.  SSAGA database doesnt say as much but pre-release 
##folders already account for this information because they refer to the HCPA redcap database as 
##gold standard for withdrawn status is appendix to name in HCPA database.    

#HCD0661448 DNR (18+)
#HCD0671956 FTC (18+)
#HCD0971261 withdrew (child) - double check that mask drops these prior to Prerelease
#HCD1027530 excluded (child) - ''
#HCD1616953 withdrew (child) - ''
#HCD1703039 excluded (child) - ''
#HCD1727558 withdrew (child) - ''
#HCD2113528 excluded (child) - ''
#HCD2384761 withdrew (child) - ''
#HCD2557463 withdrew (child) - ''

#HCD3367766 appears to be a parent of a DNR child - > remove from curated?  
#HCD3377971 is parent of child HCD2199867 according to parent database, but HCD2199867 is FTC -> remove from curated and make sure child is removed, too?  

#HCD3563665 parent of three children, all of whom were withdrawn, excluded, or DNR

#HCD4810257 is accidental alias of parent...in progress: replace HCD4810257_V2 Pin with HCD5562875_V2 in curated

#HCD5681681 and HCD5750742 are both listed as double winners, but cant find record of their role as parents in REDCap databse. 

###MAKE SURE THIS LIST IS GONE FROM PRERELEASE!!!



#DO NOT FILTER  HCD3429964. Is parent of one DNR and also an HCA and sister of another HCA

#backup
#hcdconcat2=hcdconcat.copy()
#hcdconcat=hcdconcat2.copy()
print(hcdconcat.columns)




#final cleanup of YES BUTS for known exceptions to BUT rule
#this code commented out until last run AFTER study data finished completion
#these guys were people whose mood batteries wiere missing so an ambiguous box was checked in the visit summary
hcdconcat.loc[(hcdconcat.Curated_KSADS_T=='YES BUT') & (hcdconcat.redcap_event=='V3'),'Curated_KSADS_T']='YES'
hcdconcat.loc[(hcdconcat.Curated_KSADS_T=='NO'),'Curated_KSADS_T']='NE PM'
hcdconcat.loc[(hcdconcat.Curated_KSADS_P=='NO'),'Curated_KSADS_P']='NE PM'
hcdconcat.loc[(hcdconcat.Curated_KSADS_MOOD_T=='NO'),'Curated_KSADS_MOOD_T']='NE PM'
hcdconcat.loc[(hcdconcat.Curated_KSADS_MOOD_P=='NO'),'Curated_KSADS_MOOD_P']='NE PM'


hcdconcatbackup=hcdconcat.copy()
hcdconcat.columns
#CREATE NDA VARS
#nda interview date
hcdconcat['nda_interview_date']=pd.to_datetime(hcdconcat['event_date']) - pd.offsets.QuarterBegin(startingMonth=1)

#nda age in months
hcdconcat['nda_age'] = (
        12 * (pd.to_datetime(hcdconcat['event_date']).dt.year - pd.to_datetime(hcdconcat.dob).dt.year) +
        (pd.to_datetime(hcdconcat['event_date']).dt.month - pd.to_datetime(hcdconcat.dob).dt.month) +
        (pd.to_datetime(HCAdf7['event_date']).dt.day - pd.to_datetime(HCAdf7.dob).dt.day) / 31)
hcdconcat.nda_age=hcdconcat.nda_age.round() 
#HCAdf7['check_age']= HCAdf7.event_age.replace('',np.nan).astype(float)*12
hcdconcat.loc[HCAdf7.nda_age > 1080, 'nda_age'] = 1200
hcdconcat.sort_values(by='nda_age',ascending=False)[['subject','redcap_event','event_age','nda_age','event_date','nda_interview_date']]
#merge in pedfinal by pseudo_guid

pedfinal.head()
hcdconcat=pd.merge(pedfinal,hcdconcat,how='right',on='psuedo_guid')#,indicator='bb')
#HCAdf7.bb.value_counts()  
hcdconcat[['pedid','psuedo_guid','subject','redcap_event','nda_age','event_age','event_date','nda_interview_date']]
hcdconcat.loc[hcdconcat.site=='MGH/Harvard','site']='Harvard'

hcdconcat.loc[hcdconcat.DateFinishedTLBX.astype('str').str.contains('####'),'DateFinishedTLBX']=''
hcdconcat.DateFinishedTLBX=pd.to_datetime(hcdconcat.DateFinishedTLBX)

allcols=['pedid','nda_age','nda_interview_date','id', 'redcap_id_parent', 'psuedo_guid', 'DB_Source', 'HCAid', 'HCDid', 'subject','parent_at_visit', 'parent_at_V1',
          'site', 'race', 'ethnic_group', 'M/F', 
       'height', 'weight','redcap_event_name', 'redcap_event', 'event_date','daysfromtime0',
       'event_age', 'event_register', 'Curated_Eprime',
       'Curated_PennCNP', 'Curated_Q', 'Curated_KSADS_T',
       'Curated_KSADS_MOOD_T', 'Curated_KSADS_P', 
       'Curated_KSADS_MOOD_P', 'Curated_TLBX',  'Curated_TLBX_Parent', 
       'PIN', 'DateFinishedTLBX',
       'ParentPIN','ParentDateFinishedTLBX', 'ParentChildTLBXdaydiff',
       'misscat___3',
       'misscat___9', 'misscat___5', 'misstoolbox', 'general_note',
       'misscog___1', 'misscog___2', 'misscogq', 'misscogupen', 'missssaga',
       'toolbox_complete', 'remote_toolbox_emotion_complete',
       'misstoolbox___1', 'misstoolbox___2', 'misstoolbox___3', 'data_status',
       'misstoolboxc', 'misstoolboxpc', 'misstoolboxpp', 'misscog___3',
       'misscogdd', 'missksads___1', 'missksads___2', 'missksads_c',
       'missksads_p']

slimcols=['pedid','nda_age','nda_interview_date','id', 'redcap_id_parent', 'psuedo_guid', 'DB_Source', 'HCAid', 'HCDid', 'subject','parent_at_visit', 'parent_at_V1',
          'site', 'race', 'ethnic_group', 'M/F', 
       'height', 'weight', 'redcap_event_name', 'redcap_event', 'daysfromtime0',
       'event_age', 'Curated_Eprime',
       'Curated_PennCNP', 'Curated_Q', 'Curated_KSADS_T',
       'Curated_KSADS_MOOD_T', 'Curated_KSADS_P', 
       'Curated_KSADS_MOOD_P', 'Curated_TLBX',  'Curated_TLBX_Parent', 
       'PIN','ParentPIN', 'ParentChildTLBXdaydiff']









#export a few useful forms of the inventory - slim goes up to the PreRelease folder
hcdconcat[allcols].rename(columns={'id':'REDCap_id','redcap_id_parent':'REDCap_id_parent','psuedo_guid':'pseudo_guid'}).to_csv('HCD_AllSources_'+snapshotdate+'.csv',index=False)
hcdconcat[slimcols].rename(columns={'id':'REDCap_id','redcap_id_parent':'REDCap_id_parent','psuedo_guid':'pseudo_guid'}).to_csv('HCD_AllSourcesSlim_' + snapshotdate + '.csv',index=False)<jupyter_output><empty_output><jupyter_text>Missingness Stats for TOOLBOX<jupyter_code>import sys
#print(hcdconcat[['site','redcap_event','Curated_TLBX_Parent','index']].loc[hcdconcat.redcap_event.isin(['V1','V2','V3'])].groupby(['site']).count())
#print(hcdconcat[['site','redcap_event','Curated_TLBX_Parent','index']].loc[hcdconcat.Curated_TLBX_Parent=='NO'].groupby(['site']).count())
orig_stdout = sys.stdout
f = open(os.path.join('HCD_Missingness_Report_'+snapshotdate+'.txt'),'w')
sys.stdout = f

a=hcdconcat.copy()#.groupby(['site']).count())
a['datediff']=(pd.to_datetime(a['event_date'])-datetime.datetime.today()).dt.days
t0=a.loc[a.datediff.abs()>14].copy()

print('********************************************************************************')
print('DATA UNDER PARENT ID ***********************************************************')
for curatedstar in['Curated_TLBX_Parent']:
    print('********************************************************************************')
    print(curatedstar)
    #print(hcdconcat[['site','redcap_event',curatedstar,'index']].loc[hcdconcat.redcap_event.isin(['V1','V2','V3'])].groupby(['site','redcap_event',curatedstar]).count())

    #parents
    t1=t0.loc[~(t0[curatedstar].isin(['NE AGE','NE']))][['site','redcap_event',curatedstar,'index']]
    totals=pd.DataFrame(t1.loc[t1.redcap_event.isin(['V1','V2','V3'])].groupby(['site']).count())
    #totals=pd.DataFrame(hcdconcat[['site','redcap_event',curatedstar,'index']].loc[hcdconcat.redcap_event.isin(['V1','V2','V3'])].groupby(['site']).count())
    missings=pd.DataFrame(t1[['site','redcap_event',curatedstar,'index']].loc[hcdconcat[curatedstar]=='NO'].groupby(['site']).count())

    totals['Total_Parents']=totals[curatedstar]
    missings['Missing_Parents']=missings[curatedstar]

    statsparents=pd.concat([totals[['Total_Parents']],missings[['Missing_Parents']]],axis=1,sort=True)
    statsparents['PCT_Missing']=round(((statsparents.Missing_Parents)/(statsparents.Total_Parents))*100.0,1)
    print(statsparents)

    #statsparents.to_csv('statsparents.csv')

print()
print('********************************************************************************')
print('DATA UNDER CHILD ID ************************************************************')
for curatedstar in['Curated_TLBX','Curated_KSADS_T','Curated_KSADS_MOOD_T','Curated_KSADS_P','Curated_KSADS_MOOD_P','Curated_PennCNP','Curated_Eprime','Curated_Q']:
    print('********************************************************************************')
    print(curatedstar)

    #subjects
    t1=t0.loc[~(t0[curatedstar].isin(['NE AGE','NE']))][['site','redcap_event',curatedstar,'index']]
    totals=pd.DataFrame(t1.loc[t1.redcap_event.isin(['V1','V2','V3'])].groupby(['site']).count())

    #totals=pd.DataFrame(hcdconcat[['site','redcap_event',curatedstar,'index']].loc[hcdconcat.redcap_event.isin(['V1','V2','V3'])].groupby(['site']).count())
    missings=pd.DataFrame(t1[['site','redcap_event',curatedstar,'index']].loc[hcdconcat[curatedstar]=='NO'].groupby(['site']).count())

    totals['Total_Subjects']=totals[curatedstar]
    missings['Missing_Subjects']=missings[curatedstar]

    stats=pd.concat([totals[['Total_Subjects']],missings[['Missing_Subjects']]],axis=1,sort=True)
    stats['PCT_Missing']=round(((stats.Missing_Subjects)/(stats.Total_Subjects))*100.0,1)
    print(stats)
    #stats.to_csv('statschild.csv')
    
    
sys.stdout = orig_stdout
f.close()

import sys
##TLBX is for toolbox data cleaning, specifically
HCAexpectedTLBX=HCAdf8.loc[HCAdf8.redcap_event.isin(['V1','V2','CR'])].copy()

#HCAexpectedTLBX.loc[HCAexpectedTLBX.site=='MGH/Harvard','site']='MGH'

orig_stdout = sys.stdout
f = open(os.path.join('HCA_Missingness_Report_'+snapshotdate+'.txt'),'w')
sys.stdout = f

#subjects
for curatedstar in['Curated_TLBX','Curated_Q','Curated_PennCNP','Curated_SSAGA']:
    print('***********************************************')
    print(curatedstar)
    NOs=HCAexpectedTLBX.loc[(HCAexpectedTLBX[curatedstar]=='NO') & (HCAexpectedTLBX.redcap_event.isin(['V1','V2','CR']))]
    ##print(HCAexpectedTLBX[['site','redcap_event',curatedstar,'index']].groupby(['site','redcap_event',curatedstar]).count())
    Nostring=NOs[['site','redcap_event',curatedstar,'index']].groupby(['site','redcap_event',curatedstar]).count()
    #print(str(Nostring))
    #print('*********************')

    a=HCAexpectedTLBX[['site','redcap_event',curatedstar,'index','event_date']].loc[(HCAexpectedTLBX.redcap_event.isin(['V1','V2','CR']))].copy()#.groupby(['site']).count())
    a['datediff']=(pd.to_datetime(a['event_date'])-datetime.datetime.today()).dt.days
    b=a.loc[a.datediff.abs()>14].copy()

    totals=pd.DataFrame(b[['site','redcap_event',curatedstar,'index']].loc[b.redcap_event.isin(['V1','V2','CR'])].groupby(['site']).count())
    missings=pd.DataFrame(b[['site','redcap_event',curatedstar,'index']].loc[b[curatedstar]=='NO'].groupby(['site']).count())

    totals['Total_Subjects']=totals[curatedstar]
    missings['Missing_Subjects']=missings[curatedstar]

    stats=pd.concat([totals[['Total_Subjects']],missings[['Missing_Subjects']]],axis=1,sort=True)
    stats['PCT_Missing']=round(100*(stats.Missing_Subjects)/(stats.Total_Subjects),1)

    print(stats)
#stats.to_csv('statshca.csv')
sys.stdout = orig_stdout
f.close()

#sys.stdout = orig_stdout
#f.close()
#orig_stdout
#sys.stdout

import datetime
a=HCAexpectedTLBX[['site','redcap_event','Curated_TLBX','index','event_date']].loc[(HCAexpectedTLBX.redcap_event.isin(['V1','V2','CR']))]#.groupby(['site']).count())
a['datediff']=(pd.to_datetime(a['event_date'])-datetime.datetime.today()).dt.days
b=a.loc[a.datediff.abs()>7]
#b
#a.loc[a.event_date < snapshotdate] 
#a['datediff']=(a.event_date-snapshotdate).dt.days
#a['datediff']=(pd.to_datetime(a['event_date'])-pd.to_datetime(snapshotdate)).dt.days
#togetherinventory11['ParentChildTLBXdaydiff']=(togetherinventory11.ParentDateFinishedTLBX - togetherinventory11.ChildDateFinishedTLBX).dt.days

a.datediff.abs()<7<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17511257906072034, 'fraction_numerical': 0.054446553676163884, 'mean_word_length': 8.644035793448813, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 67, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 90, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '476271', 'license': 'MIT', 'n_tokens_mistral': 49675, 'n_tokens_neox': 45227, 'n_words': 4942, 'path': 'CuratedInventory.ipynb', 'repo_name': 'humanconnectome/PreRelease', 'size': '117473'}