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()