######################################################################################################### # European DataLab: # Véhicules nouvellement immatriculés : une baisse insuffisante des émissions de Co2 ######################################################################################################### ######################################################################################################### ####David Marguerit ###february 2022 rm(list=ls()) #========================================= # Set direction & manage packages #========================================= #--------------- # Set direction #--------------- setwd("U:/EDL/") #--------------- # Manage packages #--------------- # Update packages #...................... update.packages(ask = FALSE) # Load library #...................... library (openxlsx) library (ggpmisc) library (tidyverse) #========================================= # Load databases # Download data from: http://co2cars.apps.eea.europa.eu/?source=%7B%22track_total_hits%22%3Atrue%2C%22query%22%3A%7B%22bool%22%3A%7B%22must%22%3A%5B%7B%22constant_score%22%3A%7B%22filter%22%3A%7B%22bool%22%3A%7B%22must%22%3A%5B%7B%22bool%22%3A%7B%22should%22%3A%5B%7B%22term%22%3A%7B%22year%22%3A2020%7D%7D%5D%7D%7D%2C%7B%22bool%22%3A%7B%22should%22%3A%5B%7B%22term%22%3A%7B%22scStatus%22%3A%22Provisional%22%7D%7D%5D%7D%7D%5D%7D%7D%7D%7D%5D%7D%7D%2C%22display_type%22%3A%22tabular%22%7D # Download one year per file and call it "dataYYYY.csv" #========================================= for (i in 2010:2020) { print (i) tmp <- read_csv (paste0 ("U:/Download/data", i, ".csv"), col_types = cols(.default = "c")) tmp <- rename(tmp, "enedc" = "Enedc (g/km)", "ewltp" = "Ewltp (g/km)", "cat" = "Ct", "mass" = "m (kg)", "power" = "ep (KW)") tmp <- filter (tmp, is.na (cat) == FALSE & is.na (Ft) == FALSE) # Data format tmp$Ft <- tolower(tmp$Ft) tmp$enedc <- as.numeric(tmp$enedc) tmp$ewltp <- as.numeric(tmp$ewltp) tmp$r <- as.numeric(tmp$r) tmp$year <- as.numeric(tmp$year) tmp$mass <- as.numeric(tmp$mass) tmp$power <- as.numeric(tmp$power) # Create groups for type of fuel tmp <- tmp %>% mutate (fuel = case_when( Ft == "biodiesel" | Ft == "cng" | Ft == "diesel" | Ft == "e85" | Ft == "lpg" | Ft == "ng" | Ft == "ng-biomethane" | Ft == "ng_biomethane" | Ft == "petrol" | Ft == "petrol-gas" ~ "combustion", Ft == "diesel-electric" | Ft == "diesel/electric" | Ft == "diesel-electric" | Ft == "hybrid/petrol/e" | Ft == "petrol-electric" | Ft == "petrol phev" | Ft == "petrol/electric" ~ "hybrid", Ft == "electric" | Ft == "hydrogen" ~ "elec_hydro")) # keep passenger cars tmp <- filter (tmp, cat == "m1" | cat == "M1" | cat == "M1G") tmp <- tmp %>% group_by (Country, fuel) %>% summarise (enedc = weighted.mean (enedc, r, na.rm = TRUE), ewltp = weighted.mean (ewltp, r, na.rm = TRUE), mass = weighted.mean (mass, r, na.rm = TRUE), power = weighted.mean (power, r, na.rm = TRUE), ncars = sum (r), year = mean(year)) %>% ungroup() if (i == 2010) { df <- tmp } else { df <- bind_rows (df, tmp) } } # Save data save(df, file = "Database.RData") #========================================= # Clean data #========================================= load ("Database.RData") # Keep EU countries country <- read_delim ("https://www.european-datalab.com/wp-content/uploads/2021/04/Country-groups.csv", ";", col_types = "ccccciiiii", locale = locale(encoding = "WINDOWS-1252")) country <- country %>% filter (eu == 1) %>% select (ctry_fr , iso2) df <- left_join (country, df, by = c("iso2" = "Country")) df <- select (df, -iso2) rm(country) # keep 2014 onward (Croatia is missing before 2014) df <- filter (df, year >= 2014) # Drop missing values df <- filter (df, is.na (fuel) == FALSE & is.na(enedc) == FALSE) #========================================= # Compute statistics #========================================= #------------------------------- # Graph1: Emissions by country in 2020 #------------------------------- # By country g1 <- df %>% group_by (ctry_fr, year) %>% summarise (emission = weighted.mean (enedc, ncars, na.rm = TRUE), year = mean(year)) %>% ungroup() # For EU g1eu <- df %>% group_by (year) %>% summarise (emission = weighted.mean (enedc, ncars, na.rm = TRUE), year = mean(year), ctry_fr = "UE-27") %>% ungroup() # Clean data g1 <- bind_rows (g1, g1eu) g1 <- g1 %>% filter (year == min(year) | year == max(year)) g1 <- pivot_wider (g1, names_from = year, values_from = emission) # Save data wb <- createWorkbook() addWorksheet(wb, "Graph 1") writeData(wb, "Graph 1", g1) saveWorkbook(wb, "Cars emission.xlsx", overwrite = TRUE) rm (g1eu, wb) #------------------------------- # Graph 2: target 2035: -100% compared to the limits in 2021 (95g CO2/km) #------------------------------- # Compute trajectory to reach targets g2 <- g1 g2 <- rename (g2, "y2014" = "2014", "y2020" = "2020") g2$y2035 <- 0 g2$evol14_20 <- (g2$y2020 - g2$y2014) / (2020-2014) g2$evol20_35 <- (g2$y2035 - g2$y2020) / (2035-2020) # Save data wb <- loadWorkbook("Cars emission.xlsx") addWorksheet(wb, "Graph 2") writeData(wb, "Graph 2", g2) saveWorkbook(wb, "Cars emission.xlsx", overwrite = TRUE) rm (g2, wb) #------------------------------- # Source of the evolution #------------------------------- # By country g3 <- df %>% filter (year == min (year) | year == max(year)) %>% select (ctry_fr, year, fuel, ncars) g3 <- pivot_wider (g3, names_from = fuel, values_from = ncars) g3[is.na(g3)] <- 0 # For EU-27 g3eu <- g3 %>% group_by (year) %>% summarise (combustion = sum(combustion), elec_hydro = sum(elec_hydro), hybrid = sum(hybrid), year = mean(year)) %>% mutate (ctry_fr = "UE-27") %>% ungroup() g3 <- bind_rows (g3, g3eu) # Compute stat g3$total <- g3$elec_hydro + g3$combustion + g3$hybrid g3$perc_elec_hydro <- g3$elec_hydro / (g3$total) * 100 g3 <- select (g3, ctry_fr, year, perc_elec_hydro) g3 <- pivot_wider (g3, names_from = year, values_from = perc_elec_hydro) # Save data wb <- loadWorkbook("Cars emission.xlsx") addWorksheet(wb, "Graph 3") writeData(wb, "Graph 3", g3) saveWorkbook(wb, "Cars emission.xlsx", overwrite = TRUE) rm (g3eu, wb)