presentation_omop.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "exu0YsnS6IC-"
   },
   "source": [
    "# Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "83o2DmYb6IDC"
   },
   "source": [
    "![logo_OHDSI](img/logo_OHDSI.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "PvSpImnW6IDE"
   },
   "source": [
    "## Definition OMOP - CDM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1K3IAR4J6IDG"
   },
   "source": [
    "OMOP = Observational Medical Outcomes Partnership  \n",
    "CDM = Common Data Model\n",
    "\n",
    "Open Source Community for \"Real World Data\" Analysis\n",
    "\n",
    "Deux niveaux de standardisation :\n",
    "- Modele relationnel : SQL\n",
    "- Mapping concepts locaux => concepts standard (https://github.com/MIT-LCP/mimic-omop/tree/master/extras/concept)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "8lVn18bH6IDK"
   },
   "source": [
    "## Site internet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "D-wbv9N06IDM"
   },
   "source": [
    "- https://github.com/OHDSI/CommonDataModel/wiki\n",
    "- https://github.com/MIT-LCP/mimic-omop"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "eyoz_00p6IDO"
   },
   "source": [
    "## Stucture globale"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "BSBVHaYx6IDQ"
   },
   "source": [
    "<img src=\"img/structure_OMOP_1.png\" alt=\"structure_OMOP\" width=\"700\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "OzOkemKq6IDS"
   },
   "source": [
    "### Table Concept"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Q2Mqsy006IDW"
   },
   "source": [
    "<img src=\"img/concept_table.png\" alt=\"concept_table\" width=\"600\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Fz0ERAyT6IDY"
   },
   "source": [
    "## Outils"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "BUtAHy_j6IDa"
   },
   "source": [
    "https://www.ohdsi.org/analytic-tools/\n",
    "- Achilles : outils de visualisation\n",
    "- Atlas : vocabulary search, cohort definition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "e3V56moO6IDa"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "imkVBRQu6IDg"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "fJThUCXI6IDk"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "LVTfFWQw6IDq"
   },
   "source": [
    "# Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "l5i4nNr46IDq"
   },
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import sys\n",
    "import pprint\n",
    "import pandas as pd\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "-3J0ak-M6IDy"
   },
   "outputs": [],
   "source": [
    "# creation DB\n",
    "user = 'apa'\n",
    "dbname = 'mimic'\n",
    "schema_name = 'omop'\n",
    "host = 'localhost'\n",
    "\n",
    "# Connect to the database\n",
    "con = psycopg2.connect(dbname=dbname, user=user, host=host,)\n",
    "\n",
    "query_schema = 'set search_path to ' + schema_name + ';'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "RQ9c4cnz6ID4"
   },
   "source": [
    "## Nb patients = PERSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "2WMMLc_k6ID6",
    "outputId": "9c09ec82-bb30-4d16-f321-21b81c3fc0c6"
   },
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>46520</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0  46520"
      ]
     },
     "execution_count": 4,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT count(*) \n",
    "FROM person\n",
    "\"\"\"\n",
    "nb_patients = pd.read_sql_query(query, con)\n",
    "nb_patients"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "fCEGlTi-6IEE"
   },
   "source": [
    "## Nb admissions = VISIT_OCCURRENCE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "3A36zNVD6IEG",
    "outputId": "791a1827-398c-4603-c9d1-19365f8569ae"
   },
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>58976</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0  58976"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT count(*) \n",
    "FROM visit_occurrence\n",
    "\"\"\"\n",
    "nb_adm = pd.read_sql_query(query, con)\n",
    "nb_adm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "X5tQ_65Q6IEO"
   },
   "source": [
    "## Nb de patients en ICU = VISIT_DETAIL\n",
    "- rmq : intéret de la colonne *_type_concept_id, chaque table la possède. Permet de filtrer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "-uzJq_6u6IEQ",
    "outputId": "25adea36-97e8-4576-8cb2-e48ee0aeaff8"
   },
   "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>num_totalstays_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71575</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_totalstays_count\n",
       "0                 71575"
      ]
     },
     "execution_count": 16,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT COUNT(distinct visit_detail_id) AS num_totalstays_count\n",
    "FROM visit_detail\n",
    "WHERE TRUE\n",
    "AND visit_detail_concept_id = 581382                -- concept.concept_name = 'Inpatient Intensive Care Facility'\n",
    "AND visit_detail_type_concept_id = 2000000006       -- concept.concept_name = 'Ward and physical location'\n",
    "\"\"\"\n",
    "nb_icu = pd.read_sql_query(query, con)\n",
    "nb_icu"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "_y57etnZ6IEa"
   },
   "source": [
    "## CONCEPT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "22HiMcTp6IEa",
    "outputId": "ff5296a0-9b77-4aab-ad3b-39f74b0bdb52"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                              0\n",
      "concept_id                              2103359\n",
      "concept_name           Excision of rib, partial\n",
      "domain_id                             Procedure\n",
      "vocabulary_id                              CPT4\n",
      "concept_class_id                           CPT4\n",
      "standard_concept                              S\n",
      "concept_code                              21600\n",
      "valid_start_date                     1970-01-01\n",
      "valid_end_date                       2099-12-31\n",
      "invalid_reason                             None\n",
      "search_concept    'excis':1 'partial':4 'rib':3\n"
     ]
    }
   ],
   "source": [
    "# si standard_concept = 'S' \n",
    "# => le concept est standard donc correspond  a la colonne *_concept_id et non *_source_concept_id\n",
    "query = query_schema + \"\"\"\n",
    "SELECT *\n",
    "FROM concept\n",
    "LIMIT 1\n",
    "\"\"\"\n",
    "concept_table = pd.read_sql_query(query, con)\n",
    "print(concept_table.T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6KkmNgiS6IEg"
   },
   "source": [
    "### Ex CONCEPT et DEATH table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "9lHT6JSa6IEi",
    "outputId": "13f09cee-ce17-428b-b819-14c53f74f81d"
   },
   "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>concept_name</th>\n",
       "      <th>concept_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EHR record patient status \"Deceased\"</td>\n",
       "      <td>38003569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US Social Security Death Master File record</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  concept_name  concept_id\n",
       "0         EHR record patient status \"Deceased\"    38003569\n",
       "1  US Social Security Death Master File record         261"
      ]
     },
     "execution_count": 32,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT distinct c.concept_name, d.death_type_concept_id  as concept_id\n",
    "FROM death d\n",
    "JOIN concept c ON d.death_type_concept_id = c.concept_id\n",
    "\"\"\"\n",
    "death = pd.read_sql_query(query, con)\n",
    "death"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "50TXtCdb6IEy"
   },
   "source": [
    "## Discharge from hospital (example de standardisation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "8WvxG2wZ6IE0",
    "outputId": "8e7243e9-b870-4543-b7f3-9194c0e59d5c"
   },
   "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>discharge_to_source_value</th>\n",
       "      <th>concept_name</th>\n",
       "      <th>concept_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>Home</td>\n",
       "      <td>8536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DISC-TRAN CANCER/CHLDRN H</td>\n",
       "      <td>Inpatient Hospital</td>\n",
       "      <td>8717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HOSPICE-MEDICAL FACILITY</td>\n",
       "      <td>Hospice</td>\n",
       "      <td>8546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LONG TERM CARE HOSPITAL</td>\n",
       "      <td>Inpatient Long-term Care</td>\n",
       "      <td>8970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HOME</td>\n",
       "      <td>Home</td>\n",
       "      <td>8536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ICF</td>\n",
       "      <td>Skilled Nursing Facility</td>\n",
       "      <td>8863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SHORT TERM HOSPITAL</td>\n",
       "      <td>Skilled Nursing Facility</td>\n",
       "      <td>8863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>REHAB/DISTINCT PART HOSP</td>\n",
       "      <td>Skilled Nursing Facility</td>\n",
       "      <td>8863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SNF</td>\n",
       "      <td>Skilled Nursing Facility</td>\n",
       "      <td>8863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Patient died</td>\n",
       "      <td>4216643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ORGAN DONOR</td>\n",
       "      <td>Organ donor</td>\n",
       "      <td>4022058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>HOME WITH HOME IV PROVIDR</td>\n",
       "      <td>Home</td>\n",
       "      <td>8536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ORGAN DONOR</td>\n",
       "      <td>Organ donor</td>\n",
       "      <td>4022058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>DISCH-TRAN TO PSYCH HOSP</td>\n",
       "      <td>Psychiatric Facility-Partial Hospitalization</td>\n",
       "      <td>8913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>SNF-MEDICAID ONLY CERTIF</td>\n",
       "      <td>Skilled Nursing Facility</td>\n",
       "      <td>8863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>HOSPICE-HOME</td>\n",
       "      <td>Hospice</td>\n",
       "      <td>8546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>LEFT AGAINST MEDICAL ADVI</td>\n",
       "      <td>Patient self-discharge against medical advice</td>\n",
       "      <td>4021968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ORGAN DONOR ACCOUNT</td>\n",
       "      <td>Organ donor</td>\n",
       "      <td>4022058</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    discharge_to_source_value                                   concept_name  \\\n",
       "0            HOME HEALTH CARE                                           Home   \n",
       "1   DISC-TRAN CANCER/CHLDRN H                             Inpatient Hospital   \n",
       "2    HOSPICE-MEDICAL FACILITY                                        Hospice   \n",
       "3     LONG TERM CARE HOSPITAL                       Inpatient Long-term Care   \n",
       "4                        HOME                                           Home   \n",
       "5                         ICF                       Skilled Nursing Facility   \n",
       "6         SHORT TERM HOSPITAL                       Skilled Nursing Facility   \n",
       "7    REHAB/DISTINCT PART HOSP                       Skilled Nursing Facility   \n",
       "8                         SNF                       Skilled Nursing Facility   \n",
       "9                DEAD/EXPIRED                                   Patient died   \n",
       "10                ORGAN DONOR                                    Organ donor   \n",
       "11  HOME WITH HOME IV PROVIDR                                           Home   \n",
       "12               ORGAN DONOR                                     Organ donor   \n",
       "13   DISCH-TRAN TO PSYCH HOSP   Psychiatric Facility-Partial Hospitalization   \n",
       "14   SNF-MEDICAID ONLY CERTIF                       Skilled Nursing Facility   \n",
       "15               HOSPICE-HOME                                        Hospice   \n",
       "16  LEFT AGAINST MEDICAL ADVI  Patient self-discharge against medical advice   \n",
       "17        ORGAN DONOR ACCOUNT                                    Organ donor   \n",
       "\n",
       "    concept_id  \n",
       "0         8536  \n",
       "1         8717  \n",
       "2         8546  \n",
       "3         8970  \n",
       "4         8536  \n",
       "5         8863  \n",
       "6         8863  \n",
       "7         8863  \n",
       "8         8863  \n",
       "9      4216643  \n",
       "10     4022058  \n",
       "11        8536  \n",
       "12     4022058  \n",
       "13        8913  \n",
       "14        8863  \n",
       "15        8546  \n",
       "16     4021968  \n",
       "17     4022058  "
      ]
     },
     "execution_count": 44,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Les items avec la meme signification ont le meme code (ex Home = 8536)\n",
    "query = query_schema + \"\"\"\n",
    "SELECT distinct v.discharge_to_source_value, c.concept_name, v.discharge_to_concept_id  as concept_id\n",
    "FROM visit_occurrence v\n",
    "JOIN concept c ON v.discharge_to_concept_id = c.concept_id\n",
    "\"\"\"\n",
    "discharge = pd.read_sql_query(query, con)\n",
    "discharge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "S6RBUad_6IFA"
   },
   "source": [
    "## Mort a l'hopital"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "XW4pT4pw6IFC",
    "outputId": "2591c608-776b-4c02-f985-339035ca37dd"
   },
   "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>dead_hospital_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5815</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dead_hospital_count\n",
       "0                 5815"
      ]
     },
     "execution_count": 43,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT count(distinct visit_occurrence_id) AS dead_hospital_count\n",
    "FROM visit_occurrence\n",
    "WHERE TRUE\n",
    "AND discharge_to_concept_id = 4216643                   -- concept.concept_name = 'Patient died'\n",
    "\"\"\"\n",
    "death_ICU = pd.read_sql_query(query, con)\n",
    "death_ICU"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "g6FrNqOR6IFQ"
   },
   "source": [
    "## MEASUREMENT\n",
    "- *_concept_id vs *_source_concept_id (*_concept_id à 0 si non mappé)\n",
    "- *_type_concept_id\n",
    "\n",
    "\n",
    "- Example de code dans le readme de chaque table SQL : https://github.com/MIT-LCP/mimic-omop/tree/master/etl/StandardizedClinicalDataTables/MEASUREMENT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "JSH9_VFe6IFU",
    "outputId": "478e63ff-76d2-4444-bd9d-b125564a66bd"
   },
   "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>column_name</th>\n",
       "      <th>data_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>measurement_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>person_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>measurement_concept_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>measurement_date</td>\n",
       "      <td>date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>measurement_datetime</td>\n",
       "      <td>timestamp without time zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>measurement_type_concept_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>operator_concept_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>value_as_number</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>value_as_concept_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>unit_concept_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>range_low</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>range_high</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>provider_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>visit_occurrence_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>visit_detail_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>measurement_source_value</td>\n",
       "      <td>text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>measurement_source_concept_id</td>\n",
       "      <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>unit_source_value</td>\n",
       "      <td>text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>value_source_value</td>\n",
       "      <td>text</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      column_name                    data_type\n",
       "0                  measurement_id                      integer\n",
       "1                       person_id                      integer\n",
       "2          measurement_concept_id                      integer\n",
       "3                measurement_date                         date\n",
       "4            measurement_datetime  timestamp without time zone\n",
       "5     measurement_type_concept_id                      integer\n",
       "6             operator_concept_id                      integer\n",
       "7                 value_as_number                      numeric\n",
       "8             value_as_concept_id                      integer\n",
       "9                 unit_concept_id                      integer\n",
       "10                      range_low                      numeric\n",
       "11                     range_high                      numeric\n",
       "12                    provider_id                      integer\n",
       "13            visit_occurrence_id                      integer\n",
       "14                visit_detail_id                      integer\n",
       "15       measurement_source_value                         text\n",
       "16  measurement_source_concept_id                      integer\n",
       "17              unit_source_value                         text\n",
       "18             value_source_value                         text"
      ]
     },
     "execution_count": 24,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## deux colonnes d'interet measurement_concept_id et measurement_source_concept_id\n",
    "query = query_schema + \"\"\"\n",
    "SELECT column_name, data_type \n",
    "FROM information_schema.columns \n",
    "WHERE table_name = 'measurement' \n",
    "ORDER BY ordinal_position\"\"\"\n",
    "meas = pd.read_sql_query(query, con)\n",
    "meas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "NeSD9AzK6IFc",
    "outputId": "abe10c4f-4e3f-4e89-ef7a-e93f8ca02f30"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                 0                    1\n",
      "measurement_id                          1293892407           1293894295\n",
      "person_id                                 62099621             62099621\n",
      "measurement_concept_id                           0                    0\n",
      "measurement_date                        2191-03-04           2191-03-04\n",
      "measurement_datetime           2191-03-04 08:00:00  2191-03-04 04:00:00\n",
      "measurement_type_concept_id               44818701             44818701\n",
      "operator_concept_id                        4172703              4172703\n",
      "value_as_number                                  0                  NaN\n",
      "value_as_concept_id                           None                 None\n",
      "unit_concept_id                               None                 None\n",
      "range_low                                     None                 None\n",
      "range_high                                    None                 None\n",
      "provider_id                                  94489                99910\n",
      "visit_occurrence_id                          46326                46326\n",
      "visit_detail_id                           67045676             67045676\n",
      "measurement_source_value                    223819               223989\n",
      "measurement_source_concept_id           2001030851           2001028029\n",
      "unit_source_value                             None                 None\n",
      "value_source_value                            None                Clear\n"
     ]
    }
   ],
   "source": [
    "# measurement_concept_id = 0 donc mapping non fait pour le moment (80% est fait)\n",
    "query = query_schema + \"\"\"\n",
    "SELECT *\n",
    "FROM measurement\n",
    "LIMIT 2 \n",
    "\"\"\"\n",
    "meas_result = pd.read_sql_query(query, con)\n",
    "print(meas_result.T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "B1zKqyJo6IFi",
    "outputId": "11b2dbb5-8fa4-427d-c39b-94890f267966"
   },
   "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>concept_name</th>\n",
       "      <th>concept_id</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>From physical examination</td>\n",
       "      <td>44818701</td>\n",
       "      <td>320690365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Labs - Chemistry</td>\n",
       "      <td>2000000011</td>\n",
       "      <td>18486124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>14870291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Labs - Blood Gaz</td>\n",
       "      <td>2000000010</td>\n",
       "      <td>6149218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Output Event</td>\n",
       "      <td>2000000003</td>\n",
       "      <td>4349218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Derived value</td>\n",
       "      <td>45754907</td>\n",
       "      <td>1045012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Labs - Culture Organisms</td>\n",
       "      <td>2000000007</td>\n",
       "      <td>363506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Labs - Culture Sensitivity</td>\n",
       "      <td>2000000008</td>\n",
       "      <td>267350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Lab result</td>\n",
       "      <td>44818702</td>\n",
       "      <td>5032</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 concept_name  concept_id      count\n",
       "0   From physical examination    44818701  320690365\n",
       "1            Labs - Chemistry  2000000011   18486124\n",
       "2               Labs - Hemato  2000000009   14870291\n",
       "3            Labs - Blood Gaz  2000000010    6149218\n",
       "4                Output Event  2000000003    4349218\n",
       "5               Derived value    45754907    1045012\n",
       "6    Labs - Culture Organisms  2000000007     363506\n",
       "7  Labs - Culture Sensitivity  2000000008     267350\n",
       "8                  Lab result    44818702       5032"
      ]
     },
     "execution_count": 47,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT c.concept_name, c.concept_id, COUNT(1)\n",
    "FROM measurement m\n",
    "JOIN concept c ON measurement_type_concept_id = concept_id\n",
    "GROUP BY concept_name, concept_id \n",
    "ORDER BY COUNT(1) DESC;\n",
    "\"\"\"\n",
    "type_column = pd.read_sql_query(query, con)\n",
    "type_column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "z3yrglXd6IFo"
   },
   "source": [
    "## FACT_RELATIONSHIP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "YhnhAc106IFo",
    "outputId": "d5f34a7a-606a-4ab1-8dd0-43f785c325f6"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                0         1         2         3\n",
      "domain_concept_id_1            36        36        36        36\n",
      "fact_id_1                78746625  78746626  78746627  78746628\n",
      "domain_concept_id_2            21        21        21        21\n",
      "fact_id_2                40575878  40698537  40698579  40556640\n",
      "relationship_concept_id  44818854  44818854  44818854  44818854\n"
     ]
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT *\n",
    "FROM fact_relationship\n",
    "WHERE fact_id_1 != fact_id_2 \n",
    "LIMIT 4\n",
    "\"\"\"\n",
    "fact_relation = pd.read_sql_query(query, con)\n",
    "print(fact_relation.T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1HLOpmQE6IFw"
   },
   "source": [
    "### Ex FACT_RELATIONSHIP : antibiotiques et germes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "tyIyLr8x6IFw",
    "outputId": "90eec836-57bc-4eeb-f330-62885556f46c"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  measurement_source_value  value_as_concept_id concept_name\n",
      "0               GENTAMICIN              4038110  Susceptible\n",
      "1             ERYTHROMYCIN              4148441    Resistant\n",
      "2                OXACILLIN              4148441    Resistant\n",
      "3               VANCOMYCIN              4038110  Susceptible\n",
      "4              CLINDAMYCIN              4148441    Resistant\n",
      "5             LEVOFLOXACIN              4148441    Resistant\n",
      "6                 RIFAMPIN              4038110  Susceptible\n",
      "7               PENICILLIN              4148441    Resistant\n",
      "8             TETRACYCLINE              4038110  Susceptible\n",
      "9             PENICILLIN G              4148441    Resistant\n"
     ]
    }
   ],
   "source": [
    "## fact_id_1 is always the organisms and fact_id_2 the antibiotics tested\n",
    "\n",
    "query = query_schema + \"\"\"\n",
    "SELECT m.measurement_source_value, m.value_as_concept_id, resistance.concept_name\n",
    "FROM measurement m\n",
    "JOIN concept resistance ON value_as_concept_id = concept_id\n",
    "JOIN fact_relationship ON m.measurement_id =  fact_id_2\n",
    "JOIN\n",
    "(\n",
    "    SELECT measurement_id AS id_is_staph\n",
    "    FROM measurement m\n",
    "    WHERE TRUE\n",
    "    AND measurement_type_concept_id = 2000000007          -- concept.concept_name = 'Labs - Culture Organisms'\n",
    "    AND value_as_concept_id = 4149419                     -- concept.concept_name = 'staph aureus coag +'\n",
    "    AND measurement_concept_id = 46235217                 -- concept.concept_name = 'Bacteria identified in Blood product unit.autologous by Culture';\n",
    "    LIMIT 10\n",
    ") staph ON id_is_staph = fact_id_1\n",
    "WHERE m.measurement_type_concept_id = 2000000008\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "fact_relation_ex = pd.read_sql_query(query, con)\n",
    "print(fact_relation_ex)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-UrcY7MX6IF4"
   },
   "source": [
    "## DRUG_EXPOSURE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "TJjHn-Xi6IF4",
    "outputId": "64939ed8-64be-41a1-fd61-b9b35c50f17a"
   },
   "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>concept_name</th>\n",
       "      <th>drug_type_concept_id</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Inpatient administration</td>\n",
       "      <td>38000180</td>\n",
       "      <td>20778301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Prescription written</td>\n",
       "      <td>38000177</td>\n",
       "      <td>4156457</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               concept_name  drug_type_concept_id     count\n",
       "0  Inpatient administration              38000180  20778301\n",
       "1      Prescription written              38000177   4156457"
      ]
     },
     "execution_count": 51,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# prescription written = prescriptions table \n",
    "# inpatient administration = inputevents_mv / cv \n",
    "query = query_schema + \"\"\"\n",
    "SELECT concept_name, drug_type_concept_id, count(1)\n",
    "FROM drug_exposure\n",
    "JOIN concept ON drug_type_concept_id = concept_id\n",
    "GROUP BY concept_name, drug_type_concept_id\n",
    "ORDER BY count(1) desc\n",
    "\"\"\"\n",
    "drug_type = pd.read_sql_query(query, con)\n",
    "drug_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "qgTJgc0o6IF-",
    "outputId": "a21cb899-6d64-4e52-ed62-aed12a62f4c3"
   },
   "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>concept_id</th>\n",
       "      <th>concept_name</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>No matching concept</td>\n",
       "      <td>1022421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19079524</td>\n",
       "      <td>Sodium Chloride 9 MG/ML Injectable Solution</td>\n",
       "      <td>252309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19076324</td>\n",
       "      <td>Glucose 50 MG/ML Injectable Solution</td>\n",
       "      <td>155485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40141424</td>\n",
       "      <td>Sodium Chloride Prefilled Syringe</td>\n",
       "      <td>84994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>46275280</td>\n",
       "      <td>50 ML Magnesium Sulfate 40 MG/ML Injection</td>\n",
       "      <td>55530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>43011850</td>\n",
       "      <td>heparin sodium, porcine 5000 UNT/ML Injectable...</td>\n",
       "      <td>53680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>40167213</td>\n",
       "      <td>Metoprolol Tartrate 25 MG Oral Tablet</td>\n",
       "      <td>53658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1127433</td>\n",
       "      <td>Acetaminophen 325 MG Oral Tablet</td>\n",
       "      <td>53309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>40167196</td>\n",
       "      <td>5 ML Metoprolol Tartrate 1 MG/ML Injection</td>\n",
       "      <td>49663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>19135374</td>\n",
       "      <td>Calcium Chloride 0.0014 MEQ/ML / Potassium Chl...</td>\n",
       "      <td>40820</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   concept_id                                       concept_name    count\n",
       "0           0                                No matching concept  1022421\n",
       "1    19079524        Sodium Chloride 9 MG/ML Injectable Solution   252309\n",
       "2    19076324               Glucose 50 MG/ML Injectable Solution   155485\n",
       "3    40141424                  Sodium Chloride Prefilled Syringe    84994\n",
       "4    46275280         50 ML Magnesium Sulfate 40 MG/ML Injection    55530\n",
       "5    43011850  heparin sodium, porcine 5000 UNT/ML Injectable...    53680\n",
       "6    40167213              Metoprolol Tartrate 25 MG Oral Tablet    53658\n",
       "7     1127433                   Acetaminophen 325 MG Oral Tablet    53309\n",
       "8    40167196         5 ML Metoprolol Tartrate 1 MG/ML Injection    49663\n",
       "9    19135374  Calcium Chloride 0.0014 MEQ/ML / Potassium Chl...    40820"
      ]
     },
     "execution_count": 52,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# as used in prescriptions table (= prescribed medications)\n",
    "# 0 = items non mappés 30% de realisé\n",
    "query = query_schema + \"\"\"\n",
    "SELECT concept_id, concept_name, count(1)\n",
    "FROM drug_exposure\n",
    "JOIN CONCEPT  ON drug_concept_id = concept_id\n",
    "WHERE drug_type_concept_id = 38000177                         -- concept.concept_name = 'Prescription written'\n",
    "GROUP BY concept_id, concept_name\n",
    "ORDER BY count(1) desc limit 10\n",
    "\"\"\"\n",
    "rxnorm = pd.read_sql_query(query, con)\n",
    "rxnorm"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "default_view": {},
   "name": "presentation_omop.ipynb",
   "provenance": [],
   "version": "0.3.2",
   "views": {}
  },
  "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}