Skip to content

Latest commit

 

History

History
223 lines (154 loc) · 9.95 KB

README.md

File metadata and controls

223 lines (154 loc) · 9.95 KB
Raw

AP-HP Covid-19 Bed Capacity Tracker

Summary

Context

This app aims at displaying the remaining bed capacity for Covid-19 patients in the Groupe Hospitalier Paris Saclay of AP-HP. More details are available in this one-pager.

See app online at - https://ap-hp-paris-saclay.herokuapp.com/

Input

The app takes 6 distinct files as input orbis, glims, pacs, capacity, sirius and sivic.

Each of those files are exported from one of the information system of AP-HP. Files types are specificed below. You can also find under the data folder an up to date sample of each file.

Input format files

File Type Encoding Team
Orbis csv (sep=;) cp1252 Finance
Glims xlsx - Finance
Pacs xlsx - Radio
Sirius xlsx - Accounting
Capacitaire xlsx - Finance
Sivic xlsx - Qualite

Input files description

  • Orbis: this is a snapshot of the current patients admitted in the hospital.
Column Type Description
Sexe STRING Sex of patient
Né(e) le DATE Date of birth, format DD/MM/YYYY
IPP INT Patient id.
N° Dossier INT Case number.
U.Responsabilité STRING Unit name (e.g: 010250 - BCT SRPR (UF))
U.Soins STRING Sub-unit name (e.g: 010780NE5 - BCT HC SSR NEUROLOGIE - CULLERIER)
Date d'entrée du dossier DATE Entry date of patient, format DD/MM/YYYY HH:MM
Date de sortie du dossier DATE Exit date of patient, format DD/MM/YYYY HH:MM
Date de début du mouvement DATE Datetime when a patient is moved to a room or service, format DD/MM/YYYY HH:MM.
Date de fin du mouvement DATE End date when a patient is moved from a unit, format DD/MM/YYYY HH:MM.
Chambre STRING Room where the patient is located in (e.g: N515 - CULLERIER CHAMBRE 15 DOUBLE)
Lit STRING Bed where the patient is located in (e.g: N515P - LIT 15 PORTE)
  • Glims: Export of serology tests of patients that indicates whether patients are tested positive to Covid-19. Note that there may be multiple rows for a same patient. Not all rows match an Orbis patient. For instance medical staff may appear in the Glims export.
Column Type Description
DOSSIER INT Case number
PRLVT DATE Date of the test. Format is DD/MM/YYYY.
ipp INT Patient id.
RENS_PIH STRING Name of the hospital where the test was conducted.
last_uma STRING Last unit visited by patient.
is_pcr STRING Indicates if the patient is tested positive to Covid-19.
  • Pacs: Export of lung radiology scans. Indicates whether patients are tested positive to Covid-19.
Column Type Description
ipp INT Patient id.
date DATE Date of the test. Format is DD/MM/YYYY.
radio INT 1 if the patient is Covid-19 positive from radiology.
  • Capacitaire: Daily snapshot of the bed capacity in a given hospital.
Column Type Description
hopital STRING Name of hospital.
service_covid STRING Name of the Covid service as defined by the hospital.
lits_ouverts INT Number of beds available for that service.
lits_ouverts_covid INT Number of beds available for that service dedicated to Covid patients.
Full COVID 1/0 INT 1 if service is dedicated to Covid, else N/A.
  • Sirius: Sirius extract enabling mapping between Orbis room code and the Covid service put together by the hospital.
Column Type Description
Hopital INT Code of hospital (e.g.: 96).
Localisation CDG STRING Returns the physical location of the room (e.g: BATIMENT COMMANDANT RIVIERE NIVEAU 2).
Intitulé Site Crise COVID STRING Our "service_covid" field, name of the Covid service as defined by the hospital (e.g.: PSY J. DELAY).
Code Chambre STRING The room code. This code, combined with Libelle Chambre gives a mapping to Orbis.
Libelle Chambre STRING The label of the room. This label, combined with Code Chambre gives a mapping to Orbis.
Retenir ligne O/N STRING "OUI" if the room should be included in the tablem, "NON" otherwise.

Unused so far: type chambre, commentaires, Code Site, Libelle Site, Date de création, Date de modification, Date d'effet creation, Date de fin de validité, Date d'effet modification, Code Batiment, Libelle Batiment, Date de création, Date de modification, Date d'effet creation, Date de fin de validité, Date d'effet modification, Code Secteur Batiment, Libelle Secteur Batiment, Date de création, Date de modification, Date d'effet creation, Date de fin de validité, Date d'effet modification, Code Etage, Libelle Etage, Date de création, Date de modification, Date d'effet creation, Date de fin de validité, Date d'effet modification, Date de création, Date de modification, Date d'effet creation, Date de fin de validité, Date d'effet modification

  • Sivic: Cross-check highlighting discrepancies between Sivic database and Glims/Pacs.
