Export to Excel

dashboard.export_to_excel

Functions

export_to_excel(env, date)

Exporte des données hospitalières vers un fichier Excel, en se basant sur les données extraites d'une base de données et de fichiers CSV.

Cette fonction crée plusieurs feuilles dans un fichier Excel, y compris des résumés par région, département et total, ainsi que les lits de soins critiques brûlés. Elle gère également les cas d'anomalies dans les données tout en fournissant des détails sur la qualité des données.

Paramètres :

env : object L'environnement de connexion nécessaire pour interagir avec la base de données.

str

La date de l'extraction des données au format 'YYYY-MM-DD'.

Processus :
  1. Chargement des données à partir de fichiers CSV et d'une base de données.
  2. Génération des différentes feuilles de calcul dans le fichier Excel.
  3. Calculs des totaux et gestion des anomalie dans les données.
  4. Sauvegarde du fichier Excel à l'emplacement spécifié et création d'un fichier de qualité des données.
Exceptions :
  • IOException : Si le fichier Excel ou les fichiers CSV ne peuvent pas être ouverts ou enregistrés.
  • ValueError : Dans le cas où les données extraites ne respectent pas les formats ou types attendus.
Source code in dashboard/export_to_excel.py
def export_to_excel(env:str, date:str) -> None :


    """
    Exporte des données hospitalières vers un fichier Excel, en se basant sur les données 
    extraites d'une base de données et de fichiers CSV. 

    Cette fonction crée plusieurs feuilles dans un fichier Excel, y compris des résumés 
    par région, département et total, ainsi que les lits de soins critiques brûlés. 
    Elle gère également les cas d'anomalies dans les données tout en fournissant 
    des détails sur la qualité des données.

    Paramètres :
    ----------
    env : object
        L'environnement de connexion nécessaire pour interagir avec la base de données.

    date : str
        La date de l'extraction des données au format 'YYYY-MM-DD'.

    Processus :
    ----------
    1. Chargement des données à partir de fichiers CSV et d'une base de données.
    2. Génération des différentes feuilles de calcul dans le fichier Excel.
    3. Calculs des totaux et gestion des anomalie dans les données.
    4. Sauvegarde du fichier Excel à l'emplacement spécifié et création d'un 
       fichier de qualité des données.

    Exceptions :
    ------------
    - IOException : Si le fichier Excel ou les fichiers CSV ne peuvent pas être 
      ouverts ou enregistrés.
    - ValueError : Dans le cas où les données extraites ne respectent pas 
      les formats ou types attendus.

    """
    # Création d'une instance par défaut pour exposer les constantes
    _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
    # On exporte le mapping des mois sous le nom "month" ou "month_const"
    month                = _dt_manager.MONTH_NAMES  # ou utilisez "month_const" si vous préférez
    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



    #%% ------------------ Importation des csv ----------------------
    xfile = openpyxl.load_workbook(f"{metadata_dir}/empty_TDB tensions hospitalières_ROR.xlsx")
    recap_dep = pd.read_csv(f"{csv_dir}/recap_dep.csv", dtype = {"departement" : "str"})
    recap_region = pd.read_csv(f"{csv_dir}/recap_region.csv")
    recap_tot = pd.read_csv(f"{csv_dir}/recap_tot.csv")
    recap_brules = pd.read_csv(f"{csv_dir}/recap_brules.csv")
    anomalie = pd.read_csv(f"{csv_dir}/anomalie.csv")


    #recap_dep["departement"] = ["{:02d}".format(dep) for dep in recap_dep["departement"]]
    #%% ------------------ Sheet 1  ---------------------------------

    # ------------- Regions hors IDF ------------------------------
    recap_region["region_lib"] = [mapping_regions[i] for i in recap_region["region"]]
    sheet = xfile.get_sheet_by_name('Lits-SoinsCritiques-NAT')


    sheet.cell(row = 1, column = 1,
            value = f"{PRINT_TODAY} ({hour_exec}h) - Situation hospitalière - France")

    sheet.cell(row = 4, column = 1,
            value = f"Source : ROR ({today_jmA})")

    sheet.cell(row = 14, column = 1,
            value = f"Données sur les lits disponibles à date : extraction du ROR à {hour_exec}h chaque jour.")


    for cat in recap_region["new_cate"].unique():
        i, j = pos_regions[cat]
        #worksheet.set_column(0, 5, 50)
        tmp_cate = recap_region[recap_region["new_cate"] == cat].reset_index(drop = True)
        for new_reg in np.array(REGIONS)[~np.isin(REGIONS, tmp_cate["region"].unique())]:
            #tmp_cate = tmp_cate.append(dict(region_lib = mapping_regions[new_reg]), ignore_index=True)
            new_row = pd.DataFrame([{"region_lib": mapping_regions[new_reg]}])
            tmp_cate = pd.concat([tmp_cate, new_row], ignore_index=True)
        tmp_cate.index = tmp_cate["region_lib"]
        tmp_cate = tmp_cate.loc[ordre_regions]
        for reg in tmp_cate["region_lib"].unique():
            tmp = tmp_cate[tmp_cate["region_lib"] == reg]
            for k, row in tmp.iterrows():
                sheet.cell(row = i, column = j, value = row["lit_capacite_tot"])
                sheet.cell(row = i, column = j + 1, value = row["fraicheur"])
                sheet.cell(row = i, column = j + 2, value = row["lit_dispo"])
                sheet.cell(row = i, column = j + 3, value = row["taux_occ"])


            i = i + 1

    # ----------------- Total regions -------------------------------
    for cat in recap_tot["new_cate"].unique():
        [i, j] = pos_tot[cat]
        tmp_cate = recap_tot[recap_tot["new_cate"] == cat].reset_index(drop = True)
        sheet.cell(row = i, column = j, value = tmp_cate["lit_capacite_tot"].iloc[0])
        sheet.cell(row = i, column = j + 1, value = tmp_cate["fraicheur"].iloc[0])
        sheet.cell(row = i, column = j + 2, value = tmp_cate["lit_dispo"].iloc[0])
        sheet.cell(row = i, column = j + 3, value = tmp_cate["taux_occ"].iloc[0])

    # ------------------ GUA + MAY -----------------
    # sheet.cell(row = 31, column = 2, value = "ND")
    sheet.cell(row = 35, column = 2, value = "ND")



    #%% ------------------ Sheet 2  ---------------------------------
    sheet2 = xfile.get_sheet_by_name('Lits-SoinsCritiques-DEP')
    sheet2.cell(row = 1, column = 2,
            value = f"{PRINT_TODAY} ({hour_exec}h) - Situation hospitalière - France")
    sheet2.cell(row = 4, column = 1,
            value = f"Source : ROR ({today_jmA})")
    sheet2.cell(row = 14, column = 1,
            value = f"Données sur les lits disponibles à date : extraction du ROR à {hour_exec}h chaque jour.")

    # ------------- Departements ------------------------------
    recap_dep["dep_lib"] = mapping_departement.loc[recap_dep["departement"], "nom_departement"].to_list()


    for cat in recap_dep["new_cate"].unique():
        i, j = pos_dep[cat]
        #worksheet.set_column(0, 5, 50)
        tmp_cate = recap_dep[recap_dep["new_cate"] == cat].reset_index(drop = True)
        for new_dep in np.array(ordre_dep)[~np.isin(ordre_dep, tmp_cate["dep_lib"].unique())]:
            # if mapping_departement.loc[mapping_departement["nom_departement"] == new_dep, "nom_region"].iloc[0]\
            #     != "Île-de-France":
            #tmp_cate = tmp_cate.append(dict(dep_lib = new_dep), ignore_index=True)
            new_row = pd.DataFrame([{"dep_lib": new_dep}])
            tmp_cate = pd.concat([tmp_cate, new_row], ignore_index=True)
        tmp_cate.index = tmp_cate["dep_lib"]
        tmp_cate = tmp_cate.loc[ordre_dep]
        for dep in tmp_cate["dep_lib"].unique():
            tmp = tmp_cate[tmp_cate["dep_lib"] == dep]
            sheet2.cell(row = i, column = j, value = tmp["lit_capacite_tot"].iloc[0])
            sheet2.cell(row = i, column = j + 1, value = tmp["fraicheur"].iloc[0])
            sheet2.cell(row = i, column = j + 2, value = tmp["lit_dispo"].iloc[0])
            sheet2.cell(row = i, column = j + 3, value = tmp["taux_occ"].iloc[0])
            i = i + 1

    # ----------------- Total Département -------------------------------
    for cat in recap_tot["new_cate"].unique():
        [i, j] = pos_tot_dep[cat]
        tmp_cate = recap_tot[recap_tot["new_cate"] == cat].reset_index(drop = True)
        sheet2.cell(row = i, column = j, value = tmp_cate["lit_capacite_tot"].iloc[0])
        sheet2.cell(row = i, column = j + 1, value = tmp_cate["fraicheur"].iloc[0])
        sheet2.cell(row = i, column = j + 2, value = tmp_cate["lit_dispo"].iloc[0])
        sheet2.cell(row = i, column = j + 3, value = tmp_cate["taux_occ"].iloc[0])


    # ------------------ GUA + MAY -----------------
    # sheet2.cell(row = 114, column = 3, value = "ND")
    sheet2.cell(row = 118, column = 3, value = "ND")

    #%% ------------------ Sheet 3  ---------------------------------
    sheet = xfile.get_sheet_by_name('Lits-brûlés')
    sheet.cell(row = 1, column = 5,
            value = f"{PRINT_TODAY} ({hour_exec}h) - Situation hospitalière - France  - Lits Grands brûlés")
    sheet.cell(row = 4, column = 1,
            value = f"Source : ROR ({today_jmA})")
    sheet.cell(row = 5, column = 1,
            value = f"Données sur les lits disponibles à date : extraction du ROR à {hour_exec}h chaque jour.")

    # ------------- Etablissements ------------------------------
    for cat in recap_brules["new_cate"].unique():
        i, j = pos_brules[cat]
        #worksheet.set_column(0, 5, 50)
        tmp_cate = recap_brules[recap_brules["new_cate"] == cat].reset_index(drop = True)
        tmp_cate = tmp_cate.sort_values('ordre')

        for k, row in tmp_cate.iterrows():
            sheet.cell(row = i, column = j, value = row["lit_capacite_tot"])
            sheet.cell(row = i, column = j + 1, value = row["fraicheur"])
            sheet.cell(row = i, column = j + 2, value = row["lit_dispo"])
            sheet.cell(row = i, column = j + 3, value = row["taux_occ"])
            i = i + 1

    # ----------------- Total  -------------------------------
    recap_tot_brules = recap_brules.groupby('new_cate', as_index = False)[['lit_capacite_tot', 'lit_capacite', 'lit_dispo']].sum()
    recap_tot_brules["taux_occ"] = ((recap_tot_brules["lit_capacite"] - recap_tot_brules["lit_dispo"]) \
        / recap_tot_brules["lit_capacite"]).round(2)
    recap_tot_brules['fraicheur'] = (recap_tot_brules["lit_capacite"] / recap_tot_brules["lit_capacite_tot"]).round(2)

    for cat in recap_tot_brules["new_cate"].unique():
        [i, j] = pos_tot_brules[cat]
        tmp_cate = recap_tot_brules[recap_tot_brules["new_cate"] == cat].reset_index(drop = True)
        sheet.cell(row = i, column = j, value = tmp_cate["lit_capacite_tot"].iloc[0])
        sheet.cell(row = i, column = j + 1, value = tmp_cate["fraicheur"].iloc[0])
        sheet.cell(row = i, column = j + 2, value = tmp_cate["lit_dispo"].iloc[0])
        sheet.cell(row = i, column = j + 3, value = tmp_cate["taux_occ"].iloc[0])


    #%% ------------------ Sheet 4  ---------------------------------
    recap_region["region_lib"] = [mapping_regions[i] for i in recap_region["region"]]
    sheet = xfile.get_sheet_by_name('Evolution_Capacites')


    df = req2table(f"SELECT * FROM table_union WHERE capacite_extract_datetime::date = '{prev_day_Ymd_long}' AND h_exec='{hour_exec}'",
                    env)
    print(df.shape)
    if df.shape[0] == 0:
        req = f"SELECT MAX(capacite_extract_datetime::date) FROM table_union WHERE capacite_extract_datetime < '{prev_day_Ymd_long}' AND h_exec='{hour_exec}'"
        prev_day_Ymd_short = req2table(req, env)['max'].iloc[0]
        df = req2table(f"SELECT * FROM table_union WHERE capacite_extract_datetime::date = '{prev_day_Ymd_long}' AND h_exec='{hour_exec}'", env)

    PRINT_PREV_DAY = f"{prev_day_Ymd_short.strftime('%d')} {month[prev_day_Ymd_short.strftime('%m')]} {prev_day_Ymd_short.strftime('%Y')}"

    df["date"] = pd.to_datetime(df["capacite_extract_datetime"].dt.date)
    df = df[~df["lit_capacite"].isnull()]
    df = df[(df["region"] != "IDF") | (df["lit_capacite"] != 100)]
    # df = df[(df["region"] != "IDF") | (~df["discipline"].isin(['Soins critiques COVID +', 'Soins critiques COVID -',
    #                                                            'USC COVID +', 'USC COVID -']))]
    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"] == "rea_brules", "new_cate"] = "Réanimation brûlés"
    df.loc[df["sous_categorie_lit"] == "usc_brules", "new_cate"] = "USC brûlés"
    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"

    # aggrégation niveau region
    df_region = df
    tmp = df_region.groupby(["region", "new_cate"])["lit_capacite"].sum().reset_index(name = "lit_capacite_prec")
    recap_region = recap_region.merge(tmp, on = ["new_cate", "region"], how = "left")

    # Total
    tmp = df_region.groupby(["new_cate"])["lit_capacite"].sum().reset_index(name = "lit_capacite_prec")
    recap_tot = recap_tot.merge(tmp, on = ["new_cate"], how = "left")

    sheet.cell(row = 1, column = 5,
                value = f"{PRINT_TODAY} ({hour_exec}h) - Situation hospitalière - France")


    sheet.cell(row = 3, column = 1,
                value = f"Evolution des capacités en lits installés entre le {PRINT_PREV_DAY} et le {PRINT_TODAY}")

    sheet.cell(row = 4, column = 1,
                value = f"Source : ROR ({today_jmA})")


    for j in [2, 4, 6, 8, 10, 12, 14]:
        sheet.cell(row = 11, column = j + 1, value = today.strftime("%d-%m-%Y"))
        sheet.cell(row = 11, column = j, value = prev_day_Ymd_short.strftime("%d-%m-%Y"))



    for cat in recap_region["new_cate"].unique():
        i, j = pos_regions_evolution[cat]
        #worksheet.set_column(0, 5, 50)

        tmp_cate = recap_region[recap_region["new_cate"] == cat].reset_index(drop = True)
        for new_reg in np.array(REGIONS)[~np.isin(REGIONS, tmp_cate["region"].unique())]:
            #tmp_cate = tmp_cate.append(dict(region_lib = mapping_regions[new_reg]), ignore_index=True)
            new_row = pd.DataFrame([{"region_lib": mapping_regions[new_reg]}])
            tmp_cate = pd.concat([tmp_cate, new_row], ignore_index=True)

        tmp_cate.index = tmp_cate["region_lib"]
        tmp_cate = tmp_cate.loc[ordre_regions]
        for reg in tmp_cate["region_lib"].unique():
            tmp = tmp_cate[tmp_cate["region_lib"] == reg]

            for k, row in tmp.iterrows():
                sheet.cell(row = i, column = j + 1, value = row["lit_capacite_tot"])
                sheet.cell(row = i, column = j, value = row["lit_capacite_prec"])

            i = i + 1

    # ----------------- Total regions -------------------------------
    for cat in recap_tot["new_cate"].unique():
        [i, j] = pos_tot_evolution[cat]
        tmp_cate = recap_tot[recap_tot["new_cate"] == cat].reset_index(drop = True)
        sheet.cell(row = i, column = j + 1, value = tmp_cate["lit_capacite_tot"].iloc[0])
        sheet.cell(row = i, column = j, value = tmp_cate["lit_capacite_prec"].iloc[0])

    # ------------------ GUA + MAY -----------------
    # sheet.cell(row = 25, column = 2, value = "ND")
    sheet.cell(row = 29, column = 2, value = "ND")


    #%% ----------------------- Export -------------------
    if os.path.exists(f"{DOSSIER_DIFFUSION}/{PREV_FILE_DATE}_TDB Situation hospitalières_ROR.xlsx"):
        os.remove(f"{DOSSIER_DIFFUSION}/{PREV_FILE_DATE}_TDB Situation hospitalières_ROR.xlsx")


    filename = f"{DOSSIER_DIFFUSION}/{FILE_DATE}_TDB Situation hospitalières_ROR.xlsx"
    xfile.save(filename)
    # upload_file("dgos-tension-hospitaliere-exported-files", filename, "TDB Situation hospitalières_ROR.xlsx")

    # Gestions des fichiers d'anomalies
    if os.path.exists(f"{DOSSIER_DIFFUSION}/{PREV_FILE_DATE}_anomalies.csv"):
        os.remove(f"{DOSSIER_DIFFUSION}/{PREV_FILE_DATE}_anomalies.csv")

    if os.path.exists(f"{DOSSIER_DIFFUSION}/{PREV_FILE_DATE}_anomalies_location.csv"):
        os.remove(f"{DOSSIER_DIFFUSION}/{PREV_FILE_DATE}_anomalies_location.csv")

    #%% ------------------ Qualité des données  ---------------------------------
    f = open(f"{DOSSIER_DIFFUSION}/qualité_données.txt", "w")
    f.writelines("Qualité des données : \n")
    # ------------- Lignes supprimés  ------------------------------
    capa_vide = anomalie[anomalie["type"] == "Capacité vide"]
    nb_capa_vide = capa_vide.shape[0]
    reg_capa_vide = capa_vide.groupby(["region"]).size().sort_values(ascending = False)

    idf_100 = anomalie[anomalie["type"] == "IDF capacité 100"]
    nb_idf_100 = idf_100.shape[0]

    text1 = f"* Suppression de {nb_capa_vide} lignes avec des capacités vides ("
    size =  1
    for reg in reg_capa_vide.index:
        if(size != reg_capa_vide.shape[0]):
            text1 = f"{text1}{reg_capa_vide[reg]} {reg}, "
        else:
            text1 = f"{text1}{reg_capa_vide[reg]} {reg})"
        size = size + 1

    text1 = f"{text1} + {nb_idf_100} lignes IDF avec capacité=100 \n"
    f.writelines(text1)
    # --------------------- capacité nulle et dispo fraîche non nulle --------------
    capa_nulle = anomalie[anomalie["type"] == "Capacité nulle et dispo non nulle"]
    nb_capa_nulle = capa_nulle.shape[0]
    reg_capa_nulle = capa_nulle["region"].unique()
    text2 = f"* Observation de {nb_capa_nulle} lignes avec capacité nulle et dispo fraîche non nulle ("
    size =  1
    for reg in reg_capa_nulle:
        if(size != len(reg_capa_nulle)):
            text2 = f"{text2}{reg}, "
        else:
            text2 = f"{text2}{reg})"
        size = size + 1
    f.writelines(text2 + " \n")

    # --------------------- dispo fraîche > capacité non nulle  --------------
    dispo_sup_cap = anomalie[anomalie["type"] == "Dispo supérieure capacité"]
    nb_dispo_sup_cap = dispo_sup_cap.shape[0]
    reg_dispo_sup_cap = dispo_sup_cap["region"].unique()
    text3 = f"* {nb_dispo_sup_cap} observations avec dispo fraîche > capacité non nulle ("
    size =  1
    for reg in reg_dispo_sup_cap:
        if(size != len(reg_dispo_sup_cap)):
            text3 = f"{text3}{reg}, "
        else:
            text3 = f"{text3}{reg})"
        size = size + 1

    f.writelines(text3 + " \n")
    f.close()