notebooks/query-annotations_medcat.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ES connector config\n",
    "es_user = 'admin'\n",
    "es_pass = 'admin'\n",
    "es_host = 'elasticsearch-1'\n",
    "es_port = 9200\n",
    "auth=(es_user, es_pass)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# samples postgres connector config\n",
    "pg_user = 'test'\n",
    "pg_pass = 'test'\n",
    "pg_host = 'samples-db'\n",
    "pg_port = 5432\n",
    "pg_db_name = \"db_samples\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Init and import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "# data wrangling + vis\n",
    "import pandas as pd\n",
    "import pandas.io.sql as psql\n",
    "from matplotlib import pyplot as plt\n",
    "import ssl\n",
    "# databases\n",
    "import elasticsearch\n",
    "import elasticsearch.helpers\n",
    "from elasticsearch.connection import create_ssl_context\n",
    "\n",
    "# postgres\n",
    "import psycopg2 as pg\n",
    "\n",
    "# ms sql\n",
    "import pyodbc\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Init connectors (Elasticsearch version)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating ElasticSearch connector\n",
    "#\n",
    "\n",
    "es = elasticsearch.Elasticsearch( hosts=[ { 'port': 9200, 'host': es_host } ], ssl_context=None, verify_certs=False, http_auth=auth , timeout=600, scroll='2m')\n",
    "\n",
    "\n",
    "# check status\n",
    "print('checking ES connection ...')\n",
    "if not es.ping():\n",
    "    raise Exception(\"Connection failed\")\n",
    "print('connected to ES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "connecting to PostgreSQL ...\n",
      "connected to PostgreSQL\n"
     ]
    }
   ],
   "source": [
    "# PostgreSQL connector\n",
    "#\n",
    "print('connecting to PostgreSQL ...')\n",
    "pg_conn = pg.connect(database=pg_db_name,user=pg_user, password=pg_pass, host=pg_host, port=pg_port)\n",
    "\n",
    "print('connected to PostgreSQL')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "def print_plot(cui_occ):\n",
    "    cui_names = []\n",
    "    cui_counts = []\n",
    "\n",
    "    for cui, cnt in sorted(cui_occ.items(), key=lambda kv: kv[1], reverse=True):\n",
    "        cui_names.append(cui)\n",
    "        cui_counts.append(cnt)\n",
    "\n",
    "    # display the results\n",
    "    plt.figure(figsize=(25,8))\n",
    "\n",
    "    plt.bar(range(len(cui_names)), list(cui_counts), align='center')\n",
    "    plt.xticks(range(len(cui_occ)), list(cui_names), rotation=90)\n",
    "\n",
    "    plt.tick_params(axis='x', which='major', labelsize=20)\n",
    "    plt.tick_params(axis='y', which='major', labelsize=20)\n",
    "\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ElasticSearch"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Direct data manipulations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'value': 45, 'relation': 'eq'}\n"
     ]
    }
   ],
   "source": [
    "# example 1:\n",
    "# retrieve the number of documents satisfying the query criteria\n",
    "#\n",
    "# an example query -- search for keyword 'cancer' in documents\n",
    "query_body_text = {\n",
    "    \"query\": {\n",
    "        \"match\": {\n",
    "            \"document\": \"cancer\"}\n",
    "    }\n",
    "}\n",
    "index_to_query_text = 'medical_reports_text'\n",
    "\n",
    "documents = es.search(index=index_to_query_text, body=query_body_text)\n",
    "\n",
    "print(documents['hits']['total'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAABcUAAAHECAYAAAD4REKjAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAAbhklEQVR4nO3dbYxmd3nf8d8FC0YmeI3KuI2w6rUdY6S2UtSOeLAlY2zVsloBRpjWaUsT6oIgVjAPQokCSkIbtyKtCjWCJCYQICQxDQh4Q5CrGMcSBqmzKkJReTReSjGqFpwYamMX46sv5p5q2ezszu7s3Luz1+cjjf7e+3/u65zjl18dnbu6OwAAAAAAMMETTvUFAAAAAADAsojiAAAAAACMIYoDAAAAADCGKA4AAAAAwBiiOAAAAAAAY+w51RewmzzjGc/offv2nerLAAAAAADgKPbv3//d7l450p4ofhz27duXtbW1U30ZAAAAAAAcRVV9c7M9r08BAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYQxQHAAAAAGAMURwAAAAAgDFEcQAAAAAAxhDFAQAAAAAYY6lRvKrOr6r3V9X9VfVoVR2oqndW1dOXOaeq3ldVvfj7mRO7GwAAAAAAdps9yzpRVV2c5J4k5yX5ZJIvJ3lOkpuTXFtVl3f393Z6TlW9KMm/SvJ/kvzUtm4KAAAAAIBdZZlPir8n6yH7dd19XXf/SndfleQdSS5NcstOz6mqlSTvTfKRJPtP/FYAAAAAANiNlhLFq+qiJNckOZDk3Ydt/3qSh5K8oqqeusNzblusN2312gEAAAAAOHMs60nxqxbrHd39+KEb3f2DJJ9NcnaS5+3UnKr6hSTXJXnNVl7TAgAAAADAmWdZUfzSxfrVTfa/tliftRNzquqCJP85yYe7+xPHOAcAAAAAAGeoZUXxvYv1wU32Nz4/92TPqaonJPlg1n9Y83XHmP/XVNWrq2qtqtYOHjx4vF8HAAAAAOA0sswf2jyaWqy9A3PekOQFSV7V3X95vAO7+7buXu3u1ZWVlW1eHgAAAAAAp9KyovjGE9x7N9k/57DjTsqcqrokyS1Jfr+7P7WF6wQAAAAA4Ay2rCj+lcW62TvDL1msm70r/ETn/J0kZyV5ZVX1oX9Zf3o8Sb62+Oy6Y5wbAAAAAIBdbs+SzvOZxXpNVT2hux/f2KiqpyW5PMkPk3z+JM85kOR9m8z6x0n+VpI/SfL9xbEAAAAAAJzBlhLFu/veqrojyTVJbkryrkO235bkqUl+t7sfSpKqelKSi5P8qLvvPdE53f2FJP/6SNdUVXdlPYr/and//WTcJwAAAAAAp7dlPSmeJL+Y5J4kt1bV1Um+lOS5SV6Y9dedvOWQY5+52P9mkn3bmAMAAAAAAP/fst4pnsUT36tJPpD1iP2mrD8NfmuS53f395Y5BwAAAACAeZb5pHi6+1tJXrmF4w4kqe3OOcY5rtzO9wEAAAAA2H2W9qQ4AAAAAACcaqI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMMZSo3hVnV9V76+q+6vq0ao6UFXvrKqn79Scqrqkqn65qu6sqm9V1f+tqv9dVZ+sqheevLsDAAAAAOB0t2dZJ6qqi5Pck+S8JJ9M8uUkz0lyc5Jrq+ry7v7eDsz5t0n+aZL/keRTSR5IcmmSFyd5cVXd3N23npy7BAAAAADgdLa0KJ7kPVkP2a/r7ndtfFhV/ynJG5LckuQ1OzDn00ne3t3//dAhVfWCJP81yX+oqj/p7u+c0F0BAAAAALBrVHfv/EmqLkpyb5IDSS7u7scP2Xtaku8kqSTndfdDOz3nkO/ckeQfJrm+uz92rONXV1d7bW3tWIcBAAAAAHAKVdX+7l490t6y3il+1WK949CQnSTd/YMkn01ydpLnLWnOhh8t1se2eDwAAAAAALvYsqL4pYv1q5vsf22xPmtJc1JVFyS5OsnDSe4+1vEAAAAAAOx+y4riexfrg5vsb3x+7jLmVNVZSf4wyVlJfqO7//Iox766qtaqau3gwYPHuDwAAAAAAE5ny4rix1KLdbsvOD/mnKp6YpI/SHJ5ko8k+Y9HG9jdt3X3anevrqysbPPyAAAAAAA4lZYVxTee4N67yf45hx23I3MWQfzDSV6e5L8k+Re9jF8aBQAAAADgtLCsKP6VxbrZu74vWaybvSt823Oqak+SP05yQ5I/SvLPutsPbAIAAAAADLKsKP6ZxXpNVf3EOavqaVl/lckPk3x+J+ZU1ZOTfDTrT4h/KMkruvvHJ3AfAAAAAADsYkuJ4t19b5I7kuxLctNh229L8tQkH+ruh5Kkqp5UVc+uqou3M2cx66wkH0/ykiTvS/LK7n785NwZAAAAAAC7yZ4lnusXk9yT5NaqujrJl5I8N8kLs/66k7cccuwzF/vfzHoAP9E5SfI7Sf5Rku8m+XaSX6uqww7JXd1914nfGgAAAAAAu8HSonh331tVq0n+TZJrsx6qv5Pk1iRv6+4HdmjOhYv1GUl+7Sij79rirQAAAAAAsEst80nxdPe3krxyC8cdSPLXHuc+3jmLY6/c4uUBAAAAAHCGW9YPbQIAAAAAwCknigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjiOIAAAAAAIwhigMAAAAAMIYoDgAAAADAGKI4AAAAAABjLDWKV9X5VfX+qrq/qh6tqgNV9c6qevpOz6mqy6rqU1X1QFU9XFVfrKrXV9UTt39nAAAAAADsBnuWdaKqujjJPUnOS/LJJF9O8pwkNye5tqou7+7v7cScqnpJko8leSTJR5I8kORFSd6R5PIkLz8Z9wgAAAAAwOltmU+KvyfrIft13X1dd/9Kd1+V9TB9aZJbdmJOVZ2T5L1Jfpzkyu6+sbvfnORnk3wuyfVVdcNJuD8AAAAAAE5zS4niVXVRkmuSHEjy7sO2fz3JQ0leUVVP3YE51ydZSXJ7d69tfNjdjyR56+Kfrz2O2wEAAAAAYJda1pPiVy3WO7r78UM3uvsHST6b5Owkz9uBORvf+fQR5t2d5OEkl1XVWce6CQAAAAAAdrdlRfFLF+tXN9n/2mJ91g7M2fQ73f1Ykvuy/m71i45xbgAAAAAAdrllRfG9i/XBTfY3Pj93B+Zs69xV9eqqWquqtYMHDx7j8gAAAAAAOJ0t84c2j6YWa5+COUf9Tnff1t2r3b26srKyrYsDAAAAAODUWlYU33gae+8m++ccdtzJnHOyzg0AAAAAwC63rCj+lcW62TvDL1msm70rfDtzNv1OVe1JcmGSx5J84xjnBgAAAABgl1tWFP/MYr2mqn7inFX1tCSXJ/lhks/vwJw7F+u1R5h3RZKzk9zT3Y8e6yYAAAAAANjdlhLFu/veJHck2ZfkpsO235bkqUk+1N0PJUlVPamqnl1VF29nzsJHk3w3yQ1VtbrxYVU9JclvLv752yd8cwAAAAAA7BrVvd3fttziidYD9z1JzkvyySRfSvLcJC/M+utOLuvu7y2O3ZfkviTf7O59JzrnkO9cl/U4/kiS25M8kOTFSS5dfP5Pegv/I1ZXV3ttbe247x0AAAAAgOWpqv3dvXqkvWW9PmXjKe/VJB/IesR+U5KLk9ya5PmHh+yTOae7P5HkBUnuTvKyJL+U5EdJ3pjkhq0EcQAAAAAAdr+lPSl+JvCkOAAAAADA6e+0eFIcAAAAAABONVEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGGNpUbyqLquqT1XVA1X1cFV9sapeX1VP3MlZVXV5Vf1WVf23qjpYVY9W1X1V9XtV9TMn5+4AAAAAANgNlhLFq+olSe5OckWSjyd5d5InJ3lHktt3eNbHkrwpySNJ/jDJu5Lcn+TGJF+oqucf/x0BAAAAALAbVXfv7Amqzkny9SR7k1ze3WuLz5+S5M4kz0/yc919zDh+IrOq6peT/EF333/YrF9NckuSv+juv7eVe1ldXe21tbWtHAoAAAAAwClSVfu7e/VIe8t4Uvz6JCtJbt+I2EnS3Y8keevin6/dqVnd/fbDg/jC25P8MMnfraq/scXzAwAAAACwiy0jil+1WD99hL27kzyc5LKqOmvJszrJY4v//vEWjgcAAAAAYJdbRhS/dLF+9fCN7n4syX1J9iS5aMmzXp7kaUk+391/tYXjAQAAAADY5ZYRxfcu1gc32d/4/NxlzaqqC7P+g5uPZf1HOI927Kuraq2q1g4ePLiFSwQAAAAA4HS1pSheVQeqqo/j78PHcQ21WE/GL34ec1ZVnZfkT7P+bvKbu/ueow3s7tu6e7W7V1dWVk7CJQIAAAAAcKrs2eJx9yZ55DjmHvrDlhtPb+890oFJzjnsuKPZ1qxFEL8z669hubm737OFcwIAAAAAcIbYUhTv7qu3cY6vJFlN8qwk+w/dqKo9SS7M+mtMvrGTs6rqp5P8WZJnJ7lJEAcAAAAAmGcZ7xS/c7Fee4S9K5KcneSe7n50p2ZV1flJ/jzrQfw1gjgAAAAAwEzLiOIfTfLdJDdU1erGh1X1lCS/ufjnbx/6haraW1XPXjzdvd1ZfzvrQfziJDd2923bvyUAAAAAAHajrb5T/IR19/er6lVZD9p3VdXtSR5I8uKsv9v7o0k+ctjXXprk95N8MMkvbHPWnyfZl/XXrVxQVb9xhMv8QHcfOOGbBAAAAABgV9jxKJ4k3f2JqnpBkrckeVmSpyT5epI3Jrm1u3sHZ+1brP9g8XckdyU5sNVrAAAAAABgd6rj6NHjra6u9tra2qm+DAAAAAAAjqKq9nf36pH2lvFOcQAAAAAAOC2I4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYojgAAAAAAGOI4gAAAAAAjCGKAwAAAAAwhigOAAAAAMAYS4viVXVZVX2qqh6oqoer6otV9fqqeuIyZ1XVWVX1F1XVVfW/TuxuAAAAAADYjZYSxavqJUnuTnJFko8neXeSJyd5R5Lblzzr3yW54HjOCQAAAADAmWHHo3hVnZPkvUl+nOTK7r6xu9+c5GeTfC7J9VV1wzJmVdWVSd6Q5M3buCUAAAAAAHapZTwpfn2SlSS3d/faxofd/UiSty7++dqdnrUI6h9I8mfd/TvHcwMAAAAAAJwZlhHFr1qsnz7C3t1JHk5yWVWdtcOzbk3y9CQ3buE8AAAAAACcgZYRxS9drF89fKO7H0tyX5I9SS7aqVlV9dIkP5/kjd39P7d85QAAAAAAnFGWEcX3LtYHN9nf+PzcnZhVVX8zye8m+dPuft8WzvETqurVVbVWVWsHDx483q8DAAAAAHAa2VIUr6oDVdXH8ffh47iGWqx9/Je/pVnvTfKkJK86kYHdfVt3r3b36srKynavDwAAAACAU2jPFo+7N8kjxzH3/kP+e+Pp7b1HOjDJOYcddzTHNauq/mWSFyX5+e7+9hbmAwAAAABwBttSFO/uq7dxjq8kWU3yrCT7D92oqj1JLkzyWJJv7MCsv79YP1hVHzzCvGdW1cZT5U/v7r/awjUAAAAAALBLbfVJ8e24M8k/T3Jtkj8+bO+KJGcnubu7H92BWZ9L8lObzLoxycOHzNnK+QEAAAAA2MWq+2S8yvsoJ6g6J+uvXzknyeXdvbb4/ClZj9zPT/Jz3X37Id/Zm+SnkzzY3d/ZzqyjXFcn+XZ3n7/Ve1ldXe21tbWtHg4AAAAAwClQVfu7e/VIe1v6oc3t6O7vZ/1HLp+Y5K6q+r2q+q0kX8h6xP5oko8c9rWXJvlSkn9/EmYBAAAAAECSJUTxJOnuTyR5QZK7k7wsyS8l+VGSNya5oY/jcfWTOQsAAAAAgFl2/PUpZxKvTwEAAAAAOP2d0tenAAAAAADA6UIUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgDFEcAAAAAIAxRHEAAAAAAMYQxQEAAAAAGEMUBwAAAABgjOruU30Nu0ZVHUzyzVN9HQAAAAAAHNUF3b1ypA1RHAAAAACAMbw+BQAAAACAMURxAAAAAADGEMUBAAAAABhDFAcAAAAAYAxRHAAAAACAMURxAAAAAADGEMUBAAAAABhDFAcAAAAAYAxRHAAAAACAMf4fc/9f8iJs7JQAAAAASUVORK5CYII=",
      "text/plain": [
       "<Figure size 1800x576 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# example 2:\n",
    "# - retrieve all the documents matching the query criteria \n",
    "# - calculate the CUI codes occurencies\n",
    "# - visualize the results\n",
    "\n",
    "query_body_medcat = {\n",
    "    \"query\": {\n",
    "        \"match\": {\n",
    "            \"nlp.source_value\": \"skin\"}\n",
    "    }\n",
    "}\n",
    "index_to_query_medcat = \"medical_reports_anns_medcat_medmen_cancer\"\n",
    "\n",
    "# query the elasticsearch\n",
    "results = elasticsearch.helpers.scan(es, index=index_to_query_medcat, query=query_body_medcat)\n",
    "\n",
    "# calculate the occurencies\n",
    "bio_cui_occ = {}\n",
    "bio_tui_arr = {}\n",
    "for item in results:\n",
    "    cui = item['_source']['nlp.pretty_name']\n",
    "    if cui not in bio_cui_occ: \n",
    "        bio_cui_occ[cui] = 1\n",
    "    else:\n",
    "        bio_cui_occ[cui] += 1\n",
    "    \n",
    "    tui = item['_source']['nlp.tui']\n",
    "    if tui not in bio_tui_arr:\n",
    "         bio_tui_arr[tui] = set()\n",
    "    bio_tui_arr[tui].add(cui)\n",
    "    \n",
    "\n",
    "print_plot(bio_cui_occ)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "# example 2:\n",
    "# - retrieve all the documents matching the query criteria \n",
    "# - calculate the CUI codes occurencies\n",
    "# - visualize the results\n",
    "\n",
    "def get_tui_cui_occ(es, index_name, cui_field_name, tui_field_name, query_field_name, query_phrase):\n",
    "    # query the elasticsearch\n",
    "    query = {\n",
    "        \"query\": {\n",
    "            \"match\": {\n",
    "               query_field_name : query_phrase}\n",
    "        }\n",
    "    }\n",
    "    results = elasticsearch.helpers.scan(es, index=index_name, query=query)\n",
    "\n",
    "    # calculate the occurencies\n",
    "    cui_occ = {}\n",
    "    tui_arr = {}\n",
    "    for item in results:\n",
    "        #cui = (item['_source']['nlp.inst'], item['_source']['nlp.text'])\n",
    "        cui = item['_source'][cui_field_name]\n",
    "        if cui not in cui_occ: \n",
    "            cui_occ[cui] = 1\n",
    "        else:\n",
    "            cui_occ[cui] += 1\n",
    "\n",
    "        tui = item['_source'][tui_field_name]\n",
    "        if tui not in tui_arr:\n",
    "             tui_arr[tui] = set()\n",
    "        tui_arr[tui].add(cui)\n",
    "    \n",
    "    return (cui_occ, tui_arr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "search() got an unexpected keyword argument 'query'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-74-5b46abc3ef76>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m#bioyodie_results = get_tui_cui_occ(es, 'nifi_is_doc_gp_refletters_bioyodie', 'nlp.inst', 'nlp.TUI', 'nlp.text', 'cancer')\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mmedcat_results\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_tui_cui_occ\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mes\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'medical_reports_anns_medcat_medmen_cancer'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'nlp.cui'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'nlp.tui'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'nlp.source_value'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'skin'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;31m#print_plot(bioyodie_results[0])\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mprint_plot\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmedcat_results\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-73-7015b304103c>\u001b[0m in \u001b[0;36mget_tui_cui_occ\u001b[0;34m(es, index_name, cui_field_name, tui_field_name, query_field_name, query_phrase)\u001b[0m\n\u001b[1;32m     17\u001b[0m     \u001b[0mcui_occ\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     18\u001b[0m     \u001b[0mtui_arr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 19\u001b[0;31m     \u001b[0;32mfor\u001b[0m \u001b[0mitem\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mresults\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     20\u001b[0m         \u001b[0;31m#cui = (item['_source']['nlp.inst'], item['_source']['nlp.text'])\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     21\u001b[0m         \u001b[0mcui\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mitem\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'_source'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mcui_field_name\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/elasticsearch/helpers/actions.py\u001b[0m in \u001b[0;36mscan\u001b[0;34m(client, query, scroll, raise_on_error, preserve_order, size, request_timeout, clear_scroll, scroll_kwargs, **kwargs)\u001b[0m\n\u001b[1;32m    570\u001b[0m         \u001b[0mmaintained\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mscrolled\u001b[0m \u001b[0msearch\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    571\u001b[0m     \u001b[0;34m:\u001b[0m\u001b[0marg\u001b[0m \u001b[0mscan_kwargs\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0madditional\u001b[0m \u001b[0mkwargs\u001b[0m \u001b[0mto\u001b[0m \u001b[0mbe\u001b[0m \u001b[0mpassed\u001b[0m \u001b[0mto\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 572\u001b[0;31m         \u001b[0;34m:\u001b[0m\u001b[0mfunc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;34m~\u001b[0m\u001b[0melasticsearch\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhelpers\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mscan\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    573\u001b[0m     \u001b[0;34m:\u001b[0m\u001b[0marg\u001b[0m \u001b[0mbulk_kwargs\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0madditional\u001b[0m \u001b[0mkwargs\u001b[0m \u001b[0mto\u001b[0m \u001b[0mbe\u001b[0m \u001b[0mpassed\u001b[0m \u001b[0mto\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    574\u001b[0m         \u001b[0;34m:\u001b[0m\u001b[0mfunc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;34m~\u001b[0m\u001b[0melasticsearch\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhelpers\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbulk\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/opensearchpy/client/utils.py\u001b[0m in \u001b[0;36m_wrapped\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    175\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0mp\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    176\u001b[0m                     \u001b[0mparams\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mp\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mp\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 177\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mheaders\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    178\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    179\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0m_wrapped\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: search() got an unexpected keyword argument 'query'"
     ]
    }
   ],
   "source": [
    "#bioyodie_results = get_tui_cui_occ(es, 'nifi_is_doc_gp_refletters_bioyodie', 'nlp.inst', 'nlp.TUI', 'nlp.text', 'cancer')\n",
    "medcat_results = get_tui_cui_occ(es, 'medical_reports_anns_medcat_medmen_cancer', 'nlp.cui', 'nlp.tui', 'nlp.source_value', 'skin')\n",
    "\n",
    "#print_plot(bioyodie_results[0])\n",
    "print_plot(medcat_results[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>meta.dct</th>\n",
       "      <th>meta.docid</th>\n",
       "      <th>meta.filename</th>\n",
       "      <th>meta.sampleid</th>\n",
       "      <th>meta.typeid</th>\n",
       "      <th>nlp.acc</th>\n",
       "      <th>nlp.cui</th>\n",
       "      <th>nlp.end</th>\n",
       "      <th>nlp.id</th>\n",
       "      <th>nlp.info</th>\n",
       "      <th>nlp.meta_anns</th>\n",
       "      <th>nlp.pretty_name</th>\n",
       "      <th>nlp.source_value</th>\n",
       "      <th>nlp.start</th>\n",
       "      <th>nlp.tui</th>\n",
       "      <th>nlp.type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-07-13T08:57:24.401</td>\n",
       "      <td>95</td>\n",
       "      <td>__tmp/txt/mtsamples-type-98-sample-2057.txt</td>\n",
       "      <td>2057</td>\n",
       "      <td>98</td>\n",
       "      <td>1</td>\n",
       "      <td>C0007114</td>\n",
       "      <td>1603</td>\n",
       "      <td>56</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>skin cancer</td>\n",
       "      <td>skin cancers</td>\n",
       "      <td>1591</td>\n",
       "      <td>T191</td>\n",
       "      <td>Neoplastic Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-04-10T20:02:17.349</td>\n",
       "      <td>82</td>\n",
       "      <td>__tmp/txt/mtsamples-type-98-sample-1934.txt</td>\n",
       "      <td>1934</td>\n",
       "      <td>98</td>\n",
       "      <td>1</td>\n",
       "      <td>C0007114</td>\n",
       "      <td>4709</td>\n",
       "      <td>135</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>skin cancer</td>\n",
       "      <td>skin cancers</td>\n",
       "      <td>4697</td>\n",
       "      <td>T191</td>\n",
       "      <td>Neoplastic Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-05-14T04:02:33.197</td>\n",
       "      <td>171</td>\n",
       "      <td>__tmp/txt/mtsamples-type-98-sample-2783.txt</td>\n",
       "      <td>2783</td>\n",
       "      <td>98</td>\n",
       "      <td>1</td>\n",
       "      <td>C0037297</td>\n",
       "      <td>866</td>\n",
       "      <td>27</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>skin grafting</td>\n",
       "      <td>skin grafting</td>\n",
       "      <td>853</td>\n",
       "      <td>T061</td>\n",
       "      <td>Therapeutic or Preventive Procedure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-10-18T01:36:11.854</td>\n",
       "      <td>184</td>\n",
       "      <td>__tmp/txt/mtsamples-type-98-sample-381.txt</td>\n",
       "      <td>381</td>\n",
       "      <td>98</td>\n",
       "      <td>1</td>\n",
       "      <td>C0037284</td>\n",
       "      <td>4870</td>\n",
       "      <td>128</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>skin lesions</td>\n",
       "      <td>skin lesions</td>\n",
       "      <td>4858</td>\n",
       "      <td>T047</td>\n",
       "      <td>Disease or Syndrome</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-12-04T04:18:07.580</td>\n",
       "      <td>125</td>\n",
       "      <td>__tmp/txt/mtsamples-type-98-sample-2310.txt</td>\n",
       "      <td>2310</td>\n",
       "      <td>98</td>\n",
       "      <td>1</td>\n",
       "      <td>C0037284</td>\n",
       "      <td>2908</td>\n",
       "      <td>104</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>skin lesions</td>\n",
       "      <td>skin lesions</td>\n",
       "      <td>2896</td>\n",
       "      <td>T047</td>\n",
       "      <td>Disease or Syndrome</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  meta.dct  meta.docid  \\\n",
       "0  2020-07-13T08:57:24.401          95   \n",
       "1  2020-04-10T20:02:17.349          82   \n",
       "2  2020-05-14T04:02:33.197         171   \n",
       "3  2019-10-18T01:36:11.854         184   \n",
       "4  2019-12-04T04:18:07.580         125   \n",
       "\n",
       "                                 meta.filename  meta.sampleid  meta.typeid  \\\n",
       "0  __tmp/txt/mtsamples-type-98-sample-2057.txt           2057           98   \n",
       "1  __tmp/txt/mtsamples-type-98-sample-1934.txt           1934           98   \n",
       "2  __tmp/txt/mtsamples-type-98-sample-2783.txt           2783           98   \n",
       "3   __tmp/txt/mtsamples-type-98-sample-381.txt            381           98   \n",
       "4  __tmp/txt/mtsamples-type-98-sample-2310.txt           2310           98   \n",
       "\n",
       "  nlp.acc   nlp.cui  nlp.end nlp.id nlp.info nlp.meta_anns nlp.pretty_name  \\\n",
       "0       1  C0007114     1603     56       {}            {}     skin cancer   \n",
       "1       1  C0007114     4709    135       {}            {}     skin cancer   \n",
       "2       1  C0037297      866     27       {}            {}   skin grafting   \n",
       "3       1  C0037284     4870    128       {}            {}    skin lesions   \n",
       "4       1  C0037284     2908    104       {}            {}    skin lesions   \n",
       "\n",
       "  nlp.source_value  nlp.start nlp.tui                             nlp.type  \n",
       "0     skin cancers       1591    T191                   Neoplastic Process  \n",
       "1     skin cancers       4697    T191                   Neoplastic Process  \n",
       "2    skin grafting        853    T061  Therapeutic or Preventive Procedure  \n",
       "3     skin lesions       4858    T047                  Disease or Syndrome  \n",
       "4     skin lesions       2896    T047                  Disease or Syndrome  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query template\n",
    "#\n",
    "query_body_medcat = {\n",
    "    \"query\": {\n",
    "        \"match\": {\n",
    "            \"nlp.source_value\": \"skin\"}\n",
    "    }\n",
    "}\n",
    "\n",
    "index_to_query_medcat = 'medical_reports_anns_medcat_medmen_cancer'\n",
    "\n",
    "\n",
    "# need to re-query ES to fetch the results\n",
    "#\n",
    "results = elasticsearch.helpers.scan(es, index=index_to_query_medcat, query=query_body_medcat)\n",
    "\n",
    "\n",
    "# create a data frame from the results\n",
    "#\n",
    "es_df = pd.DataFrame.from_dict([item['_source'] for item in results])\n",
    "es_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   meta.docid nlp.source_value   nlp.cui nlp.tui\n",
      "0          95     skin cancers  C0007114    T191\n",
      "1          82     skin cancers  C0007114    T191\n"
     ]
    }
   ],
   "source": [
    "# select only a subset of columns and perform filtering based on TUI and CUI\n",
    "es_df_sub = es_df.filter(items=['meta.docid', 'nlp.source_value', 'nlp.cui', 'nlp.tui'])\n",
    "rows = es_df_sub.loc[(es_df_sub['nlp.tui'] == 'T191')]\n",
    "\n",
    "print(rows[0:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PostgreSQL (samples-db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>docid</th>\n",
       "      <th>sampleid</th>\n",
       "      <th>dct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1041</td>\n",
       "      <td>2019-10-30 16:53:58.476127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1042</td>\n",
       "      <td>2019-10-07 23:38:43.876148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1054</td>\n",
       "      <td>2019-12-02 17:05:18.100627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1068</td>\n",
       "      <td>2020-08-03 03:19:12.581134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1069</td>\n",
       "      <td>2020-03-14 06:47:10.383254</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   docid  sampleid                        dct\n",
       "0      1      1041 2019-10-30 16:53:58.476127\n",
       "1      2      1042 2019-10-07 23:38:43.876148\n",
       "2      3      1054 2019-12-02 17:05:18.100627\n",
       "3      4      1068 2020-08-03 03:19:12.581134\n",
       "4      5      1069 2020-03-14 06:47:10.383254"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example: read the DB and create pandas data frame\n",
    "#\n",
    "pg_df = pd.read_sql('SELECT docid, sampleid, dct FROM medical_reports_text', pg_conn)\n",
    "pg_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}