app.py

import panel as pn
import hvplot.pandas
import pandas as pd
import numpy as np
import duckdb as ddb

# Chargement de l'extension httpfs pour lecture de jeux données externes
ddb.execute("install httpfs")
ddb.execute("load httpfs")

# Téléchargement et enregistrement en table (in-memory) d'un subset du fichier du recensement 2020 au format parquet
ddb.sql(f"""create or replace table ods_ins_rp2020_individus as select * from  read_parquet("https://static.data.gouv.fr/resources/recensement-de-la-population-fichiers-detail-individus-localises-au-canton-ou-ville-2020-1/20231023-122841/fd-indcvi-2020.parquet") where iris like '95%'""")

# Téléchargement et enregistrement en table (in-memory) de la liste des variables et modalités
ddb.sql(f"""create or replace table ods_ins_rp2020_individus_dict \
as SELECT * FROM read_csv_auto("https://static.data.gouv.fr/resources/recensement-de-la-population-fichiers-detail-individus-localises-au-canton-ou-ville-2020-1/20231025-082910/dictionnaire-variables-indcvi-2020.csv")""");

# Normalisation des données pour une interrogation par indicateur
def prepare_data( filtre_territoire="95018", lib_territoire="", persistant_table_name="selected_data_stats_proportions"):
    # Subset sans les mineurs + territoire sélectionné
    ddb.sql(f""" \
        create or replace table selected_data as (select * from ods_ins_rp2020_individus where iris like '{filtre_territoire}%' and AGER20 not in ('02','05','10','14','17') ); -- restriction aux adultes
        """);
    # Normalisation type FT
    ddb.sql(f"""
        create or replace table selected_data_stats as\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ACHLR = b.COD_MOD and b.COD_VAR::varchar='ACHLR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.AEMMR = b.COD_MOD and b.COD_VAR::varchar='AEMMR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.AGER20 = b.COD_MOD and b.COD_VAR::varchar='AGER20') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.AGEREVQ = b.COD_MOD and b.COD_VAR::varchar='AGEREVQ') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ANEMR = b.COD_MOD and b.COD_VAR::varchar='ANEMR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.APAF = b.COD_MOD and b.COD_VAR::varchar='APAF') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ARM = b.COD_MOD and b.COD_VAR::varchar='ARM') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ASCEN = b.COD_MOD and b.COD_VAR::varchar='ASCEN') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.BAIN = b.COD_MOD and b.COD_VAR::varchar='BAIN') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.BATI = b.COD_MOD and b.COD_VAR::varchar='BATI') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CANTVILLE = b.COD_MOD and b.COD_VAR::varchar='CANTVILLE') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CATIRIS = b.COD_MOD and b.COD_VAR::varchar='CATIRIS') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CATL = b.COD_MOD and b.COD_VAR::varchar='CATL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CATPC = b.COD_MOD and b.COD_VAR::varchar='CATPC') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CHAU = b.COD_MOD and b.COD_VAR::varchar='CHAU') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CHFL = b.COD_MOD and b.COD_VAR::varchar='CHFL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CHOS = b.COD_MOD and b.COD_VAR::varchar='CHOS') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CLIM = b.COD_MOD and b.COD_VAR::varchar='CLIM') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CMBL = b.COD_MOD and b.COD_VAR::varchar='CMBL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.COUPLE = b.COD_MOD and b.COD_VAR::varchar='COUPLE') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CS1 = b.COD_MOD and b.COD_VAR::varchar='CS1') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.CUIS = b.COD_MOD and b.COD_VAR::varchar='CUIS') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.DEPT = b.COD_MOD and b.COD_VAR::varchar='DEPT') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.DEROU = b.COD_MOD and b.COD_VAR::varchar='DEROU') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.DIPL = b.COD_MOD and b.COD_VAR::varchar='DIPL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.DNAI = b.COD_MOD and b.COD_VAR::varchar='DNAI') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.EAU = b.COD_MOD and b.COD_VAR::varchar='EAU') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.EGOUL = b.COD_MOD and b.COD_VAR::varchar='EGOUL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ELEC = b.COD_MOD and b.COD_VAR::varchar='ELEC') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.EMPL = b.COD_MOD and b.COD_VAR::varchar='EMPL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ETUD = b.COD_MOD and b.COD_VAR::varchar='ETUD') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.GARL = b.COD_MOD and b.COD_VAR::varchar='GARL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.HLML = b.COD_MOD and b.COD_VAR::varchar='HLML') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ILETUD = b.COD_MOD and b.COD_VAR::varchar='ILETUD') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ILT = b.COD_MOD and b.COD_VAR::varchar='ILT') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.IMMI = b.COD_MOD and b.COD_VAR::varchar='IMMI') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.INAI = b.COD_MOD and b.COD_VAR::varchar='INAI') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.INATC = b.COD_MOD and b.COD_VAR::varchar='INATC') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.INFAM = b.COD_MOD and b.COD_VAR::varchar='INFAM') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.INPER = b.COD_MOD and b.COD_VAR::varchar='INPER') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.INPERF = b.COD_MOD and b.COD_VAR::varchar='INPERF') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.IRAN = b.COD_MOD and b.COD_VAR::varchar='IRAN') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.IRIS = b.COD_MOD and b.COD_VAR::varchar='IRIS') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.LIENF = b.COD_MOD and b.COD_VAR::varchar='LIENF') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.LPRF = b.COD_MOD and b.COD_VAR::varchar='LPRF') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.LPRM = b.COD_MOD and b.COD_VAR::varchar='LPRM') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.METRODOM = b.COD_MOD and b.COD_VAR::varchar='METRODOM') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.MOCO = b.COD_MOD and b.COD_VAR::varchar='MOCO') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.MODV = b.COD_MOD and b.COD_VAR::varchar='MODV') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NA17 = b.COD_MOD and b.COD_VAR::varchar='NA17') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NA5 = b.COD_MOD and b.COD_VAR::varchar='NA5') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NAIDT = b.COD_MOD and b.COD_VAR::varchar='NAIDT') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NBPI = b.COD_MOD and b.COD_VAR::varchar='NBPI') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NE17FR = b.COD_MOD and b.COD_VAR::varchar='NE17FR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NE24FR = b.COD_MOD and b.COD_VAR::varchar='NE24FR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NE3FR = b.COD_MOD and b.COD_VAR::varchar='NE3FR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NE5FR = b.COD_MOD and b.COD_VAR::varchar='NE5FR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NENFR = b.COD_MOD and b.COD_VAR::varchar='NENFR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NPERR = b.COD_MOD and b.COD_VAR::varchar='NPERR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NUMF = b.COD_MOD and b.COD_VAR::varchar='NUMF') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.NUMMI = b.COD_MOD and b.COD_VAR::varchar='NUMMI') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.ORIDT = b.COD_MOD and b.COD_VAR::varchar='ORIDT') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.RECH = b.COD_MOD and b.COD_VAR::varchar='RECH') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.REGION = b.COD_MOD and b.COD_VAR::varchar='REGION') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.SANI = b.COD_MOD and b.COD_VAR::varchar='SANI') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.SANIDOM = b.COD_MOD and b.COD_VAR::varchar='SANIDOM') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.SEXE = b.COD_MOD and b.COD_VAR::varchar='SEXE') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.SFM = b.COD_MOD and b.COD_VAR::varchar='SFM') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.STATR = b.COD_MOD and b.COD_VAR::varchar='STATR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.STAT_CONJ = b.COD_MOD and b.COD_VAR::varchar='STAT_CONJ') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.STOCD = b.COD_MOD and b.COD_VAR::varchar='STOCD') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.SURF = b.COD_MOD and b.COD_VAR::varchar='SURF') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TACT = b.COD_MOD and b.COD_VAR::varchar='TACT') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TACTD16 = b.COD_MOD and b.COD_VAR::varchar='TACTD16') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TP = b.COD_MOD and b.COD_VAR::varchar='TP') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TRANS = b.COD_MOD and b.COD_VAR::varchar='TRANS') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TRIRIS = b.COD_MOD and b.COD_VAR::varchar='TRIRIS') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TYPC = b.COD_MOD and b.COD_VAR::varchar='TYPC') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TYPFC = b.COD_MOD and b.COD_VAR::varchar='TYPFC') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TYPL = b.COD_MOD and b.COD_VAR::varchar='TYPL') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TYPMC = b.COD_MOD and b.COD_VAR::varchar='TYPMC') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.TYPMR = b.COD_MOD and b.COD_VAR::varchar='TYPMR') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.VOIT = b.COD_MOD and b.COD_VAR::varchar='VOIT') group by b.LIB_VAR , b.LIB_MOD union\
        select b.LIB_VAR , b.LIB_MOD , sum(ipondi) nb_individus from selected_data a inner join ods_ins_rp2020_individus_dict b on (a.WC = b.COD_MOD and b.COD_VAR::varchar='WC') group by b.LIB_VAR , b.LIB_MOD     
        """)
    
    # ratios et rang par modalité de variable
    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;""")
    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="",persistant_table_name="ds_national")

# Comparaison de territoires
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()

# Diagramme simple 
def plot_analyse_histogramme(code_variable = "TYPL"):
    histo = ddb.sql(f"""select lib_territoire territoire, code_variable, variable, modalite, proportion_locale "Pourcentage de population estimé (%)" from agr_comparaison_territoires where code_variable = '{code_variable}' \
                   /*union select 'National' territoire,code_variable, variable, modalite, proportion_reference proportion from agr_comparaison_territoires where code_variable = '{code_variable}' */
                   order by 5 """).to_df().hvplot.barh( x='modalite', y="Pourcentage de population estimé (%)",   legend='bottom_right', height=400, width=800 , group_label=None)
    return histo

# Fonction principale
def run_territoire_histo(filtre_territoire="95018", code_variable="TYPL"):
    prepare_data(filtre_territoire,"<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"]))

options_com = ddb.sql("""select code_commune_INSEE, nom_commune||' ('||code_commune_INSEE||')' nom_commune   from read_csv_auto("https://static.data.gouv.fr/resources/communes-de-france-base-des-codes-postaux/20200309-131459/communes-departement-region.csv") where code_departement='95' order by 1""").to_df()
options_com_dict = dict(zip(options_com["nom_commune"], options_com["code_commune_INSEE"]))


# 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_com_dict)

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

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