csg_datafusion_db_merger_old.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CSV Databases Merger (old)\n",
    "# (see csg_fileutil_db_merger.ipynb for a more general purpose merger, this one is limited to only a few fields, whereas the new one can merge everything)\n",
    "# By Stephen Larroque @ Coma Science Group, GIGA Research, University of Liege\n",
    "# Creation date: 2017-02-07\n",
    "# License: MIT\n",
    "# v1.1.2\n",
    "#\n",
    "# INSTALL NOTE:\n",
    "# You need to pip install pandas before launching this script.\n",
    "# Tested on Python 2.7.11\n",
    "#\n",
    "# USAGE:\n",
    "# Any two csv files can be used for the merge, you just need to ensure both contain a 'name' field with patient's name, and a 'final_diagnosis' field with the CRS-R final score. Other fields may have different names, however if you want to merge fields automatically, you should rename other fields as well to have similar column names (if there are columns in one csv that are not available in the other, don't worry, these will be added in the final merged csv).\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",
    "import re\n",
    "\n",
    "from csg_fileutil_libs.distance import distance\n",
    "\n",
    "from csg_fileutil_libs.aux_funcs import distance_jaccard_words_split, cleanup_name, cleanup_name_df, replace_buggy_accents, save_df_as_csv, _tqdm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# PARAMETERS\n",
    "\n",
    "# Reports database\n",
    "reports_csv = 'all_patients_fields.csv'\n",
    "\n",
    "# FileMakerPro (FMP) database\n",
    "fmp_csv = 'db-fmp.csv'\n",
    "\n",
    "# Final_diagnosis translation in FMP database to match nomenclatura of reports database\n",
    "fmp_diag_translation = [\n",
    "    ('VS/UWS', 'UWS'),\n",
    "    ('LIS incomplete', 'partial LIS'),\n",
    "    ('COMA', 'coma')\n",
    "]\n",
    "\n",
    "# Optional: How to translate FMP fields to merge with reports fields?\n",
    "fmp_col_translation = {'date_birth': 'birthdate',\n",
    "                      'date_accident': 'accident_date',\n",
    "                      #'etiology_specified': 'accident_etiology',\n",
    "                      'sedation_mri': 'mri_sedation',\n",
    "                      'epilepsia': 'epileptic'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------------------------------------\n",
    "# Loading databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "cf = pd.read_csv(reports_csv, sep=';')\n",
    "cf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cdb = pd.read_csv(fmp_csv, sep=';').dropna(how='all').dropna(subset=['name'], how='all')\n",
    "# Clean up names of fmp database\n",
    "#cdb = cleanup_name_df(cdb)\n",
    "# Reorder by name\n",
    "cdb.sort_values('name', inplace=True)\n",
    "# Renaming fields (to better merge with reports csv database later)\n",
    "if fmp_col_translation:\n",
    "    cdb.rename(columns=fmp_col_translation, inplace=True)\n",
    "# Renaming diagnoses to match nomenclatura of the other csv\n",
    "if fmp_diag_translation:\n",
    "    for pattern, replacement in fmp_diag_translation:\n",
    "        cdb.loc[cdb['final_diagnosis'] == pattern, 'final_diagnosis'] = replacement\n",
    "# Print db\n",
    "print(len(cdb))\n",
    "cdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Reports database contains %i diagnoses over %i records in total (%i unique diagnoses for %i unique patients).' % (cf['final_diagnosis'].count(), len(cf), len(cf[~cf['final_diagnosis'].isnull()]['name'].unique()), len(cf['name'].unique())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('FMP database contains %i diagnoses over %i records in total (%i unique diagnoses for %i unique patients).' % (cdb['final_diagnosis'].count(), len(cdb), len(cdb[~cdb['final_diagnosis'].isnull()]['name'].unique()), len(cdb['name'].unique())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('All names in FMP database:')\n",
    "cdb['name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------------------------\n",
    "## Comparison of the two csv databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge csv and fmp final diagnoses if name matches\n",
    "# TODO: replace by updated compute_names_distance_matrix() function\n",
    "dist_threshold = 0.2\n",
    "comp_res = []\n",
    "for crowid, c in _tqdm(cf.iterrows(), total=len(cf), desc='MERGE'):\n",
    "    found = False\n",
    "    for cdrowid, cd in cdb.iterrows():\n",
    "        if not cd['name']:\n",
    "            continue\n",
    "        pts_name = cleanup_name(replace_buggy_accents(cd['name']), 'utf-8')\n",
    "        if distance.nlevenshtein(pts_name, c['name'], method=1) <= dist_threshold or distance_jaccard_words_split(pts_name, c['name'], partial=True, norm=True, dist=dist_threshold) <= dist_threshold: # use shortest distance with normalized levenshtein\n",
    "            comp_res.append( {'name': c['name'], 'name_fmp': cd['name'], 'csv_final_diagnosis': c['final_diagnosis'], 'fmp_final_diagnosis': cd['final_diagnosis'], 'report_path': c['report_path']} )\n",
    "            found = True\n",
    "    if not found:\n",
    "        comp_res.append( {'name': c['name'], 'name_fmp': None, 'csv_final_diagnosis': c['final_diagnosis'], 'fmp_final_diagnosis': None} )\n",
    "comp_res = pd.DataFrame(comp_res)\n",
    "print('FMP and reports databases were merged successfully!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of all merged records (ie, records that are present in both databases):')\n",
    "comp_res = comp_res.where((pd.notnull(comp_res)), None)\n",
    "print('Total number of unique patients: %i' % len(comp_res['name'].unique()))\n",
    "comp_res_diag = comp_res[~(comp_res['csv_final_diagnosis'].isnull() & comp_res['fmp_final_diagnosis'].isnull())]\n",
    "print('Total number of unique patients with at least a diagnosis: %i' % len(comp_res_diag['name'].unique()))\n",
    "comp_res.to_csv('diff_merge_test.csv', sep=';')\n",
    "comp_res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of unique patients in csv database but missing from fmp database (not accounted in subsequent analyses):')\n",
    "comp_miss_fmp = comp_res[comp_res['name_fmp'].isnull()]\n",
    "comp_miss_fmp_with_diag = comp_miss_fmp.dropna(subset=['csv_final_diagnosis'], how='all')\n",
    "comp_miss_fmp_full = cf[cf['name'].isin(comp_miss_fmp['name'])].sort_values('name')\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', 3):\n",
    "    print(\"Total new records: %i\" % (len(comp_miss_fmp_full)))\n",
    "    print(\"Total unique patients: %i (with diagnosis: %i)\" % (len(comp_miss_fmp['name'].unique()), len(comp_miss_fmp_with_diag['name'].unique())))\n",
    "    print(pd.Series(comp_miss_fmp['name'].unique()))\n",
    "# Save result to a csv\n",
    "comp_miss_fmp_full.to_csv('diff_reports_more.csv', index=False, sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of records in csv database present in fmp database but without a diagnosis:')\n",
    "comp_res_fmp_with_diag_unique = comp_res[~comp_res['fmp_final_diagnosis'].isnull()]['name'].unique()\n",
    "comp_miss_fmp2 = comp_res[~comp_res['name'].isin(comp_res_fmp_with_diag_unique) & ~comp_res['name_fmp'].isnull()]\n",
    "comp_miss_fmp2_full = cf[cf['name'].isin(comp_miss_fmp2['name'])].sort_values('name')\n",
    "\n",
    "print(\"Total number of fmp unique patients found also in csv: %i\" % len(comp_res_fmp_with_diag_unique))\n",
    "print(\"Total records matched in fmp but without a diagnosis: %i\" % (len(comp_miss_fmp2_full)))\n",
    "print(\"Total unique patients matched in fmp without a diagnosis: %i\" % (len(comp_miss_fmp2_full['name'].unique())))\n",
    "print(\"Total unique patients with diag from csv: %i\" % (len(comp_miss_fmp2_full[~comp_miss_fmp2_full['final_diagnosis'].isnull()]['name'].unique())))\n",
    "#with pd.option_context('display.max_rows', None, 'display.max_columns', 3):\n",
    "    #print(pd.Series(comp_miss_fmp2['name'].unique()))\n",
    "# Save result to a csv\n",
    "comp_miss_fmp2_full.to_csv('diff_reports_more_diag_only.csv', index=False, sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of patients in fmp database but missing from csv database (not accounted in subsequent analyses):')\n",
    "comp_miss_csv = cdb[~cdb['name'].isin(comp_res['name_fmp'])]\n",
    "comp_miss_csv_with_diag = comp_miss_csv.dropna(subset=['final_diagnosis'], how='all')\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', 3):\n",
    "    print(\"%i unique patients, with %i having a diagnosis.\" % (len(comp_miss_csv['name'].unique()), comp_miss_csv_with_diag['name'].count()))\n",
    "    print\n",
    "    print(\"-> With diagnosis:\")\n",
    "    print(comp_miss_csv_with_diag.loc[:, ['name', 'final_diagnosis']])\n",
    "    print\n",
    "    print(\"-> Without diagnosis:\")\n",
    "    print(comp_miss_csv[comp_miss_csv['final_diagnosis'].isnull()]['name'])\n",
    "cdb[cdb['name'].isin(comp_miss_csv_with_diag['name'])].sort_values('name').to_csv('diff_fmp_more.csv', index=False, sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute list of records found in both databases, with a diagnosis in fmp db\n",
    "comp_res2 = comp_res.copy()\n",
    "comp_res2 = comp_res2.dropna(subset=['fmp_final_diagnosis'], how='all')\n",
    "comp_res2.sort_values(by=['name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of all agreements in final diagnoses between databases (where FMP has a diagnosis):')\n",
    "comp_eqs = comp_res2[comp_res2['csv_final_diagnosis'] == comp_res2['fmp_final_diagnosis']]\n",
    "print('Total: %i records (%i unique patients)' % (len(comp_eqs), len(comp_eqs['name'].unique())))\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', 3):\n",
    "    print(comp_eqs.sort_values('name').loc[:, ['name', 'csv_final_diagnosis', 'fmp_final_diagnosis']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of all differences in final diagnoses between databases (where FMP has a diagnosis):')\n",
    "comp_diffs = comp_res2[comp_res2['csv_final_diagnosis'] != comp_res2['fmp_final_diagnosis']]\n",
    "print(len(comp_diffs))\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', 3):\n",
    "    print(comp_diffs.sort_values('name').loc[:, ['name', 'csv_final_diagnosis', 'fmp_final_diagnosis']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of all real differences in final diagnoses between databases (where FMP has a diagnosis AND not in agreement for this subject for another session):')\n",
    "comp_diffs2 = comp_res2[comp_res2['csv_final_diagnosis'] != comp_res2['fmp_final_diagnosis']]\n",
    "comp_diffs2 = comp_diffs2[~comp_diffs2['name'].isin(comp_eqs['name'])]\n",
    "print(len(comp_diffs2))\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', None, 'expand_frame_repr', False):\n",
    "    print(comp_diffs2.sort_values('name').loc[:, ['name', 'csv_final_diagnosis', 'fmp_final_diagnosis', 'report_path']].rename(columns={'csv_final_diagnosis': 'csv_diag', 'fmp_final_diagnosis': 'fmp_diag', 'report_path': 'path'}))  # To shorten to first 4 characters each column name: .rename(columns=lambda x: x[:4])\n",
    "comp_diffs2.sort_values('name').to_csv('diff_reports_vs_fmp_conflicts.csv', sep=';', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('List of diffs because one report is incomplete but others are OK:')\n",
    "comp_diffs3 = comp_res2[comp_res2['csv_final_diagnosis'] != comp_res2['fmp_final_diagnosis']]\n",
    "comp_diffs3 = comp_diffs3[comp_diffs3['name'].isin(comp_eqs['name'])]\n",
    "print(len(comp_diffs3))\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', None, 'expand_frame_repr', False):\n",
    "    print(comp_diffs3.sort_values('name').rename(columns=lambda x: x[:8]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fmp_replace_reduce = [\n",
    "    ('MCS+', 'MCS'),\n",
    "    ('MCS-', 'MCS'),\n",
    "    ('coma', 'UWS')\n",
    "]\n",
    "comp_res3 = comp_res2.copy()\n",
    "for pattern, replacement in fmp_replace_reduce:\n",
    "    comp_res3.loc[comp_res3['fmp_final_diagnosis'] == pattern, 'fmp_final_diagnosis'] = replacement\n",
    "    comp_res3.loc[comp_res3['csv_final_diagnosis'] == pattern, 'csv_final_diagnosis'] = replacement\n",
    "\n",
    "print('Removing differences between MCS+/- and coma/uws, here are the remaining differences:')\n",
    "comp_diffs3 = comp_res3[comp_res3['csv_final_diagnosis'] != comp_res3['fmp_final_diagnosis']]\n",
    "print(len(comp_diffs3))\n",
    "comp_diffs3.sort_values('name').loc[:, ['name', 'csv_final_diagnosis', 'fmp_final_diagnosis', 'report_path']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "comp_res4 = comp_res.copy() # TODO: add also patient missing from fmp eventually\n",
    "comp_res4 = comp_res4.dropna(subset=['csv_final_diagnosis'], how='all')\n",
    "comp_res4.sort_values(by=['name'])\n",
    "\n",
    "fmp_replace = [\n",
    "    ('VS/UWS', 'UWS'),\n",
    "    ('LIS incomplete', 'partial LIS'),\n",
    "    ('COMA', 'coma')\n",
    "]\n",
    "for pattern, replacement in fmp_replace:\n",
    "    comp_res4.loc[comp_res4['fmp_final_diagnosis'] == pattern, 'fmp_final_diagnosis'] = replacement\n",
    "comp_res4.sort_values(by=['name'])\n",
    "\n",
    "print('List of all new entries (new from csv and not present in FMP):')\n",
    "comp_new = comp_res4[comp_res4['csv_final_diagnosis'] != comp_res4['fmp_final_diagnosis']]\n",
    "comp_new = comp_new[~comp_new['name_fmp'].isin(cdb['name'])]\n",
    "print(\"%s records (%s unique patients)\" %(len(comp_new), len(comp_new['name'].unique())))\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', 3):\n",
    "    print(comp_new.sort_values('name').loc[:, ['name', 'csv_final_diagnosis', 'fmp_final_diagnosis']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----------------------------------\n",
    "## Saving final database, merging as much as possible automatically (rejecting conflicts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save full database: reports + additional records from fmp with diagnosis that are missing in reports + diagnosis from fmp where csv diagnosis is missing\n",
    "cf_cdb = cf.copy()\n",
    "# Extract only new fmp names\n",
    "cdb_only_new = cdb[cdb['name'].isin(comp_miss_csv_with_diag['name'])].sort_values('name')\n",
    "# Cleanup the names\n",
    "cdb_only_now = cleanup_name_df(cdb_only_new)\n",
    "# Append new names from fmp\n",
    "cf_cdb = cf_cdb.append(cdb_only_new)\n",
    "# Assign fmp diagnosis for patients where there is no csv diagnosis (for any record)\n",
    "comp_no_csv_diag_but_fmp = comp_diffs2[~comp_diffs2['name'].isin(cf_cdb.dropna(subset=['final_diagnosis'], how='all')['name'].unique())]\n",
    "for c in comp_no_csv_diag_but_fmp.iterrows():\n",
    "    for c2 in cf_cdb[cf_cdb['name'] == c['name']].itertuples(): # TODO: warning, if there are more than 255 columns or elements, this will raise an error (because we get tuple() instead of namedtuple())\n",
    "        cf_cdb.loc[c2.Index, 'final_diagnosis'] = c['fmp_final_diagnosis']\n",
    "    #possible solution to itertuples() issue:\n",
    "    #idxs = cf_cdb[cf_cdb['name'] == c['name']]\n",
    "    #cf_cdb.loc[idxs, 'final_diagnosis'] = c['fmp_final_diagnosis']\n",
    "# Save to csv\n",
    "#cf_cdb.to_csv('db_reports_plus_fmp.csv', index=False, sep=';')\n",
    "fields_order = ['name', 'gender', 'age', 'final_diagnosis', 'mri_sedation']\n",
    "save_df_as_csv(cf_cdb, 'db_reports_plus_fmp.csv', fields_order, csv_order_by='name', verbose=True)\n",
    "print('Full database (reports + additional records from fmp) was saved in db_reports_plus_fmp.csv.')\n",
    "print('Full database has %i records (%i with diagnosis) and %i unique patients (%i with diagnosis)' % (len(cf_cdb),\n",
    "                                                                                                       len(cf_cdb.dropna(subset=['final_diagnosis'], how='all')),\n",
    "                                                                                                       len(cf_cdb['name'].unique()),\n",
    "                                                                                                       len(cf_cdb.dropna(subset=['final_diagnosis'], how='all')['name'].unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------------------------------------------------------\n",
    "## Final stats and charts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_db_stats = {'conflicts': len(comp_diffs2['name'].unique()),\n",
    "                  'agreements': len(comp_eqs[~comp_eqs['csv_final_diagnosis'].isnull()]['name'].unique()),\n",
    "                  'fmp_add': len(comp_miss_csv_with_diag['name'].unique()),\n",
    "                  'reports_add': len(comp_new[~comp_new['csv_final_diagnosis'].isnull()]['name'].unique()),\n",
    "                  'reports_add_just_diag': len(comp_miss_fmp2_full[~comp_miss_fmp2_full['final_diagnosis'].isnull()]['name'].unique()),\n",
    "                 }\n",
    "print('Conflicts: %i subjects (with %i having no csv diagnosis so fmp diagnosis was copied)' % (final_db_stats['conflicts'], len(comp_no_csv_diag_but_fmp)))\n",
    "print('Agreements: %i records (%i unique patients, %i with diagnosis)' % (len(comp_eqs), len(comp_eqs['name'].unique()), final_db_stats['agreements']))\n",
    "print(\"FMP adds %i unique subjects having a diagnosis.\" % final_db_stats['fmp_add'])\n",
    "print(\"CSV adds %s totally new records (%s unique patients with a diagnosis)\" %(len(comp_new), final_db_stats['reports_add']))\n",
    "print(\"CSV adds %i diagnoses to unique subjects present in both databases.\" % final_db_stats['reports_add_just_diag'])\n",
    "print('Full database has %i records (%i with diagnosis) and %i unique patients (%i with diagnosis)' % (len(cf_cdb),\n",
    "                                                                                                       len(cf_cdb.dropna(subset=['final_diagnosis'], how='all')),\n",
    "                                                                                                       len(cf_cdb['name'].unique()),\n",
    "                                                                                                       len(cf_cdb.dropna(subset=['final_diagnosis'], how='all')['name'].unique())))\n",
    "cdb_missing_diag = cdb[~cdb['name'].isin(comp_res['name_fmp'].unique()) & ~cdb['name'].isin(comp_miss_csv_with_diag['name'].unique())]\n",
    "print('FMP missing diagnosis (and not present in reports db): %i' % len(cdb_missing_diag))\n",
    "cdb_missing_diag.to_csv('diff_fmp_missing_diags.csv', sep=';', index=False)\n",
    "print('List of FMP subjects with no diagnosis and missing from reports was saved in diff_fmp_missing_diags.csv.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "df_db_stats = pd.Series(final_db_stats, name='All unique subjects w/ diag (%i)' % sum(final_db_stats.values()))\n",
    "df_db_stats.plot(kind='pie', title='Final db subjects provenance', use_index=False, figsize=(6, 6), fontsize=10, pctdistance=1.15, labeldistance=1.3, autopct=lambda(p): '%.0f%% (%i)' % (p, round(p / 100.0 * df_db_stats.sum())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_stats = {'agreements': final_db_stats['agreements'],\n",
    "             'conflicts': final_db_stats['conflicts'],\n",
    "            }\n",
    "df_merge_stats = pd.Series(merge_stats, name='Merge (%i subjects)' % sum(final_db_stats.values()))\n",
    "df_merge_stats.plot(kind='pie', title='Merge results', use_index=False, figsize=(6, 6), fontsize=10, pctdistance=0.6, labeldistance=1.1, autopct=lambda(p): '%.0f%% (%i)' % (p, round(p / 100.0 * df_merge_stats.sum())), colors=['g', 'r'], startangle=292)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_db_stats = pd.DataFrame(columns=['total', 'diag', 'unique', 'unique_w_diag'])\n",
    "all_db_stats.loc['reports'] = [len(cf), cf['final_diagnosis'].count(), len(cf['name'].unique()), len(cf[~cf['final_diagnosis'].isnull()]['name'].unique())]\n",
    "all_db_stats.loc['fmp'] = [len(cdb), cdb['final_diagnosis'].count(), len(cdb['name'].unique()), len(cdb[~cdb['final_diagnosis'].isnull()]['name'].unique())]\n",
    "all_db_stats.loc['new_db'] = [len(cf_cdb),\n",
    "                       len(cf_cdb.dropna(subset=['final_diagnosis'], how='all')),\n",
    "                       len(cf_cdb['name'].unique()),\n",
    "                       len(cf_cdb.dropna(subset=['final_diagnosis'], how='all')['name'].unique())]\n",
    "\n",
    "# Plot!\n",
    "ax = all_db_stats.plot(kind='bar')\n",
    "# Place legend better\n",
    "ax.legend(loc='upper right', bbox_to_anchor=(1.4, 1.0),\n",
    "          ncol=1, fancybox=True, shadow=True)\n",
    "# Annotate bar with values\n",
    "x_offset = -0.08\n",
    "y_offset = -0.1\n",
    "for p in ax.patches:\n",
    "    b = p.get_bbox()\n",
    "    val = \"{:.0f}\".format(b.y1 + b.y0) # use {+:.2f} for possibly negative values to get the sign\n",
    "    ax.annotate(val, ((b.x0 + b.x1)/2 + x_offset, b.y1 + y_offset))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------------------------------------\n",
    "## Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compare_test(c1, c2, eq=False):\n",
    "    if eq:\n",
    "        return c1[c1['name'].isin(c2['name'].unique())]\n",
    "    else:\n",
    "        return c1[~c1['name'].isin(c2['name'].unique())]\n",
    "def get_unique(c):\n",
    "    return c['name'].unique()\n",
    "print(len( get_unique(compare_test(cf_cdb.dropna(subset=['final_diagnosis'], how='all'), comp_diffs2)) ))\n",
    "compare_test(comp_eqs, comp_diffs2, True)\n",
    "compare_test(comp_diffs2, cf_cdb.dropna(subset=['final_diagnosis'], how='all'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(comp_miss_fmp2_full[comp_miss_fmp2_full['name'].isin(comp_eqs['name'].unique())]['name'].unique()))\n",
    "print(len(comp_miss_fmp2_full[comp_miss_fmp2_full['name'].isin(comp_diffs2['name'].unique())]['name']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(comp_miss_fmp2_full[comp_miss_fmp2_full['name'].isin(comp_eqs['name'].unique())]['name'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "comp_miss_fmp2_full[comp_miss_fmp2_full['name'].isin(comp_diffs2['name'].unique())]['name']"
   ]
  }
 ],
 "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": 1
}