コミット: ecad0dace60e0a402fea37e0ad59df0daf2356e3 - myprojects-hg-reborn (hg)

コミットメタ情報

• リビジョン: ecad0dace60e0a402fea37e0ad59df0daf2356e3 (tree)
• 日時: 2023-03-16 22:49:07
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• コミッター: Lorenzo Isella

ログメッセージ

I finished rewriting the code for the scoreboard, but I need to check the output.

変更サマリ

• modified: R-codes/create_csv_sdmx.R

差分

diff -r 99e448d15e56 -r ecad0dace60e R-codes/create_csv_sdmx.R
--- a/R-codes/create_csv_sdmx.R	Wed Mar 15 17:33:09 2023 +0100
+++ b/R-codes/create_csv_sdmx.R	Thu Mar 16 14:49:07 2023 +0100
@@ -19,18 +19,18 @@
 ### This codes write SDMX-CSV files!
 
 
-### Do it only once!
-## query <- "nama_10_gdp/A.CP_MEUR.B1GQ."
+## Do it only once!
+query <- "nama_10_gdp/A.CP_MEUR.B1GQ."
 
-## gdp <- estat_retrieval(query) %>%
-##     clean_data() %>%
-##     mutate(time_period=as.numeric(time_period)) %>%
-##     select(time_period, obs_value,geo ) 
+gdp <- estat_retrieval(query) %>%
+    clean_data() %>%
+    mutate(time_period=as.numeric(time_period)) %>%
+    select(time_period, obs_value,geo ) 
 
 
-## saveRDS(gdp, "gdp.RDS")
+saveRDS(gdp, "gdp.RDS")
 
-## gdp <- readRDS("gdp.RDS")
+gdp <- readRDS("gdp.RDS")
 
 ## gdp <- read_excel("scb_data.xlsx", "gdp_ameco_long_format") %>%
 ## gdp <- read_excel("../LI.xlsx", "gdp_ameco_long_format") %>%
@@ -57,6 +57,11 @@
 
 
 
+## gdp <- df |>
+##     select(member_state, year, gdp_eur_bn) |>
+##     distinct()
+
+
 ### Aid by objective
 
 
@@ -723,7 +728,6 @@
 
 ### aid to railways
 
-if (stop_here != 1){
 
 
 ## objectives_rail <- read_csv("CL_OBJ_RAIL+COMP+1.0.csv") %>%
@@ -866,8 +870,8 @@
     mutate(OBS_VALUE=format_col(OBS_VALUE,2,""))
 
 
-write_csv(df_rail_fin_save, "aid_rail+COMP+2.1.sdmx.csv")
-write_tsv(df_rail_fin_save, "aid_rail+COMP+2.1.sdmx.tsv")
+write_csv(df_rail_fin_save, "./railways/aid_rail+COMP+3.0.sdmx.csv")
+write_tsv(df_rail_fin_save, "./railways/aid_rail+COMP+3.0.sdmx.tsv")
 
 
 
@@ -876,8 +880,8 @@
 ############### financial aid data----> used aid
 
 
-objectives_finance <- read_csv("CL_INST_FI+COMP+1.0.csv") %>%
-    clean_data()
+## objectives_finance <- read_csv("CL_INST_FI+COMP+1.0.csv") %>%
+##     clean_data()
 
 used1 <- read_csv("recap_used2.csv") %>%
     rename("country"="Member State") %>%
@@ -943,7 +947,8 @@
            INST_FI,TIME_PERIOD, OBS_VALUE) 
 
 
-used_13 <- bind_rows(used1, used2, used3)
+    used_13 <- bind_rows(used1, used2, used3) |>
+        filter(GEO!="UK") ## this year I remove the UK
 
 used_total <- used_13 %>%
     group_by(DATAFLOW, FREQ, GEO, UNIT, TIME_PERIOD  ) %>%
@@ -970,8 +975,8 @@
 df_used_finance_fin_save <- df_used_finance_fin%>%
     mutate(OBS_VALUE=format_col(OBS_VALUE,2,""))
 
-write_csv(df_used_finance_fin_save, "aid_fi_used+COMP+2.1.sdmx.csv")
-write_tsv(df_used_finance_fin_save, "aid_fi_used+COMP+2.1.sdmx.tsv")
+write_csv(df_used_finance_fin_save, "./finance_used/aid_fi_used+COMP+3.0.sdmx.csv")
+write_tsv(df_used_finance_fin_save, "./finance_used/aid_fi_used+COMP+3.0.sdmx.tsv")
 
 
 
@@ -1073,8 +1078,8 @@
     mutate(OBS_VALUE=format_col(OBS_VALUE,2,""))
 
 
