<jupyter_start><jupyter_text>ADNI Cleaning<jupyter_code>adni1.head(5)
# necessary and/or useful columns to do future calculations on:

# icv value and % icv value: code, roi_volume, roi_name

# examDate needed for age calculation, 
# examDate original format: %m/%day/%Y
# need to format to datetime: %Y-%m-%d

# volumes must be cleared of unnecessary values as specified below

# icv will have to be removed later in cleaning, post merge

# examDate column must be filled of missing values

# Normalize the format of 'examDate' column for each dataframe
# desired format: %Y-%m-%d

# accomplishing clearning individual to maintain integrity of indices
adni3['examDate'] = pd.to_datetime(adni3['examDate']).dt.date
adni1['examDate'] = pd.to_datetime(adni1['examDate']).dt.date
adni2['examDate'] = pd.to_datetime(adni2['examDate']).dt.date

# adni1['examDate']
# adni2['examDate']
# adni3['examDate']

#icv value column for each dataframe:
adni1['icv_values'] = (adni1[adni1['roi_name'] == 'icv']['roi_volume'])
adni2['icv_values'] = (adni2[adni2['roi_name'] == 'icv']['roi_volume'])
adni3['icv_values'] = (adni3[adni3['roi_name'] == 'icv']['roi_volume'])
# adni3['thing'] at this point will have Nas

#fillna(0) for non-icv values:may change column name to 'icv_vals'
adni1['icv_values'] = adni1['icv_values'].fillna(0)
adni2['icv_values'] = adni2['icv_values'].fillna(0)
adni3['icv_values'] = adni3['icv_values'].fillna(0)
# adni3['thing'] 0s for all values other than icv values such as 1652002.0
# desired grouping when calculting %icv for each dataframe
# adni1.groupby('code')['thing'].max()

# function to divide roi_volume by icv values 
def divide_two_cols(adni):
    adni['%_icv'] = adni['roi_volume'] / adni['icv_values'].max()
    return adni['%_icv']

adni1['%_icv'] = adni1.groupby('code').apply(divide_two_cols).tolist()
adni2['%_icv'] = adni2.groupby('code').apply(divide_two_cols).tolist()
adni3['%_icv'] = adni3.groupby('code').apply(divide_two_cols).tolist()

# check correct % icv calculations:
adni3[((adni3['roi_name']=='icv') | (adni3['roi_name']== 'Left Amygdala'))][['project','code','roi_name','roi_volume','icv_values','%_icv']]

# merge adni dataframes, remove unwanted regions, split code column

# merge:
adni12 = pd.concat([adni1,adni2], axis=0)
adni123 = pd.concat([adni12, adni3], axis=0)
# adni123

# discard unwanted brain regions:

# to remove: uppercase items
adni123.drop(adni123.index[adni123['roi_name'].str.isupper()], inplace=True)

discard = (['Right Cerebellum Exterior', 'Left Cerebellum Exterior',
            'Right Cerebellum White Matter','Left Cerebellum White Matter',
            'frontal lobe WM right','frontal lobe WM left',
            'occipital lobe WM right','occipital lobe WM left',
            'parietal lobe WM right','parietal lobe WM left',
            'temporal lobe WM right','temporal lobe WM left',
            'fornix right','fornix left','corpus callosum'])

adni123 = adni123[adni123.roi_name.isin(discard) == False]
# adni123

#split code:

#remove unwanted ADNI3 partial string from code entries:
bonus_pat = r'[ADNI]+\d+_'

adni123['code'] = adni123['code'].str.replace("[ADNI]+\d+_","")
adni123['split_code'] = adni123['code'].str.split('_')

# split up code into new columns: PTID, RID, unique image #
# example code: 002_S_0295xia_118671

adni123['SITEID'] = adni123['split_code'].str[0]
adni123['RID_VISCODE'] = adni123['split_code'].str[2]
adni123['IMAGEUID'] = adni123['split_code'].str[3]

