提交: 4fb846d53255fe3ad23b90eb85f758a01021a4de - myprojects-hg-reborn (hg)

Commit MetaInfo

• 修订版: 4fb846d53255fe3ad23b90eb85f758a01021a4de (tree)
• 时间: 2021-04-21 21:23:02
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• Commiter: Lorenzo Isella

Log Message

A code to fix some typos in the ISIS database about the allocated budgets.

更改概述

• add: R-codes/correct_isis.R

差异

diff -r 85c19e4289cb -r 4fb846d53255 R-codes/correct_isis.R
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/R-codes/correct_isis.R	Wed Apr 21 14:23:02 2021 +0200
@@ -0,0 +1,136 @@
+rm(list=ls())
+
+## last saved on  Time-stamp: "2021-04-21 11:50:39 lorenzo"
+
+
+library(tidyverse)
+library(janitor)
+library(lubridate)
+library(openxlsx)
+library(stringr)
+library(stringi)
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+my_sel <- c("SA.54376",
+            "SA.54375",
+            "SA.54042",
+            "SA.23842",
+            "SA.50760",
+            "SA.20961",
+            "SA.24091",
+            "SA.22989",
+            "SA.19238",
+            "SA.20857",
+            "SA.20875",
+            "SA.46894",
+            "SA.40171",
+            "SA.53525",
+            "SA.39399",
+            "SA.47205",
+            "SA.33134",
+            "SA.33384",
+            "SA.32125",
+            "SA.44840",
+            "SA.43756")
+
+df_isis_ini <- read.xlsx("SA-ISIS_full_extraction_DWH-258527.xlsx") %>%
+    as_tibble
+
+## df_isis_ini <- readRDS("scoreboard.RDS")
+
+df_isis <- df_isis_ini %>%
+    clean_data() %>%
+    mutate(aid_start_date=excel_numeric_to_date(aid_start_date),
+           aid_end_date=excel_numeric_to_date(aid_end_date)
+           )
+
+df_isis_non_gber <- df_isis %>% 
+    filter(procedure_type_code!="X",
+           !is.na(decision_date)) %>%
+    mutate(decision_date=excel_numeric_to_date(decision_date))  %>%
+    mutate(case_creation_date=excel_numeric_to_date(case_creation_date)) %>% 
+    mutate(year=year(decision_date)) %>%
+    distinct(procedure_reference, .keep_all=T) %>%
+    mutate(primary_law=tolower(primary_law),
+           secondary_law=tolower(secondary_law))
+
+my_filter <- df_isis_non_gber %>%
+    filter(grepl("environment", secondary_law)) %>%
+    pull(secondary_law) %>%
+    unique
+
+df_isis_non_gber <- df_isis_non_gber %>%
+    filter(secondary_law %in% c(my_filter))
+
+df_isis_non_gber_budget <- df_isis_non_gber %>%
+    filter(!is.na(overall_aid_amount_eur)) %>% 
+    distinct(case_reference, .keep_all=T) %>%
+    filter(decision_type_article %in% c("Decision not to raise objections" ,
+                                        "Corrigendum"
+                                        ))
+
+
+non_gber_stat_ms_budget <- df_isis_non_gber_budget %>%
+    ## mutate(year=year(decision_date)) %>%
+    group_by(year, member_state_long_name) %>%
+    summarise(allocated_mio_eur=sum(overall_aid_amount_eur)) %>%
+    ungroup
+
+
+outliers <- detectOutlier(non_gber_stat_ms_budget$allocated_mio_eur)
+
+
+test_outliers <- non_gber_stat_ms_budget %>%
+    filter(outliers!="no") %>%
+    arrange(desc(allocated_mio_eur))
+
+save_excel(test_outliers, "outliers_to_check.xlsx")
+
+
+test_outliers2 <- df_isis_non_gber %>%
+    filter(year %in% test_outliers$year,
+           member_state_long_name %in% test_outliers$member_state_long_name) %>%
+    arrange(desc(overall_aid_amount_eur))
+
+save_excel(test_outliers2, "outliers_to_check2.xlsx")
+
+
+
+df_isis_corrections <- df_isis_non_gber %>%
+    filter(case_reference %in% my_sel) %>%
+    select(case_reference,procedure_reference ,
+           case_title, case_creation_date,
+           overall_aid_amount_eur) %>%
+    arrange(desc(overall_aid_amount_eur)) %>% 
+    mutate(amount_corrected=c(2042,
+                              1903,
+                              93.810483,
+                              70,
+                              30,
+                              12.547515,
+                              10.174779,
+                              10,
+                              7,
+                              2.630162,
+                              1,
+                              78.135/4.4904,
+                              30.95,
+                              3e4,
+                              22.1e3,
+                              20160,
+                              19.5,
+                              10450,
+                              8350,
+                              6783,
+                              6000                              
+                              )) %>%
+    mutate(comments=if_else(overall_aid_amount_eur==amount_corrected,
+                                         "further checks needed", "OK"))
+
+
+save_excel(df_isis_corrections,  "isis_corrections.xlsx")
+write_csv(df_isis_corrections, "isis_corrections.csv")
+
+
+print("So far so good")