adni_csv_merging.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# notebook to merge ADNI spreadsheets together "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from dateutil.parser import parse\n",
    "from copy import deepcopy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 522,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path_csv = '/Users/AngelaTam/Desktop/adsf/adni_csv/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 523,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/AngelaTam/anaconda3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2698: DtypeWarning: Columns (8) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "adni_merge = pd.read_csv(path_csv + 'ADNIMERGE_20171206.csv')\n",
    "upenn_csf = pd.read_csv(path_csv + 'UPENNBIOMK_MASTER.csv')\n",
    "ucb_av45 = pd.read_csv(path_csv + 'UCBERKELEYAV45_11_14_17.csv')\n",
    "mem_ef = pd.read_csv(path_csv + 'UWNPSYCHSUM_10_27_17.csv')\n",
    "neurobat = pd.read_csv(path_csv + 'NEUROBAT.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## filter out upenn csf data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 524,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# only keep the correct batch for CSF based on ADNI reccomendations\n",
    "for i,row in upenn_csf.iterrows():\n",
    "    batch = row[2]\n",
    "    if batch == 'MEDIAN':\n",
    "        upenn_csf.drop(i,axis=0,inplace=True)\n",
    "    if batch == 'UPENNBIOMK2':\n",
    "        upenn_csf.drop(i,axis=0,inplace=True)\n",
    "    if batch == 'UPENNBIOMK4':\n",
    "        upenn_csf.drop(i,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 525,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# only keep the one measurement per visit for each subject\n",
    "upenn_csf.drop_duplicates(subset=['VISCODE', 'DRWDTE'], keep='first',inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## filter out the uc berkeley av45 data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 526,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "av45 = ucb_av45.filter(['RID','VISCODE2','SUMMARYSUVR_WHOLECEREBNORM_1.11CUTOFF'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 527,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "av45.rename(columns={'VISCODE2':'VISCODE'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## filter out the uwn psych data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 528,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mem_ef = mem_ef.filter(['RID','VISCODE2','ADNI_MEM','ADNI_EF'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 529,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mem_ef.rename(columns={'VISCODE2':'VISCODE'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## filter out the neuropsych battery data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 530,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "neurobat = neurobat.filter(['RID', 'VISCODE2', 'CLOCKSCOR', 'DSPANFOR', 'DSPANBAC', 'BNTTOTAL'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 531,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "neurobat.rename(columns={'VISCODE2':'VISCODE'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## merge the csvs together to make one big csv with longitudinal data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 532,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_df = pd.merge(adni_merge, mem_ef, how='left', on=['RID','VISCODE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 533,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_df = pd.merge(merged_df, neurobat, how='left', on=['RID', 'VISCODE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 534,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_df = pd.merge(merged_df, upenn_csf, how='left', on=['RID','VISCODE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 535,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_df = pd.merge(merged_df, av45, how='left', on=['RID','VISCODE'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## determine who is a converter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 536,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# sort the df by RID and Years_bl\n",
    "merged_df.sort_values(by=['RID','Years_bl'], ascending=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 537,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_df.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 538,
   "metadata": {},
   "outputs": [],
   "source": [
    "# forward fill\n",
    "missing_dx = [x for x in merged_df.RID.unique() if any([not pd.notnull(y) for y in merged_df[merged_df.RID==x]['DX']])]\n",
    "for i,sub in enumerate(missing_dx):\n",
    "    tmp = merged_df[merged_df.RID==sub]['DX'].fillna(method='ffill')\n",
    "    merged_df.loc[merged_df.RID==sub,'DX'] = tmp.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 539,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/AngelaTam/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:8: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n"
     ]
    }
   ],
   "source": [
    "# get the final DX for each subject and put into new column\n",
    "dx_dict = {}\n",
    "for sid in merged_df.RID.unique():\n",
    "    final_dx = merged_df[merged_df.RID == sid]['DX'].values[-1]\n",
    "    dx_dict.update({sid: final_dx})\n",
    "for i,row in merged_df.iterrows():\n",
    "    sid = row['RID']\n",
    "    merged_df.ix[i, 'final_DX'] = dx_dict[sid]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 540,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['MCI', 'Dementia', 'CN', nan], dtype=object)"
      ]
     },
     "execution_count": 540,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df['final_DX'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 541,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['CN', 'MCI', 'Dementia', nan], dtype=object)"
      ]
     },
     "execution_count": 541,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df['DX'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 542,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conv_dict = {}\n",
    "for sid in merged_df.RID.unique():\n",
    "    # compare baseline and final DX to determine conversion\n",
    "    bl_dx = merged_df[merged_df.RID == sid]['DX_bl'].values[0]\n",
    "    final_dx = merged_df[merged_df.RID == sid]['DX'].values[-1]\n",
    "    if pd.isnull(final_dx):\n",
    "        status = 'NaN'\n",
    "    if bl_dx == 'CN' and final_dx == 'CN':\n",
    "        status = 'stable'\n",
    "    if bl_dx == 'LMCI' and final_dx == 'MCI':\n",
    "        status = 'stable'\n",
    "    if bl_dx == 'EMCI' and final_dx == 'MCI':\n",
    "        status = 'stable'\n",
    "    if bl_dx == 'AD' and final_dx == 'Dementia':\n",
    "        status = 'stable'\n",
    "    if bl_dx == 'CN' and final_dx == 'MCI':\n",
    "        status = 'CN to MCI'\n",
    "    if bl_dx == 'CN' and final_dx == 'Dementia':\n",
    "        status = 'CN to Dementia'\n",
    "    if bl_dx == 'LMCI' and final_dx == 'CN':\n",
    "        status = 'MCI to CN'\n",
    "    if bl_dx == 'LMCI' and final_dx == 'Dementia':\n",
    "        status = 'MCI to Dementia'\n",
    "    if bl_dx == 'EMCI' and final_dx == 'CN':\n",
    "        status = 'MCI to CN'\n",
    "    if bl_dx == 'EMCI' and final_dx == 'Dementia':\n",
    "        status = 'MCI to Dementia'\n",
    "    if bl_dx == 'AD' and final_dx == 'MCI':\n",
    "        status = 'Dementia to MCI'\n",
    "    if bl_dx == 'AD' and final_dx == 'CN':\n",
    "        status = 'Dementia to CN'\n",
    "    conv_dict.update({sid: status})\n",
    "for i,row in merged_df.iterrows():\n",
    "    sid = row['RID']\n",
    "    merged_df.ix[i, 'conversion'] = conv_dict[sid]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 543,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dummy variables for conversion\n",
    "for i,row in merged_df.iterrows():\n",
    "    status = row[merged_df.columns.get_loc('conversion')]\n",
    "    if status == 'stable':\n",
    "        merged_df.ix[i,'conv_2_ad'] = 0\n",
    "        merged_df.ix[i,'conv_2_mci'] = 0\n",
    "    if status == 'CN to MCI':\n",
    "        merged_df.ix[i,'conv_2_ad'] = 0\n",
    "        merged_df.ix[i,'conv_2_mci'] = 1\n",
    "    if status == 'MCI to Dementia':\n",
    "        merged_df.ix[i,'conv_2_ad'] = 1\n",
    "        merged_df.ix[i,'conv_2_mci'] = 0\n",
    "    if status == 'MCI to CN':\n",
    "        merged_df.ix[i,'conv_2_ad'] = 0\n",
    "        merged_df.ix[i,'conv_2_mci'] = 0\n",
    "    if status == 'CN to Dementia':\n",
    "        merged_df.ix[i,'conv_2_ad'] = 1\n",
    "        merged_df.ix[i,'conv_2_mci'] = 0\n",
    "    if status == 'Dementia to CN':\n",
    "        merged_df.ix[i,'conv_2_ad'] = 0\n",
    "        merged_df.ix[i,'conv_2_mci'] = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## create dummy variables for dx, gender, apoe4 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 544,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dummy variables for diagnosis\n",
    "for i,row in merged_df.iterrows():\n",
    "    dx = row[merged_df.columns.get_loc('DX')]\n",
    "    if dx == 'CN':\n",
    "        merged_df.ix[i,'CN'] = 1\n",
    "        merged_df.ix[i,'MCI'] = 0\n",
    "        merged_df.ix[i,'AD'] = 0\n",
    "    elif dx == 'MCI':\n",
    "        merged_df.ix[i,'CN'] = 0\n",
    "        merged_df.ix[i,'MCI'] = 1\n",
    "        merged_df.ix[i,'AD'] = 0\n",
    "    elif dx == 'Dementia':\n",
    "        merged_df.ix[i,'CN'] = 0\n",
    "        merged_df.ix[i,'MCI'] = 0\n",
    "        merged_df.ix[i,'AD'] = 1\n",
    "    else:\n",
    "        merged_df.ix[i,'CN'] = 'NaN'\n",
    "        merged_df.ix[i,'MCI'] = 'NaN'\n",
    "        merged_df.ix[i,'AD'] = 'NaN'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 545,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dummy variable for gender (0 = female, 1 = male)\n",
    "sex_dict = {}\n",
    "for sid in merged_df.RID.unique():\n",
    "    sex = merged_df[merged_df.RID == sid]['PTGENDER'].values[0]\n",
    "    if sex == 'Male':\n",
    "        gender = 1\n",
    "    elif sex == 'Female':\n",
    "        gender = 0\n",
    "    else:\n",
    "        gender = 'NaN'\n",
    "    sex_dict.update({sid: gender})\n",
    "for i,row in merged_df.iterrows():\n",
    "    sid = row['RID']\n",
    "    merged_df.ix[i, 'gender'] = sex_dict[sid]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 546,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# create binary variable for APOE4 carriership\n",
    "e4_dict = {}\n",
    "for sid in merged_df.RID.unique():\n",
    "    e4_no = merged_df[merged_df.RID == sid]['APOE4'].values[0]\n",
    "    if e4_no == 0:\n",
    "        e4_bin = 0\n",
    "    elif e4_no > 0:\n",
    "        e4_bin = 1\n",
    "    else:\n",
    "        e4_bin = 'NaN'\n",
    "    e4_dict.update({sid: e4_bin})\n",
    "for i,row in merged_df.iterrows():\n",
    "    sid = row['RID']\n",
    "    merged_df.ix[i,'APOE4_bin'] = e4_dict[sid]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 547,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_df.to_csv(path_csv + 'adnimerge_upenn_unw_av45_neurobat.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ADNI1 baseline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 548,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni1_mri = pd.read_csv('/Users/AngelaTam/Desktop/adsf/adni1_bl_vbm_qc/adni1_screening_metadata.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 549,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni1_merged = deepcopy(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 550,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grab just ADNI1 from merged_df\n",
    "for i,row in adni1_merged.iterrows():\n",
    "    colprot = row[adni1_merged.columns.get_loc('COLPROT')]\n",
    "    if colprot != 'ADNI1':\n",
    "        adni1_merged.drop(i, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 551,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# grab the baselines from merged_df\n",
    "for i,row in adni1_merged.iterrows():\n",
    "    viscode = row[adni1_merged.columns.get_loc('VISCODE')]\n",
    "    if viscode != 'bl':\n",
    "        adni1_merged.drop(i, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 552,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# filter the mri data\n",
    "adni1_mr = adni1_mri.filter(['RID','Age','Acq Date','vbm_qc'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 553,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni1_mr.rename(columns={'Age':'age_scan', 'Acq Date': 'scan_date'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 554,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# merge the MRI data with rest of data\n",
    "adni1 = pd.merge(adni1_mr, adni1_merged, how='left', on='RID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 555,
   "metadata": {},
   "outputs": [],
   "source": [
    "adni1.to_csv('/Users/AngelaTam/Desktop/adsf/adni1_bl_vbm_qc/adni1_bl_demog_qc.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ADNI2 baseline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 556,
   "metadata": {},
   "outputs": [],
   "source": [
    "adni2_scan = pd.read_csv('/Users/AngelaTam/Desktop/adsf/adni2_bl_vbm_qc/adni2_baseline_mprage_3T_12_05_2017.csv')\n",
    "adni2_qc = pd.read_csv('/Users/AngelaTam/Desktop/adsf/adni2_bl_vbm_qc/adni2_bl_vbm_dartel_20171201_qc.csv',\n",
    "                      skipinitialspace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 557,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni2_merged = deepcopy(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 558,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# grab just ADNI2 from merged_df\n",
    "for i,row in adni2_merged.iterrows():\n",
    "    colprot = row[adni2_merged.columns.get_loc('COLPROT')]\n",
    "    if colprot != 'ADNI2':\n",
    "        adni2_merged.drop(i, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 559,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# get the baselines by taking first instance of each subject\n",
    "adni2_merged.drop_duplicates(subset='RID',keep='first',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 560,
   "metadata": {},
   "outputs": [],
   "source": [
    "adni2_qc.rename(columns={'rid':'RID'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 561,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/AngelaTam/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:4: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n"
     ]
    }
   ],
   "source": [
    "for i,row in adni2_scan.iterrows():\n",
    "    sid = row[adni2_scan.columns.get_loc('Subject')]\n",
    "    rid = sid[6:]\n",
    "    adni2_scan.ix[i,'RID'] = int(rid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 562,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# merge the MRI data together\n",
    "adni2_mri = pd.merge(adni2_qc, adni2_scan, how='left', on='RID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 563,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['subject', 'RID', 'vbm_qc', 'vbm_comments', 'scan_date', 'scan',\n",
      "       'Image Data ID', 'Subject', 'Group', 'Sex', 'Age', 'Visit', 'Modality',\n",
      "       'Description', 'Type', 'Acq Date', 'Format', 'Downloaded'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(adni2_mri.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 564,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni2_mri = adni2_mri.filter(['RID','Age','scan_date','vbm_qc'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 565,
   "metadata": {},
   "outputs": [],
   "source": [
    "adni2_mri.rename(columns={'Age':'age_scan'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 566,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni2 = pd.merge(adni2_mri, adni2_merged, how='left', on='RID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 567,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni2.to_csv('/Users/AngelaTam/Desktop/adsf/adni2_bl_vbm_qc/adni2_bl_demog_qc.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## put ADNI1 and ADNI2 baselines together for subtyping pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 568,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# get rid of subjects that rolled over from ADNI1 in ADNI2 baseline df\n",
    "adni2.drop(adni2[adni2.RID < 2000].index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 569,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# set index to RID\n",
    "adni2.set_index('RID',inplace=True)\n",
    "adni1.set_index('RID',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 570,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "frames = [adni1, adni2]\n",
    "all_df = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 571,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "all_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 572,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_df.to_csv('/Users/AngelaTam/Desktop/adsf/adni1_adni2_bl_demog_qc.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### get the gmv and tiv for each dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 573,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "adni1_gmv = pd.read_csv('/Users/AngelaTam/Desktop/adsf/adni1_bl_vbm_qc/adni1_bl_vbm_mean_gm_tiv.csv',\n",
    "                       skipinitialspace=True)\n",
    "adni2_gmv = pd.read_csv('/Users/AngelaTam/Desktop/adsf/adni2_bl_vbm_qc/adni2_bl_vbm_mean_gm_tiv.csv',\n",
    "                       skipinitialspace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 574,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/AngelaTam/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:9: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n"
     ]
    }
   ],
   "source": [
    "# get rid of whitespaces in column names\n",
    "adni1_gmv.rename(columns=lambda x: x.strip(),inplace=True)\n",
    "# rename ' ' to subject\n",
    "adni1_gmv.rename(index=str,columns={'':'subject'},inplace=True)\n",
    "# get rid of trailing spaces in subject names in wt_df\n",
    "for i,row in adni1_gmv.iterrows():\n",
    "    subj = row[adni1_gmv.columns.get_loc(\"subject\")]\n",
    "    subj = subj.rstrip()\n",
    "    adni1_gmv.ix[i,'subject'] = subj"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 575,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/AngelaTam/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:9: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n"
     ]
    }
   ],
   "source": [
    "# get rid of whitespaces in column names\n",
    "adni2_gmv.rename(columns=lambda x: x.strip(),inplace=True)\n",
    "# rename ' ' to subject\n",
    "adni2_gmv.rename(index=str,columns={'':'subject'},inplace=True)\n",
    "# get rid of trailing spaces in subject names in wt_df\n",
    "for i,row in adni2_gmv.iterrows():\n",
    "    subj = row[adni2_gmv.columns.get_loc(\"subject\")]\n",
    "    subj = subj.rstrip()\n",
    "    adni2_gmv.ix[i,'subject'] = subj"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 576,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# get rid of subjects that rolled over from ADNI1 in ADNI2 baseline df\n",
    "adni2_gmv.drop(adni2_gmv[adni2_gmv.RID < 2000].index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 577,
   "metadata": {},
   "outputs": [],
   "source": [
    "adni1_gmv.set_index('subject', inplace=True)\n",
    "adni2_gmv.set_index('subject', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 578,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "frames = [adni1_gmv, adni2_gmv]\n",
    "gmv_df = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 579,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gmv_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 580,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "model_df = pd.merge(all_df, gmv_df, how='left', on='RID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 581,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/AngelaTam/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:3: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n",
      "  app.launch_new_instance()\n"
     ]
    }
   ],
   "source": [
    "cols = list(model_df)\n",
    "cols.insert(0, cols.pop(cols.index('subject')))\n",
    "model_df = model_df.ix[:,cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 582,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "model_df.to_csv('/Users/AngelaTam/Desktop/adsf/adni1_adni2_bl_vbm_model_niak.csv',index=False,na_rep='NaN')\n",
    "model_df.to_csv('/Users/AngelaTam/Desktop/adsf/adni1_adni2_bl_vbm_model_python.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}