création d'un répertoire tableurs à la racine du projet et production des...

création d'un répertoire tableurs à la racine du projet et production des fichiers .xlsx décrits par GLB

Closes #48 (closed)

data-raw/data_tableurs_bis.R

+
+library(dplyr)
+
+ocsge <- propre.artificialisation::ocsge
+observatoire_artificialisation <- propre.artificialisation::observatoire_artificialisation
+stock_artificialise <- propre.artificialisation::stock_artificialise
+population_legale <- propre.artificialisation::population_legale
+
+
+# carte 1_3 (source OCSGE) ---------------
+millesime_ocsge <- 2016
+code_reg <- "52"
+
+data <- ocsge %>%
+  dplyr::filter(grepl(millesime_ocsge, .data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  # dplyr::select(.data$TypeZone,.data$Zone,.data$CodeZone,.data$date,.data$espace_artificialise) %>%
+  # dplyr::mutate(valeur=round(.data$espace_artificialise / 10000,0)) %>%
+  dplyr::mutate(espace_artificialise_en_hectares=round(.data$espace_artificialise / 10000,0))
+  # dplyr::select(-.data$espace_artificialise)
+
+xlsx::write.xlsx(data,file = "tableurs/ocsge_carte_1_3.xlsx")
+
+
+# graphe 1_6 (source OCSGE) -------------
+# millesime_ocsge <- 2016
+# code_reg <- "52"
+
+ocsge2 <- ocsge %>%
+  dplyr::mutate(date = lubridate::year(lubridate::as_date(.data$date)),
+                espace_naturel = .data$autre_surface_naturelle + .data$surface_en_eau + .data$surface_naturelle_boisee) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  # dplyr::filter(.data$date == millesime_ocsge,
+  #               .data$TypeZone %in% c("Communes")) %>%
+  dplyr::filter(.data$date == millesime_ocsge) %>%
+  dplyr::select(.data$TypeZone, .data$Zone, .data$CodeZone,
+                .data$espace_naturel,
+                .data$espace_agricole, .data$espace_artificialise)
+
+data <- ocsge2 %>%
+  tidyr::pivot_longer(.data$espace_naturel:.data$espace_artificialise,
+                      names_to = "variable",
+                      values_to = "valeur") %>%
+  dplyr::group_by(.data$CodeZone) %>%
+  dplyr::mutate(variable = .data$variable,
+                valeur = .data$valeur / 10000,
+                taux = .data$valeur / sum(.data$valeur, na.rm = T) * 100) %>%
+  dplyr::mutate(variable = forcats::fct_relevel(variable,"espace_naturel","espace_agricole","espace_artificialise"))
+
+rm(ocsge2)
+
+xlsx::write.xlsx(data,file = "tableurs/ocsge_1_6_et_1_7.xlsx")
+
+
+# tableau 1_6 (source OCSGE)-----------
+# millesime_ocsge <- 2016
+# code_reg <- "52"
+
+# ocsge2 <- ocsge %>%
+#   dplyr::filter(grepl(millesime_ocsge, .data$date))
+#
+# mois <- lubridate::month(ocsge[1,"date"],label=TRUE)
+#
+# ocsge2 <- ocsge2 %>%
+#   dplyr::mutate(date=lubridate::year(lubridate::as_date(.data$date)),
+#                 espace_naturel= .data$autre_surface_naturelle + .data$surface_en_eau + .data$surface_naturelle_boisee) %>%
+#   COGiter::filtrer_cog(reg = code_reg) %>%
+#   dplyr::select (.data$TypeZone,.data$Zone,.data$CodeZone,
+#                  .data$espace_naturel,.data$espace_agricole,.data$espace_artificialise)  %>%
+#   dplyr::filter(.data$TypeZone == "Communes") %>%
+#   mutate(dep = substr(.data$CodeZone,1,2))
+#
+# data <- ocsge2 %>%
+#   tidyr::gather(variable, valeur, .data$espace_naturel:.data$espace_artificialise) %>%
+#   dplyr::group_by(.data$Zone,.data$CodeZone) %>%
+#   dplyr::mutate(variable = .data$variable,
+#                 valeur = .data$valeur/10000,
+#                 taux = .data$valeur / sum(.data$valeur, na.rm=T)*100) %>%
+#   dplyr::ungroup() %>%
+#   dplyr::left_join(COGiter::departements %>%
+#                      dplyr::filter (.data$REG == code_reg) %>%
+#                      dplyr::select(.data$DEP, .data$NOM_DEP),
+#                    by=c("dep"= "DEP")) %>%
+#   dplyr::rename("Departement" = "NOM_DEP") %>%
+#   dplyr::select (.data$TypeZone, .data$Zone, .data$CodeZone,
+#                  .data$dep, .data$Departement,
+#                  everything())
+#
+# rm(ocsge2,mois)
+#
+# xlsx::write.xlsx(data,file = "tableurs/tableau_1_6.xlsx")
+
+
+# carte 1_7 (source OCSGE) ------------
+# millesime_ocsge <- 2016
+# code_reg <- "52"
+
+# data1 <- ocsge %>%
+#   dplyr::filter(grepl(millesime_ocsge, .data$date),
+#                 .data$TypeZone == "Communes") %>%
+#   COGiter::filtrer_cog(reg = code_reg)
+#
+# mois <- lubridate::month(data1[1,"date"],label=TRUE)
+#
+# data2 <- data1 %>%
+#   tidyr::gather(variable,valeur,.data$a_definir:.data$surface_naturelle_boisee) %>%
+#   dplyr::arrange(.data$CodeZone) %>%
+#   dplyr::group_by(.data$TypeZone,.data$Zone,.data$CodeZone,.data$date)  %>%
+#   dplyr::summarise(variable=.data$variable,valeur=.data$valeur / sum(.data$valeur,na.rm=T))  %>%
+#   dplyr::filter(.data$variable == "espace_artificialise") %>%
+#   dplyr::ungroup() %>%
+#   dplyr::select(.data$TypeZone,.data$CodeZone,.data$Zone,.data$date,.data$valeur) %>%
+#   dplyr::mutate(valeur=.data$valeur * 100) %>%
+#   dplyr::rename(part_artificialisee = valeur)
+#
+# rm(data1,mois)
+#
+# xlsx::write.xlsx(data,file = "tableurs/carte_1_7.xlsx")
+
+
+# source observatoire artificialisation ---------
+millesime_obs_artif <- 2020
+
+# donnees observatoire artificialisation completes sur 10 ans
+# communes et epci region
+observatoire_artificialisation_0a <- observatoire_artificialisation %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$TypeZone == "Communes" | .data$TypeZone == "Epci")
+ # autres zones france
+observatoire_artificialisation_0b <- observatoire_artificialisation %>%
+  dplyr::filter(.data$TypeZone != "Communes" , .data$TypeZone != "Epci")
+# regroupement des donnees
+observatoire_artificialisation_0 <- dplyr::bind_rows(observatoire_artificialisation_0a,observatoire_artificialisation_0b)   %>%
+  dplyr::mutate(date = as.character(lubridate::year(.data$date - 1))) %>%
+  dplyr::filter(.data$date < millesime_obs_artif, .data$date > millesime_obs_artif - 11)  # conserve les 10 derniers millesimes
+rm(observatoire_artificialisation_0a,observatoire_artificialisation_0b)
+
+xlsx::write.xlsx(observatoire_artificialisation_0,file = "tableurs/obs_artif_flux_par_annee_2_4.xlsx")
+
+
+# somme des flux 10 ans
+data <- observatoire_artificialisation_0 %>%
+  dplyr::select(.data$CodeZone, .data$TypeZone, .data$Zone, .data$date, .data$flux_naf_artificialisation_total) %>%
+  dplyr::mutate(flux_naf_artificialisation_total = .data$flux_naf_artificialisation_total / 10000) %>%
+  dplyr::arrange(.data$CodeZone) %>%
+  dplyr::group_by(.data$CodeZone, .data$TypeZone, .data$Zone) %>%
+  dplyr::summarise(`surf artificialisees 10 ans` = sum(.data$flux_naf_artificialisation_total, na.rm = T)) %>%
+  ungroup()
+
+xlsx::write.xlsx(data,file = "tableurs/obs_artif_flux_10_ans_2_2_et_2_3_et_2_7.xlsx")
+
+
+
+
+
+
+
+
+
+
+# # départements métropolitains
+# data_dep <- observatoire_artificialisation_0 %>%
+#   dplyr::filter(.data$TypeZone == "D\u00e9partements") %>%
+#   dplyr::filter(!(.data$CodeZone %in% c("971", "972", "973", "974", "975", "976"))) %>%
+#   dplyr::select(.data$CodeZone, .data$TypeZone, .data$Zone, .data$date, .data$flux_naf_artificialisation_total) %>%
+#   dplyr::mutate(flux_naf_artificialisation_total = .data$flux_naf_artificialisation_total / 10000) %>%
+#   dplyr::arrange(.data$CodeZone) %>%
+#   dplyr::group_by(.data$CodeZone, .data$TypeZone, .data$Zone) %>%
+#   dplyr::summarise(`surf artificialisees 10 ans` = sum(.data$flux_naf_artificialisation_total, na.rm = T)) %>%
+#   ungroup()
+#
+# # communes de Pays de la Loire
+# data_com_pdl <- observatoire_artificialisation_0 %>%
+#   dplyr::filter(.data$TypeZone == "Communes") %>%
+#   dplyr::select(.data$CodeZone, .data$TypeZone, .data$Zone, .data$date, .data$flux_naf_artificialisation_total) %>%
+#   dplyr::mutate(flux_naf_artificialisation_total = .data$flux_naf_artificialisation_total / 10000) %>%
+#   dplyr::arrange(.data$CodeZone) %>%
+#   dplyr::group_by(.data$CodeZone, .data$TypeZone, .data$Zone) %>%
+#   dplyr::summarise(`surf artificialisees 10 ans` = sum(.data$flux_naf_artificialisation_total, na.rm = T)) %>%
+#   ungroup()
+#
+# data <- dplyr::bind_rows(data_dep,data_com_pdl)
+#
+# rm(data_dep,data_com_pdl)
+#
+# xlsx::write.xlsx(data,file = "tableurs/carte_2_2.xlsx")
+
+
+# graphe 2_3 (source observatoire artificialisation) -----------
+# millesime_obs_artif <- 2019
+
+# data <- observatoire_artificialisation %>%
+#   dplyr::mutate(date = as.character(lubridate::year(.data$date - 1))) %>%
+#   dplyr::filter(.data$TypeZone == "R\u00e9gions",
+#                 !(.data$CodeZone %in% c("01","02","03","04","06"))) %>%
+#   dplyr::select(.data$CodeZone, .data$TypeZone, .data$Zone, .data$date, .data$flux_naf_artificialisation_total) %>%
+#   dplyr::mutate(flux_naf_artificialisation_total = .data$flux_naf_artificialisation_total / 10000) %>%
+#   dplyr::filter(.data$date < millesime_obs_artif, .data$date > millesime_obs_artif - 11) %>% # conserve les 10 derniers millesimes
+#   dplyr::arrange(.data$Zone) %>%
+#   dplyr::group_by(.data$CodeZone, .data$TypeZone, .data$Zone) %>%
+#   dplyr::summarise(`total en hectares` = sum(.data$flux_naf_artificialisation_total, na.rm = T)) %>%
+#   dplyr::ungroup()
+#
+# xlsx::write.xlsx(data,file = "tableurs/graphe_2_3.xlsx")
+
+
+# graphe 2_5 (source observatoire artificialisation) ---------
+
+data_dep_pdl <- observatoire_artificialisation %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$TypeZone == "D\u00e9partements") %>%
+  dplyr::arrange(.data$CodeZone) %>%
+  dplyr::mutate(Zone=factor(.data$Zone) %>% forcats::fct_inorder())%>%
+  dplyr::select(.data$TypeZone,
+                .data$CodeZone,
+                .data$Zone,
+                .data$date,
+                .data$flux_naf_artificialisation_activite,
+                .data$flux_naf_artificialisation_habitation,
+                .data$flux_naf_artificialisation_mixte) %>%
+  dplyr::mutate(Zone = forcats::fct_drop(.data$Zone)) %>%
+  dplyr::arrange(.data$Zone) %>%
+  dplyr::mutate(date = as.character(lubridate::year(.data$date - 1))) %>%
+  dplyr::filter(.data$date < millesime_obs_artif, .data$date > millesime_obs_artif - 11) %>% # conserve les 10 derniers millesimes
+  tidyr::gather(variable, valeur, .data$flux_naf_artificialisation_activite:.data$flux_naf_artificialisation_mixte) %>%
+  dplyr::mutate(valeur = .data$valeur / 10000) %>%
+  dplyr::mutate(
+    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_activite", "activit\u00e9"),
+    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_habitation", "habitation"),
+    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_mixte", "mixte"))
+
+data_com_pdl <- observatoire_artificialisation %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$TypeZone == "Communes") %>%
+  dplyr::arrange(.data$CodeZone) %>%
+  dplyr::mutate(Zone=factor(.data$Zone) %>% forcats::fct_inorder())%>%
+  dplyr::select(.data$TypeZone,
+                .data$CodeZone,
+                .data$Zone,
+                .data$date,
+                .data$flux_naf_artificialisation_activite,
+                .data$flux_naf_artificialisation_habitation,
+                .data$flux_naf_artificialisation_mixte) %>%
+  dplyr::mutate(Zone = forcats::fct_drop(.data$Zone)) %>%
+  dplyr::arrange(.data$Zone) %>%
+  dplyr::mutate(date = as.character(lubridate::year(.data$date - 1))) %>%
+  dplyr::filter(.data$date < millesime_obs_artif, .data$date > millesime_obs_artif - 11) %>% # conserve les 10 derniers millesimes
+  tidyr::gather(variable, valeur, .data$flux_naf_artificialisation_activite:.data$flux_naf_artificialisation_mixte) %>%
+  dplyr::mutate(valeur = .data$valeur / 10000) %>%
+  dplyr::mutate(
+    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_activite", "activit\u00e9"),
+    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_habitation", "habitation"),
+    variable = replace(.data$variable, .data$variable == "flux_naf_artificialisation_mixte", "mixte"))
+
+data <- dplyr::bind_rows(data_com_pdl,data_dep_pdl)
+
+rm(data_com_pdl,data_dep_pdl)
+
+xlsx::write.xlsx(data,file = "tableurs/obs_artif_graphe_2_5.xlsx")
+
+
+# graphe 2_6 (source stock artificialisé) ------------
+millesime_stock_artif <- 2020
+millesime_debut <- 2016
+
+stock_artificialise_0 <- COGiter::filtrer_cog(stock_artificialise,
+                                              reg = code_reg)
+xlsx::write.xlsx(data,file = "tableurs/stock_artificialise_origine.xlsx")
+
+data <- stock_artificialise_0 %>%
+  # dplyr::filter(.data$TypeZone == "Communes") %>%
+  dplyr::mutate(date = lubridate::year(.data$date)) %>%
+  dplyr::filter(.data$date == millesime_stock_artif | .data$date == millesime_debut) %>%
+  dplyr::select (-.data$surf_cadastree) %>%
+  dplyr::group_by(.data$CodeZone) %>%
+  dplyr::mutate(evolution = (.data$surface_artificialisee - dplyr::lag(.data$surface_artificialisee))*100/ dplyr::lag(.data$surface_artificialisee)) %>%
+  dplyr::ungroup() %>%
+  # dplyr::filter(.data$date == millesime_stock_artif)
+  dplyr::arrange(.data$CodeZone)
+
+xlsx::write.xlsx(data,file = "tableurs/stock_artificialise_evolution_graphe_2_6_et_2_8_et_3_1.xlsx")
+
+
+# graphe 3_1 (sources population légale et stock artificialisé) ---------
+# cog_reg <- "52"
+millesime_stock_artif <- 2020
+millesime_debut <- 2016
+millesime_population <- 2019
+millesime_population_debut <- millesime_population - (millesime_stock_artif - millesime_debut)
+
+# evol_artif <- stock_artificialise %>%
+#   dplyr::mutate(date = lubridate::year(.data$date)) %>%
+#   COGiter::filtrer_cog(reg = code_reg) %>%
+#   dplyr::filter(.data$date == millesime_stock_artif | .data$date == millesime_debut) %>%
+#   dplyr::select (-.data$surf_cadastree) %>%
+#   dplyr::arrange(.data$TypeZone, .data$Zone, .data$CodeZone, .data$date) %>%
+#   dplyr::group_by(.data$TypeZone, .data$Zone, .data$CodeZone) %>%
+#   dplyr::mutate(evolution_artificialisation = round(.data$surface_artificialisee * 100 / dplyr::lag(.data$surface_artificialisee) - 100, 1)) %>%
+#   dplyr::ungroup() %>%
+#   dplyr::filter(.data$date == millesime_stock_artif)
+
+
+evol_popul <- population_legale %>%
+  dplyr::mutate(date = lubridate::year(.data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  # dplyr::filter(.data$TypeZone %in% c("R\u00e9gions", "D\u00e9partements"),
+  #               .data$date == millesime_population | .data$date == millesime_population_debut) %>%
+  dplyr::filter(.data$date == millesime_population | .data$date == millesime_population_debut) %>%
+  dplyr::arrange(.data$TypeZone, .data$Zone, .data$CodeZone, .data$date) %>%
+  dplyr::group_by(.data$TypeZone, .data$Zone, .data$CodeZone) %>%
+  dplyr::mutate(evolution_population = round(.data$population_municipale * 100 / dplyr::lag(.data$population_municipale) - 100, 1)) %>%
+  dplyr::ungroup() %>%
+  # dplyr::filter(.data$date == millesime_population)
+  dplyr::arrange(.data$CodeZone)
+
+# xlsx::write.xlsx(evol_artif,file = "tableurs/graphe_3_1_evol_artif.xlsx")
+xlsx::write.xlsx(evol_popul,file = "tableurs/graphe_3_1_evol_popul.xlsx")
+
+# rm(evol_artif,evol_popul)
+rm(evol_popul)
+
+# graphe 3_3 (sources population légale et stock artificialisé) -----------------
+code_reg <- "52"
+millesime_population <- 2019
+millesime_stock_artif <- 2020
+millesime_debut <- 2016
+millesime_population_debut <- millesime_population - (millesime_stock_artif - millesime_debut)
+
+# seuils population
+pop_3 <- 40000
+pop_2 <- 10000
+pop_1 <- 2000
+
+# population du millesime
+population <- population_legale %>%
+  dplyr::mutate(date=lubridate::year(.data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$date == millesime_population,
+                .data$TypeZone =="Communes") %>%
+  dplyr::mutate(seuil_pop = dplyr::case_when(
+    .data$population_municipale > pop_3  ~ glue::glue("communes de plus\nde ",pop_3," habitants"),
+    .data$population_municipale > pop_2  ~ glue::glue("communes entre\n",pop_2," et ",pop_3, " habitants"),
+    .data$population_municipale > pop_1  ~ glue::glue("communes entre\n",pop_1," et ",pop_2, " habitants"),
+    .data$population_municipale > 0  ~ glue::glue("communes de moins\nde ",pop_1," habitants"),
+    TRUE ~ "")
+  ) %>%
+  dplyr::mutate(seuil_code = dplyr::case_when(
+    .data$population_municipale > pop_3  ~ "D",
+    .data$population_municipale > pop_2  ~ "C",
+    .data$population_municipale > pop_1  ~ "B",
+    .data$population_municipale > 0  ~ "A",
+    TRUE ~ "")
+  ) %>%
+  dplyr::rename ("population_n"="population_municipale") %>%
+  dplyr::select (-.data$date)
+
+# table des seuils
+seuil_population <- population %>%
+  dplyr::select (.data$CodeZone,.data$seuil_pop)
+
+
+# preparation des donnees evolution_artificialisation
+
+evol_artif <- stock_artificialise %>%
+  dplyr::mutate(date = lubridate::year(.data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$TypeZone == "Communes",
+                .data$date == millesime_stock_artif | .data$date == millesime_debut) %>%
+  dplyr::select (-.data$surf_cadastree)
+
+evol_artif1 <- evol_artif %>%
+  dplyr::left_join(seuil_population) %>%
+  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
+  dplyr::filter (.data$date == millesime_stock_artif) %>%
+  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
+  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::left_join(COGiter::departements %>%
+                     dplyr::filter (.data$REG == code_reg) %>%
+                     dplyr::select(.data$DEP, .data$NOM_DEP),
+                   by=c("dep"= "DEP")) %>%
+  dplyr::rename("Zone" = "NOM_DEP") %>%
+  dplyr::select(-.data$dep)
+
+evol_artif1_2 <- evol_artif1 %>%
+  dplyr::group_by(.data$seuil_pop,.data$date) %>%
+  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::mutate(Zone = COGiter::regions %>%
+                  dplyr::filter (.data$REG == code_reg) %>%
+                  dplyr::pull(.data$NOM_REG))
+
+evol_artif1 <-dplyr::bind_rows(evol_artif1,evol_artif1_2) %>%
+  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$surface_artificialisee)
+
+evol_artif2 <- evol_artif %>%
+  dplyr::left_join(seuil_population) %>%
+  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
+  dplyr::filter (.data$date == millesime_debut) %>%
+  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
+  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::left_join(COGiter::departements %>%
+                     dplyr::filter (.data$REG == code_reg) %>%
+                     dplyr::select(.data$DEP, .data$NOM_DEP),
+                   by=c("dep"= "DEP")) %>%
+  dplyr::rename("Zone" = "NOM_DEP") %>%
+  dplyr::select(-.data$dep)
+
+evol_artif2_2 <- evol_artif2 %>%
+  dplyr::group_by(.data$seuil_pop, .data$date) %>%
+  dplyr::summarise(surface_artificialisee = sum(.data$surface_artificialisee, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::mutate(Zone = COGiter::regions %>%
+                  dplyr::filter (.data$REG == code_reg) %>%
+                  dplyr::pull(.data$NOM_REG))
+
+
+evol_artif2 <-dplyr::bind_rows(evol_artif2,evol_artif2_2) %>%
+  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$surface_artificialisee)
+
+evol_artif3 <- rbind(evol_artif1,evol_artif2) %>%  #regroupement des annees
+  dplyr::arrange(.data$seuil_pop, .data$Zone, .data$date) %>%
+  dplyr::group_by(.data$seuil_pop, .data$Zone) %>%
+  dplyr::mutate(evolution_artificialisation = round(.data$surface_artificialisee * 100 / dplyr::lag(.data$surface_artificialisee) - 100, 1)) %>%
+  dplyr::ungroup() %>%
+  dplyr::filter(.data$date == millesime_stock_artif) %>%
+  dplyr::select(.data$seuil_pop, .data$Zone, .data$evolution_artificialisation)
+
+# preparation des donnees evol_population
+
+evol_popul <- population_legale %>%
+  dplyr::mutate(date = lubridate::year(.data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$TypeZone == "Communes",
+                .data$date == millesime_population | .data$date == millesime_population_debut)
+
+evol_popul1 <- evol_popul %>%
+  dplyr::left_join(seuil_population) %>%
+  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
+  dplyr::filter (.data$date == millesime_population) %>%
+  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
+  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::left_join(COGiter::departements %>%
+                     dplyr::filter (.data$REG == code_reg) %>%
+                     dplyr::select(.data$DEP, .data$NOM_DEP),
+                   by=c("dep"= "DEP")) %>%
+  dplyr::rename("Zone" = "NOM_DEP") %>%
+  dplyr::select(-.data$dep)
+
+evol_popul1_2 <- evol_popul1 %>%
+  dplyr::group_by(.data$seuil_pop, .data$date) %>%
+  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::mutate(Zone = COGiter::regions %>%
+                  dplyr::filter (.data$REG == code_reg) %>%
+                  dplyr::pull(.data$NOM_REG))
+
+evol_popul1 <-dplyr::bind_rows(evol_popul1,evol_popul1_2) %>%
+  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$population_municipale)
+
+evol_popul2 <- evol_popul %>%
+  dplyr::left_join(seuil_population) %>%
+  dplyr::mutate(dep = substr(.data$CodeZone,1,2)) %>%
+  dplyr::filter (.data$date == millesime_population_debut) %>%
+  dplyr::group_by(.data$dep, .data$seuil_pop, .data$date) %>%
+  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::left_join(COGiter::departements %>%
+                     dplyr::filter (.data$REG == code_reg) %>%
+                     dplyr::select(.data$DEP, .data$NOM_DEP),
+                   by=c("dep"= "DEP")) %>%
+  dplyr::rename("Zone" = "NOM_DEP") %>%
+  dplyr::select(-.data$dep)
+
+evol_popul2_2 <- evol_popul2 %>%
+  dplyr::group_by(.data$seuil_pop, .data$date) %>%
+  dplyr::summarise(population_municipale = sum(.data$population_municipale, na.rm = T)) %>%
+  dplyr::ungroup() %>%
+  dplyr::mutate(Zone = COGiter::regions %>%
+                  dplyr::filter (.data$REG == code_reg) %>%
+                  dplyr::pull(.data$NOM_REG))
+
+evol_popul2 <-dplyr::bind_rows(evol_popul2,evol_popul2_2) %>%
+  dplyr::select(.data$seuil_pop, .data$Zone, .data$date, .data$population_municipale)
+
+evol_popul3 <- rbind(evol_popul1,evol_popul2) %>%  #regroupement des annees
+  dplyr::arrange(.data$seuil_pop, .data$Zone, .data$date) %>%
+  dplyr::group_by(.data$seuil_pop, .data$Zone) %>%
+  dplyr::mutate(evolution_population = round(.data$population_municipale * 100 / dplyr::lag(.data$population_municipale) - 100, 1)) %>%
+  dplyr::ungroup() %>%
+  dplyr::filter(.data$date == millesime_population) %>%
+  dplyr::select(.data$seuil_pop, .data$Zone, .data$evolution_population)
+
+data <- evol_artif3 %>%
+  dplyr::full_join(evol_popul3) %>%
+  dplyr::mutate(`Seuil de population` = factor(.data$seuil_pop, levels = c(glue::glue("communes de moins\nde ",pop_1," habitants"),
+                                                                           glue::glue("communes entre\n",pop_1," et ",pop_2, " habitants"),
+                                                                           glue::glue("communes entre\n",pop_2," et ",pop_3, " habitants"),
+                                                                           glue::glue("communes de plus\nde ",pop_3," habitants")))) %>%
+  dplyr::select(`Seuil de population`,dplyr::everything(),-seuil_pop)
+
+rm(population,seuil_population)
+rm(evol_artif,evol_artif1,evol_artif1_2,evol_artif2,evol_artif2_2,evol_artif3)
+rm(evol_popul,evol_popul1,evol_popul1_2,evol_popul2,evol_popul2_2,evol_popul3)
+
+xlsx::write.xlsx(data,file = "tableurs/graphe_3_3.xlsx")
+
+
+# graphe 3_4 (sources population légale et stock artificialisé) ------------
+code_reg <- "52"
+millesime_population <- 2019
+millesime_stock_artif <- 2020
+millesime_debut <- 2016
+millesime_debut_population <- millesime_population - (millesime_stock_artif - millesime_debut)
+millesime_fin_population <- millesime_population
+
+stock_depart <- stock_artificialise %>%
+  dplyr::mutate(date=lubridate::year(.data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$date == millesime_debut) %>%
+  dplyr::mutate(surface_artificialisee = .data$surface_artificialisee) %>%
+  dplyr::select (-.data$surf_cadastree,-.data$date) %>%
+  dplyr::rename ("stock_depart"="surface_artificialisee")
+
+# preparation des flux
+flux <- stock_artificialise %>%
+  dplyr::mutate(date=lubridate::year(.data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter((.data$date == millesime_debut) |(.data$date == millesime_stock_artif)) %>%
+  dplyr::select (-.data$surf_cadastree) %>%
+  dplyr::arrange(.data$TypeZone, .data$Zone, .data$CodeZone, .data$date) %>%
+  dplyr::group_by(.data$TypeZone, .data$Zone, .data$CodeZone) %>%
+  dplyr::mutate(flux_artificialisation = .data$surface_artificialisee - dplyr::lag(.data$surface_artificialisee) ) %>%
+  dplyr::ungroup()  %>%
+  dplyr::filter(.data$date == millesime_stock_artif) %>%
+  dplyr::select (-.data$date , -.data$surface_artificialisee)
+
+# preparation des donnees population
+evol_popul <- population_legale %>%
+  dplyr::mutate(date=lubridate::year(.data$date)) %>%
+  COGiter::filtrer_cog(reg = code_reg) %>%
+  dplyr::filter(.data$date == millesime_debut_population | .data$date == millesime_fin_population)
+
+popul_debut <- evol_popul %>%
+  dplyr::filter(.data$date == millesime_debut_population) %>%
+  dplyr::rename ("population_debut"="population_municipale") %>%
+  dplyr::select (-.data$date)
+
+evol_popul <- evol_popul %>%
+  dplyr::filter(.data$date %in% c(millesime_debut_population,millesime_fin_population)) %>%
+  dplyr::arrange(.data$date) %>%
+  dplyr::group_by(.data$TypeZone,.data$Zone,.data$CodeZone) %>%
+  dplyr::mutate(evolution_population=.data$population_municipale - dplyr::lag(.data$population_municipale))  %>%
+  dplyr::ungroup() %>%
+  dplyr::filter(.data$date %in% c(millesime_fin_population)) %>%
+  dplyr::select(.data$TypeZone,.data$Zone,.data$CodeZone,.data$evolution_population)
+
+data <-  stock_depart %>%
+  dplyr::left_join(flux) %>%
+  dplyr::left_join(popul_debut) %>%
+  dplyr::left_join(evol_popul) %>%
+  dplyr::mutate(surf_artif_par_hab_an=.data$stock_depart / .data$population_debut *10000 ) %>%
+  dplyr::mutate(surf_artif_par_nouv_hab =.data$flux_artificialisation / .data$evolution_population *10000) %>%
+  dplyr::select (.data$TypeZone,.data$CodeZone,.data$Zone,.data$surf_artif_par_hab_an,.data$surf_artif_par_nouv_hab) %>%
+  tidyr::gather(variable, valeur, 4:5) %>%
+  # dplyr::arrange(desc(.data$TypeZone),desc(.data$Zone)) %>%
+  dplyr::mutate(variable=forcats::fct_relevel(.data$variable,"surf_artif_par_nouv_hab","surf_artif_par_hab_an")) %>%
+  dplyr::mutate(Zone = forcats::fct_drop(.data$Zone) %>%
+                  forcats::fct_inorder()) %>%
+  dplyr::mutate(valeur = ifelse(.data$valeur<0, 0, valeur))
+
+rm(stock_depart,flux,evol_popul,popul_debut)
+
+xlsx::write.xlsx(data,file = "tableurs/graphe_3_4.xlsx")
+
+
+
+
+
+
+
+
+
+
+
+