ap_hp_exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### AP-HP Data exploration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dans ce notebook, nous analysons les données issues du GH Paris Saclay pour remonter de possibles erreurs ou incohérences dans les données."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# deactivate warnings for chaining \n",
    "pd.options.mode.chained_assignment = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Constants \n",
    "\n",
    "code_hospital = {\n",
    "  '009': 'BRK',\n",
    "  '010': 'BCT',\n",
    "  '014': 'APR',\n",
    "  '028': 'ABC',\n",
    "  '068': 'RPC',\n",
    "  '079': 'SPR',\n",
    "  '096': 'PBR',\n",
    "}\n",
    "\n",
    "hospital_name = {\n",
    "  'BRK': 'BERCK',\n",
    "  'BCT': 'BICETRE',\n",
    "  'PBR': 'PAUL-BROUSSE',\n",
    "  'ABC': 'ANTOINE BECLERE',\n",
    "  'SPR': 'SAINTE PERINE',\n",
    "  'APR': 'AMBROISE PARE',\n",
    "  'RPC': 'RAYMOND POINCARE',\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import excel files \n",
    "\n",
    "orbis = pd.read_csv('data/valide/orbis.csv', sep=';', encoding='cp1252')\n",
    "pacs = pd.read_excel('data/valide/pacs.xlsx')\n",
    "glims = pd.read_excel('data/valide/glims.xlsx')\n",
    "capacitaire = pd.read_excel('data/valide/capacitaire.xlsx')\n",
    "sirius = pd.read_excel('data/valide/sirius.xlsx',  converters={'Hopital':str, 'Code Chambre':str})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Orbis - take only relevant columns, rename and extract chambre code\n",
    "\n",
    "orbis = orbis[['IPP', 'Chambre', 'U.Responsabilité']]\n",
    "orbis.rename(columns={'IPP':'ipp', 'Chambre':'room'}, \n",
    "             inplace=True)\n",
    "\n",
    "# Extract code chambre from chambre (e.g: C134 from C134 - CHAMBRE SEULE C134)\n",
    "orbis['code_room'] = orbis['room'].str.split(r\"\\ - \", expand=True)[0]\n",
    "\n",
    "# Extract hospital name from U.Responsabilité (e.g: ABC from 028081 - ABC OBSTETRIQUE (UF))\n",
    "orbis['hospital_name'] = orbis['U.Responsabilité'].str.split(r\"\\ - \", expand=True)[1].str[0:3]\n",
    "\n",
    "# From room, remove white spaces and uppercase\n",
    "orbis['room'] = orbis['room'].str.replace(' ', '').str.upper()\n",
    "\n",
    "orbis = orbis[['ipp', 'code_room', 'room', 'hospital_name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Glims - rename columns, drop_duplicates, map is_pcr value to 1\n",
    "\n",
    "glims = glims[['ipp', 'is_pcr']]\n",
    "glims.drop_duplicates('ipp', inplace=True) #note: drop_duplicate only returns first value for each IPP\n",
    "glims['is_pcr'] = glims['is_pcr'].map({'Positif':1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pacs - select columns, drop_duplicates\n",
    "pacs = pacs[['ipp', 'radio']]\n",
    "pacs.drop_duplicates('ipp', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Capacitaire, rename is_covid column\n",
    "capacitaire.rename(columns={'Full COVID 1/0':'is_covid_dedicated',\n",
    "                            'hopital':'hospital_name',\n",
    "                            'service_covid':'covid_service'}, inplace=True)\n",
    "\n",
    "# Capacitaire, rename is_covid column\n",
    "capacitaire.drop_duplicates(['hospital_name', 'covid_service'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sirius, select columns, rename and filter \n",
    "sirius = sirius[['Hopital', \n",
    "                 'Intitulé Site Crise COVID', \n",
    "                 'Retenir ligne O/N',\n",
    "                 'Libelle Chambre',\n",
    "                 'Code Chambre']]\n",
    "\n",
    "sirius.rename(columns={'Hopital':'code_hospital',\n",
    "                       'Intitulé Site Crise COVID': 'covid_service',\n",
    "                       'Retenir ligne O/N':'filter_row',\n",
    "                       'Code Chambre':'code_room',\n",
    "                       'Libelle Chambre':'label_room'},\n",
    "             inplace=True)\n",
    "\n",
    "# only filter row with OUI \n",
    "sirius = sirius.query(\"filter_row=='OUI'\")\n",
    "\n",
    "# remove white spaces from label room, uppercase \n",
    "sirius['label_room'] = sirius['label_room'].astype(str)\n",
    "sirius['label_room'] = sirius['label_room'].str.replace(' ', '').str.upper()\n",
    "\n",
    "# remove white spaces from code room, uppercase \n",
    "sirius['code_room'] = sirius['code_room'].astype(str)\n",
    "sirius['code_room'] = sirius['code_room'].str.replace(' ', '').str.upper()\n",
    "\n",
    "#create room as concatenate of code_room and label_room\n",
    "\n",
    "sirius['room'] = sirius[['code_room', 'label_room']].agg('-'.join, axis=1)\n",
    "\n",
    "sirius.drop('filter_row', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge Orbis with Glims\n",
    "\n",
    "orbis =\\\n",
    "    pd.merge(orbis,\n",
    "             glims,\n",
    "             on='ipp',\n",
    "             how='left')\n",
    "\n",
    "# merge with Pacs\n",
    "\n",
    "orbis =\\\n",
    "    pd.merge(orbis,\n",
    "             pacs,\n",
    "             on='ipp',\n",
    "             how='left')\n",
    "\n",
    "orbis.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute is_covid patient\n",
    "\n",
    "def is_covid(x):\n",
    "    if (x['is_pcr'] + x['radio']) > 0:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "orbis['is_covid'] = orbis.apply(is_covid, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "il y a 98 patients sans Chambre dans Orbis dont 8 Covid positif:\n"
     ]
    },
    {
     "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>ipp</th>\n",
       "      <th>code_room</th>\n",
       "      <th>room</th>\n",
       "      <th>hospital_name</th>\n",
       "      <th>is_pcr</th>\n",
       "      <th>radio</th>\n",
       "      <th>is_covid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>8003232267</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>ABC</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>8014201282</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>APR</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td>8001043127</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>APR</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>760</th>\n",
       "      <td>8014207211</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>BCT</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>780</th>\n",
       "      <td>8014213145</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>BCT</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>821</th>\n",
       "      <td>8008578325</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>BCT</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1182</th>\n",
       "      <td>8014232794</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>BCT</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1301</th>\n",
       "      <td>8008890155</td>\n",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td>BCT</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ipp code_room room hospital_name  is_pcr  radio  is_covid\n",
       "118   8003232267              -           ABC     1.0    0.0         1\n",
       "381   8014201282              -           APR     1.0    0.0         1\n",
       "402   8001043127              -           APR     1.0    0.0         1\n",
       "760   8014207211              -           BCT     1.0    0.0         1\n",
       "780   8014213145              -           BCT     1.0    0.0         1\n",
       "821   8008578325              -           BCT     1.0    0.0         1\n",
       "1182  8014232794              -           BCT     1.0    0.0         1\n",
       "1301  8008890155              -           BCT     1.0    0.0         1"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many Covid patients do not have a room associated with ? \n",
    "print('il y a {n_patients_no_room} patients sans'\n",
    "       ' Chambre dans Orbis dont {n_patients_no_room_covid} Covid positif:'\\\n",
    "      .format(n_patients_no_room = len(orbis.query(\"code_room == ''\")),\n",
    "              n_patients_no_room_covid = len(orbis.query(\"code_room == ''\").query(\"is_covid==1\")),\n",
    "             ))\n",
    "\n",
    "orbis.query(\"code_room == ''\").query(\"is_covid==1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add hospital_name to sirius \n",
    "\n",
    "sirius['hospital_name'] = sirius['code_hospital'].map(code_hospital)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge Sirius and Orbis\n",
    "\n",
    "orbis =\\\n",
    "    pd.merge(orbis,\n",
    "             sirius,\n",
    "             how='left',\n",
    "             on=['hospital_name', \n",
    "                 'room'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Discrepancies between Sirius and Orbis room matching"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After a left merge on the room matching key, we end up with rooms that do not match in Sirius. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "il y a 147 chambres Orbis qu'on ne retrouve pas dans Sirius\n"
     ]
    }
   ],
   "source": [
    "print('il y a {n_rooms_missing} chambres Orbis'\n",
    "      ' qu\\'on ne retrouve pas dans Sirius'\\\n",
    "      .format(n_rooms_missing=len(orbis.query(\"code_hospital.isnull()\"))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why is it the case? Let's look at the difference in label in the two files: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a df with the rooms that did not find a match in Sirius\n",
    "\n",
    "room_gap = orbis[orbis['covid_service'].isna()][['code_room_x', 'room', 'hospital_name']]\n",
    "room_gap.rename(columns={'code_room_x':'code_room'}, \n",
    "                inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A first reason is that the code_room in Orbis can be empty. This can be patients that have not yet a room allocated or newborns that sleep with their mama. How many are those? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "il y a 98 patients dans Orbis sans chambre. Reste a expliquer 49 patients\n"
     ]
    }
   ],
   "source": [
    "n_patients_with_no_rooms_in_orbis = len(room_gap.query(\"code_room == ''\"))\n",
    "n_patients_remaining = len(orbis.query(\"code_hospital.isnull()\")) - n_patients_with_no_rooms_in_orbis\n",
    "\n",
    "print('il y a {n_patients_with_no_rooms_in_orbis} patients dans Orbis'\n",
    "      ' sans chambre. Reste a expliquer {n_patients_remaining} patients'\\\n",
    "      .format(n_patients_with_no_rooms_in_orbis=n_patients_with_no_rooms_in_orbis,\n",
    "              n_patients_remaining=n_patients_remaining))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge room_gap with sirius only on those that have a code_room in Sirius\n",
    "\n",
    "df_gap = pd.merge(room_gap.query(\"code_room != ''\"),\n",
    "         sirius,\n",
    "         on=['code_room', 'hospital_name'],\n",
    "         how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another reason is that the code_room does not match. This can be because when imported, sirius Excel file cast columns and consider some rows as string or integer. See below an example for code_room '04' in Orbis and '4' in Sirius: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>code_hospital</th>\n",
       "      <th>covid_service</th>\n",
       "      <th>label_room</th>\n",
       "      <th>code_room</th>\n",
       "      <th>room</th>\n",
       "      <th>hospital_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1408</th>\n",
       "      <td>068</td>\n",
       "      <td>Widal 2 Covid+</td>\n",
       "      <td>CHAMBRE201</td>\n",
       "      <td>201</td>\n",
       "      <td>201-CHAMBRE201</td>\n",
       "      <td>RPC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2888</th>\n",
       "      <td>009</td>\n",
       "      <td>Réadaptation Gériatrique</td>\n",
       "      <td>SORREL2-CHAMBREA1LIT</td>\n",
       "      <td>201</td>\n",
       "      <td>201-SORREL2-CHAMBREA1LIT</td>\n",
       "      <td>BRK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3892</th>\n",
       "      <td>010</td>\n",
       "      <td>Médecine interne</td>\n",
       "      <td>CHAMBRESEULE201M2I</td>\n",
       "      <td>201</td>\n",
       "      <td>201-CHAMBRESEULE201M2I</td>\n",
       "      <td>BCT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code_hospital             covid_service            label_room code_room  \\\n",
       "1408           068            Widal 2 Covid+            CHAMBRE201       201   \n",
       "2888           009  Réadaptation Gériatrique  SORREL2-CHAMBREA1LIT       201   \n",
       "3892           010          Médecine interne    CHAMBRESEULE201M2I       201   \n",
       "\n",
       "                          room hospital_name  \n",
       "1408            201-CHAMBRE201           RPC  \n",
       "2888  201-SORREL2-CHAMBREA1LIT           BRK  \n",
       "3892    201-CHAMBRESEULE201M2I           BCT  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sirius.query(\"code_room=='201'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "il y a 5 patients dont le code chambre Sirius ne correspond pas dans Orbis. Reste a expliquer 44 patients\n"
     ]
    }
   ],
   "source": [
    "n_patients_with_no_code_room_matching_in_sirius =\\\n",
    "    len(df_gap.drop_duplicates(['room_x', \n",
    "                                'hospital_name']).query('code_hospital.isnull()'))\n",
    "n_patients_remaining = n_patients_remaining - n_patients_with_no_code_room_matching_in_sirius\n",
    "\n",
    "print('il y a {n_patients_with_no_code_room_matching_in_sirius} patients dont le code chambre'\n",
    "      ' Sirius ne correspond pas dans Orbis. Reste a expliquer {n_patients_remaining} patients'\\\n",
    "      .format(n_patients_with_no_code_room_matching_in_sirius=n_patients_with_no_code_room_matching_in_sirius,\n",
    "              n_patients_remaining=n_patients_remaining))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we have room labels that do not match because strings are different. A few examples: \n",
    "\n",
    "- There is a space at the end \"ABC\" vs \"ABC \"\n",
    "- Lower case vs Upper case \"CHAMBRE 5 NEO NATALE\" vs \"Chambre 5 Neo Natale\"\n",
    "- Characters used 321 - \"ferm√©\" vs \"ferm?\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>room_orbis</th>\n",
       "      <th>room_sirius</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CHA1-CHAMBRESRMN.A1</td>\n",
       "      <td>CHA1-CHAMBRESRMN°A1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P14-CHAMBRE14PÉDIATRIE</td>\n",
       "      <td>P14-CHAMBRE14P�DIATRIE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CHA5-CHAMBRESRMN.A5</td>\n",
       "      <td>CHA5-CHAMBRESRMN°A5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CHD4-CHAMBRESCMN.D4</td>\n",
       "      <td>CHD4-CHAMBRESCMN°D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CHD2-CHAMBRESCMN.D2</td>\n",
       "      <td>CHD2-CHAMBRESCMN°D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               room_orbis             room_sirius\n",
       "1     CHA1-CHAMBRESRMN.A1     CHA1-CHAMBRESRMN°A1\n",
       "2  P14-CHAMBRE14PÉDIATRIE  P14-CHAMBRE14P�DIATRIE\n",
       "3     CHA5-CHAMBRESRMN.A5     CHA5-CHAMBRESRMN°A5\n",
       "4     CHD4-CHAMBRESCMN.D4     CHD4-CHAMBRESCMN°D4\n",
       "5     CHD2-CHAMBRESCMN.D2     CHD2-CHAMBRESCMN°D2"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gap_label = df_gap.query(\"code_hospital.notnull()\")[['room_x', \n",
    "                                         'room_y']]\\\n",
    "      .rename(columns={'room_x':'room_orbis',\n",
    "                       'room_y':'room_sirius'})\n",
    "df_gap_label.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "il y a 40 patients avec une difference de label . Reste a expliquer 4 patients\n"
     ]
    }
   ],
   "source": [
    "n_patients_with_label_diff =\\\n",
    "    len(df_gap.query(\"code_hospital.notnull()\")\\\n",
    "              .drop_duplicates(['room_x', \n",
    "                                'hospital_name']))\n",
    "n_patients_remaining = n_patients_remaining - n_patients_with_label_diff\n",
    "\n",
    "print('il y a {n_patients_with_label_diff} patients avec une difference de label'\n",
    "      ' . Reste a expliquer {n_patients_remaining} patients'\\\n",
    "      .format(n_patients_with_label_diff=n_patients_with_label_diff,\n",
    "              n_patients_remaining=n_patients_remaining))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export rooms that did not have a match on code_room in Sirius\n",
    "room_gap.query(\"code_room!=''\").to_excel('no_match_in_sirius.xlsx', index=False)\n",
    "\n",
    "# Export rooms that did not have a match on code_room in Sirius\n",
    "df_gap.query('code_hospital.isnull()').to_excel('no_code_chambre_in_sirius.xlsx', index=False)\n",
    "\n",
    "# Export rooms that did not have a match on code_room in Sirius\n",
    "df_gap_label.to_excel('mismatch_label.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Capacitaire"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge capacitaire \n",
    "\n",
    "orbis =\\\n",
    "    pd.merge(orbis,\n",
    "             capacitaire,\n",
    "             how='left',\n",
    "             on=['hospital_name', \n",
    "                 'covid_service'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "orbis.query('covid_service.isnull()').query(\"code_room_x!=''\").to_excel(\"orbis_patient_with_no_match.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = orbis.groupby(['hospital_name', \n",
    "               'covid_service'])\\\n",
    "     .agg({'ipp': 'nunique',\n",
    "           'is_covid': 'sum',\n",
    "           'lits_ouverts': 'max',\n",
    "           'lits_ouverts_covid': 'max'})\\\n",
    "     .reset_index()\\\n",
    "     .fillna(0)"
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}