Create PPT
dashboard.create_ppt
Functions
create_ppt(env, date)
Crée une présentation PowerPoint contenant des informations sur la situation hospitalière en matière de lits de réanimation et d'occupation hospitalière à partir de données extraites de la base de données.
Cette fonction enregistre des graphiques, des tableaux et des textes dans un modèle de présentation existant pour générer un rapport visuel.
Paramètres :
env : object L'environnement de connexion nécessaire pour interagir avec la base de données.
str
La date de rapport à traiter, au format 'YYYY-MM-DD'.
Processus :
- Chargement et traitement des données depuis la table
table_union. - Création d'une présentation en utilisant un modèle PowerPoint existant.
- Insertion de graphiques et de cartes pertinents dans la présentation.
- Remplissage des zones de texte avec des informations et des données statistiques.
Exceptions :
- IOError : Si le modèle de présentation ou les fichiers nécessaires ne peuvent pas être trouvés ou chargés.
- ValueError : Si les données extraites ne respectent pas les formats ou types attendus.
s
Source code in dashboard/create_ppt.py
def create_ppt(env:str, date):
"""
Crée une présentation PowerPoint contenant des informations sur la situation
hospitalière en matière de lits de réanimation et d'occupation hospitalière à partir
de données extraites de la base de données.
Cette fonction enregistre des graphiques, des tableaux et des textes dans un modèle
de présentation existant pour générer un rapport visuel.
Paramètres :
----------
env : object
L'environnement de connexion nécessaire pour interagir avec la base de données.
date : str
La date de rapport à traiter, au format 'YYYY-MM-DD'.
Processus :
----------
1. Chargement et traitement des données depuis la table `table_union`.
2. Création d'une présentation en utilisant un modèle PowerPoint existant.
3. Insertion de graphiques et de cartes pertinents dans la présentation.
4. Remplissage des zones de texte avec des informations et des données statistiques.
Exceptions :
------------
- IOError : Si le modèle de présentation ou les fichiers nécessaires ne peuvent pas
être trouvés ou chargés.
- ValueError : Si les données extraites ne respectent pas les formats ou types
attendus.
s"""
from src.constantes_front import ordre_regions, mapping_regions
# Création d'une instance DateTimeManager
_dt_manager = DateTimeManager(date)
# Variables à exporter pour compatibilité avec vos anciens imports
hour_exec = _dt_manager.execution_hour
today = _dt_manager.today
today_Ymd_short = _dt_manager.today_Ymd_short
today_Ymd_long = _dt_manager.today_Ymd_long
today_jmA = _dt_manager.today_jmA
prev_day_Ymd_short = _dt_manager.prev_day_Ymd_short
prev_day_Ymd_long = _dt_manager.prev_day_Ymd_long
month_const = _dt_manager.MONTH_NAMES
PRINT_TODAY = _dt_manager.print_today
PRINT_PREV_DAY = _dt_manager.print_prev_day
PREV_FILE_DATE = _dt_manager.prev_file_date
FILE_DATE = _dt_manager.file_date
PRINT_PPT_DATETIME = _dt_manager.print_ppt_datetime
start_date = _dt_manager.start_date
# Import template and csv
logger.info("Importing recap_brules and template_brules.")
#%% ----------- Chargement des données ----------
prs = Presentation(f"{metadata_dir}/Maquette_ppt.pptx")
df = req2table(f"SELECT * FROM table_union WHERE capacite_extract_datetime::date = '{today_Ymd_long}' AND h_exec='{hour_exec}'", env)
df["date"] = pd.to_datetime(df["capacite_extract_datetime"].dt.date)
df = df[~df["lit_capacite"].isnull()]
df = df[df["sous_categorie_lit"] != "rea_brules"]
df = df[(df["region"] != "IDF") | (df["lit_capacite"] != 100)]
df["new_cate"] = np.repeat(np.nan, df.shape[0])
df.loc[df["categorie_lit"] == "reanimation", "new_cate"] = "Réanimation"
df.loc[df["categorie_lit"] == "surveillance_continue", "new_cate"] = "Surveillance continue"
df.loc[df["sous_categorie_lit"] == "usin", "new_cate"] = "USINV"
df.loc[df["sous_categorie_lit"] == "usic", "new_cate"] = "USIC"
df.loc[df["sous_categorie_lit"] == "autres_usi", "new_cate"] = "Autres USI"
loca_finess = pd.read_csv(f'{csv_dir}/GEOLOCALISATION_FINESS_12022021.csv', sep = ';')
loca_finess = loca_finess.drop_duplicates()
loca_finess["finess"] = ['{0:0>9}'.format(i) for i in loca_finess["finess"]]
loca_finess["latitude"] = loca_finess["latitude"].str.replace(",", ".").astype(float)
loca_finess["longitude"] = loca_finess["longitude"].str.replace(",", ".").astype(float)
recap_region = pd.read_csv(f"{csv_dir}/recap_region.csv")
#%% ------------------ Slide réanimation national -----------
slide = prs.slides[0]
name_map = 'Espace réservé pour une image\xa0 1'
name_barplot1 = 'Espace réservé pour une image\xa0 2'
name_barplot2 = 'Espace réservé pour une image\xa0 4'
name_source1 = 'Espace réservé du texte 3'
name_source2 = 'Espace réservé du texte 14'
name_map_gua = 'Espace réservé pour une image\xa0 5'
name_map_mar = 'Espace réservé pour une image\xa0 6'
name_map_guy = 'Espace réservé pour une image\xa0 7'
name_map_reu = 'Espace réservé pour une image\xa0 8'
name_map_may = 'Espace réservé pour une image\xa0 9'
name_legend = 'Espace réservé pour une image\xa0 26'
name_to1 = 'Espace réservé du texte 21'
name_to2 = 'Espace réservé du texte 22'
name_to3 = 'Espace réservé du texte 23'
name_to4 = 'Espace réservé du texte 24'
name_to5 = 'Espace réservé du texte 25'
name_x = 'Espace réservé du texte 29'
name_fraicheur = 'Espace réservé du texte 30'
name_legend_title = 'Espace réservé du texte 20'
name_title_colorbar = 'Espace réservé du texte 19'
name_colorbar = 'Espace réservé pour une image\xa0 16'
name_capa1 = 'Espace réservé du texte 18'
name_capa2 = 'Espace réservé du texte 17'
name_capa3 = 'Espace réservé du texte 27'
name_capa4 = 'Espace réservé du texte 28'
title_map = 'Espace réservé du texte 15'
title_barplot1 = 'Espace réservé du texte 10'
title_barplot2 = 'Espace réservé du texte 11'
# ND_gua = 'Espace réservé du texte 12'
ND_may = 'Espace réservé du texte 13'
i = 0
for shape in slide.shapes:
if shape.name == name_map:
id_map = shape.placeholder_format.idx
elif shape.name == name_barplot1:
id_barplot1 = shape.placeholder_format.idx
elif shape.name == name_barplot2:
id_barplot2 = shape.placeholder_format.idx
elif shape.name == name_source1:
id_source1 = i
elif shape.name == name_source2:
id_source2 = i
elif shape.name == name_map_gua:
id_gua = shape.placeholder_format.idx
elif shape.name == name_map_mar:
id_mar = shape.placeholder_format.idx
elif shape.name == name_map_guy:
id_guy = shape.placeholder_format.idx
elif shape.name == name_map_reu:
id_reu = shape.placeholder_format.idx
elif shape.name == name_map_may:
id_may = shape.placeholder_format.idx
elif shape.name == name_legend:
id_legend = shape.placeholder_format.idx
elif shape.name == name_legend_title:
id_legend_title = i
elif shape.name == name_to1:
id_to1 = i
elif shape.name == name_to2:
id_to2 = i
elif shape.name == name_to3:
id_to3 = i
elif shape.name == name_to4:
id_to4 = i
elif shape.name == name_to5:
id_to5 = i
elif shape.name == name_x:
id_x = i
elif shape.name == name_fraicheur:
id_fraicheur = i
elif shape.name == name_title_colorbar:
id_title_colorbar = i
elif shape.name == name_colorbar:
id_colorbar = shape.placeholder_format.idx
elif shape.name == name_capa1:
id_capa1 = i
elif shape.name == name_capa2:
id_capa2 = i
elif shape.name == name_capa3:
id_capa3 = i
elif shape.name == name_capa4:
id_capa4 = i
elif shape.name == title_map:
id_title_map = i
elif shape.name == title_barplot1:
id_title_barplot1 = i
elif shape.name == title_barplot2:
id_title_barplot2 = i
# elif shape.name == ND_gua:
# id_ND_gua = i
elif shape.name == ND_may:
id_ND_may = i
else:
pass
i = i + 1
# -------------- Graphiques -----------------
slide.placeholders[id_barplot1].insert_picture(f'{pictures_dir}/barplot_reg_rea.png')
slide.placeholders[id_barplot2].insert_picture(f'{pictures_dir}/barplot_evol_rea.png')
slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_rea.png')
slide.placeholders[id_gua].insert_picture(f'{pictures_dir}/map_rea_GUA.png')
slide.placeholders[id_mar].insert_picture(f'{pictures_dir}/map_rea_MAR.png')
slide.placeholders[id_guy].insert_picture(f'{pictures_dir}/map_rea_GUY.png')
slide.placeholders[id_reu].insert_picture(f'{pictures_dir}/map_rea_REU.png')
slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_to.png')
slide.placeholders[id_colorbar].insert_picture(f'{metadata_dir}/plots/legend_capa_rea.png')
#---------------- empty metadata graphics --------------------------------
slide.placeholders[id_may].insert_picture(f'{metadata_dir}/plots/carto_mayotte.png')
# ------------- Zones de texte -------------
shape = slide.shapes[id_source1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_source2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_title_colorbar]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Nombre de lits installés à date (/100 000 habitants)"
shape = slide.shapes[id_capa1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "0"
shape = slide.shapes[id_capa2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "25"
shape = slide.shapes[id_capa3]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "10"
shape = slide.shapes[id_capa4]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "20"
shape = slide.shapes[id_x]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "X"
shape = slide.shapes[id_fraicheur]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Défaut de mise à jour"
shape = slide.shapes[id_legend_title]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Taux d'occupation à date"
shape = slide.shapes[id_to1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ">= 95%"
shape = slide.shapes[id_to2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[90%; 95%["
shape = slide.shapes[id_to3]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[85%; 90%["
shape = slide.shapes[id_to4]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[80%; 85%["
shape = slide.shapes[id_to5]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "< 80%"
shape = slide.shapes[id_title_map]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Situation dans les départements au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Régions France hors Mayotte – {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"France entière hors Mayotte, du {start_date} au {PRINT_PPT_DATETIME}"
# shape = slide.shapes[id_ND_gua]
# text_frame = shape.text_frame
# text_frame.clear()
# p = text_frame.paragraphs[0]
# run = p.add_run()
# run.text = "Non Disponible"
shape = slide.shapes[id_ND_may]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Non Disponible"
#%% ------------------ Slide USC national -----------
slide = prs.slides[1]
name_map = 'Espace réservé pour une image\xa0 1'
name_barplot1 = 'Espace réservé pour une image\xa0 2'
name_barplot2 = 'Espace réservé pour une image\xa0 4'
name_source1 = 'Espace réservé du texte 3'
name_source2 = 'Espace réservé du texte 14'
name_map_gua = 'Espace réservé pour une image\xa0 5'
name_map_mar = 'Espace réservé pour une image\xa0 6'
name_map_guy = 'Espace réservé pour une image\xa0 7'
name_map_reu = 'Espace réservé pour une image\xa0 8'
name_map_may = 'Espace réservé pour une image\xa0 9'
name_legend = 'Espace réservé pour une image\xa0 26'
name_to1 = 'Espace réservé du texte 21'
name_to2 = 'Espace réservé du texte 22'
name_to3 = 'Espace réservé du texte 23'
name_to4 = 'Espace réservé du texte 24'
name_to5 = 'Espace réservé du texte 25'
name_x = 'Espace réservé du texte 29'
name_fraicheur = 'Espace réservé du texte 30'
name_legend_title = 'Espace réservé du texte 20'
name_title_colorbar = 'Espace réservé du texte 19'
name_colorbar = 'Espace réservé pour une image\xa0 16'
name_capa1 = 'Espace réservé du texte 18'
name_capa2 = 'Espace réservé du texte 17'
name_capa3 = 'Espace réservé du texte 27'
name_capa4 = 'Espace réservé du texte 28'
title_map = 'Espace réservé du texte 15'
title_barplot1 = 'Espace réservé du texte 10'
title_barplot2 = 'Espace réservé du texte 11'
# ND_gua = 'Espace réservé du texte 12'
ND_may = 'Espace réservé du texte 13'
i = 0
for shape in slide.shapes:
if shape.name == name_map:
id_map = shape.placeholder_format.idx
elif shape.name == name_barplot1:
id_barplot1 = shape.placeholder_format.idx
elif shape.name == name_barplot2:
id_barplot2 = shape.placeholder_format.idx
elif shape.name == name_source1:
id_source1 = i
elif shape.name == name_source2:
id_source2 = i
elif shape.name == name_map_gua:
id_gua = shape.placeholder_format.idx
elif shape.name == name_map_mar:
id_mar = shape.placeholder_format.idx
elif shape.name == name_map_guy:
id_guy = shape.placeholder_format.idx
elif shape.name == name_map_reu:
id_reu = shape.placeholder_format.idx
elif shape.name == name_map_may:
id_may = shape.placeholder_format.idx
elif shape.name == name_legend:
id_legend = shape.placeholder_format.idx
elif shape.name == name_legend_title:
id_legend_title = i
elif shape.name == name_to1:
id_to1 = i
elif shape.name == name_to2:
id_to2 = i
elif shape.name == name_to3:
id_to3 = i
elif shape.name == name_to4:
id_to4 = i
elif shape.name == name_to5:
id_to5 = i
elif shape.name == name_x:
id_x = i
elif shape.name == name_fraicheur:
id_fraicheur = i
elif shape.name == name_title_colorbar:
id_title_colorbar = i
elif shape.name == name_colorbar:
id_colorbar = shape.placeholder_format.idx
elif shape.name == name_capa1:
id_capa1 = i
elif shape.name == name_capa2:
id_capa2 = i
elif shape.name == name_capa3:
id_capa3 = i
elif shape.name == name_capa4:
id_capa4 = i
elif shape.name == title_map:
id_title_map = i
elif shape.name == title_barplot1:
id_title_barplot1 = i
elif shape.name == title_barplot2:
id_title_barplot2 = i
# elif shape.name == ND_gua:
# id_ND_gua = i
elif shape.name == ND_may:
id_ND_may = i
else:
pass
i = i + 1
# -------------- Graphiques -----------------
slide.placeholders[id_barplot1].insert_picture(f'{pictures_dir}/barplot_reg_usc.png')
slide.placeholders[id_barplot2].insert_picture(f'{pictures_dir}/barplot_evol_usc.png')
slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_usc.png')
slide.placeholders[id_gua].insert_picture(f'{pictures_dir}/map_usc_GUA.png')
slide.placeholders[id_mar].insert_picture(f'{pictures_dir}/map_usc_MAR.png')
slide.placeholders[id_guy].insert_picture(f'{pictures_dir}/map_usc_GUY.png')
slide.placeholders[id_reu].insert_picture(f'{pictures_dir}/map_usc_REU.png')
slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_to.png')
slide.placeholders[id_colorbar].insert_picture(f'{metadata_dir}/plots/legend_capa_rea.png')
# ----- empty mayotte maps
slide.placeholders[id_may].insert_picture(f'{metadata_dir}/plots/carto_mayotte.png')
# ------------- Zones de texte -------------
shape = slide.shapes[id_source1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_source2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue (hors unités grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_title_colorbar]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Nombre de lits installés à date (/100 000 habitants)"
shape = slide.shapes[id_capa1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "0"
shape = slide.shapes[id_capa2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "25"
shape = slide.shapes[id_capa3]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "10"
shape = slide.shapes[id_capa4]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "20"
shape = slide.shapes[id_x]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "X"
shape = slide.shapes[id_fraicheur]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Défaut de mise à jour"
shape = slide.shapes[id_legend_title]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Taux d'occupation à date"
shape = slide.shapes[id_to1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ">= 95%"
shape = slide.shapes[id_to2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[90%; 95%["
shape = slide.shapes[id_to3]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[85%; 90%["
shape = slide.shapes[id_to4]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[80%; 85%["
shape = slide.shapes[id_to5]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "< 80%"
shape = slide.shapes[id_title_map]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Situation dans les départements au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Régions France hors Mayotte – {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"France entière hors Mayotte, du {start_date} au {PRINT_PPT_DATETIME}"
# shape = slide.shapes[id_ND_gua]
# text_frame = shape.text_frame
# text_frame.clear()
# p = text_frame.paragraphs[0]
# run = p.add_run()
# run.text = "Non Disponible"
shape = slide.shapes[id_ND_may]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Non Disponible"
#%% ------------------ Slide IDF reanimation -----------
slide = prs.slides[2]
name_map1 = 'Espace réservé pour une image\xa0 8'
name_map2 = 'Espace réservé pour une image\xa0 7'
name_barplot = 'Espace réservé pour une image\xa0 2'
name_source1 = 'Espace réservé du texte 1'
name_source2 = 'Espace réservé du texte 5'
name_legend = 'Espace réservé pour une image\xa0 11'
name_legend_title = 'Espace réservé du texte 9'
name_to1 = 'Espace réservé du texte 10'
name_to2 = 'Espace réservé du texte 11'
name_to3 = 'Espace réservé du texte 12'
name_to4 = 'Espace réservé du texte 13'
name_to5 = 'Espace réservé du texte 14'
title_map = 'Espace réservé du texte 6'
title_barplot = 'Espace réservé du texte 4'
name_table = 'Espace réservé du tableau 16'
name_title_table = 'Espace réservé du texte 3'
i = 0
for shape in slide.shapes:
if shape.name == name_map1:
id_map1 = shape.placeholder_format.idx
elif shape.name == name_map2:
id_map2 = shape.placeholder_format.idx
elif shape.name == name_barplot:
id_barplot = shape.placeholder_format.idx
elif shape.name == name_source1:
id_source1 = i
elif shape.name == name_source2:
id_source2 = i
elif shape.name == name_colorbar:
id_colorbar = i
elif shape.name == title_map:
id_title_map = i
elif shape.name == title_barplot:
id_title_barplot = i
elif shape.name == name_legend:
id_legend = shape.placeholder_format.idx
elif shape.name == name_legend_title:
id_legend_title = i
elif shape.name == name_to1:
id_to1 = i
elif shape.name == name_to2:
id_to2 = i
elif shape.name == name_to3:
id_to3 = i
elif shape.name == name_to4:
id_to4 = i
elif shape.name == name_to5:
id_to5 = i
elif shape.name == name_table:
id_table = i
elif shape.name == name_title_table:
id_title_table = i
i = i + 1
# -------------- Graphiques -----------------
slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_rea_IDF.png')
slide.placeholders[id_map1].insert_picture(f'{pictures_dir}/map_rea_region_IDF.png')
slide.placeholders[id_map2].insert_picture(f'{pictures_dir}/map_rea_region_IDF_pcr.png')
slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_color.png')
# ------------- Zones de texte -------------
shape = slide.shapes[id_source1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_source2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_title_map]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Situation en Île-de-France au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Île-de-France du {start_date} au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_legend_title]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Taux d'occupation à date"
shape = slide.shapes[id_to1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ">= 95%"
shape = slide.shapes[id_to2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[90%; 95%["
shape = slide.shapes[id_to3]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[85%; 90%["
shape = slide.shapes[id_to4]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[80%; 85%["
shape = slide.shapes[id_to5]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "< 80%"
to_reg = recap_region.loc[(recap_region["new_cate"] == "Surveillance continue") &\
(recap_region["region"] == 'IDF'), 'taux_occ']
if to_reg.shape[0] == 1 :
if ~np.isnan(to_reg.iloc[0]):
to_reg = int(to_reg.iloc[0] * 100)
else:
to_reg = ""
else:
to_reg = ""
shape = slide.shapes[id_title_table]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Liste des établissements en Île-de-France par taux d’occupation au {PRINT_PPT_DATETIME}"
# ----------------- Tableau -------------
tmp = df[(df["region"] == "IDF")]
tmp = tmp[tmp["new_cate"] == "Réanimation"]
tmp_frais = tmp[tmp["dispo_maj_fraicheur"] <= 24]
tmp_frais = tmp_frais[tmp_frais['lit_capacite'] >= tmp_frais['lit_dispo']]
recap = tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"]\
.sum().reset_index(name = 'lit_capacite_tot')
recap_frais = tmp_frais.groupby(["finess", "etablissement", "departement"], as_index = False)\
[["lit_capacite", 'lit_dispo']].sum()
recap = recap.merge(recap_frais, how = 'left', on = ["finess", "etablissement", "departement"])
fraicheur = (tmp_frais.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum() \
/ tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum())\
.reset_index(name = 'fraicheur')
recap = recap.merge(fraicheur, how = 'left', on = ["finess", "etablissement", "departement"])
recap["taux_occ"] = ((recap["lit_capacite"] - recap["lit_dispo"])\
/ recap["lit_capacite"]).round(2)
recap.loc[recap["fraicheur"].isnull(), "fraicheur"] = 0
recap = recap.merge(loca_finess, on = "finess", how = "left")
recap = recap[recap["fraicheur"] >= 0.7]
capa_median = recap["lit_capacite"].median()
recap = recap[recap["lit_capacite"] >= capa_median]
top10 = recap.nlargest(10,[ "taux_occ", "lit_capacite"]).\
sort_values(['taux_occ', 'lit_capacite'], ascending = False)
top10 = top10.reset_index(drop = True)
top10 = top10[~top10["taux_occ"].isnull()]
top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
top10.loc[top10["ville"].isnull(), "ville"] = "NULL"
# top10["etablissement"] = [row["etablissement"][0:53] for i, row in top10.iterrows()]
# top10["ville"] = [row["ville"][0:53] for i, row in top10.iterrows()]
pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
table = pl_table.table
table.columns[0].width = Cm(7.80)
table.columns[1].width = Cm(4.69)
table.columns[2].width = Cm(2.46)
table.columns[3].width = Cm(2.46)
for i in range(0, 11):
for j in range(0, 4):
cond = [j == 0, j == 1, j == 2, j == 3]
choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
col = str(np.select(cond, choices))
label_col = str(np.select(cond, choices2))
if i == 0:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(183, 184, 217)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = label_col
paragraph.font.size = Pt(7)
elif i <= top10.shape[0]:
text = top10.loc[i - 1, col]
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = text
font_size = 6 if len(text) > 50 else 7
paragraph.font.size = Pt(font_size)
if (j == 2) | (j == 3):
paragraph.alignment = PP_ALIGN.RIGHT
else:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = ""
paragraph.font.size = Pt(7)
table.rows[i].height = Cm(0.64)
#%% ------------------ Slide IDF USC -----------
slide = prs.slides[3]
name_map1 = 'Espace réservé pour une image\xa0 8'
name_map2 = 'Espace réservé pour une image\xa0 7'
name_barplot = 'Espace réservé pour une image\xa0 2'
name_source1 = 'Espace réservé du texte 1'
name_source2 = 'Espace réservé du texte 5'
name_legend = 'Espace réservé pour une image\xa0 11'
name_legend_title = 'Espace réservé du texte 9'
name_to1 = 'Espace réservé du texte 10'
name_to2 = 'Espace réservé du texte 11'
name_to3 = 'Espace réservé du texte 12'
name_to4 = 'Espace réservé du texte 13'
name_to5 = 'Espace réservé du texte 14'
title_map = 'Espace réservé du texte 6'
title_barplot = 'Espace réservé du texte 4'
name_table = 'Espace réservé du tableau 16'
name_title_table = 'Espace réservé du texte 3'
i = 0
for shape in slide.shapes:
if shape.name == name_map1:
id_map1 = shape.placeholder_format.idx
elif shape.name == name_map2:
id_map2 = shape.placeholder_format.idx
elif shape.name == name_barplot:
id_barplot = shape.placeholder_format.idx
elif shape.name == name_source1:
id_source1 = i
elif shape.name == name_source2:
id_source2 = i
elif shape.name == name_colorbar:
id_colorbar = i
elif shape.name == title_map:
id_title_map = i
elif shape.name == title_barplot:
id_title_barplot = i
elif shape.name == name_legend:
id_legend = shape.placeholder_format.idx
elif shape.name == name_legend_title:
id_legend_title = i
elif shape.name == name_to1:
id_to1 = i
elif shape.name == name_to2:
id_to2 = i
elif shape.name == name_to3:
id_to3 = i
elif shape.name == name_to4:
id_to4 = i
elif shape.name == name_to5:
id_to5 = i
elif shape.name == name_table:
id_table = i
elif shape.name == name_title_table:
id_title_table = i
i = i + 1
# -------------- Graphiques -----------------
slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_usc_IDF.png')
slide.placeholders[id_map1].insert_picture(f'{pictures_dir}/map_usc_region_IDF.png')
slide.placeholders[id_map2].insert_picture(f'{pictures_dir}/map_usc_region_IDF_pcr.png')
slide.placeholders[id_legend].insert_picture(f'{metadata_dir}/plots/legend_color.png')
# ------------- Zones de texte -------------
shape = slide.shapes[id_source1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_source2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_title_map]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Situation en Île-de-France au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Île-de-France du {start_date} au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_legend_title]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "Taux d'occupation à date"
shape = slide.shapes[id_to1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ">= 95%"
shape = slide.shapes[id_to2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[90%; 95%["
shape = slide.shapes[id_to3]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[85%; 90%["
shape = slide.shapes[id_to4]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "[80%; 85%["
shape = slide.shapes[id_to5]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = "< 80%"
to_reg = recap_region.loc[(recap_region["new_cate"] == "Surveillance continue") &\
(recap_region["region"] == 'IDF'), 'taux_occ']
if to_reg.shape[0] == 1 :
if ~np.isnan(to_reg.iloc[0]):
to_reg = int(to_reg.iloc[0] * 100)
else:
to_reg = ""
else:
to_reg = ""
shape = slide.shapes[id_title_table]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Liste des établissements en Île-de-France par taux d’occupation au {PRINT_PPT_DATETIME}"
# ----------------- Tableau -------------
tmp = df[(df["region"] == "IDF")]
tmp = tmp[tmp["new_cate"] == "Surveillance continue"]
tmp_frais = tmp[tmp["dispo_maj_fraicheur"] <= 24]
tmp_frais = tmp_frais[tmp_frais['lit_capacite'] >= tmp_frais['lit_dispo']]
recap = tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"]\
.sum().reset_index(name = 'lit_capacite_tot')
recap_frais = tmp_frais.groupby(["finess", "etablissement", "departement"], as_index = False)\
[["lit_capacite", 'lit_dispo']].sum()
recap = recap.merge(recap_frais, how = 'left', on = ["finess", "etablissement", "departement"])
fraicheur = (tmp_frais.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum() \
/ tmp.groupby(["finess", "etablissement", "departement"])["lit_capacite"].sum())\
.reset_index(name = 'fraicheur')
recap = recap.merge(fraicheur, how = 'left', on = ["finess", "etablissement", "departement"])
recap["taux_occ"] = ((recap["lit_capacite"] - recap["lit_dispo"])\
/ recap["lit_capacite"]).round(2)
recap.loc[recap["fraicheur"].isnull(), "fraicheur"] = 0
recap = recap.merge(loca_finess, on = "finess", how = "left")
recap = recap[recap["fraicheur"] >= 0.7]
capa_median = recap["lit_capacite"].median()
recap = recap[recap["lit_capacite"] >= capa_median]
top10 = recap.nlargest(10, ["taux_occ", "lit_capacite"]).\
sort_values(['taux_occ', 'lit_capacite'], ascending = False)
top10 = top10.reset_index(drop = True)
top10 = top10[~top10["taux_occ"].isnull()]
top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
top10.loc[top10["ville"].isnull(), "ville"] = "NULL"
# top10["etablissement"] = [row["etablissement"][0:53] for i, row in top10.iterrows()]
# top10["ville"] = [row["ville"][0:53] for i, row in top10.iterrows()]
pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
table = pl_table.table
table.columns[0].width = Cm(7.80)
table.columns[1].width = Cm(4.69)
table.columns[2].width = Cm(2.46)
table.columns[3].width = Cm(2.46)
for i in range(0, 11):
for j in range(0, 4):
cond = [j == 0, j == 1, j == 2, j == 3]
choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
col = str(np.select(cond, choices))
label_col = str(np.select(cond, choices2))
if i == 0:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(206, 147, 126)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = label_col
paragraph.font.size = Pt(7)
elif i <= top10.shape[0]:
text = top10.loc[i - 1, col]
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = text
font_size = 6 if len(text) > 50 else 7
paragraph.font.size = Pt(font_size)
if (j == 2) | (j == 3):
paragraph.alignment = PP_ALIGN.RIGHT
else:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = ""
paragraph.font.size = Pt(7)
table.rows[i].height = Cm(0.64)
#%% ------------------ Slides region -----------
ordre_regions = np.array(ordre_regions)[~np.isin(ordre_regions, ['Île-de-France', 'Mayotte'])]
for reg in ordre_regions:
tri = list(mapping_regions.keys())[list(mapping_regions.values()).index(reg)]
# --------------- Réanimation -----------------
slide_layout = prs.slide_layouts[4]
slide = prs.slides.add_slide(slide_layout)
name_map = 'Picture Placeholder 3'
name_barplot = 'Picture Placeholder 5'
name_region = 'Text Placeholder 2'
name_source1 = 'Text Placeholder 4'
name_source2 = 'Text Placeholder 8'
title_map = 'Text Placeholder 1'
title_barplot = 'Text Placeholder 7'
name_table = 'Table Placeholder 9'
name_title_table = 'Text Placeholder 6'
name_to = 'Text Placeholder 10'
i = 0
for shape in slide.shapes:
if shape.name == name_map:
id_map = shape.placeholder_format.idx
elif shape.name == name_barplot:
id_barplot = shape.placeholder_format.idx
elif shape.name == name_source1:
id_source1 = i
elif shape.name == name_source2:
id_source2 = i
elif shape.name == name_region:
id_region = i
elif shape.name == title_map:
id_title_map = i
elif shape.name == title_barplot:
id_title_barplot = i
elif shape.name == name_table:
id_table = i
elif shape.name == name_title_table:
id_title_table = i
elif shape.name == name_to:
id_to = i
i = i + 1
# ------------- Graphiques ----------------
if tri!="MAR" :
slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_rea_{tri}.png')
slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_rea_region_{tri}.png')
# ------------- Zones de texte -------------
shape = slide.shapes[id_source1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_source2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de réanimation adulte (hors unités de réanimation grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_region]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"{reg.upper()}"
shape = slide.shapes[id_title_map]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Situation en {reg} au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"{reg} du {start_date} au {PRINT_PPT_DATETIME}"
rea_reg = recap_region[(recap_region["new_cate"] == "Réanimation") &\
(recap_region["region"] == tri)]
if rea_reg.shape[0] != 0:
to_reg = rea_reg["taux_occ"].iloc[0]
if ~np.isnan(to_reg):
to_reg = int(to_reg * 100)
else:
to_reg = ""
fraicheur = rea_reg["fraicheur"].iloc[0]
else:
to_reg = ""
fraicheur = np.nan
shape = slide.shapes[id_to]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Taux d’occupation de la région à date : {to_reg}%"
if (fraicheur <= 0.7) | (np.isnan(fraicheur)):
run.font.color.rgb = RGBColor(166, 166, 166)
if tri == "REU":
word = 'à'
else:
word = 'en'
shape = slide.shapes[id_title_table]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Liste des établissements {word} {reg} par taux d’occupation au {PRINT_PPT_DATETIME}"
# ----------------- Tableau -------------
tmp = df[(df["region"] == tri)]
tmp_rea = tmp[tmp["new_cate"] == "Réanimation"]
tmp_usc = tmp[tmp["new_cate"] == "Surveillance continue"]
if np.isin(tri, ["GUY", "REU", "MAR"]):
tmp_rea_frais = tmp_rea[tmp_rea["dispo_maj_fraicheur"] <= 48]
tmp_usc_frais = tmp_usc[tmp_usc["dispo_maj_fraicheur"] <= 48]
else:
tmp_rea_frais = tmp_rea[tmp_rea["dispo_maj_fraicheur"] <= 24]
tmp_usc_frais = tmp_usc[tmp_usc["dispo_maj_fraicheur"] <= 24]
tmp_rea_frais = tmp_rea_frais[tmp_rea_frais['lit_capacite'] >= tmp_rea_frais['lit_dispo']]
tmp_usc_frais = tmp_usc_frais[tmp_usc_frais['lit_capacite'] >= tmp_usc_frais['lit_dispo']]
recap_rea = tmp_rea.groupby(["finess", "etablissement"])["lit_capacite"]\
.sum().reset_index(name = 'lit_capacite_tot')
recap_frais = tmp_rea_frais.groupby(["finess", "etablissement"], as_index = False)\
[["lit_capacite", 'lit_dispo']].sum()
recap_rea = recap_rea.merge(recap_frais, how = 'left', on = ["finess", "etablissement"])
fraicheur = (tmp_rea_frais.groupby(["finess", "etablissement"])["lit_capacite"].sum() \
/ tmp_rea.groupby(["finess", "etablissement"])["lit_capacite"].sum())\
.reset_index(name = 'fraicheur')
recap_rea = recap_rea.merge(fraicheur, how = 'left', on = ["finess", "etablissement"])
recap_rea["taux_occ"] = ((recap_rea["lit_capacite"] - recap_rea["lit_dispo"])\
/ recap_rea["lit_capacite"]).round(2)
recap_rea.loc[recap_rea["fraicheur"].isnull(), "fraicheur"] = 0
recap_rea = recap_rea.merge(loca_finess, on = "finess", how = "left")
recap_rea = recap_rea[recap_rea["fraicheur"] >= 0.7]
capa_median = recap_rea["lit_capacite"].median()
recap_rea = recap_rea[recap_rea["lit_capacite"] >= capa_median]
top10 = recap_rea.nlargest(10,[ "taux_occ", "lit_capacite"]).\
sort_values(['taux_occ', 'lit_capacite'], ascending = False)
top10 = top10.reset_index(drop = True)
top10 = top10[~top10["taux_occ"].isnull()]
top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
top10.loc[top10["ville"].isnull(), "ville"] = "NULL"
pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
table = pl_table.table
table.columns[0].width = Cm(7.80)
table.columns[1].width = Cm(4.69)
table.columns[2].width = Cm(2.46)
table.columns[3].width = Cm(2.46)
for i in range(0, 11):
for j in range(0, 4):
cond = [j == 0, j == 1, j == 2, j == 3]
choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
col = str(np.select(cond, choices))
label_col = str(np.select(cond, choices2))
if i == 0:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(183, 184, 217)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = label_col
paragraph.font.size = Pt(7)
elif i <= top10.shape[0]:
text = top10.loc[i - 1, col]
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = text
font_size = 6 if len(text) > 50 else 7
paragraph.font.size = Pt(font_size)
if (j == 2) | (j == 3):
paragraph.alignment = PP_ALIGN.RIGHT
else:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = ""
paragraph.font.size = Pt(7)
table.rows[i].height = Cm(0.64)
# --------------- Soins continues -----------------
slide_layout = prs.slide_layouts[5]
slide = prs.slides.add_slide(slide_layout)
#------------- Graphiques ----------------
if tri!="MAR" :
slide.placeholders[id_barplot].insert_picture(f'{pictures_dir}/barplot_evol_usc_{tri}.png')
slide.placeholders[id_map].insert_picture(f'{pictures_dir}/map_usc_region_{tri}.png')
# ------------- Zones de texte -------------
shape = slide.shapes[id_source1]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_source2]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Source : ROR, {today_jmA}; Champ : Lits de surveillance continue adulte (hors unités grands brûlés) / toutes causes confondues"
shape = slide.shapes[id_region]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"{reg.upper()}"
shape = slide.shapes[id_title_map]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Situation en {reg} au {PRINT_PPT_DATETIME}"
shape = slide.shapes[id_title_barplot]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"{reg} du {start_date} au {PRINT_PPT_DATETIME}"
usc_reg = recap_region[(recap_region["new_cate"] == "Surveillance continue") &\
(recap_region["region"] == tri)]
if usc_reg.shape[0] != 0:
to_reg = usc_reg["taux_occ"].iloc[0]
if ~np.isnan(to_reg):
to_reg = int(to_reg * 100)
else:
to_reg = ""
fraicheur = usc_reg["fraicheur"].iloc[0]
else:
to_reg = ""
fraicheur = np.nan
shape = slide.shapes[id_to]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Taux d’occupation de la région à date : {to_reg}%"
if fraicheur <= 0.7:
run.font.color.rgb = RGBColor(166, 166, 166)
if tri == "REU":
word = 'à'
else:
word = 'en'
shape = slide.shapes[id_title_table]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = f"Liste des établissements {word} {reg} par taux d’occupation au {PRINT_PPT_DATETIME}"
# ----------------- Tableau -------------
recap_usc = tmp_usc.groupby(["finess", "etablissement"])["lit_capacite"]\
.sum().reset_index(name = 'lit_capacite_tot')
recap_frais = tmp_usc_frais.groupby(["finess", "etablissement"], as_index = False)\
[["lit_capacite", 'lit_dispo']].sum()
recap_usc = recap_usc.merge(recap_frais, how = 'left', on = ["finess", "etablissement"])
fraicheur = (tmp_usc_frais.groupby(["finess", "etablissement"])["lit_capacite"].sum() \
/ tmp_usc.groupby(["finess", "etablissement"])["lit_capacite"].sum())\
.reset_index(name = 'fraicheur')
recap_usc = recap_usc.merge(fraicheur, how = 'left', on = ["finess", "etablissement"])
recap_usc["taux_occ"] = ((recap_usc["lit_capacite"] - recap_usc["lit_dispo"])\
/ recap_usc["lit_capacite"]).round(2)
recap_usc.loc[recap_usc["fraicheur"].isnull(), "fraicheur"] = 0
recap_usc = recap_usc.merge(loca_finess, on = "finess", how = "left")
recap_usc = recap_usc[recap_usc["fraicheur"] >= 0.7]
capa_median = recap_usc["lit_capacite"].median()
recap_usc = recap_usc[recap_usc["lit_capacite"] >= capa_median]
top10 = recap_usc.nlargest(10,[ "taux_occ", "lit_capacite"]).\
sort_values(['taux_occ', 'lit_capacite'], ascending = False)
top10 = top10.reset_index(drop = True)
top10 = top10[~top10["taux_occ"].isnull()]
top10["taux_occ"] = (100 * top10["taux_occ"]).astype(int)
top10["taux_occ"] = [f"{top10.loc[i, 'taux_occ']}%" for i in top10.index]
top10["lit_capacite"] = top10["lit_capacite"].astype(int).astype(str)
top10.loc[top10["etablissement"].isnull(), "etablissement"] = "NULL"
top10.loc[top10["ville"].isnull(), "ville"] = "NULL"
pl_table = slide.shapes[id_table].insert_table(rows = 11, cols = 4)
table = pl_table.table
table.columns[0].width = Cm(7.80)
table.columns[1].width = Cm(4.69)
table.columns[2].width = Cm(2.46)
table.columns[3].width = Cm(2.46)
for i in range(0, 11):
for j in range(0, 4):
cond = [j == 0, j == 1, j == 2, j == 3]
choices = ["etablissement", "ville", "taux_occ", "lit_capacite"]
choices2 = ["Raison sociale", "Commune", "Taux d'occupation", "Lits installés à date"]
col = str(np.select(cond, choices))
label_col = str(np.select(cond, choices2))
if i == 0:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(206, 147, 126)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = label_col
paragraph.font.size = Pt(7)
elif i <= top10.shape[0]:
text = top10.loc[i - 1, col]
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = text
font_size = 6 if len(text) > 50 else 7
paragraph.font.size = Pt(font_size)
if (j == 2) | (j == 3):
paragraph.alignment = PP_ALIGN.RIGHT
else:
table.cell(i, j).fill.solid()
table.cell(i, j).fill.fore_color.rgb = RGBColor(255, 255, 255)
cell = table.rows[i].cells[j]
paragraph = cell.text_frame.paragraphs[0]
p = cell.text_frame.paragraphs[0]
run = p.add_run()
run.text = ""
paragraph.font.size = Pt(7)
table.rows[i].height = Cm(0.64)
# %% -------------------- Export ---------------------------
if os.path.exists(f"{diffusion_dir}/{PREV_FILE_DATE}_TDB_Situation_hospitalière_bulletin_quotidien.pptx"):
os.remove(f"{diffusion_dir}/{PREV_FILE_DATE}_TDB_Situation_hospitalière_bulletin_quotidien.pptx")
prs.save(f'{diffusion_dir}/{FILE_DATE}_TDB_Situation_hospitalière_bulletin_quotidien.pptx')
logger.success("Dashboard powerpoint enregistré")