-write_csv(df_approved_finance_fin_save, "aid_fi_approved+COMP+2.1.sdmx.csv")
-write_tsv(df_approved_finance_fin_save, "aid_fi_approved+COMP+2.1.sdmx.tsv")
+write_csv(df_approved_finance_fin_save, "./finance_approuved/aid_fi_approved+COMP+3.0.sdmx.csv")
+write_tsv(df_approved_finance_fin_save, "./finance_approuved/aid_fi_approved+COMP+3.0.sdmx.tsv")
 
 
 #####################################################################
@@ -1125,18 +1130,37 @@
 #########################################################################à
 #########################################################################à
 
+
+
+
+## df_notified <- df_ini %>%
+##     filter(case_type=="Notified Aid") %>%
+##     group_by(member_state_2_letter_codes, expenditure_year, scoreboard_objective) %>%
+##     summarise(aid_gdp=sum(aid_element_as_percent_national_gdp, na.rm=T),
+##               aid_mio_eur=sum(aid_element_eur_bn, na.rm=T)*1e3) %>%
+##     ungroup %>%
+##     group_by(member_state_2_letter_codes, expenditure_year) %>% 
+##     group_modify(~ .x %>%
+##                      adorn_totals("row", fill="Total"))  %>%
+##     ungroup
+
+
+
 df_notified <- df_ini %>%
+    filter(aid_element_eur>0) |>
     filter(case_type=="Notified Aid") %>%
     group_by(member_state_2_letter_codes, expenditure_year, scoreboard_objective) %>%
     summarise(aid_gdp=sum(aid_element_as_percent_national_gdp, na.rm=T),
-              aid_mio_eur=sum(aid_element_eur_bn, na.rm=T)*1e3) %>%
+              aid_mio_eur=sum(aid_element_eur_bn, na.rm=T)*1e3,
+              aid_mio_eur_adj=sum(aid_element_eur_adj_bn, na.rm=T)*1e3
+              ) %>%
     ungroup %>%
     group_by(member_state_2_letter_codes, expenditure_year) %>% 
     group_modify(~ .x %>%
                      adorn_totals("row", fill="Total"))  %>%
     ungroup
 
-
+    
 
 
 objectives_revised_notified <- df_notified %>%
@@ -1166,7 +1190,7 @@
                                        objectives_revised_notified$NAME_en,
                                          objectives_revised_notified$CODE
                                          )) %>%
-    pivot_longer(cols=c(aid_gdp, aid_mio_eur)) %>%
+    pivot_longer(cols=c(aid_gdp, aid_mio_eur, aid_mio_eur_adj)) %>%
     mutate(DATAFLOW="COMP:AID_NOTI(1.0)",
            FREQ="A") %>%
     rename("GEO"="member_state_2_letter_codes",
@@ -1175,7 +1199,9 @@
            "OBS_VALUE"="value",
            "OBJECTIV_SCB"="scoreboard_objective") %>%
     mutate(UNIT=recode(UNIT,"aid_mio_eur"="MIO_EUR",
-                       "aid_gdp"="PC_GDP")) %>%
+                       "aid_gdp"="PC_GDP",
+                       "aid_mio_eur_adj"="MIO_EUR_ADJ"
+                       )) %>%
     select(DATAFLOW,
            FREQ,
            GEO,
@@ -1185,13 +1211,13 @@
            OBS_VALUE)
 
 
-write_csv(df_notified_fin_save, "aid_scb_obj_notified+COMP+2.1.sdmx.csv")
-write_tsv(df_notified_fin_save, "aid_scb_obj_notified+COMP+2.1.sdmx.tsv")
+write_csv(df_notified_fin_save, "./aid_notified/aid_scb_obj_notified+COMP+3.0.sdmx.csv")
+write_tsv(df_notified_fin_save, "./aid_notified/aid_scb_obj_notified+COMP+3.0.sdmx.tsv")
 
 
 
-write_csv(objectives_revised_notified,"CL_OBJ_SCB_NOTIFIED+COMP+2.1.csv")
-write_tsv(objectives_revised_notified,"CL_OBJ_SCB_NOTIFIED+COMP+2.1.tsv")
+write_csv(objectives_revised_notified,"./aid_notified/CL_OBJ_SCB_NOTIFIED+COMP+3.0.csv")
+write_tsv(objectives_revised_notified,"./aid_notified/CL_OBJ_SCB_NOTIFIED+COMP+3.0.tsv")
 
 
 ### creation of a DSD file.
@@ -1209,9 +1235,10 @@
     
 
 
