csg_datafusion_sarah_db_cleaner.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sarah DB cleaner & FMP comparison\n",
    "By Stephen Larroque @ Coma Science Group, GIGA Research, University of Liege\n",
    "Creation date: 2018-02-16\n",
    "License: MIT\n",
    "v1.0.2\n",
    "\n",
    "DESCRIPTION:\n",
    "This script compiles all CRS-R sessions of one subject and create a hierarchical multiindex by subject name and crs-r date, which can then be compared with filemakerpro\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",
    "\n",
    "TODO:\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",
    "# For DB reorganization\n",
    "from csg_fileutil_libs.aux_funcs import save_df_as_csv, _tqdm, merge_two_df, remove_strings_from_df, find_columns_matching, reorder_cols_df, compute_best_diag\n",
    "\n",
    "# For multi DB comparison\n",
    "import re\n",
    "from csg_fileutil_libs.distance import distance\n",
    "from csg_fileutil_libs.aux_funcs import distance_jaccard_words_split\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# PARAMETERS\n",
    "\n",
    "# Sarah's database\n",
    "sarah_csv = 'BDD_rCRS-R_.csv'\n",
    "fmp_csv = 'fmp_db_subjects_crsr.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# AUX FUNCTIONS\n",
    "\n",
    "# Moved to aux file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "cf = pd.read_csv(sarah_csv, sep=';', low_memory=False).dropna(axis=0, how='all')  # drop empty lines (but NOT columns else we might lose important columns such as 13_since_onset which was never filled but is necessary for the stacking!)\n",
    "cf.drop(columns=find_columns_matching(cf, 'Unnamed'), inplace=True)  # drop all unnamed column\n",
    "cf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract CRS-R fields in a separate column\n",
    "cf_crsr_columns = find_columns_matching(cf, ['%i_' % i for i in xrange(1,19)])\n",
    "print(cf_crsr_columns)\n",
    "cf_crsr = cf[['Name'] + cf_crsr_columns].set_index('Name')\n",
    "cf_crsr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stack CRSr 2/3 columns as rows and rename as CRSr (copy the date of CRSr over)\n",
    "cf_crsr_columns_all = [find_columns_matching(cf_crsr, '%i_' % i, startswith=True) for i in xrange(1,19)]\n",
    "cf_crsr_allblocks = []\n",
    "for i, cols in enumerate(cf_crsr_columns_all):\n",
    "    # Extract the columns as separate dataframes\n",
    "    cf_crsr_temp = cf_crsr[cols]\n",
    "    # Drop empty rows\n",
    "    cf_crsr_temp = cf_crsr_temp.dropna(axis=0, how='all')\n",
    "    # Rename columns\n",
    "    cf_crsr_temp.columns = [x.replace('%i_' % (i+1), '').lower() for x in cf_crsr_temp.columns]\n",
    "    # Set assessment date as key too to allow for concatenation later on\n",
    "    cf_crsr_temp = cf_crsr_temp.reset_index().set_index(['Name', 'date_assess'])\n",
    "    # Add to the stack of dataframes, we will concatenate after\n",
    "    cf_crsr_allblocks.append(cf_crsr_temp)\n",
    "\n",
    "# Sanity check: all stacking dataframes should have exactly the same columns\n",
    "start = cf_crsr_allblocks[2].columns.tolist()\n",
    "for i, df in enumerate(cf_crsr_allblocks):\n",
    "    try:\n",
    "        assert df.columns.tolist() == start\n",
    "    except AssertionError as exc:\n",
    "        raise AssertionError('Cannot stack the columns as they have changing names: %i, %s vs 0, %s' % (i, df.columns.tolist(), start))\n",
    "\n",
    "# Stack/Concatenate vertically all CRS-Rs\n",
    "cf_crsr_all = pd.concat(cf_crsr_allblocks).sort_index()\n",
    "# Drop empty CRS-Rs\n",
    "cf_crsr_all = cf_crsr_all.reset_index().dropna(subset=['date_assess']).set_index(['Name', 'date_assess']).sort_index()\n",
    "#Display!\n",
    "cf_crsr_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find patients with weird date_assess, and drop them\n",
    "cf_crsr_weird_dates = cf_crsr_all.reset_index().set_index('Name')[cf_crsr_all.reset_index().set_index('Name')['date_assess'] == 'XXX']\n",
    "cf_crsr_weird_dates = cf_crsr_weird_dates.reset_index().set_index(['Name','date_assess'])\n",
    "cf_crsr_all = cf_crsr_all.drop(cf_crsr_weird_dates.index)\n",
    "cf_crsr_weird_dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find problematic dates and remove them\n",
    "dates = pd.to_datetime(cf_crsr_all.reset_index().set_index('Name')['date_assess'], dayfirst=True, errors='coerce')\n",
    "cf_crsr_buggy_dates = cf_crsr_all.reset_index().set_index('Name')[dates.isnull()].reset_index().set_index(['Name', 'date_assess'])\n",
    "cf_crsr_all = cf_crsr_all.drop(cf_crsr_buggy_dates.index)\n",
    "save_df_as_csv(cf_crsr_buggy_dates, 'SarahBDD_buggy_crsr_dates.csv', fields_order=cf_crsr_buggy_dates.columns, keep_index=True)\n",
    "cf_crsr_buggy_dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Finally reformat all date_assess CRS-R dates in a uniform format\n",
    "# Convert to uniformized dates, there should be no error here, else add errors='coerce'\n",
    "dates = pd.to_datetime(cf_crsr_all.reset_index().set_index('Name')['date_assess'], dayfirst=True)\n",
    "# Reassign similar index (based on Name only, to be able to merge)\n",
    "cf_crsr_all = cf_crsr_all.reset_index().set_index('Name')\n",
    "# Replace the date column\n",
    "cf_crsr_all['date_assess'] = dates\n",
    "# Reset the hierarchical name/date index and sort\n",
    "cf_crsr_all = cf_crsr_all.reset_index().set_index(['Name', 'date_assess']).sort_index()\n",
    "# Display!\n",
    "cf_crsr_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------------------\n",
    "## Checking misdiagnosis and typos and maximum limits (sanity check of db), based on Sarah's rules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove buggy CRS-Rs (with string or non numeric numbers)\n",
    "# Make a copy\n",
    "cf_crsr_all_nostr = cf_crsr_all.copy()\n",
    "# Find all CRS-R subscore related columns\n",
    "crs_cols = find_columns_matching(cf_crsr_all, 'crs', startswith=True)\n",
    "# Remove strings from these columns (replace by nan)\n",
    "cf_crsr_all_nostr[crs_cols] = remove_strings_from_df(cf_crsr_all[crs_cols])\n",
    "# Save and display the buggy assessments\n",
    "cf_crsr_buggy2 = cf_crsr_all[cf_crsr_all_nostr[crs_cols].isnull().any(axis=1)]\n",
    "save_df_as_csv(cf_crsr_buggy2, 'SarahBDD_buggy_crsr_nonnumeric.csv', fields_order=cf_crsr_buggy2.columns, keep_index=True)\n",
    "cf_crsr_buggy2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check if subscores are outside limits\n",
    "#\n",
    "#* au: 0-4\n",
    "#* vis: 0-5\n",
    "#* mot: 0-6\n",
    "#* oromot/verbal: 0-3\n",
    "#* communication: 0-2\n",
    "#* éveil/arousal: 0-3 (attention étant binaire, score max)\n",
    "\n",
    "def find_outside_range_df(df, col, mini, maxi):\n",
    "    return df[(df[col] > maxi) | (df[col] < mini)]\n",
    "\n",
    "limits_check = [('crs_au', 0, 4),\n",
    "                ('crs_vis', 0, 5),\n",
    "                ('crs_mot', 0, 6),\n",
    "                ('crs_ver', 0, 3),\n",
    "                ('crs_com', 0, 2),\n",
    "                ('crs_ar', 0, 3),\n",
    "               ]\n",
    "\n",
    "for lim in limits_check:\n",
    "    res = find_outside_range_df(cf_crsr_all_nostr, lim[0], lim[1], lim[2])\n",
    "    if len(res):\n",
    "        save_df_as_csv(res, 'SarahBDD_buggylimits_%s.csv' % lim[0], fields_order=res.columns, keep_index=True)\n",
    "        print('Found an outside limits for %s:' % lim[0])\n",
    "        print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check impossible combinations of scores\n",
    "#\n",
    "#Scorages impossibles:\n",
    "#1. auditif 0-2 + visuel 5\n",
    "#2. auditif 0-2 + com 1-2\n",
    "#\n",
    "#Scorage bizarres:\n",
    "#3. éveil 3 + UWS\n",
    "\n",
    "cf_crsr_impossible1 = cf_crsr_all_nostr[(cf_crsr_all_nostr['crs_au'] <= 2) & (cf_crsr_all_nostr['crs_vis'] >= 5)]\n",
    "cf_crsr_impossible2 = cf_crsr_all_nostr[(cf_crsr_all_nostr['crs_au'] <= 2) & (cf_crsr_all_nostr['crs_com'] >= 1)]\n",
    "cf_crsr_impossible3 = cf_crsr_all_nostr[(cf_crsr_all_nostr['crs_ar'] >= 3) & (cf_crsr_all_nostr['diagn_crs'].str.lower().str.strip().isin(['vs','coma']))]\n",
    "save_df_as_csv(cf_crsr_impossible1, 'SarahBDD_impossible_aud0-2_vis5.csv', fields_order=cf_crsr_impossible1.columns, keep_index=True)\n",
    "save_df_as_csv(cf_crsr_impossible2, 'SarahBDD_impossible_aud0-2_com1-2.csv', fields_order=cf_crsr_impossible2.columns, keep_index=True)\n",
    "save_df_as_csv(cf_crsr_impossible3, 'SarahBDD_impossible_ar3_uws.csv', fields_order=cf_crsr_impossible3.columns, keep_index=True)\n",
    "print('Scorages impossibles:')\n",
    "print('1. auditif 0-2 + visuel 5')\n",
    "print(cf_crsr_impossible1)\n",
    "print('2. auditif 0-2 + com 1-2')\n",
    "print(cf_crsr_impossible2)\n",
    "print('Scorages bizarres')\n",
    "print('3. éveil 3 + UWS')\n",
    "print(cf_crsr_impossible3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---------------------------------\n",
    "## Merge with FMP database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: merge names first, then rename names of one or the other, then compare sessions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cfmp = pd.read_csv(fmp_csv, sep=';', low_memory=False).dropna(axis=0, how='all')  # drop empty lines (but NOT columns else we might lose important columns such as 13_since_onset which was never filled but is necessary for the stacking!)\n",
    "cfmp.rename(columns={'CRSr::Date of CRSr': 'date_assess'}, inplace=True)\n",
    "cfmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert to uniformized dates, there should be no error here, else add errors='coerce'\n",
    "dates = pd.to_datetime(cfmp.reset_index().set_index('Name')['date_assess'], dayfirst=True)\n",
    "# Reassign similar index (based on Name only, to be able to merge)\n",
    "cfmp = cfmp.reset_index().set_index('Name')\n",
    "# Replace the date column\n",
    "cfmp['date_assess'] = dates\n",
    "# Reset the hierarchical name/date index and sort\n",
    "cfmp = cfmp.reset_index().set_index(['Name', 'date_assess']).sort_index()\n",
    "# Display!\n",
    "cfmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge csv and fmp final diagnoses if name matches\n",
    "cmerge = merge_two_df(cf_crsr_all_nostr.reset_index(), cfmp.reset_index(), mode=1, skip_sanity=True)\n",
    "cmerge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract subjects missing in either database\n",
    "missing_in_fmp = cmerge[cmerge['Name2'].isnull()]['Name'].tolist()\n",
    "missing_in_sarah = cmerge[cmerge['Name'].isnull()]['Name2'].tolist()\n",
    "save_df_as_csv(pd.DataFrame(missing_in_fmp, columns=['Name']), 'SarahBDD_subjects_missing_in_fmp.csv', csv_order_by='Name')\n",
    "save_df_as_csv(pd.DataFrame(missing_in_sarah, columns=['Name']), 'SarahBDD_subjects_missing_in_sarah.csv', csv_order_by='Name')\n",
    "print('Missing subjects saved in SarahBDD_subjects_missing_in_fmp.csv and SarahBDD_subjects_missing_in_sarah.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remap names in Sarah's db to match FMP db (so we can compare sessions by subject name)\n",
    "def replace_nonnull_df(x, repmap):\n",
    "    replacement = repmap[x]\n",
    "    return replacement if replacement is not None else x\n",
    "\n",
    "keep_nulls = False\n",
    "repmap = cmerge.set_index('Name')['Name2'].to_dict()\n",
    "cf_crsr_all_ren = cf_crsr_all_nostr.copy().reset_index()\n",
    "if keep_nulls:\n",
    "    # Much faster but if there are nulls they will be replaced\n",
    "    cf_crsr_all_ren['Name'] = cf_crsr_all_ren['Name'].map(repmap)\n",
    "else:\n",
    "    # Slower but remap only if the remap is not null\n",
    "    cf_crsr_all_ren['Name'] = cf_crsr_all_ren['Name'].apply(lambda x: replace_nonnull_df(x, repmap))\n",
    "cf_crsr_all_ren = cf_crsr_all_ren.set_index(['Name', 'date_assess'])\n",
    "cf_crsr_all_ren"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find all CRS-Rs present in Sarah's db but not in FMP db (we simply compute the difference of the name/date_assess indices!)\n",
    "crsr_missing_in_fmp = cf_crsr_all_ren.loc[cf_crsr_all_ren.index.difference(cfmp.index)]\n",
    "save_df_as_csv(crsr_missing_in_fmp, 'SarahBDD_crsr_missing_in_fmp.csv', fields_order=crsr_missing_in_fmp.columns, keep_index=True)\n",
    "print('Missing CRS-Rs in FMP that are available in SarahBDD saved in SarahBDD_crsr_missing_in_fmp.csv')\n",
    "crsr_missing_in_fmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find all best diagnosis for each patient in Sarah's db\n",
    "cf_crsr_bestdiags = compute_best_diag(cf_crsr_all_ren['diagn_crs'].replace({'vs':'vs/uws', 'vs ':'vs/uws', 'uws':'vs/uws'}), diag_order=['coma', 'vs/uws', 'mcs', 'mcs-', 'mcs+', 'emcs', 'lis', 'lis incomplete', 'mcs-/lis??'])\n",
    "cf_crsr_bestdiags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find any CRS-R session that has a different diagnosis in both db\n",
    "diags_sarah = cf_crsr_all_ren['diagn_crs'].replace({'vs':'vs/uws', 'vs ':'vs/uws', 'uws':'vs/uws'}).str.lower().str.strip()\n",
    "diags_fmp = cfmp['CRSr::Computed Outcome'].str.lower().str.strip()\n",
    "diags_intersection_idxs = diags_sarah.index.intersection(diags_fmp.index.intersection(diags_sarah.index))\n",
    "# Need to do a manual loop because there are duplications (multiple CRS-Rs sessions on the same day, thus the same key/index...)\n",
    "conflicts = []\n",
    "for idx in diags_intersection_idxs:\n",
    "    ds = diags_sarah.loc[idx]\n",
    "    df = diags_fmp.loc[idx]\n",
    "    try:\n",
    "        flagEq = True\n",
    "        for s in ds:\n",
    "            for f in df:\n",
    "                if s != f:\n",
    "                    flagEq = False\n",
    "                    break\n",
    "            if not flagEq:\n",
    "                break\n",
    "        if not flagEq:\n",
    "            n = diags_sarah.loc[idx].reset_index()['Name'][0]\n",
    "            d = diags_sarah.loc[idx].reset_index()['date_assess'][0]\n",
    "            conflicts.append((n,d,s,f))\n",
    "    except ValueError as exc:\n",
    "        print(ds)\n",
    "        print(df)\n",
    "        raise\n",
    "conflicts_any = pd.DataFrame(conflicts, columns=['Name', 'date_assess', 'Sarah_diag', 'FMP_diag']).set_index(['Name','date_assess'])\n",
    "print('Conflicts saved in SarahBDD_any_conflicts_with_fmp.csv')\n",
    "save_df_as_csv(conflicts_any, 'SarahBDD_any_conflicts_with_fmp.csv', keep_index=True)\n",
    "conflicts_any"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find any CRS-R session that has a different diagnosis in both db in all CRS-R sessions of the day (because eg, fmp can store more sessions on one day, thus explaining a different diagnosis but it's only because we have an additional session)\n",
    "diags_sarah = cf_crsr_all_ren['diagn_crs'].replace({'vs':'vs/uws', 'vs ':'vs/uws', 'uws':'vs/uws'}).str.lower().str.strip()\n",
    "diags_fmp = cfmp['CRSr::Computed Outcome'].str.lower().str.strip()\n",
    "diags_intersection_idxs = diags_sarah.index.intersection(diags_fmp.index.intersection(diags_sarah.index))\n",
    "# Need to do a manual loop because there are duplications (multiple CRS-Rs sessions on the same day, thus the same key/index...)\n",
    "conflicts = []\n",
    "for idx in diags_intersection_idxs:\n",
    "    ds = diags_sarah.loc[idx]\n",
    "    df = diags_fmp.loc[idx]\n",
    "    try:\n",
    "        flagEq = False\n",
    "        for s in ds:\n",
    "            for f in df:\n",
    "                if s == f:\n",
    "                    flagEq = True\n",
    "                    break\n",
    "            if flagEq:\n",
    "                break\n",
    "        if not flagEq:\n",
    "            n = diags_sarah.loc[idx].reset_index()['Name'][0]\n",
    "            d = diags_sarah.loc[idx].reset_index()['date_assess'][0]\n",
    "            conflicts.append((n,d,s,f))\n",
    "    except ValueError as exc:\n",
    "        print(ds)\n",
    "        print(df)\n",
    "        raise\n",
    "conflicts_all = pd.DataFrame(conflicts, columns=['Name', 'date_assess', 'Sarah_diag', 'FMP_diag']).set_index(['Name','date_assess'])\n",
    "save_df_as_csv(conflicts_all, 'SarahBDD_all_conflicts_with_fmp.csv', keep_index=True)\n",
    "print('Conflicts saved in SarahBDD_all_conflicts_with_fmp.csv')\n",
    "conflicts_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}