csg_datafusion_db_merger.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CSV Databases Merger\n",
    "By Stephen Larroque @ Coma Science Group, GIGA Research, University of Liege\n",
    "Creation date: 2018-05-27\n",
    "License: MIT\n",
    "v1.2.4\n",
    "\n",
    "DESCRIPTION:\n",
    "Generic tool to merge two CSV databases based on the subject's name (hence expecting a column 'name' in each csv file).\n",
    "This script will take care of fuzzy matching names and append all columns of each csv file, hence centralizing all informations into one file.\n",
    "\n",
    "Note: in case of multiple names/lines matching, they will all be concatenated into a single line: if one name in one of the two databases match multiple names/lines of the second database, then the second databases lines will be concatenated into one. In the opposite case (second database's name match multiple names in first database), the same holds.\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",
    "* Better support for accentuated characters when saving (they are replaced during merging but then they are copied over from the original dataframe and when saving the encoding is incorrect)"
   ]
  },
  {
   "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",
    "\n",
    "from csg_fileutil_libs.aux_funcs import save_df_as_csv, _tqdm, merge_two_df, df_remap_names, concat_vals\n",
    "\n",
    "def df_literal_eval(x):\n",
    "    try:\n",
    "        return(ast.literal_eval(x))\n",
    "    except (SyntaxError, ValueError):\n",
    "        return x"
   ]
  },
  {
   "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",
    "id_db = r'latestdbs2018\\patients-sedation-2018-checked-by-Stephen-from-archives_v7_2018-10-18_full-names-merged.csv'\n",
    "# Second (reference) database to merge. The names will be added as a new column 'name_altx'.\n",
    "ref_db = r'latestdbs2018\\fmp_db_subjects_aggregated.csv_etiosedatfixed_dicomsdatediag.csv_acute_mergesedat.csv'\n",
    "# Output database with the merge results\n",
    "out_db = r'latestdbs2018\\merged_db.csv'\n",
    "\n",
    "# Similarity search parameters (of names in both databases)\n",
    "dist_threshold = 0.2 # character distance (normalized on 1 over the total number of characters = jaccard distance), lower is more similar, default: 0.2\n",
    "dist_words_threshold = 0.4 # words distance (normalized idem but on number of words), default: 0.2\n",
    "keep_lastname_only = False # keep only the lastname? (supposed to be the first word) - this can enhance the matching if there are too many false positives, particularly if one database only include the last name but the other one has the full name\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load first database\n",
    "cref = pd.read_csv(ref_db, sep=';').dropna(how='all').dropna(how='any', subset=['name'])  # drop all rows where name is empty (necessary else this will produce an error, we expect the name to exist)\n",
    "cref['name_orig'] = cref['name'] # make a backup of the original name\n",
    "cref['name'] = cref['name'].apply(lambda x: df_literal_eval(x)[0] if isinstance(df_literal_eval(x), list) else x)  # if doing multiple consecutive merges, a name can in fact be a list of concatenated names, then extract the first name in the list\n",
    "if keep_lastname_only: # keep only the lastname (supposed to be first)\n",
    "    cref['name'] = cref['name'].apply(lambda x: x.split()[0])\n",
    "cref"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cid = pd.read_csv(id_db, sep=';').dropna(how='all').dropna(how='any', subset=['name'])\n",
    "cid['name_orig2'] = cid['name'] # make a backup of the original name\n",
    "cid['name'] = cid['name'].apply(lambda x: df_literal_eval(x)[0] if isinstance(df_literal_eval(x), list) else x)  # if doing multiple consecutive merges, a name can in fact be a list of concatenated names, then extract the first name in the list\n",
    "if keep_lastname_only:\n",
    "    cid['name'] = cid['name'].apply(lambda x: x.split()[0])\n",
    "cid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Merge both databases if name matches (here we extract the names/indices where they match)\n",
    "cmerge = merge_two_df(cref, cid, col='name', mode=0, dist_threshold=dist_threshold, dist_words_threshold=dist_words_threshold, skip_sanity=True)\n",
    "cmerge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cmerge.loc[cmerge['name'].isnull(), 'name'] = cmerge['name2']\n",
    "cmerge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remap names\n",
    "cid2 = df_remap_names(cid, cmerge, 'name', 'name2', keep_nulls=True)\n",
    "del cid2['index']\n",
    "cid2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make sure each name is unique, else concatenate all rows for each name into one row\n",
    "cref = cref.reset_index().groupby('name').agg(concat_vals)\n",
    "cid2 = cid2.reset_index().groupby('name').agg(concat_vals)\n",
    "cref.reset_index(inplace=True)\n",
    "cid2.reset_index(inplace=True)\n",
    "#cref.set_index('name', inplace=True)\n",
    "\n",
    "# Join both databases\n",
    "cfinal = pd.merge(cid2, cref, how='outer', on='name')\n",
    "# Reset the original name and keep the 1st database names\n",
    "cfinal['name'] = cfinal['name_orig2']\n",
    "del cfinal['name_orig2']\n",
    "\n",
    "# Create another columns to store the name from 2nd database and create a column with any name from first db or second db\n",
    "for x in range(1000):\n",
    "    # If we do multiple merge, we will have multiple name_alt columns: name_alt0, name_alt1, etc\n",
    "    if not ('name_alt%i' % x) in cfinal.columns:\n",
    "        # Rename the name column from the 2nd database\n",
    "        cfinal.insert(1, ('name_alt%i' % x), cfinal['name_orig']) # insert the column just after 'name' for ergonomy\n",
    "        \n",
    "        # Create a new name column with either name from 1st db or 2nd if nan in 1st\n",
    "        nametemp = cfinal['name']\n",
    "        nametemp[nametemp.isnull()] = cfinal['name_orig']\n",
    "        cfinal.insert(2, ('name_all%i' % x), nametemp)\n",
    "\n",
    "        # Finally delete the useless column (that we copied over to name_altx)\n",
    "        del cfinal['name_orig']\n",
    "\n",
    "        # Finish!\n",
    "        break\n",
    "# Show the merged result\n",
    "cfinal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if save_df_as_csv(cfinal.reset_index(), out_db, fields_order=list(cfinal.columns), csv_order_by='name'):\n",
    "    print('Merged database successfully saved in %s!' % out_db)\n",
    "else:\n",
    "    print('ERROR: the merged database could not be saved!')"
   ]
  }
 ],
 "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
}