-write_csv(dsd_save,"DSD_OBJ_SCB_NOTIFIED+COMP+2.1.csv")
-write_tsv(dsd_save,"DSD_OBJ_SCB_NOTIFIED+COMP+2.1.tsv")
+write_csv(dsd_save,"./aid_notified/DSD_OBJ_SCB_NOTIFIED+COMP+3.0.csv")
+write_tsv(dsd_save,"./aid_notified/DSD_OBJ_SCB_NOTIFIED+COMP+3.0.tsv")
 
+## if (stop_here != 1){
 
 ####################################################################à
 ####################################################################à
@@ -1219,10 +1246,13 @@
 ####################################################################à
 
 df_case_type <- df_ini %>%
+    filter(aid_element_eur>0) |>
     ## filter(case_type=="Notified Aid") %>%
     group_by(member_state_2_letter_codes, expenditure_year, scoreboard_objective, case_type) %>%
     summarise(aid_gdp=sum(aid_element_as_percent_national_gdp, na.rm=T),
-              aid_mio_eur=sum(aid_element_eur_bn, na.rm=T)*1e3) %>%
+              aid_mio_eur=sum(aid_element_eur_bn, na.rm=T)*1e3,
+              aid_mio_eur_adj=sum(aid_element_eur_adj_bn, na.rm=T)*1e3
+              ) %>%
     ungroup %>%
     group_by(member_state_2_letter_codes, expenditure_year) %>% 
     group_modify(~ .x %>%
@@ -1283,7 +1313,7 @@
                                        objectives_revised_notified$NAME_en,
                                          objectives_revised_notified$CODE
                                          )) %>%
-    pivot_longer(cols=c(aid_gdp, aid_mio_eur)) %>%
+    pivot_longer(cols=c(aid_gdp, aid_mio_eur, aid_mio_eur_adj)) %>%
     pivot_longer(cols=c(case_type), names_to="name2", values_to="CASE_TYPE") %>%
     select(-name2) %>% 
 
@@ -1296,7 +1326,8 @@
            "OBS_VALUE"="value",
            "OBJECTIV_SCB"="scoreboard_objective") %>%
     mutate(UNIT=recode(UNIT,"aid_mio_eur"="MIO_EUR",
-                       "aid_gdp"="PC_GDP")) %>%
+                       "aid_gdp"="PC_GDP",
+                       "aid_mio_eur_adj"="MIO_EUR_ADJ")) %>%
     mutate(CASE_TYPE=recode_many(CASE_TYPE, types_all$NAME_en,
                                  types_all$CODE)) %>% 
     select(DATAFLOW,
@@ -1310,16 +1341,16 @@
 
 
 
-write_csv(df_case_type_fin, "aid_scb_obj_type+COMP+2.1.sdmx.csv")
-write_tsv(df_case_type_fin, "aid_scb_obj_type+COMP+2.1.sdmx.tsv")
+write_csv(df_case_type_fin, "./aid_type/aid_scb_obj_type+COMP+2.1.sdmx.csv")
+write_tsv(df_case_type_fin, "./aid_type/aid_scb_obj_type+COMP+2.1.sdmx.tsv")
 
 
 
-write_csv(types_all,"CL_OBJ_SCB_TYPE+COMP+2.1.csv")
-write_tsv(types_all,"CL_OBJ_SCB_TYPE+COMP+2.1.tsv")
+write_csv(types_all,"./aid_type/CL_OBJ_SCB_TYPE+COMP+2.1.csv")
+write_tsv(types_all,"./aid_type/CL_OBJ_SCB_TYPE+COMP+2.1.tsv")
 
-write_csv(objectives_revised_notified,"CL_OBJ_SCB_OBJ+COMP+2.1.csv")
-write_tsv(objectives_revised_notified,"CL_OBJ_SCB_OBJ+COMP+2.1.tsv")
+write_csv(objectives_revised_notified,"./aid_type/CL_OBJ_SCB_OBJ+COMP+2.1.csv")
+write_tsv(objectives_revised_notified,"./aid_type/CL_OBJ_SCB_OBJ+COMP+2.1.tsv")
 
 
 
@@ -1340,9 +1371,9 @@
     
 
 
-write_csv(dsd_case_type_save,"DSD_OBJ_SCB_OBJ+COMP+2.1.csv")
-write_tsv(dsd_case_type_save,"DSD_OBJ_SCB_OBJ+COMP+2.1.tsv")
+write_csv(dsd_case_type_save,"./aid_type/DSD_OBJ_SCB_OBJ+COMP+3.0.csv")
+write_tsv(dsd_case_type_save,"./aid_type/DSD_OBJ_SCB_OBJ+COMP+3.0.tsv")
 
 
-}
+## }
 print("So far so good")