Skip to main content
Explore
Sign in
Explore
Snippets Groups Projects

Logements modif

Merged Logements modif
logements_modif into master
Merged
Benoît Courtyrequested to merge
logements_modif into master
Viewing commit 419632e1
Previous Next
Show latest version
1 file
+ 54
− 190
Compare changes
  • Side-by-side
  • Inline
notebooks/logement.ipynb
+ 54
− 190
%% Cell type:code id: tags:
%% Cell type:code id: tags:
from datacirco.modules_data.data import DataCirco, GetDataCirco
from datacirco.modules_data.data import DataCirco, GetDataCirco
from datacirco.export_to_json.logement import export_logement
from datacirco.export_to_json.logement import export_logement
data_logement = GetDataCirco(db = db, data = DataCirco('2B', '02B-01'))
data_logement = GetDataCirco(db = db, data = DataCirco('973', '973-01'))
export_data = export_logement(
# export_data = export_logement(
db = db,
# db = db,
data = DataCirco('2B', '02B-01'),
# data = DataCirco('2B', '02B-01'),
in_communes = data_logement.data.in_communes,
# in_communes = data_logement.data.in_communes,
superficie_circo = data_logement.data.superficie_circo,
# superficie_circo = data_logement.data.superficie_circo,
geo = data_logement.data.geo)
# geo = data_logement.data.geo)
%% Output
%% Output
SELECT ST_AsGeojson(wkb_geometry)::json FROM zone_circo WHERE ref= '02B-01'
SELECT ST_AsGeojson(wkb_geometry)::json FROM zone_circo WHERE ref= '973-01'
select st_area(wkb_geometry::geography)/1000000 from zone_circo where ref = '02B-01'
select st_area(wkb_geometry::geography)/1000000 from zone_circo where ref = '973-01'
select sum(st_area(wkb_geometry::geography)::float)/1000000 from ign_region where insee_reg != '03';
select sum(st_area(wkb_geometry::geography)::float)/1000000 from ign_region where insee_reg != '03';
select
SUM(p20_log::numeric)
from zone_circo c
join iris_ge i on (st_intersects(c.wkb_geometry, i.wkb_geometry))
join insee_logement_2020 p on (p.iris=i.code_iris)
where ref='02B-01'
SELECT
st_asgeojson((st_dump(st_intersection(p.wkb_geometry,c.wkb_geometry))).geom)::json as geojson,
round(100.0*men_coll/men,1) as pct_logement_collectifs,
round(100.0*men_prop/men,1) as pct_proprietaires
FROM
insee_population_carroyee_2015_1k p
JOIN zone_circo c on (st_intersects(c.wkb_geometry,p.wkb_geometry))
WHERE ref='02B-01'
02B-01
SELECT enum.etiquette_dpe as classe_consommation_energie, coalesce(compte.nb_dpe, 0) as nb
FROM (
SELECT DISTINCT etiquette_dpe
FROM ademe_dpe_logements_2024
GROUP BY 1) as enum
NATURAL LEFT JOIN (
SELECT d.etiquette_dpe, count(DISTINCT d.ndpe) as nb_dpe
FROM ademe_dpe_logements_2024 as d
LEFT JOIN zone_circo c
ON st_intersects(c.wkb_geometry, d.geom)
WHERE c.ref = '02B-01' AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
AND d.etiquette_dpe between 'A' and 'G'
GROUP BY 1
) as compte
ORDER BY 1;
SELECT enum.etiquette_dpe as classe_consommation_energie, coalesce(compte.nb_dpe, 0) as sum
FROM (
SELECT DISTINCT etiquette_dpe
FROM ademe_dpe_logements_2024
GROUP BY 1) as enum
NATURAL LEFT JOIN (
SELECT d.etiquette_dpe, count(DISTINCT d.ndpe) as nb_dpe
FROM ademe_dpe_logements_2024 as d
LEFT JOIN zone_circo c
ON st_intersects(c.wkb_geometry, d.geom)
WHERE LPAD(RTRIM(RTRIM(CAST(d.n_departement__ban_ AS VARCHAR),'.'),'.0'),3,'0') = '02B'
AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
AND d.etiquette_dpe between 'A' and 'G'
GROUP BY d.etiquette_dpe) as compte;
SELECT *
FROM ademe_dpe_france_2024;
select * from crosstab(
$$select
classe_consommation_energie,
annee,
sum(coalesce(nb,0)) as nb
from ademe_dpe_logements_stats
where code_insee_commune_actualise in ('2B029','2B030','2B033','2B037','2B042','2B043','2B046','2B086','2B107','2B109','2B120','2B136','2B148','2B152','2B159','2B170','2B183','2B184','2B185','2B187','2B188','2B205','2B224','2B223','2B230','2B233','2B239','2B257','2B261','2B265','2B298','2B301','2B305','2B309','2B314','2B281','2B287','2B327','2B332','2B333','2B353','2B058','2B172','2B178','2B350')
and annee between '2013' and '2021'
and classe_consommation_energie between 'A' and 'G'
group by 1,2$$,
$$SELECT generate_series(2013,2021)$$
) as T (
classe_consommation_energie text,
"annee_2013" int,
"annee_2014" int,
"annee_2015" int,
"annee_2016" int,
"annee_2017" int,
"annee_2018" int,
"annee_2019" int,
"annee_2020" int,
"annee_2021" int
);
SELECT classe_consommation_energie, coalesce(a.annee_2021, 0) as annee_2021, coalesce(a.annee_2022, 0) as annee_2022, coalesce(a.annee_2023, 0) as annee_2023 FROM (
select * from crosstab(
$$SELECT d.etiquette_dpe, LEFT(d.date_etablissement_dpe,4) as annee_etablissement_DPE, coalesce(count(DISTINCT d.ndpe), 0) as nb_dpe
FROM ademe_dpe_logements_2024 as d
LEFT JOIN zone_circo c
ON st_intersects(c.wkb_geometry, d.geom)
WHERE c.ref = '02B-01'
AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
AND d.etiquette_dpe between 'A' and 'G'
GROUP BY 1,2 ORDER BY 1,2$$,
$$SELECT generate_series(2021,2023)$$
) as T (
classe_consommation_energie text,
"annee_2021" int,
"annee_2022" int,
"annee_2023" int
)) as a
ORDER BY 1;
/datacirco/notebooks/datacirco/modules_data/logement_data.py:314: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_logement_pre_2021 = pd.read_sql(sql=sql1, con=self.db.connection).rename(columns = {"annee_2021" : "annee_2021_avant"})
/datacirco/notebooks/datacirco/modules_data/logement_data.py:316: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_logement_post_2021 = pd.read_sql(sql=sql2, con=self.db.connection).rename(columns = {"annee_2021" : "annee_2021_apres"})
classe_consommation_energie annee_2013 annee_2014 annee_2015 annee_2016 \
0 A 1 11 8 101
1 B 10 62 109 94
2 C 52 219 323 326
3 D 100 214 262 310
4 E 34 89 90 113
5 F 12 40 24 56
6 G 3 10 15 11
annee_2017 annee_2018 annee_2019 annee_2020 annee_2021_avant
0 61 63 136 30 64
1 118 118 253 90 156
2 388 423 448 439 308
3 300 362 382 435 301
4 119 150 165 183 122
5 30 42 49 41 18
6 8 7 9 16 9
classe_consommation_energie annee_2021_apres annee_2022 annee_2023
0 A 76 282 348
1 B 48 258 308
2 C 172 933 1974
3 D 185 491 604
4 E 86 235 246
5 F 40 76 69
6 G 22 68 41
SELECT enum.etiquette_ges as classe_ges, coalesce(compte.nb_ges, 0) as nb
FROM (
SELECT DISTINCT etiquette_ges
FROM ademe_dpe_logements_2024
GROUP BY 1) as enum
NATURAL LEFT JOIN (
SELECT d.etiquette_ges, count(DISTINCT d.ndpe) as nb_ges
FROM ademe_dpe_logements_2024 as d
LEFT JOIN zone_circo c
ON st_intersects(c.wkb_geometry, d.geom)
WHERE c.ref = '02B-01'
AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
AND d.etiquette_ges between 'A' and 'G'
GROUP BY 1
) as compte
ORDER BY 1;
SELECT enum.etiquette_ges as classe_ges, coalesce(compte.nb_ges, 0) as sum
FROM (
SELECT DISTINCT etiquette_ges
FROM ademe_dpe_logements_2024
GROUP BY 1) as enum
NATURAL LEFT JOIN (
SELECT d.etiquette_ges, count(DISTINCT d.ndpe) as nb_ges
FROM ademe_dpe_logements_2024 as d
LEFT JOIN zone_circo c
ON st_intersects(c.wkb_geometry, d.geom)
WHERE LPAD(RTRIM(RTRIM(CAST(d.n_departement__ban_ AS VARCHAR),'.'),'.0'),3,'0') = '02B'
AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
AND d.etiquette_ges between 'A' and 'G'
GROUP BY d.etiquette_ges) as compte;
SELECT *
FROM ademe_ges_france_2024;
SELECT classe_ges, coalesce(a.annee_2021, 0) as annee_2021, coalesce(a.annee_2022, 0) as annee_2022, coalesce(a.annee_2023, 0) as annee_2023 FROM (
select * from crosstab(
$$SELECT d.etiquette_ges, LEFT(d.date_etablissement_dpe,4) as annee_etablissement_GES, coalesce(count(DISTINCT d.ndpe), 0) as nb_dpe
FROM ademe_dpe_logements_2024 as d
LEFT JOIN zone_circo c
ON st_intersects(c.wkb_geometry, d.geom)
WHERE c.ref = '02B-01'
AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
AND d.etiquette_ges between 'A' and 'G'
GROUP BY 1,2 ORDER BY 1,2$$,
$$SELECT generate_series(2021,2023)$$
) as T (
classe_ges text,
"annee_2021" int,
"annee_2022" int,
"annee_2023" int
)) as a
ORDER BY 1;
%% Cell type:code id: tags:
%% Cell type:code id: tags:
resultat
resultat
%% Cell type:markdown id: tags:
%% Cell type:markdown id: tags:
 
