csg_fileutil_neurobotmerger_and_downloader.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CTBI Neurobot databases merger and downloader for WP15\n",
    "By Stephen Larroque @ Coma Science Group, GIGA Research, University of Liege\n",
    "Creation date: 2018-10-27\n",
    "License: MIT\n",
    "v0.4.7\n",
    "\n",
    "DESCRIPTION:\n",
    "Generic tool to merge two neurobot CSV databases based on GUPI and to download all MRI nifti files (with automatic ungunzipping!)\n",
    "\n",
    "INSTALL NOTE:\n",
    "You need to pip install pandas before launching this script.\n",
    "Tested on Python 2.7.13\n",
    "\n",
    "USAGE:\n",
    "* For the merge: First download from Neurobot the Imaging (and Subject) database in one CSV, then the Outcomes database in another CSV.Then input the path of these csv files below and run all cells!\n",
    "* For the imaging nifti download: you need to input your credentials in login.cfg.example and rename to login.cfg. You also need the Imaging/Subject databases in a csv.\n",
    "\n",
    "TODO:\n",
    "* Nothing here!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Forcefully autoreload all python modules\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# AUX FUNCTIONS\n",
    "\n",
    "import os, sys\n",
    "\n",
    "cur_path = os.path.realpath('.')\n",
    "sys.path.append(os.path.join(cur_path, 'csg_fileutil_libs'))  # for unidecode and cleanup_name, because it does not support relative paths (yet?)\n",
    "\n",
    "import ast\n",
    "import re\n",
    "import pandas as pd\n",
    "\n",
    "from csg_fileutil_libs.aux_funcs import save_df_as_csv, _tqdm, merge_two_df, df_remap_names, concat_vals, df_concatenate_all_but\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# PARAMETERS\n",
    "\n",
    "# First (ID) database to merge (both need to have a column 'name'). The merged 'name' column will use the names from this database.\n",
    "db_imaging = r'neurobot_v1.0_011118_subjectsimaging.csv'\n",
    "# Second (reference) database to merge. The names will be added as a new column 'name_altx'.\n",
    "db_outcome = r'neurobot_v1.0_011118_outcomes.csv'\n",
    "\n",
    "# Output database with the merge results\n",
    "out_db = r'neurobot_v1.0_011118_merged.csv'\n",
    "# Output folder to store imaging data\n",
    "out_imdir = r'H:\\neurobot_mri'\n",
    "# Uncompress nifti files on-the-fly?\n",
    "nifti_ungz = True\n",
    "# When downloading the nifti files, pass errors (but will still be printed) - else the program will stop at the first exception\n",
    "pass_errors = True\n",
    "# If any error when downloading files, how many times should we retry before skipping?\n",
    "max_retries = 5\n",
    "\n",
    "# Demographics merge filter by: 0=MR 2 Weeks, 1= Imaging.XsiType is xnat:mrSessionData (ie, keep only MR data), 2=keep MR data that was acquired between day 5 and day 45 post trauma (acute patients, same definition as used for Paris ComaWeb platform), 3=no filter at all (ie, keep CT scans).\n",
    "demo_filter_mode = 1\n",
    "\n",
    "verbose = False\n",
    "\n",
    "# What kind of series (DTI, etc) to include? (look at the Imaging.ScanType column)\n",
    "#series_to_include = []\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database merging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load first database\n",
    "try:\n",
    "    df_im = pd.read_csv(db_imaging, sep=';').dropna(how='all').dropna(how='any', subset=['gupi', 'Imaging.SubjectGroup'])  # drop all rows where name is empty (necessary else this will produce an error, we expect the name to exist)\n",
    "except Exception as exc:\n",
    "    # Try the same but with a comma separator instead of semicolon\n",
    "    df_im = pd.read_csv(db_imaging, sep=',').dropna(how='all').dropna(how='any', subset=['gupi', 'Imaging.SubjectGroup'])\n",
    "df_im"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load second database\n",
    "try:\n",
    "    df_oc = pd.read_csv(db_outcome, sep=';').dropna(how='all').dropna(how='any', subset=['gupi'])  # drop all rows where name is empty (necessary else this will produce an error, we expect the name to exist)\n",
    "except Exception as exc:\n",
    "    # Try the same but with a comma separator instead of semicolon\n",
    "    df_oc = pd.read_csv(db_outcome, sep=',').dropna(how='all').dropna(how='any', subset=['gupi'])\n",
    "df_oc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add acute column (between D5 and D45, same definition as used by Paris ComaWeb)\n",
    "df_im_days = pd.to_datetime(df_im['Imaging.ExperimentDateTime'], yearfirst=True, origin='unix') - pd.Timestamp('1970-01-01 00:00:00')\n",
    "df_im_acute = (df_im_days >= pd.Timedelta('5 days')) & (df_im_days <= pd.Timedelta('45 days'))\n",
    "df_im['DaysSinceAccident'] = df_im_days\n",
    "df_im['Acute'] = df_im_acute\n",
    "df_im"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter demographics\n",
    "if demo_filter_mode == 0:\n",
    "    # Keep only MR 2 weeks\n",
    "    df_im = df_im.loc[(df_im['Imaging.Timepoint'] == 'MR 2 weeks'), :]\n",
    "elif demo_filter_mode == 1:\n",
    "    # Keep all MRI\n",
    "    df_im = df_im.loc[(df_im['Imaging.XsiType'] == 'xnat:mrSessionData'), :]\n",
    "elif demo_filter_mode == 2:\n",
    "    # Keep acute MRI (D5-D45 included)\n",
    "    df_im = df_im.loc[(df_im['Imaging.XsiType'] == 'xnat:mrSessionData'), :]\n",
    "    df_im = df_im.loc[(df_im['Acute'] == True), :]\n",
    "df_im"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge both databases if gupi matches (keep only those that are in df_im - thus have an MR 2 weeks!)\n",
    "df_merge, df_final = merge_two_df(df_im, df_oc, col='gupi', mode=0, dist_threshold=0, dist_words_threshold=0, skip_sanity=True, keep_nulls=1, returnmerged=True)\n",
    "df_final.set_index('gupi', inplace=True)\n",
    "df_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if save_df_as_csv(df_final.reset_index(), out_db, fields_order=list(df_final.columns), csv_order_by='gupi'):\n",
    "    print('Merged database successfully saved in %s!' % out_db)\n",
    "else:\n",
    "    print('ERROR: the merged database could not be saved!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database imaging downloading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gzip\n",
    "import json\n",
    "import re\n",
    "import requests\n",
    "import shutil\n",
    "from urlparse import urlparse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loading login infos\n",
    "cfgpath = os.path.join(os.getcwd(), 'login.cfg')\n",
    "with open(cfgpath) as f:\n",
    "    # Strip out comments first\n",
    "    login_infos = f.read()\n",
    "    login_infos = re.sub(r'\\\\\\n', '', login_infos)\n",
    "    login_infos = re.sub(r'//.*\\n', '\\n', login_infos)\n",
    "    # Load as JSON\n",
    "    login_infos = json.loads(login_infos)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load first database\n",
    "try:\n",
    "    df_im = pd.read_csv(db_imaging, sep=';').dropna(how='all').dropna(how='any', subset=['gupi', 'Imaging.SubjectGroup'])  # drop all rows where name is empty (necessary else this will produce an error, we expect the name to exist)\n",
    "except Exception as exc:\n",
    "    df_im = pd.read_csv(db_imaging, sep=',').dropna(how='all').dropna(how='any', subset=['gupi', 'Imaging.SubjectGroup'])\n",
    "df_im.set_index('gupi', inplace=True)\n",
    "df_im"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter demographics\n",
    "if demo_filter_mode == 0:\n",
    "    # Keep only MR 2 weeks\n",
    "    df_im = df_im.loc[(df_im['Imaging.Timepoint'] == 'MR 2 weeks'), :]\n",
    "elif demo_filter_mode == 1:\n",
    "    # Keep all MRI\n",
    "    df_im = df_im.loc[(df_im['Imaging.XsiType'] == 'xnat:mrSessionData'), :]\n",
    "elif demo_filter_mode == 2:\n",
    "    # Keep acute MRI (D5-D45 included)\n",
    "    df_im = df_im.loc[(df_im['Imaging.XsiType'] == 'xnat:mrSessionData'), :]\n",
    "    df_im = df_im.loc[(df_im['Acute'] == True), :]\n",
    "df_im"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Precalculate total number of files to download (to show a progressbar)\n",
    "#total = 0\n",
    "#for idx, row in df_final.iterrows():\n",
    "#    try:\n",
    "#        niftiurls = ast.literal_eval(row['Imaging.NiftiURL'])\n",
    "#        total += len(niftiurls)\n",
    "#    except Exception:\n",
    "#        niftiurls = row['Imaging.NiftiURL']\n",
    "#        total += 1\n",
    "#    try:\n",
    "#        dicomheadersurls = ast.literal_eval(row['Imaging.DicomHeaderURL'])\n",
    "#        total += len(dicomheadersurls)\n",
    "#    except Exception:\n",
    "#        dicomheadersurls = row['Imaging.DicomHeaderURL']\n",
    "#        total +=1\n",
    "#total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total = df_im['Imaging.NiftiURL'].dropna().count() + df_im['Imaging.DicomHeaderURL'].dropna().count()\n",
    "total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pathsafe(s):\n",
    "    \"\"\"Make sure a string is path safe (replace any path unsafe character). From https://stackoverflow.com/a/295146/1121352\"\"\"\n",
    "    import string\n",
    "    valid_chars = \"-_.() %s%s\" % (string.ascii_letters, string.digits)\n",
    "    valid_chars = frozenset(valid_chars)\n",
    "    return ''.join(c for c in str(s) if c in valid_chars)\n",
    "\n",
    "# Create output directory if does not exist\n",
    "if not os.path.exists(out_imdir):\n",
    "    os.makedirs(out_imdir)\n",
    "\n",
    "# Prepare progressbar\n",
    "pbar = _tqdm(total=total, desc=\"DOWN\", unit=\"files\")\n",
    "\n",
    "# Iterate for each row/gupi/subject\n",
    "for gupi, row in df_im.iterrows():\n",
    "    niftiurl = row['Imaging.NiftiURL']\n",
    "    dicomheaderurl = row['Imaging.DicomHeaderURL']\n",
    "    if verbose:\n",
    "        print(niftiurl, dicomheaderurl)\n",
    "\n",
    "    for url in (niftiurl, dicomheaderurl):\n",
    "        # Empty URL: skip (some dicomheaders are missing)\n",
    "        if not isinstance(url, str):\n",
    "            continue\n",
    "        # Try to be robust against errors (will retry and show more detailed info in case of exception)\n",
    "        flag_successful = False\n",
    "        for ri in range(max_retries):\n",
    "            try:\n",
    "                # Get filename from server\n",
    "                filename = os.path.basename(urlparse(url).path)\n",
    "                # Build local filepath according to site name and scan type (and clean up each item to make sure it's pathsafe)\n",
    "                filepath = os.path.join(out_imdir, pathsafe(row['Subject.SiteCode']), pathsafe(gupi), pathsafe(str(row['Imaging.ScanType']).replace(' ', '_') + ' ' + str(row['Imaging.SeriesDescription']).replace(' ', '_')), pathsafe(filename))\n",
    "\n",
    "                # Access online the resource\n",
    "                r = requests.get(url, auth=(login_infos['username'],login_infos['password']))\n",
    "\n",
    "                # Try to download\n",
    "                if r.status_code == 200:\n",
    "                    # Create folder if necessary\n",
    "                    if not os.path.exists(os.path.dirname(filepath)):\n",
    "                        os.makedirs(os.path.dirname(filepath))\n",
    "                    # Write the content (download)\n",
    "                    with open(filepath, 'wb') as out:\n",
    "                        for bits in r.iter_content():\n",
    "                            out.write(bits)\n",
    "                    # Uncompress file if user wants\n",
    "                    if nifti_ungz and filepath[-3:] == '.gz':\n",
    "                        with gzip.open(filepath, 'rb') as f_in:\n",
    "                            with open(filepath[:-3], 'wb') as f_out:\n",
    "                                shutil.copyfileobj(f_in, f_out)\n",
    "                        os.remove(filepath)\n",
    "                    flag_successful = True\n",
    "                    pbar.update()\n",
    "                else:\n",
    "                    raise Exception('Could not download file: %s of subject gupi %s, got this response code: %i' % (filename, row['gupi'], r.status_code))\n",
    "            except Exception as exc:\n",
    "                # Show downloading error only if there is no further retry\n",
    "                print('Error:')\n",
    "                print(exc)\n",
    "                print('Debug infos:')\n",
    "                print(url)\n",
    "                print(niftiurl, dicomheaderurl)\n",
    "                print(gupi)\n",
    "                if ri < (max_retries - 1):\n",
    "                    print('Retrying download...')\n",
    "                else:\n",
    "                    print('Retries failed, you should download the file manually.')\n",
    "                    # If pass errors we continue downloading other files, else we stop here\n",
    "                    if not pass_errors:\n",
    "                        raise(exc)\n",
    "            # The file was successfully downloaded, break the retry loop\n",
    "            if flag_successful:\n",
    "                break\n",
    "\n",
    "print('All files successfully downloaded!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Figures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import itertools\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract fmri and dti types (because it's not totally standardized...)\n",
    "print('All scan types:')\n",
    "print(df_im['Imaging.ScanType'].unique())\n",
    "scantypes = {'dti': [], 'fmri': []}\n",
    "for s in df_im['Imaging.ScanType'].unique():\n",
    "    if 'dti' in s.lower():\n",
    "        scantypes['dti'].append(s)\n",
    "    if 'fmri' in s.lower():\n",
    "        scantypes['fmri'].append(s)\n",
    "\n",
    "print('\\nFound scantypes:')\n",
    "print('* DTI: %s' % scantypes['dti'])\n",
    "print('* fMRI: %s' % scantypes['fmri'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "scantypes_count = {key: 0 for key in scantypes.keys()}\n",
    "gupi_allscantypes = df_im.reset_index().set_index('gupi').index.unique()\n",
    "for key, scantype in scantypes.items():\n",
    "    print(key)\n",
    "    scantypes_count[key] = df_im.loc[df_im['Imaging.ScanType'].isin(scantype), :].groupby('gupi').count().reset_index()['gupi'].count()\n",
    "    gupi_allscantypes = gupi_allscantypes.intersection(df_im.loc[df_im['Imaging.ScanType'].isin(scantype), :].reset_index().set_index('gupi').index.unique())\n",
    "scantypes_count['alltogether'] = len(gupi_allscantypes)\n",
    "scantypes_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subjects_count = len(df_im.groupby('gupi').count().reset_index()['gupi'].unique())\n",
    "subjects_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subjects_with_dgose = len(df_final.dropna(subset=['Outcomes.DerivedCompositeGOSE']).reset_index()['gupi'].unique())\n",
    "subjects_with_dgose"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subjects_with_all = len(df_final.dropna(subset=['Outcomes.DerivedCompositeGOSE']).index.unique().intersection(gupi_allscantypes).unique())\n",
    "subjects_with_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subjects_dgose_dti = len(df_final.dropna(subset=['Outcomes.DerivedCompositeGOSE']).index.unique().intersection(df_im.loc[df_im['Imaging.ScanType'].isin(scantypes['dti']), :].reset_index().set_index('gupi').index.unique()).unique())\n",
    "subjects_dgose_dti"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subjects_dgose_fmri = len(df_final.dropna(subset=['Outcomes.DerivedCompositeGOSE']).index.unique().intersection(df_im.loc[df_im['Imaging.ScanType'].isin(scantypes['fmri']), :].reset_index().set_index('gupi').index.unique()).unique())\n",
    "subjects_dgose_fmri"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert data to a pandas Series to more easily plot\n",
    "toplot = pd.Series({'total': subjects_count, 'with dGOSE': subjects_with_dgose, 'DTI': scantypes_count['dti'], 'fMRI': scantypes_count['fmri'], 'fMRI+DTI': scantypes_count['alltogether'], 'fMRI+DTI+dGOSE': subjects_with_all, 'dGOSE+DTI': subjects_dgose_dti, 'dGOSE+fMRI':subjects_dgose_fmri})\n",
    "toplot = toplot[['total', 'with dGOSE', 'DTI', 'fMRI', 'fMRI+DTI', 'dGOSE+DTI', 'dGOSE+fMRI', 'fMRI+DTI+dGOSE']] # reorder columns\n",
    "# Prepare the plot\n",
    "if demo_filter_mode == 0:\n",
    "    ax = toplot.plot(kind='bar', title='Count of subjects with MR 2 weeks')\n",
    "elif demo_filter_mode == 1:\n",
    "    ax = toplot.plot(kind='bar', title='Count of subjects with any MR data')\n",
    "elif demo_filter_mode == 2:\n",
    "    ax = toplot.plot(kind='bar', title='Count of acute subjects (D5-D45 included)')\n",
    "else:\n",
    "    ax = toplot.plot(kind='bar', title='Count of subjects (MR or CT, no filter)')\n",
    "# Add exact value on top of each bar\n",
    "for p in ax.patches:\n",
    "    ax.annotate(str(p.get_height()), (p.get_x() + 0.02, p.get_height() * 1.005))\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of patients without a dGOSE:')\n",
    "df_final.index.difference(df_final.dropna(subset=['Outcomes.DerivedCompositeGOSE']).reset_index()['gupi'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sanity check: find any patient who has two MR 2 Weeks (which should be impossible!)\n",
    "df_test = df_im.reset_index().set_index('gupi')['Imaging.ExperimentDateTime']\n",
    "df_test_g = df_test.groupby(df_test.index).nunique()\n",
    "df_test_g[df_test_g > 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}