提交: 98937fb8782470cfb04572a13e5d87f7d56c12a7 - myprojects-hg-reborn (hg)

Commit MetaInfo

• 修订版: 98937fb8782470cfb04572a13e5d87f7d56c12a7 (tree)
• 时间: 2022-03-19 01:03:31
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• Commiter: Lorenzo Isella

Log Message

Script to process the scoreboard with the 2020 data.

更改概述

• add: R-codes/process_scoreboard_2021.R

差异

diff -r a1e176beaabc -r 98937fb87824 R-codes/process_scoreboard_2021.R
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/R-codes/process_scoreboard_2021.R	Fri Mar 18 17:03:31 2022 +0100
@@ -0,0 +1,273 @@
+rm(list=ls())
+library(tidyverse)
+library(janitor)
+library(openxlsx)
+library(RJSDMX)
+library(stringi)
+library(stringr)
+library(ggsci)
+library(Cairo)
+library(scales)
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+######################################################################
+
+golden_ratio <- golden()
+
+
+## year_expenditure <- 2020
+
+year_cut <- 2017
+
+years_recent <- 2017:2019
+
+df_ini <- read_excel("scb_new.xlsx")
+
+df <- df_ini %>%
+    clean_data() %>%
+    mutate(across(contains("duration"), ~excel_numeric_to_date(.x)))
+
+year_expenditure <- df %>%
+    pull(expenditure_year) %>%
+    max
+
+df_single_year <- df %>%
+    filter(expenditure_year==year_expenditure)
+    
+
+expenditure_ms <- df_single_year %>%
+    group_by(member_state) %>%
+    summarise(aid_elem_mio_eur=sum(aid_element_eur, na.rm=T)) %>%
+    ungroup %>%
+    arrange(desc(aid_elem_mio_eur))
+
+
+ms_list <- expenditure_ms %>%
+    select(member_state)
+
+
+query1 <- "tec00001/A.B1GQ.CP_MEUR."
+
+
+    
+
+    
+gdp <- estat_retrieval(query1) %>%
+    clean_data()
+
+
+
+gdp_sel <- gdp %>%
+    mutate(time_period=as.numeric(time_period)) %>%
+    filter(time_period==year_expenditure) %>%
+    select(obs_value, geo) %>%
+    left_join(y=iso_map_eu28, by=c("geo"="iso2")) %>%
+    filter(iso3 %in% ms_list$member_state) %>%
+    select(-geo)
+
+
+
+
+exp_year_ms <- df %>%
+    group_by(member_state, expenditure_year) %>%
+    summarise(exp_mio_eur=sum(aid_element_eur, na.rm=T)) %>%
+    ungroup
+
+
+
+exp_year_ms_wide <- exp_year_ms %>%
+    make_wide_with_total("expenditure_year", "exp_mio_eur")     
+
+
+save_excel(exp_year_ms_wide, "aggregated_exp_year.xlsx")
+
+
+exp_ms_comp <- df %>%
+    filter(expenditure_year>=year_cut) %>%
+    mutate(period=if_else(expenditure_year %in% years_recent,
+                          "recent", "last")) %>%
+    group_by(member_state, expenditure_year, period) %>%
+    summarise(exp_mio_eur=sum(aid_element_eur, na.rm=T)) %>%
+    ungroup %>%
+    group_by(member_state, period) %>%
+    summarise(aver_exp=mean(exp_mio_eur)) %>%
+    ungroup %>%
+    arrange(desc(aver_exp)) %>%
+    mutate(member_state=fct_inorder(member_state)) %>%
+    mutate(member_state=fct_rev(member_state))
+
+
+save_excel(exp_ms_comp, "data_aggreation_last_recent.xlsx")
+
+
+gpl <- ggplot(exp_ms_comp, aes(y  = member_state, x=aver_exp, fill=period)) +
+    geom_bar( stat="identity", alpha=1, position="dodge")+
+    my_ggplot_theme2("top")   +
+    scale_fill_npg( labels=c("2020", "Average 2017-2019"))  +
+
+    theme(legend.title = element_blank())+
+    
+coord_cartesian(xlim=c(0,12e4))+
+
+    
+## labs(title="Outward FATS - Persons employed in foreign affiliates\nof EU28 enterprises by partner country (2017)")+
+## ## coord_cartesian(ylim=c(0,8000))+
+
+scale_x_continuous(breaks=pretty_breaks(n=5) )+
+##     scale_y_discrete(labels = function(x) str_wrap(x, width = 20))+
+    
+xlab("Aid Expenditure (MIO €)")  +
+ylab(NULL)
+##  ylab(NULL)
+
+ggsave("expenditure.pdf", gpl, width=7*golden_ratio,
+       height=9, device = cairo_pdf)
+
+
+
+
+
+
+#################################################################à
+### Covid Expenditure and GDP
+
+
+expenditure_gdp <- expenditure_ms %>%
+    left_join(y=gdp_sel, by=c("member_state"="iso3")) %>%
+    mutate(percentage_gdp=aid_elem_mio_eur/obs_value) %>%
+    arrange(desc(percentage_gdp))
+
+
+expenditure_covid <- df_single_year %>%
+    filter(str_detect(all_intq, "(?i)covid")) %>%
+    filter(!str_detect(all_intq, "Temporary Framework 2009")) %>% 
+    mutate(qualifier_extended=str_split(all_intq, ";")) %>%
+    mutate(count_qualifiers=map_dbl(qualifier_extended, length))
+
+
+
+expenditure_covid_ms <- expenditure_covid %>%
+    group_by(member_state) %>%
+    summarise(aid_elem_mio_eur_covid=sum(aid_element_eur, na.rm=T)) %>%
+    ungroup %>%
+    ## arrange(desc(aid_elem_mio_eur_covid)) %>%
+    left_join(expenditure_ms, by="member_state") %>%
+    mutate(covid_share=aid_elem_mio_eur_covid/aid_elem_mio_eur) %>%
+    arrange(desc(covid_share)) %>%
+    mutate(member_state=fct_inorder(member_state)) %>%
+    mutate(member_state=fct_rev(member_state))
+
+
+save_excel(expenditure_covid_ms, "covid_expenditure.xlsx")
+
+gpl <- ggplot(expenditure_covid_ms, aes(y  = member_state, x=covid_share)) +
+    geom_bar( stat="identity", alpha=1, position="dodge")+
+    my_ggplot_theme2("top")   +
+ geom_vline(xintercept=0.5, col="blue")+
+    coord_cartesian(xlim=c(0,1))+
+
+    
+## labs(title="Outward FATS - Persons employed in foreign affiliates\nof EU28 enterprises by partner country (2017)")+
+## ## coord_cartesian(ylim=c(0,8000))+
+
+scale_x_continuous(breaks=pretty_breaks(n=5) , labels = label_percent())+
+##     scale_y_discrete(labels = function(x) str_wrap(x, width = 20))+
+    
+xlab("Share of Covid Aid")  +
+ylab(NULL)
+##  ylab(NULL)
+
+ggsave("covid_expenditure.pdf", gpl, width=7*golden_ratio,
+       height=9, device = cairo_pdf)
+
+
+
+###########################################################################
+###########################################################################
+###########################################################################
+###########################################################################
+
+gdp_aver <- gdp %>%
+    mutate(time_period=as.numeric(time_period)) %>%
+    filter(time_period >= year_cut )  %>% 
+    mutate(period=if_else(time_period %in% years_recent,
+                          "recent", "last")) %>%
+    group_by(geo, period) %>%
+    summarise(aver_gdp=mean(obs_value, na.rm=T)) %>%
+    ungroup %>%
+    left_join(y=iso_map_eu28, by=c("geo"="iso2"))
+
+
+expenditure_and_gdp <- exp_ms_comp %>%
+    left_join(y=gdp_aver, by=c("member_state"="iso3", "period" )) %>%
+    filter(geo!="UK") %>%
+    mutate(aid_on_gdp=aver_exp/aver_gdp) %>%
+    arrange(desc(aid_on_gdp)) %>%
+    mutate(member_state=fct_inorder(member_state)) %>%
+    mutate(member_state=fct_rev(member_state))
+
+
+save_excel(expenditure_and_gdp, "expenditure_vs_gdp.xlsx")
+
+
+gpl <- ggplot(expenditure_and_gdp, aes(y  = member_state, x=aid_on_gdp, fill=period)) +
+    geom_bar( stat="identity", alpha=1, position="dodge")+
+    my_ggplot_theme2("top")   +
+    scale_fill_npg( labels=c("2020", "Average 2017-2019"))  +
+
+    theme(legend.title = element_blank())+
+    
+coord_cartesian(xlim=c(0,5e-2))+
+
+    
+## labs(title="Outward FATS - Persons employed in foreign affiliates\nof EU28 enterprises by partner country (2017)")+
+## ## coord_cartesian(ylim=c(0,8000))+
+
+    scale_x_continuous(breaks=pretty_breaks(n=5),
+                       labels = label_percent(accuracy=1) )+
+##     scale_y_discrete(labels = function(x) str_wrap(x, width = 20))+
+    
+xlab("Aid over GDP")  +
+ylab(NULL)
+##  ylab(NULL)
+
+ggsave("expenditure_vs_gdp.pdf", gpl, width=7*golden_ratio,
+       height=9, device = cairo_pdf)
+
+
+
+
+
+
+#######################################################################
+
+
+
+
+
+## A test for Antoine
+
+## df_tot_by_year <- df %>%
+##     filter(expenditure_year>=year_cut)%>%
+##     group_by(expenditure_year) %>%
+##     summarise(expenditure=sum(aid_element_eur, na.rm=T)) %>%
+##     ungroup %>%
+##     arrange(expenditure_year)
+
+
+## df_env <- df %>%
+##     filter(scoreboard_objective=="Environmental protection including energy savings", expenditure_year>=year_cut ) %>%
+##     group_by(expenditure_year, scoreboard_objective) %>%
+##     summarise(objective_exp=sum(aid_element_eur, na.rm=T)) %>%
+##     ungroup %>%
+##     arrange(expenditure_year) %>%
+##     mutate(percentage=objective_exp/df_tot_by_year$expenditure)
+
+
+
+
+
+
+
+print("So far so good")