Column Type Description
Cas de figure STRING Type of discrepancy between Sivic and Glims/Pacs. Expected useful values are "Absent CDGPrésent QLT" and "Présent CDGAbsent QLT".
IPP INT Patient id.
Commentaires normalisés STRING Validation of the discrepancy by Qualite team. Expected values are "RAS", "AJOUT" or "RETRAIT".
commentaires libre STRING Free text field for any additional comment by Qualite team.

Logic

There are a number of subtelties in how each file is connected to the other and the data manipulation. In this section we list each of the steps we need to encode to make sure we get an accurate picture of bed capacity in AP-HP.

Orbis

  • Patients with no room: this can happen when a patient had a folder created in Orbis but was not yet assigned to a room when the export was run. This should be explicited in our warning section.
  • Newborns: two hospital (BCT - Bicêtre, ABC - Antoine Beclere) have Obstetric services. When a baby is born, he stays with his mum in the same room, meaning he does not occupy a room. We should account for those and remove patients, from those care units, that were born in 2020.

Sirius

  • Code room and label: Code room are not unique per hospital. That means that we need to match rooms between Sirius and Orbis on a concatenate of the Code Chambre and Label Chambre. Unfortunately, see a data exploration here, not all label and code chambre match between the two systems (case, trailing 0, special characters). In the meantime we should remove spaces and use one case during data cleaning.

Output

The output is a table that gives, for each hospital the following:

Text displayed Column Type Description
Site crise Covid-19 service_covid STRING Name of the Covid-19 service as defined by the hospital. Field from the Correspondance table.
Nombre de lits ouverts lits_ouverts INT Number of beds available for that service_covid (from the file capacitaire.csv).
Nombre de lits dédiés Covid lits_ouverts_covid INT Number of beds available for that service_covid and dedicated to Covid patients (from the file capacitaire.csv).
Total patients total_patients INT Total number of for that service_covid. This field is computed from Orbis.
Total patients Covid total_patients_covid INT Total number of Covid patients for that service_covid. This field is computed by summing Covid patients (glims + pacs + other).
Patients Covid-19+ biologie glims_patients_covid INT Total number of patients for that service_covid that are positive according to Glims.
Patients Covid-19+ radiologie pacs_patients_covid INT Total number of patients for that service_covid that are positive according to Pacs.
Patients Covid-19+ (autres) orbis_patients_covid INT Number of patients for that service that are Covid-19 from the dedicated field in Orbis.
Nombre de lits disponibles remaining_beds INT Remaining number of beds for Covid patients for that service_covid. Equal to lits_ouverts - total_patients.

React Application

The app is a single webpage. Data processing and display is done using react. To get started:

yarn
yarn start

The app is hosted on Heroku. You first need to add heroku branch to master:

git remote add heroku https://git.heroku.com/ap-hp-paris-saclay.git

To update the app:

git push heroku master

Generate fixture files

In order to test the app in development without uploading each time XLS files, you can pass parameter the following parameter:

http://localhost:3000/?fixture=on

That parameter will call json fixture files defined in /src/fixture. The logic to generate those files is defined in export_fixture.py. Steps are:

  • Filter data only for 2 hospitals
  • Remove unecessary columns to limit file sizes
  • Encrypt personal data like IPP

To generate those files use the following steps:

  • Make sure you have the python libraries installed with:
pip install -r requirements-data.txt
  • Then run from a terminal
python export_fixture.py
  • The command above will generate 3 json files. Since Python escapes / you need to replace \/ by /. You can achieve this by running from a terminal:
cat orbis_fixture.json | sed 's/\\\//\//g' >> orbis_fixture.json
cat sirius_fixture.json | sed 's/\\\//\//g' >> sirius_fixture.json
  • You now have 3 json files. You can copy paste the content of those in the src/fixtures folder.

Data Exploration

To quickly investigate data discrepancies, we have put together a Jupyter notebook at ap_hp_exploration.ipynb. The file can also be rendered on Github.

To run Jupyter notebook locally, install python dependencies with:

pip install -r requirements-data.txt

Then at the root of the repo run:

jupyter notebook

More details on Jupyter at - https://jupyter.org/