Indicateurs La Ciotat Elio

Indicateurs La Ciotat
Premier Indicateur : 
Proportion de locataires par carreau de 200m, deux variables sont prises en compte que sont : 
le nombre de ménages propriétaires (men_prop)
le nombre de ménages (men)
La formule est donc 1- men_prop/men
L'indicateur montre un facteur d'instabilité, plus la proportion est élevée, plus la population est mobile, les locataires ayant plus tendance à déménager que les propriétaires il permet donc de montrer les zones avec une certaine instabilité sociale et les zones où l'on a moins de leviers financiers en cas de crise économique. 
Deuxième indicateur : 
Proportion de ménages pauvres par carreau de 200m, encore une fois deux variables sont prises en compte : 
Le nombre de ménages pauvres (men_pauv)
Le nombre de ménages (men)
La formule est simple et est men_pauv/men
L'indicateur permet d'identifier la pauvreté monétaire globale en mesurant l'incapacité des foyers à couvrir leurs besoin fondamentaux. Cela correspond exactement à la part de personnes vivant en dessous de 60% du revenu médian. Cela correspond à 1154 € pour un ménage seul en 2021 années des données et à 2423,40 € pour une famille non monoparentale et 2 enfants de moins de 14 ans. La formule pour une famille correspond à 2,1 x 1154, 2,1 correspondant aux unités de consommation.
Troisième indicateur : 
Revenu winsorisé annuel moyen par individu par carreau de 200m, deux variables sont prises en compte:
Somme des revenus winsorisés par individu (ind_snv)
Nombre d'individus (ind)
La formule correspond à ind_snv/ind
Dans une analyse les valeurs dites extrêmes peuvent fausser la moyenne et masquer ainsi la réalité. En winsorisant les données c'est à dire en prenant l'ensemble des revenus d'un ménage en le divisant par les unités de consommation correspondant à 1 unité de consommation pour le premier adulte, 0,5 unité de consommation pour tout individu de plus de 14 ans et 0,3 unités de consommations pour tout individu de moins de 14 ans., on obtient une moyenne reflétant plus la situation. De plus dans cette unité on enlève les valeurs extrêmes à savoir 0.5% de chaque côté. Le revenu de départ sélectionné est le revenu net d'impôt auquel on ajoute l'ensemble des prestations sociales du ménage, à savoir ce qu'il reste au ménage après les prélèvements obligatoires, ce avec quoi il vit.
Quatrième indicateur : 
Proportion de ménages pauvres vivant en logements sociaux par carreau de 200m : trois variables sont prises en compte : 
Nombre de logements sociaux (log_soc)
Nombre de ménages (men)
Nombre de ménages pauvres (men_pauv)
On obtient une formule  : (men_pauv/men) *(1- (log_soc)/(men))
Le logement social est souvent un facteur de ségrégation spatiale et il est plus précis que le taux de pauvreté donc associé à ce dernier il met en valeur la part de ménages pauvres au point d'avoir des difficultés à accéder aux logements sociaux tout en mettant en lumière la proportion de logements sociaux qui seule mettrait aussi en avant une proportion de personne ayant accès à un logement social à cause d'un handicap, de personnes relogées etc...

Calcul indicateur crise de confiance

Indicateur crise de confiance
Code pour obtenir la proportion d'établissements non conventionnels sur la base Sirene dans une commune spécifique
CREATE TABLE sirene_la_ciotat_sante AS 
WITH base_data AS (
    -- On isole d'abord les établissements actifs, sièges et de santé à La Ciotat
    SELECT * FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\StockEtablissement_utf8.csv', ALL_VARCHAR=TRUE)
    WHERE codeCommuneEtablissement = '13028' 
      AND etatAdministratifEtablissement = 'A'
      AND etablissementSiege = 'true'
      AND activitePrincipaleEtablissement IN (
          '86.90E', '86.90D', '86.23Z', 
          '86.22C', '86.22B', '86.22A', '86.21Z', '86.90F'
      )
)
SELECT 
    *,
    -- Calcul de l'indicateur : (Nombre de 86.90F / Nombre total de la sélection) * 100
    (COUNT(*) FILTER (WHERE activitePrincipaleEtablissement = '86.90F') OVER () * 100.0 / COUNT(*) OVER ()) as part_non_conv
