######################################################################################################### # European DataLab # L'année 2020 marquée par une très forte surmortalité ######################################################################################################### ######################################################################################################### ####David Marguerit ###Avril 2021 #========================================= # Set direction & manage packages #========================================= #--------------- # Set direction #--------------- setwd("...") #--------------- # Manage packages #--------------- #...................... # Updates library #...................... .libPaths("E:/Documents/R/library") #...................... # Load library #...................... library (openxlsx) library (eurostat) library (tidyverse) #=========================== # Prepare data for analysis #=========================== # Download data euro <- get_eurostat("demo_r_mwk_20") # Clean variables euro$year <- substr (euro$time, 1, 4) euro$week <- substr (euro$time, 6, 7) euro <- filter (euro, week != 99) euro <- select (euro, -unit, -time) euro <- rename (euro, iso2 = geo) # Compute average for 2017-2018-2019 euro1719 <- filter(euro, year == 2017 | year == 2018 | year == 2019) euro1719 <- euro1719 %>% group_by (iso2, age, sex, week) %>% summarise (values = mean (values), n = n()) %>% mutate (year = "17_19") %>% ungroup() euro1719 <- filter (euro1719, n == 3) euro1719 <- select (euro1719, -n) # Combine datasets euro <- filter (euro, year == 2020) euro <- bind_rows (euro, euro1719) rm (euro1719) # Keep European 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) euro$iso2[euro$iso2 == "EL"] <- "GR" euro <- left_join (country, euro, by = "iso2") euro <- select (euro, -iso2) rm(country) #=========================== # Compute statistics #=========================== #------------------------------- # Surmortality by month #------------------------------- # Compute indicator df1 <- euro %>% filter (age == "TOTAL", sex == "T") %>% select (-age, -sex) tmp <- df1 %>% group_by (year, week) %>% summarise (values = sum (values)) %>% mutate (ctry_fr = "Union européenne") df1 <- bind_rows (df1, tmp) df1 <- pivot_wider (df1, names_from = year, values_from = values, names_prefix = "year_") df1$var <- (df1$year_2020 - df1$year_17_19)/df1$year_17_19 * 100 # Export result wb <- createWorkbook () addWorksheet (wb, "DF1") writeData (wb, "DF1", df1) saveWorkbook (wb, "Surmortalité 2020.xlsx", overwrite = TRUE) rm (wb, df1, tmp) #------------------------------- # Surmortality by country #------------------------------- # Compute indicator df2 <- euro %>% filter (age == "TOTAL", sex == "T") %>% select (-age, -sex) df2 <- df2 %>% group_by (ctry_fr, year) %>% summarise (values = sum (values)) %>% ungroup () tmp <- df2 %>% group_by (year) %>% summarise (values = sum (values)) %>% mutate (ctry_fr = "Union européenne") df2 <- bind_rows (df2, tmp) df2 <- pivot_wider (df2, names_from = year, values_from = values, names_prefix = "year_") df2$var <- (df2$year_2020 - df2$year_17_19)/df2$year_17_19 * 100 # Export result wb <- loadWorkbook ("Surmortalité 2020.xlsx") addWorksheet (wb, "DF2") writeData (wb, "DF2", df2) saveWorkbook (wb, "Surmortalité 2020.xlsx", overwrite = TRUE) rm (wb, df2, tmp) #------------------------------- # Surmortality by age group #------------------------------- # Compute indicator df3 <- euro %>% filter (sex == "T" & age != "TOTAL" & age != "UNK") %>% select (-sex) df3 <- df3 %>% group_by (ctry_fr, year, age) %>% summarise (values = sum (values)) %>% ungroup () tmp <- df3 %>% group_by (year, age) %>% summarise (values = sum (values)) %>% mutate (ctry_fr = "Union européenne") df3 <- bind_rows (df3, tmp) df3 <- df3 %>% arrange (ctry_fr, age, year) %>% group_by (ctry_fr, age) %>% mutate (var = (values - lag (values, 1)) / lag (values, 1) * 100) %>% ungroup () df3 <- df3 %>% select (-year, -values) %>% na.omit df3 <- pivot_wider (df3, names_from = age, values_from = var) # Export result wb <- loadWorkbook ("Surmortalité 2020.xlsx") addWorksheet (wb, "DF3") writeData (wb, "DF3", df3) saveWorkbook (wb, "Surmortalité 2020.xlsx", overwrite = TRUE) rm (wb, df3, tmp) #------------------------------- # Surmortality by sex #------------------------------- # Compute indicator df4 <- euro %>% filter (sex != "T" & age == "TOTAL") %>% select (-age) df4 <- df4 %>% group_by (ctry_fr, year, sex) %>% summarise (values = sum (values)) %>% ungroup () tmp <- df4 %>% group_by (year, sex) %>% summarise (values = sum (values)) %>% mutate (ctry_fr = "Union européenne") df4 <- bind_rows (df4, tmp) df4 <- df4 %>% arrange (ctry_fr, sex, year) %>% group_by (ctry_fr, sex) %>% mutate (var = (values - lag (values, 1)) / lag (values, 1) * 100) %>% ungroup () df4 <- df4 %>% select (-year, -values) %>% na.omit df4 <- pivot_wider (df4, names_from = sex, values_from = var) # Export result wb <- loadWorkbook ("Surmortalité 2020.xlsx") addWorksheet (wb, "DF4") writeData (wb, "DF4", df4) saveWorkbook (wb, "Surmortalité 2020.xlsx", overwrite = TRUE) rm (wb, df4, tmp)