Il n'y a pas de données pour la Martinique, ni pour la Guyane (un logement E dans la 2ème circonscription et la 3ème), ni pour la Réunion (dans l'ancien DataCirco). Test pour la Corse (02B-01).
 
%% Cell type:code id: tags:
 
``` python
 
# Vérification pour la Corse
 
from datacirco.connexion_db import db, run
 
db.execute(
 
"""select classe_consommation_energie,
 
coalesce(annee_2021, 0) as annee_2021, coalesce(annee_2020, 0) as annee_2020,
 
coalesce(annee_2019, 0) as annee_2019, coalesce(annee_2018, 0) as annee_2018,
 
coalesce(annee_2017, 0) as annee_2017, coalesce(annee_2016, 0) as annee_2016,
 
coalesce(annee_2015, 0) as annee_2015, coalesce(annee_2014, 0) as annee_2014,
 
coalesce(annee_2013, 0) as annee_2013 from crosstab(
 
$$select
 
classe_consommation_energie,
 
annee,
 
sum(coalesce(nb,0)) as nb
 
from ademe_dpe_logements_stats
 
where code_insee_commune_actualise in ('97356','97301','97302','97307','97314','97309','97310','97308')
 
and annee between '2013' and '2021'
 
and classe_consommation_energie between 'A' and 'G'
 
group by 1,2$$,
 
$$SELECT generate_series(2013,2021)$$
 
) as T (
 
classe_consommation_energie text,
 
"annee_2013" int,
 
"annee_2014" int,
 
"annee_2015" int,
 
"annee_2016" int,
 
"annee_2017" int,
 
"annee_2018" int,
 
"annee_2019" int,
 
"annee_2020" int,
 
"annee_2021" int
 
);""")
 
db.fetchall()
 
%% Output
 
[]
 
%% Cell type:markdown id: tags: