datafreeze_notebooks/merge_and_audit_dx.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This script creates a binary spreadsheet of all participants enrolled in EF and whether or not their \n",
    "#pre/post scan SR data is complete \n",
    "#Inputs:\n",
    "    #enrollment sheets pulled from AXIS, stored at afp://saturn/Coordinators/Protocols/TED_PROTOCOLS/EXECUTIVE_829744/2022_data_freeze/inputs\n",
    "    #all diagnosis data from LIBI, pulled from oracle and stored at afp://saturn/Coordinators/Protocols/TED_PROTOCOLS/EXECUTIVE_829744/2022_data_freeze/inputs\n",
    "#Outputs:\n",
    "    #merged sheet of all EF diagnoses, stored at afp://saturn/Coordinators/Protocols/TED_PROTOCOLS/EXECUTIVE_829744/2022_data_freeze/outputs\n",
    "    #a binary spreadsheet of diagnosis collected for T1 participants under LIBI enrolled prior to April 1st, 2022 \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in T1 enrollment\n",
    "axis_t1=pd.read_csv('/Volumes/Coordinators/Protocols/TED_PROTOCOLS/EXECUTIVE_829744/2022_data_freeze/inputs/axis_enroll_t1.csv',dtype=str)\n",
    "axis_t1=axis_t1.drop(columns=['scan_1_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reformat \n",
    "t1_enroll=axis_t1['bblid']\n",
    "t1_enroll=t1_enroll.tolist()\n",
    "t1_enroll = [str(t) for t in t1_enroll]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in all LiBI dx data \n",
    "libi_dx=pd.read_csv('/Volumes/Coordinators/Protocols/TED_PROTOCOLS/EXECUTIVE_829744/2022_data_freeze/inputs/oracle_dx_libi.csv')\n",
    "libi_dx=libi_dx.drop(columns=['DODIAGNOSIS'])\n",
    "libi_dx=libi_dx.astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BBLID</th>\n",
       "      <th>CONSENSUS_TYPE</th>\n",
       "      <th>DOCONSENSUS</th>\n",
       "      <th>CONSENSUSBY</th>\n",
       "      <th>INTERVIEWER</th>\n",
       "      <th>PROTOCOL</th>\n",
       "      <th>VISITNUM</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>INTERVIEWEE</th>\n",
       "      <th>HSTATUS</th>\n",
       "      <th>...</th>\n",
       "      <th>AGEONSET_AXIS1</th>\n",
       "      <th>AXIS1_LIFETIME</th>\n",
       "      <th>AXIS1_UK</th>\n",
       "      <th>AGEONSET_CLINICRISK</th>\n",
       "      <th>CLINICRISK_LIFETIME</th>\n",
       "      <th>CLINICRISK_UK</th>\n",
       "      <th>DEFICIT</th>\n",
       "      <th>ENTBY</th>\n",
       "      <th>DOENT</th>\n",
       "      <th>Unnamed: 92</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>19861</td>\n",
       "      <td>Faculty Review</td>\n",
       "      <td>08/23/18</td>\n",
       "      <td>Ran</td>\n",
       "      <td>rsga</td>\n",
       "      <td>16-013305 - LIBI_Common</td>\n",
       "      <td>1.0</td>\n",
       "      <td>P</td>\n",
       "      <td>proband</td>\n",
       "      <td>BP1</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>N</td>\n",
       "      <td>rsga</td>\n",
       "      <td>00:00.0</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>20124</td>\n",
       "      <td>Faculty Review</td>\n",
       "      <td>06/25/19</td>\n",
       "      <td>Monica</td>\n",
       "      <td>ehaderer</td>\n",
       "      <td>16-013305 - LIBI_Common</td>\n",
       "      <td>nan</td>\n",
       "      <td>P</td>\n",
       "      <td>proband</td>\n",
       "      <td>NC</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>ehaderer</td>\n",
       "      <td>00:00.0</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>20125</td>\n",
       "      <td>Case Conference</td>\n",
       "      <td>12/20/18</td>\n",
       "      <td>Monica, Ran, Jerome</td>\n",
       "      <td>diegodav</td>\n",
       "      <td>16-013305 - LIBI_Common</td>\n",
       "      <td>nan</td>\n",
       "      <td>P</td>\n",
       "      <td>proband</td>\n",
       "      <td>PRO</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>diegodav</td>\n",
       "      <td>00:00.0</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>20139</td>\n",
       "      <td>Faculty Review</td>\n",
       "      <td>10/03/19</td>\n",
       "      <td>Monica</td>\n",
       "      <td>kpiiwaa</td>\n",
       "      <td>16-013305 - LIBI_Common</td>\n",
       "      <td>nan</td>\n",
       "      <td>P</td>\n",
       "      <td>both</td>\n",
       "      <td>O</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>redcap</td>\n",
       "      <td>00:00.0</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>20141</td>\n",
       "      <td>Faculty Review</td>\n",
       "      <td>09/16/19</td>\n",
       "      <td>Lauren</td>\n",
       "      <td>rsga</td>\n",
       "      <td>16-013305 - LIBI_Common</td>\n",
       "      <td>nan</td>\n",
       "      <td>P</td>\n",
       "      <td>proband</td>\n",
       "      <td>NC</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>redcap</td>\n",
       "      <td>00:00.0</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 92 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    BBLID   CONSENSUS_TYPE DOCONSENSUS          CONSENSUSBY INTERVIEWER  \\\n",
       "48  19861   Faculty Review    08/23/18                  Ran        rsga   \n",
       "69  20124   Faculty Review    06/25/19               Monica    ehaderer   \n",
       "70  20125  Case Conference    12/20/18  Monica, Ran, Jerome    diegodav   \n",
       "72  20139   Faculty Review    10/03/19               Monica     kpiiwaa   \n",
       "73  20141   Faculty Review    09/16/19               Lauren        rsga   \n",
       "\n",
       "                   PROTOCOL VISITNUM TYPE INTERVIEWEE HSTATUS  ...  \\\n",
       "48  16-013305 - LIBI_Common      1.0    P     proband     BP1  ...   \n",
       "69  16-013305 - LIBI_Common      nan    P     proband      NC  ...   \n",
       "70  16-013305 - LIBI_Common      nan    P     proband     PRO  ...   \n",
       "72  16-013305 - LIBI_Common      nan    P        both       O  ...   \n",
       "73  16-013305 - LIBI_Common      nan    P     proband      NC  ...   \n",
       "\n",
       "   AGEONSET_AXIS1 AXIS1_LIFETIME AXIS1_UK AGEONSET_CLINICRISK  \\\n",
       "48            nan            nan      nan                 nan   \n",
       "69            nan            nan      nan                 nan   \n",
       "70            nan            nan      nan                 nan   \n",
       "72            nan            nan      nan                 nan   \n",
       "73            nan            nan      nan                 nan   \n",
       "\n",
       "   CLINICRISK_LIFETIME CLINICRISK_UK DEFICIT     ENTBY    DOENT Unnamed: 92  \n",
       "48                 nan           nan       N      rsga  00:00.0         nan  \n",
       "69                 nan           nan     nan  ehaderer  00:00.0         nan  \n",
       "70                 nan           nan     nan  diegodav  00:00.0         nan  \n",
       "72                 nan           nan     nan    redcap  00:00.0         nan  \n",
       "73                 nan           nan     nan    redcap  00:00.0         nan  \n",
       "\n",
       "[5 rows x 92 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#filter for EF participants\n",
    "ef_dx=libi_dx[libi_dx['BBLID'].isin(t1_enroll)]\n",
    "ef_dx.head(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loop through and make a list of all enrolled ID's that are are in the data \n",
    "has_dx=[]\n",
    "for index, row in ef_dx.iterrows():\n",
    "    for t in t1_enroll:\n",
    "        if (t in row['BBLID']):\n",
    "            has_dx.append(t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "173\n"
     ]
    }
   ],
   "source": [
    "audit = pd.DataFrame (t1_enroll, columns = ['BBLID'])\n",
    "print(len(audit))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#check if each ID is in the list\n",
    "dx=[]\n",
    "for val in audit.values:\n",
    "    if val in has_dx:\n",
    "        dx.append(1)\n",
    "    if val not in has_dx:\n",
    "        dx.append(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add column\n",
    "audit['diagnosis']=dx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BBLID</th>\n",
       "      <th>diagnosis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19861</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20124</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20125</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20139</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20141</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BBLID  diagnosis\n",
       "0  19861          1\n",
       "1  20124          1\n",
       "2  20125          1\n",
       "3  20139          1\n",
       "4  20141          1"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "audit.head(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "audit.to_csv('/Volumes/Coordinators/Protocols/TED_PROTOCOLS/EXECUTIVE_829744/2022_data_freeze/audits/EF_dx_audit.csv', sep = ',', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "ef_dx.to_csv('/Volumes/Coordinators/Protocols/TED_PROTOCOLS/EXECUTIVE_829744/2022_data_freeze/outputs/EF_all_dx.csv', sep = ',', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}