提交: 9bbf97f599906b991ce8420f8e7bb3cad3717394 - myprojects-hg-reborn (hg)

Commit MetaInfo

• 修訂: 9bbf97f599906b991ce8420f8e7bb3cad3717394 (tree)
• 時間: 2024-12-17 22:37:45
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• Commiter: Lorenzo Isella

Log Message

I added a script to handle the various lists of SA numbers for financial aid to banks.

Change Summary

• add: R-codes/consolidate_list.R

差異

diff -r fd149a6c8464 -r 9bbf97f59990 R-codes/consolidate_list.R
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/R-codes/consolidate_list.R	Tue Dec 17 14:37:45 2024 +0100
@@ -0,0 +1,184 @@
+rm(list=ls())
+
+library(tidyverse)
+library(janitor)
+library(openxlsx)
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+
+
+positive_decisions <- c("Article 4(3) - decision not to raise objections", "Article 7(3) - positive decision"  )
+
+
+
+df_ini <- read_csv("../input/cases_to_filter_complete.csv")
+
+df <- df_ini |>
+    clean_data() |>
+    all_to_lower()
+
+df_out <- df |>
+    filter(banking_aid_yes_no=="yes") |>
+    distinct(case_reference, .keep_all=T)
+
+save_excel(df_out,"../output/case_list_provisional.xlsx")
+
+
+df_ec <- read_csv("../input/SA-Case_Full_Extract_Case_EC_2024-11-1511-06-55.csv.gz") |>
+    clean_names()
+
+
+df_out2 <- df_out |>
+    mutate(case_reference=toupper(case_reference))
+
+df_ec_out <- df_ec |>
+    filter(case_reference %in% df_out2$case_reference) |>
+    group_by(case_reference, case_title) |>
+    summarise(primary_obj=paste(unique(primary_objective), collapse=("------")),
+              secondary_obj=paste(unique(secondary_objective), collapse=("------")),
+              aid_instr=paste(unique(aid_instrument), collapse=("------")),
+              MS=unique(member_state_long_name),
+              primary_legal_basis=paste(unique(primary_law), collapse=("------")),
+              secondary_legal_basis=paste(unique(secondary_law_in_force), collapse=("------")),
+              internal_qualifier=paste(unique(internal_qualifier_s), collapse=("------"))
+              
+              ) |>
+    ungroup() |>
+    arrange(case_reference)
+
+
+save_excel(df_ec_out, "../output/list_extra_variables.xlsx")
+
+
+
+
+df_fin <- read_csv("../input/YES_cases-Copy.csv") |>
+    mutate(case_reference=toupper(case_reference))
+
+
+extra_cases <- setdiff(df_fin$case_reference, df_ec_out$case_reference)
+extra_cases2 <- setdiff(df_ec_out$case_reference,df_fin$case_reference )
+
+
+
+df_rod_approuved <- read_csv("../input/20241212_Internal_qualifier_banking_aid-approved.csv") |>
+    clean_data()
+
+df_rod_dupes <- df_rod_approuved |>
+    get_dupes_short(sa_case)
+
+save_excel(df_rod_dupes, "../output/state_aid_approved_database_duplicated.xlsx")
+
+
+
+df_not <- df_rod_approuved |>
+    filter(str_starts(in_lorenzos_extraction,"Not" )) |>
+    mutate(sa_case=remove_trailing_spaces(sa_case)) |> 
+    mutate(sa_case=str_replace(sa_case, " ", ""))
+
+df_not2 <- setdiff(df_rod_approuved$sa_case, df_fin$case_reference)
+
+df_sa_present <- df_fin |>
+    filter(case_reference %in% df_not$sa_case)
+
+save_excel(df_sa_present, "../output/cases_present_initial_list_lorenzo.xlsx")
+
+df_ec_not <- df_ec |>
+    filter(case_reference %in% df_not$sa_case)
+
+
+df_ec_not_real <- df_ec |>
+    filter(case_reference %in% df_not2)
+
+
+
+df_ec_not_article <- df_ec_not |>
+    group_by(case_reference) |>
+    summarise(article=paste(unique(decision_type_article ), collapse="------")) |>
+    ungroup() |>
+    mutate(articles_positive_decision=if_else(article %in% positive_decisions,
+                                              "yes", "no"))
+
+
+sa_missing <- setdiff(df_not$sa_case, df_ec$case_reference)
+
+df_ec_not_compact <- df_ec_not_real |>
+    group_by(case_reference, case_title) |>
+    summarise(article=paste(unique(decision_type_article ), collapse="------"),
+              primary_obj=paste(unique(primary_objective), collapse=("------")),
+              secondary_obj=paste(unique(secondary_objective), collapse=("------")),
+              aid_instr=paste(unique(aid_instrument), collapse=("------")),
+              MS=unique(member_state_long_name),
+              primary_legal_basis=paste(unique(primary_law), collapse=("------")),
+              secondary_legal_basis=paste(unique(secondary_law_in_force), collapse=("------")),
+              internal_qualifier=paste(unique(internal_qualifier_s), collapse=("------"))
+              
+              ) |>
+    ungroup() |>
+    arrange(case_reference) |>
+    mutate(articles_positive_decision=if_else(article %in% positive_decisions,
+                                              "yes", "no")) |>
+    mutate(new_objective=paste(primary_obj, secondary_obj, sep=" ")) |>
+    mutate(is_remedy = if_else(str_detect(new_objective, "Remedy for a serious disturbance in the economy"), "yes", "no" )) |>
+    mutate(is_temporary_framework=if_else(str_detect(internal_qualifier, "Temporary Framework"), "yes", "no")) |>
+    mutate(is_financial_crisis=if_else(str_detect(internal_qualifier, "Financial crisis case"), "yes", "no")) |>
+    mutate(is_UK=if_else(MS=="United Kingdom", "yes", "no")) |>
+    mutate(is_prolongation=if_else(str_detect(case_title, "(?i)prolongation"),
+                                   "yes", "no"))
+    
+           
+
+
+
+df_ec_filtered <- df_ec_not_compact |>
+    filter(is_UK=="no",
+           articles_positive_decision=="yes")
+
+
+save_excel(df_ec_filtered, "../output/cases_investigation.xlsx")           
+
+
+
+tt <- "SA.103450"
+
+test <- df_ec_filtered |>
+    filter(case_reference==tt)
+
+
+df_rod_fin <- df_ec_filtered |>
+    select(case_reference) |>
+    mutate(origin="Rodrigo")
+
+df_lor_fin <- df_fin|>
+    select(case_reference) |>
+    mutate(origin="Lorenzo")
+
+
+df_fin_combined <- bind_rows(df_rod_fin, df_lor_fin)
+
+save_excel(df_fin_combined, "../output/combined_list_A3_D3.xlsx")
+
+df_fin_combined_expanded <- df_ec |>
+    filter(case_reference %in% df_fin_combined$case_reference) |>
+    group_by(case_reference, case_title) |>
+    summarise(primary_obj=paste(unique(primary_objective), collapse=("------")),
+              secondary_obj=paste(unique(secondary_objective), collapse=("------")),
+              aid_instr=paste(unique(aid_instrument), collapse=("------")),
+              MS=unique(member_state_long_name),
+              primary_legal_basis=paste(unique(primary_law), collapse=("------")),
+              secondary_legal_basis=paste(unique(secondary_law_in_force), collapse=("------")),
+              internal_qualifier=paste(unique(internal_qualifier_s), collapse=("------"))
+              
+              ) |>
+    ungroup() |>
+    arrange(case_reference)
+
+
+df_out_exp <- df_fin_combined |>
+    left_join(y=df_fin_combined_expanded, by=c("case_reference"))
+
+save_excel(df_out_exp, "../output/combined_list_A3_D3_case_ec.xlsx")
+
+
+print("So far so good")