# RID=1326 and VISCODE=m12
rid_pattern3 = r'(\d\d\d\d)'
adni123['RID_VISCODE'] =  adni123['RID_VISCODE'].str.split(pat=rid_pattern3)

adni123['RID'] = adni123['RID_VISCODE'].str[1]
adni123['RID'] = adni123['RID'].str.lstrip('0')
adni123['VISCODE'] = adni123['RID_VISCODE'].str[2]

# adni123
# check if everything remains functional: 
# adni123
# adni123['examDate']
# adni123['RID']
adni123.columns
adni123.head(1)<jupyter_output><empty_output><jupyter_text>DXSUM Cleaning<jupyter_code>DXSUM.head(5)
# necessary and/or useful columns to do future calculations on:

# Keep: Phase	ID	RID	PTID	SITEID
# Keep until end/export: rest

# * EXAMDATE for age calculation
# column must be filled of missing values
# * DXCHANGE, DXCURREN, and DIAGNOSIS for complete diagnosis column

# final cleaning: removing ADNIGO cases at end as to not interupt indices

# Note: PTID values in format: 022_S_0004
# curate a complete diagnosis column for DXSUM:
DXSUM['CHAN_CURR'] = DXSUM['DXCHANGE'].fillna(value=DXSUM['DXCURREN'])
DXSUM['complete_diagnosis'] = DXSUM['CHAN_CURR'].fillna(value=DXSUM['DIAGNOSIS'])
DXSUM['complete_diagnosis']
# DXSUM.head(5)<jupyter_output><empty_output><jupyter_text>DATADIC Cleaning<jupyter_code>DATADIC.head(5)
# necessary and/or useful columns to do future calculations on:
# Phase: 'ADNI1', 'ADNIGO', 'ADNI2', 'ADNI3'

# may not be necessary to merge<jupyter_output><empty_output><jupyter_text>PTDEMOG Cleaning<jupyter_code>PTDEMOG.head(5)
# necessary and/or useful columns to do future calculations on:

# Keep until end/export: 
# Phase	ID	RID	SITEID	VISCODE	

# Use for Calculation of age of individual: 
#* PTDOBMM, PTDOBYY 
PTDEMOG['PTDOBYY']

# Name: PTDOBYY, Length: 4768, dtype: float64<jupyter_output><empty_output><jupyter_text>FULL MERGE<jupyter_code># Potential merges: 

# approach 1) adni123 merge with DXSUM on key value: RID
# combination: should include: both forms of examDate
# should check if two columns are equal
# then merge with PTDEMOG on 'RID'

# *Note: so far successful when running this approach, until reaching age calc

# adni_DXSUM_merged = pd.merge(adni123.astype(str), DXSUM.astype(str), on='RID')
# full_merged = pd.merge(adni_DXSUM_merged.astype(str), PTDEMOG.astype(str), on='RID')

#*******************************************************

# approach 2) PTDEMOG merge with DXSUM, then merge with adni123 
# proven issue: merging PTDEMOG and DXSUM leads to loss of 'DOB' col

# PTDEMOG_DXSUM = PTDEMOG.merge(DXSUM,  how='right')
# full_merged = pd.merge(adni123.astype(str), PTDEMOG_DXSUM.astype(str), on='RID')


#current working version ***
# PTDEMOG_DXSUM = pd.merge(PTDEMOG.astype(str), DXSUM.astype(str), on='RID')
# full_merged = pd.merge(adni123.astype(str), PTDEMOG_DXSUM.astype(str), on='RID')
# end current working version ***

PTDEMOG_DXSUM = pd.merge(PTDEMOG.astype(str), DXSUM.astype(str), on='RID')
full_merged = pd.merge(adni123.astype(str), PTDEMOG_DXSUM.astype(str), on='RID')

# *Note: so far successful when running this approach, until reaching age calc

#*******************************************************