FROM base_data;
-- Exportation vers un CSV propre pour QGIS
COPY sirene_la_ciotat_sante TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\Sirene_Sante_La_Ciotat_Clean.csv' (HEADER, DELIMITER ',');
Code pour obtenir la proportion d'établissements non conventionnels sur la base Sirene dans un département : 
-- 1. Création de la table pour tout le département 13
CREATE TABLE sirene_13_sante_complet AS 
WITH base_data AS (
    -- On extrait les données brutes avec les filtres de base
    SELECT * FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\StockEtablissement_utf8.csv', ALL_VARCHAR=TRUE)
    WHERE codeCommuneEtablissement LIKE '13%' 
      AND etatAdministratifEtablissement = 'A'
      AND etablissementSiege = 'true'
      AND activitePrincipaleEtablissement IN (
          '86.90E', '86.90D', '86.23Z', 
          '86.22C', '86.22B', '86.22A', '86.21Z', '86.90F'
      )
)
SELECT 
    *,
    -- Calcul de l'indicateur arrondi à 2 décimales
    ROUND(
        (COUNT(*) FILTER (WHERE activitePrincipaleEtablissement = '86.90F') OVER () * 100.0 / COUNT(*) OVER ()), 
        2
    ) as part_non_conv
FROM base_data;
-- 2. Exportation du fichier départemental
COPY sirene_13_sante_complet TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\Sirene_Sante_13_Final.csv' (HEADER, DELIMITER ',');
Modification rougeole et grippe : 
-- 1. TRAITEMENT DE LA BASE GRIPPE
-- On crée la table en mémoire avec la moyenne du 13
CREATE TABLE Vacc_Grippe_13 AS
SELECT 
    *,
    -- Calcul de la moyenne sur la colonne 'Vaccines' pour le 13 uniquement
    ROUND(AVG(Vaccines) OVER (), 2) as moy_vacc_grippe_13
FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Grippe.csv')
-- Conversion de Code en texte pour permettre le filtrage LIKE '13%'
WHERE CAST(Code AS VARCHAR) LIKE '13%';
-- Exportation vers le fichier CSV réel
COPY Vacc_Grippe_13 TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Grippe_13_Clean.csv' (HEADER, DELIMITER ',');
-- 2. TRAITEMENT DE LA BASE ROUGEOLE
-- On crée la table en mémoire avec la moyenne du 13
CREATE TABLE Vacc_Rougeole_13 AS
SELECT 
    *,
    -- Calcul de la moyenne sur la colonne 'Vaccination' pour le 13 uniquement
    ROUND(AVG(Vaccination) OVER (), 2) as moy_vacc_rougeole_13
FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Rougeole.csv')
-- Conversion de Code en texte pour permettre le filtrage LIKE '13%'
WHERE CAST(Code AS VARCHAR) LIKE '13%';
-- Exportation vers le fichier CSV réel
COPY Vacc_Rougeole_13 TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Rougeole_13_Clean.csv' (HEADER, DELIMITER ',');
Creation Indicateurs Commune : 
-- 1. CREATION DE LA TABLE
CREATE TABLE Table_Indicateurs_Final AS 
SELECT 
    r.Code,
    -- Calcul avec les noms de colonnes exacts détectés par DuckDB
    ROUND(
        (0.5 * CAST(r.Vaccination AS FLOAT) + 
         0.25 * CAST(g."Vaccinés" AS FLOAT) + 
         0.25 * AVG(CAST(s.part_8690Z AS FLOAT))), 
    2) AS I_T1_02_Com
FROM 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g 
    ON CAST(r.Code AS VARCHAR) = CAST(g.Code AS VARCHAR)
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_La_Ciotat_Clean.csv') s
    ON CAST(r.Code AS VARCHAR) = CAST(s.codeCommuneEtablissement AS VARCHAR)
WHERE 
    CAST(r.Code AS VARCHAR) = '13028'
GROUP BY r.Code, r.Vaccination, g."Vaccinés";
-- 2. EXPORTATION
COPY Table_Indicateurs_Final TO 'C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Indicateurs.csv' (HEADER, DELIMITER ',');
Creation Indicateurs Dept :
 
