app.py

import panel as pn
import hvplot.pandas
import pandas as pd
import numpy as np
import duckdb as ddb
import geopandas as gpd
from datasets import load_dataset

data_files_csv = {"rp2020_logements_dict": "dictionnaire-variables-logemt-2020.csv", 
              "rp2020_individus_dict": "dictionnaire-variables-indcvi-2020.csv"}
dataset = load_dataset("alihmaou/DGVFR_RP2020", data_files=data_files_csv, sep=";")

df_dataset = dataset["rp2020_logements_dict"].to_pandas()
ddb.sql(f"""create or replace table ods_ins_rp2020_logements_dict as SELECT * FROM df_dataset""");

df_dataset = dataset["rp2020_individus_dict"].to_pandas()
ddb.sql(f"""create or replace table ods_ins_rp2020_individus_dict as SELECT * FROM df_dataset""");

#dataset = load_dataset("alihmaou/RP2020_LOGEMENTS_CSV")
#df_dataset = pd.DataFrame(dataset["RP2020_LOGEMT_csv.zip"])
#ddb.sql(f"""create or replace table ods_ins_rp2020_logements as SELECT * FROM df_dataset""");

dataset = load_dataset("alihmaou/AGR_RP2020_IND_DEPT")
df_dataset = pd.DataFrame(dataset['train'])
ddb.sql(f"""create or replace table selected_data_stats as SELECT * FROM df_dataset""");

def prepare_data( filtre_territoire="99", lib_territoire="", persistant_table_name="selected_data_stats_proportions"):
    ddb.sql(f"""create or replace table {persistant_table_name} as \
            select \
                '{lib_territoire}' lib_territoire,\
                '{filtre_territoire}' filtre_territoire,\
                LIB_VAR caracteristique, \
                LIB_MOD etat_caracteristique, \
                nb_individus,
                100 * nb_individus / (select max(nb_individus) from selected_data_stats)  AS pc_correspondance, \
                DENSE_RANK() over(partition by lib_var order by nb_individus desc) rang \
                from selected_data_stats
                where code_dept='{filtre_territoire}'""")
    return ddb.sql(f"select * from {persistant_table_name}").to_df()

## Initialisation des référence nationales
agr_stats_nationales = prepare_data(lib_territoire="NATIONAL",filtre_territoire="99",persistant_table_name="ds_national")


def analyse_comparaison_territoires(table_territoire = "selected_data_stats_proportions" , table_territoire_reference = "ds_national"):
    ddb.sql(f"""
            create or replace table agr_comparaison_territoires as (\
                select   a.lib_territoire, a.filtre_territoire, c.COD_VAR code_variable,a.variable, a.modalite, a.nb_individus, a.proportion_locale, a.proportion_reference, \
                DENSE_RANK() over (PARTITION by c.COD_VAR order by proportion_locale desc) rang_local,  \
                DENSE_RANK() over (PARTITION by c.COD_VAR order by proportion_reference desc) rang_reference \
                from ( \
                    select a.lib_territoire, a.filtre_territoire, COALESCE (a.caracteristique, b.caracteristique ) variable  , COALESCE (a.etat_caracteristique,b.etat_caracteristique ) modalite, \
                    a.nb_individus, a.pc_correspondance proportion_locale, b.pc_correspondance proportion_reference, \
                    from {table_territoire} a  \
                    full outer join {table_territoire_reference}  b on (a.caracteristique=b.caracteristique and a.etat_caracteristique = b.etat_caracteristique) \
                ) a  \
                left outer join ods_ins_rp2020_individus_dict c on (a.variable = c.lib_var and a.modalite=c.lib_mod) \
            ) order by 1,2,3,4,5 """)
    return ddb.sql("select * from agr_comparaison_territoires order by 3, 5").to_df()

def label_inside(plot):
    # Access the Bokeh plot from Holoviews
    p = hv.render(plot)
    # Adjust y-axis labels to be inside the plot
    p.yaxis.major_label_text_font_size = "10pt"  # Adjust font size if needed
    p.yaxis.major_label_standoff = -10  # Negative standoff to move labels inside
    p.yaxis.major_label_orientation = "horizontal"
    return p

def plot_analyse_histogramme(code_variable = "NPERR"):
    df = ddb.sql(f"""select 'Selection' territoire, code_variable, variable, modalite, proportion_locale pc_est from agr_comparaison_territoires where code_variable = '{code_variable}' \
                   union select 'National' territoire,code_variable, variable, modalite, proportion_reference pc_est from agr_comparaison_territoires where code_variable = '{code_variable}' 
                   order by 5 """).to_df()
    plot = df.hvplot.barh(
        x='modalite', 
        y="pc_est",
        legend=None,  # Disable legend
        height=400, 
        width=800,
        group_label=None,
        color='territoire',  # Assign a color based on the 'modalite' field
        cmap='Category20',  # Use a categorical color map
        hover_cols=['territoire', 'variable']  # Add more columns to hover tool
    )
    # Improve aesthetics
    plot.opts(
        xlabel="Modalité", 
        ylabel="Pourcentage de population estimé (%)", 
        tools=['hover'],  # Enable hover tool
        show_grid=True,   # Show grid
        fontscale=1.2,    # Increase font scale for better readability
        hooks=[lambda p: label_inside(p)]  # Custom hook for label positioning
    )
    return plot

def run_territoire_histo(filtre_territoire="95", lib_territoire="Val Oise", code_variable="TYPL"):
    prepare_data(filtre_territoire,lib_territoire)
    agr_comparaison_territoires = analyse_comparaison_territoires()
    return plot_analyse_histogramme(code_variable)

## Création d'une page Panel

# Listes de valeurs
options_vars = ddb.sql("select distinct  cod_var, lib_var  from ods_ins_rp2020_individus_dict order by 2").to_df()
options_vars_dict = dict(zip(options_vars["LIB_VAR"], options_vars["COD_VAR"]))

# Listes de valeurs
options_depts = ddb.sql("select distinct code_dept as lib_dept, code_dept  from selected_data_stats order by 2").to_df()
options_depts_dict = dict(zip(options_depts["lib_dept"], options_depts["code_dept"]))

# Widgets
variable_widget = pn.widgets.Select(name="code_variable", options=options_vars_dict)
#territoire_widget = pn.widgets.TextInput(name="filtre_territoire")
territoire_widget = pn.widgets.Select(name="filtre_territoire", options=options_depts_dict)
lib_territoire_widget = pn.widgets.TextInput(name="lib_territoire")

# Bindings
bound_plot_histogramme = pn.bind(run_territoire_histo, code_variable=variable_widget, filtre_territoire=territoire_widget, lib_territoire=lib_territoire_widget)


# Instanciation de l'app
rp2020_app = pn.Column(pn.Column(territoire_widget, variable_widget), pn.Column( bound_plot_histogramme))
rp2020_app.servable()