# approach 3) adni123 merge with PTDEMOG on key value: RID
# then merge with DXSUM
# *Note: so far successful when running this approach, until reaching age calc

# adni_PTDEMOG_merged = pd.merge(adni123.astype(str), PTDEMOG.astype(str), on='RID')
# full_merged = pd.merge(adni_PTDEMOG_merged.astype(str), DXSUM.astype(str), on='RID')


# full_merged
# full_merged['PTDOBYY']
# pd.to_datetime(full_merged['PTDOBYY']).dt.year<jupyter_output><empty_output><jupyter_text>PTDOBYY/MM as DATETIME<jupyter_code># dob_year = pd.to_datetime(full_merged['PTDOBYY']).dt.year
full_merged['PTDOBYY'] = pd.to_datetime(full_merged['PTDOBYY'],format='%Y.0').dt.year

# pd.to_datetime(full_merged['DOB']).dt.year
# pd.to_datetime(full_merged['DOB']).dt.month
# pd.to_datetime(full_merged['DOB']).dt.day
#  dob_month = pd.to_datetime(full_merged['PTDOBMM'], format='%m').dt.month

full_merged['PTDOBMM'] = pd.to_datetime(full_merged['PTDOBMM'], format='%m.0').dt.month<jupyter_output><empty_output><jupyter_text>RENAME PTDOBMM/YY as MONTH/YEAR for DATETIME<jupyter_code># must rename: full_merged['PTDOBYY'], full_merged['PTDOBMM']

full_merged.rename(columns={'PTDOBYY':'YEAR','PTDOBMM':'MONTH'}, inplace=True)
full_merged.columns
# full_merged['YEAR']

# full_merged['MONTH']
full_merged['PTGENDER']<jupyter_output><empty_output><jupyter_text>Revive unique image identifier column<jupyter_code># # split up code into new columns: PTID, RID, unique image #
# # example code: 002_S_0295xia_118671

full_merged['IMAGEUID'] = full_merged['code'].str.split('_').str[3]
full_merged['IMAGEUID'] = full_merged['IMAGEUID'].str.replace('i','')

full_merged['IMAGEUID']<jupyter_output><empty_output><jupyter_text>JOIN YEAR and MONTH col as DOB<jupyter_code>full_merged['DOB_DATE'] = pd.to_datetime(full_merged[['YEAR', 'MONTH']].assign(DAY=1))

full_merged['DOB_DATE']
full_merged['examDate'] 
# Name: examDate, Length: 8110579, dtype: object

# full_merged['DOB']
# Name: DOB, Length: 8110579, dtype: object
# compute age:

# approach 1:calculate mid merge of two dataframes

# adni_merged['age_in_days'] = adni_merged['examDate'] - adni_merged['DOB']
# adni_merged['age_in_years'] = adni_merged["age_in_days"] / timedelta(days=365)

# approach 2:calculate post all merges

#format string cols into datetime again:
full_merged['examDate']  = pd.to_datetime(full_merged['examDate'], format='%Y-%m-%d')
full_merged['DOB_DATE'] = pd.to_datetime(full_merged['DOB_DATE'], format='%Y-%m-%d')
# however, when attempting to do this:
# ValueError: time data nan-nan-15 doesn't match format specified

full_merged['age_in_days'] = full_merged['examDate'] - full_merged['DOB_DATE']
full_merged['age_in_years'] = full_merged["age_in_days"] / timedelta(days=365)

# full_merged
full_merged['age_in_years']


# current error for all three merge options: TypeError: unsupported operand type(s) for -: 'str' and 'str'
# idea: change string columns for date back into datetime<jupyter_output><empty_output><jupyter_text>Next Steps<jupyter_code># dropping ADNIGO
# icv will have to be removed later in cleaning, post merge
# Drop ADNIGO rows

full_merged = full_merged[full_merged['project'] != 'ADNIGO']

full_merged.head(5)
# Drop icv volumne rows