-- 1. CREATION DE LA TABLE FINALE COMBINEE
CREATE TABLE Table_Indicateurs_Complet AS 
WITH stats_dept AS (
    -- On calcule la valeur départementale (Moyenne des moyennes)
    SELECT 
        AVG(CAST(r.moy_vacc_rougeole_13 AS FLOAT)) * 0.5 + 
        AVG(CAST(g.moy_vacc_grippe_13 AS FLOAT)) * 0.25 + 
        AVG(CAST(s.part_non_conv AS FLOAT)) * 0.25 AS val_dep
    FROM 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r,
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g,
        -- MISE À JOUR DU CHEMIN ICI :
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_13_Final.csv') s
)
SELECT 
    r.Code,
    -- Colonne 1 : Indicateur de La Ciotat
    ROUND(
        (0.5 * CAST(r.Vaccination AS FLOAT) + 
         0.25 * CAST(g."Vaccinés" AS FLOAT) + 
         0.25 * AVG(CAST(sc.part_8690Z AS FLOAT))), 
    2) AS I_T1_02_Com,
    -- Colonne 2 : Indicateur du Département
    ROUND((SELECT val_dep FROM stats_dept), 2) AS I_T1_02_Dep
FROM 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g 
    ON CAST(r.Code AS VARCHAR) = CAST(g.Code AS VARCHAR)
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_La_Ciotat_Clean.csv') sc
    ON CAST(r.Code AS VARCHAR) = CAST(sc.codeCommuneEtablissement AS VARCHAR)
WHERE 
    CAST(r.Code AS VARCHAR) = '13028'
GROUP BY r.Code, r.Vaccination, g."Vaccinés";
-- 2. EXPORTATION FINALE
COPY Table_Indicateurs_Complet TO 'C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Indicateurs.csv' (HEADER, DELIMITER ',');
Creation colonne Significative : 
-- 1. CRÉATION DE LA TABLE COMPLÈTE AVEC INDICATEURS ET TEST
CREATE TABLE Table_Indicateurs_Final_Test AS 
WITH base_calculs AS (
    -- On récupère les valeurs de la commune et du département
    SELECT 
        r.Code,
        -- p_com (La Ciotat)
        ROUND((0.5 * CAST(r.Vaccination AS FLOAT) + 0.25 * CAST(g."Vaccinés" AS FLOAT) + 0.25 * AVG(CAST(sc.part_8690Z AS FLOAT))), 2) AS p_com,
        -- p_dep (Moyenne du 13 calculée dynamiquement)
        (SELECT ROUND(
            AVG(CAST(rv.moy_vacc_rougeole_13 AS FLOAT)) * 0.5 + 
            AVG(CAST(gv.moy_vacc_grippe_13 AS FLOAT)) * 0.25 + 
            AVG(CAST(sv.part_non_conv AS FLOAT)) * 0.25, 2
        ) FROM 
            read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') rv,
            read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') gv,
            read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_13_Final.csv') sv
        ) AS p_dep,
        -- n : Population de La Ciotat (pour la précision du test)
        35000 AS n
    FROM 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r
    JOIN 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g ON CAST(r.Code AS VARCHAR) = CAST(g.Code AS VARCHAR)
    JOIN 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_La_Ciotat_Clean.csv') sc ON CAST(r.Code AS VARCHAR) = CAST(sc.codeCommuneEtablissement AS VARCHAR)
    WHERE CAST(r.Code AS VARCHAR) = '13028'
    GROUP BY r.Code, r.Vaccination, g."Vaccinés"
)
SELECT 
    Code,
    p_com AS I_T1_02_Com,
    p_dep AS I_T1_02_Dep,
    -- Calcul de la marge d'erreur (Z=1.96 pour alpha=5%)
    ROUND(1.96 * SQRT((p_dep/100 * (1 - p_dep/100)) / n) * 100, 3) AS marge_erreur,
    -- Diagnostic final
    CASE 
        WHEN p_com < (p_dep - (1.96 * SQRT((p_dep/100 * (1 - p_dep/100)) / n) * 100)) THEN 'Significativement PLUS BAS'
        WHEN p_com > (p_dep + (1.96 * SQRT((p_dep/100 * (1 - p_dep/100)) / n) * 100)) THEN 'Significativement PLUS HAUT'
        ELSE 'Conforme à la moyenne'
    END AS Interpretation
FROM base_calculs;
-- 2. EXPORTATION
COPY Table_Indicateurs_Final_Test TO 'C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Indicateurs.csv' (HEADER, DELIMITER ',');