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