# this didn't seem to work...
# discard_icv = (['icv'])
# full_merged = full_merged[full_merged.roi_name.isin(discard_icv) == False]

# this worked 
full_merged = full_merged[full_merged['roi_name'] != 'icv']

full_merged.head(5)
full_merged[full_merged['roi_name'] == 'icv'].head(5)
# First check which columns have NaN values:
full_merged[full_merged.columns[full_merged.isna().any()]]

# Drop rows with missing data in the following columns:
# examDate	MONTH	YEAR	DOB_DATE	age_in_days	age_in_years
print(full_merged.columns)
# full_merged = (full_merged[['project', 'code', 'roi_volume',
#         'roi_name', 'roi_index','examDate', 'icv_values', '%_icv',
#         'split_code','complete_diagnosis', 'DOB_DATE', 
#         'age_in_days','age_in_years', 'IMAGEUID']])

full_merged = (full_merged[['project', 'code', 'roi_volume',
        'roi_name', 'examDate', 'icv_values', '%_icv',
        'split_code','complete_diagnosis', 'PTGENDER', 'DOB_DATE', 
        'age_in_years', 'IMAGEUID']])

full_merged.head(5)
# the method will drop all rows with at least one missing value.
full_merged = full_merged.dropna()

# Note: If I want to maintain the entire dataframe curated above, 
# but want to drop rows with NaN values present in only a particular
# set of columns:
# df = df.dropna(subset=['colA', 'colC'])
# full_merged.head(5)

full_merged[full_merged.columns[full_merged.isna().any()]]
# the above .dropna appears to have been successful when checking for 
# remaining columns with NaN values/ 
full_merged.head(5)
full_merged['complete_diagnosis'].unique()
# result: 
# array(['1.0', '3.0', '2.0', '5.0', '7.0', '4.0', '6.0', '8.0', 'nan'],
# dtype=object)

# Code Details 
# 1=Stable: NL to NL; 2=Stable: MCI to MCI; 
# 3=Stable: Dementia to Dementia; 4=Conversion: NL to MCI; 
# 5=Conversion: MCI to Dementia; 6=Conversion: NL to Dementia; 
# 7=Reversion: MCI to NL; 8=Reversion: Dementia to MCI; 
# 9=Reversion: Dementia to NL

# should remove the Reversion codes*
# Remove diagnoses of the following values:
#  

full_merged = (full_merged[(full_merged['complete_diagnosis'] != '7.0') 
                           & (full_merged['complete_diagnosis'] != '8.0') 
                           & (full_merged['complete_diagnosis'] != '9.0')
                           & (full_merged['complete_diagnosis'] != 'nan')])
full_merged['complete_diagnosis'].unique()
full_merged.head(5)
#check match for .nii.gz images from ucsf box:
# https://ucsf.app.box.com/folder/154227125425?utm_campaign=collab%20auto%20accept%20user&utm_medium=email&utm_source=trans
# full_merged[full_merged['code']== '098_S_0160X7a_172295']
# for the above example: 612 rows
# check for redundant rows:
# full_merged.duplicated() #there are many :,) #

full_merged = full_merged.drop_duplicates()
full_merged[full_merged.duplicated()]

# yay, no more duplicates, ,<jupyter_output><empty_output><jupyter_text>Export dataset! :,)<jupyter_code>full_merged.to_csv(r'C:/Users/glh19/OneDrive/Desktop/Winter_Spring 2022/Tosun-Turgut/data/output/adni_age_data_diag_clean.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15600684718331778, 'fraction_numerical': 0.027622159975101153, 'mean_word_length': 6.212682379349046, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '193578', 'license': 'MIT', 'n_tokens_mistral': 5175, 'n_tokens_neox': 4755, 'n_words': 1012, 'path': 'data_cleaning/reorganized_dob_column.ipynb', 'repo_name': 'gabbieHoyer/Brain_Age_prediction', 'size': '12852'}