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

コミットメタ情報

• リビジョン: b054b5b0f13d72abe202d579dc73aeef05c7074f (tree)
• 日時: 2024-06-11 02:45:28
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• コミッター: Lorenzo Isella

ログメッセージ

I am translating the country fiche from markdown to quarto.

変更サマリ

• add: markdown/fiches_quarto.qmd

差分

diff -r 68fee9ce6b1f -r b054b5b0f13d markdown/fiches_quarto.qmd
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/markdown/fiches_quarto.qmd	Mon Jun 10 19:45:28 2024 +0200
@@ -0,0 +1,1024 @@
+---
+title: "My Document"
+format:
+  docx:
+    toc: false
+    number-sections: true
+    highlight-style: github
+execute:
+  echo: false
+  warning: false
+  message: false
+---
+
+
+```{r, scoreboard, echo=FALSE, eval=TRUE}
+
+
+options( scipen = 16 )
+
+## options(tidyverse.quiet = TRUE)
+## suppressWarnings(suppressMessages(library(janitor)))
+## suppressWarnings(suppressMessages(library(viridis)))
+## suppressWarnings(suppressMessages(library(scales)))
+## suppressWarnings(suppressMessages(library(treemap)))
+## suppressWarnings(suppressMessages(library(ggsci)))
+## suppressWarnings(suppressMessages(library(flextable)))
+## suppressWarnings(suppressMessages(library(readr)))
+
+## options(message = FALSE)
+
+
+library(tidyverse, warn.conflicts = FALSE)
+library(janitor)
+library(viridis)
+library(scales)
+library(treemap)
+library(ggsci)
+library(flextable)
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+
+
+
+ ## Suppress summarise info
+options(dplyr.summarise.inform = FALSE)
+
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+
+MS <-  "AUT" ## state
+
+cutoff <- 4 ## cutoff for keeping some expenditure objectives
+
+ms3 <- iso_map_eu27 |> 
+    filter(iso3==MS) |> 
+    pull(country) |> 
+    as.character()
+
+covid_years <- c(2020, 2021, 2022)
+
+ukraine_years <- c(2022)
+
+
+
+scoreboard <- readRDS("../scoreboard.RDS") |> 
+    filter(!is.na(aid_element_eur)) |>
+    mutate(case_type=if_else(case_number=="SA.38701", "Notified Aid",
+                             case_type)) |> 
+    rename("year"="expenditure_year",
+           "amount_spent_aid_element_in_eur_million"  ="aid_element_eur",
+           "case_no"="case_number",
+           "procedure_name"="case_type",
+           "all_objective_names_gber_only"= "all_gber_obj",
+           "aid_instrument_name"="aid_instrument") |>
+    filter(amount_spent_aid_element_in_eur_million>0)
+
+
+
+
+scoreboard_ms <- scoreboard |>   
+    filter(member_state_3_letter_codes==MS)
+
+year_focus <- scoreboard_ms |> 
+    pull(year) |> 
+    max()
+
+
+ini_focus <- year_focus-10
+
+
+cases <- scoreboard_ms |> 
+    filter(year==max(year),
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0
+           ) |>
+    select(member_state_3_letter_codes,case_no, year,amount_spent_aid_element_in_eur_million,
+           procedure_name) |> 
+    ## filter(grepl("SA.", case_no)) %>%
+    distinct(case_no,.keep_all =T )
+
+
+
+
+
+total_amount_eu27 <- scoreboard |> 
+    filter(year==max(year),
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million))
+
+total_amount_eu27_covid_years <- scoreboard |> 
+    filter(year %in% covid_years,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million),
+              .by=year) |>
+    mutate(country="EU27")
+
+
+
+total_amount_eu27_ukraine_years <- scoreboard |> 
+    filter(year %in% ukraine_years,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million),
+              .by=year) |>
+    mutate(country="EU27")
+
+
+
+
+
+total_amount_eu27_covid <- scoreboard |> 
+    filter(year==max(year),
+           ## all_intq %in% covid_qualifiers,
+           covid==T,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million)) |>
+    ungroup()
+
+total_amount_eu27_ukraine <- scoreboard |> 
+    filter(year==max(year),
+           ## all_intq %in% covid_qualifiers,
+           ukraine==T,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million)) |>
+    ungroup()
+
+
+
+
+
+total_amount_eu27_covid_covid_years <- scoreboard |> 
+    filter(year %in% covid_years,
+           ## all_intq %in% covid_qualifiers,
+           covid==T,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million), .by=c(year)) |>
+    mutate(country="EU27")
+
+
+total_amount_eu27_ukraine_ukraine_years <- scoreboard |> 
+    filter(year %in% ukraine_years,
+           ## all_intq %in% covid_qualifiers,
+           ukraine==T,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million), .by=c(year)) |>
+    mutate(country="EU27")
+
+
+
+
+total_amount_MS <- scoreboard %>%
+    filter(year==max(year),
+           member_state_3_letter_codes==MS,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) %>%
+    summarise(total=sum(amount_spent_aid_element_in_eur_million)) |>
+    ungroup()
+
+
+total_amount_MS_covid <- scoreboard |> 
+    filter(year==max(year),
+           member_state_3_letter_codes==MS,
+           ## all_intq %in% covid_qualifiers,
+           covid==T,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million)) |>
+    ungroup()
+
+
+
+total_amount_MS_ukraine <- scoreboard |> 
+    filter(year==max(year),
+           member_state_3_letter_codes==MS,
+           ## all_intq %in% covid_qualifiers,
+           ukraine==T,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0,
+           member_state_3_letter_codes %in% iso_map_eu27$iso3
+           ) |> 
+    summarise(total=sum(amount_spent_aid_element_in_eur_million)) |>
+    ungroup()
+
+
+
+covid_summary <- total_amount_eu27 |> 
+    rename("total_eu"="total") |> 
+    mutate(total_eu27_covid=total_amount_eu27_covid$total,
+           total_ms=total_amount_MS$total,
+           total_ms_covid=total_amount_MS_covid$total) |> 
+    mutate(eu_covid_percentage=total_eu27_covid/total_eu,
+           ms_covid_percentage=total_ms_covid/total_ms)
+
+
+
+
+ukraine_summary <- total_amount_eu27 |> 
+    rename("total_eu"="total") |> 
+    mutate(total_eu27_ukraine=total_amount_eu27_ukraine$total,
+           total_ms=total_amount_MS$total,
+           total_ms_ukraine=total_amount_MS_ukraine$total) |> 
+    mutate(eu_ukraine_percentage=total_eu27_ukraine/total_eu,
+           ms_ukraine_percentage=total_ms_ukraine/total_ms)
+
+
+covid_summary_print <- covid_summary |>
+    mutate(across(starts_with("total"), ~ ifelse(.x>1e3,
+        paste("EUR",round(.x/1e3,1), "billion", sep=" "),
+        paste("EUR", round(.x,1), "million", sep=" ") )))
+
+
+
+ukraine_summary_print <- ukraine_summary |>
+    mutate(across(starts_with("total"), ~ ifelse(.x>1e3,
+        paste("EUR",round(.x/1e3,1), "billion", sep=" "),
+        paste("EUR", round(.x,1), "million", sep=" ") )))
+
+
+covid_summary_long <- covid_summary |> 
+    select(contains("percentage")) |> 
+    pivot_longer(cols =  c(eu_covid_percentage ,
+                           ms_covid_percentage
+                           ) ,  names_to= "country", values_to="covid_percentage" ) |> 
+    mutate(ll=format_col(covid_percentage*100,1)) |> 
+    mutate(ll=paste(ll, "%", sep="")) |> 
+    arrange(country) |> 
+    mutate(country=fct_inorder(country))
+
+
+
+ukraine_summary_long <- ukraine_summary |> 
+    select(contains("percentage")) |> 
+    pivot_longer(cols =  c(eu_ukraine_percentage ,
+                           ms_ukraine_percentage
+                           ) ,  names_to= "country", values_to="ukraine_percentage" ) |> 
+    mutate(ll=format_col(ukraine_percentage*100,1)) |> 
+    mutate(ll=paste(ll, "%", sep="")) |> 
+    arrange(country) |> 
+    mutate(country=fct_inorder(country))
+
+
+
+
+n_cases <- nrow(cases)
+
+stat_cases <- cases |> 
+    tabyl(procedure_name) |> 
+    mutate(percent=round_preserve_sum(percent*100,1)) |> 
+    add_total() |> 
+    mutate(percent=format_col(percent,1)) |> 
+    mutate(percent=paste(percent, "%", sep="")) 
+
+
+old_cases <- scoreboard |> 
+    filter(year>=ini_focus, year!= year_focus,  member_state_3_letter_codes==MS)
+
+new_cases_list <- setdiff(cases$case_no, old_cases$case_no)
+
+cases_new <- scoreboard_ms |> 
+    filter(case_no %in% new_cases_list ,
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0
+           ) |> 
+    select(member_state_3_letter_codes,case_no, year,amount_spent_aid_element_in_eur_million,
+           procedure_name) |> 
+    ## filter(grepl("SA.", case_no)) %>%
+    distinct(case_no,.keep_all =T )
+
+stat_cases_new <- cases_new |> 
+    tabyl(procedure_name) |> 
+    mutate(percent=round(percent*100,1))
+
+
+if (nrow(stat_cases_new)==0){
+    stat_cases_new <- stat_cases_new |> 
+        add_row(percent=0)
+
+    
+}
+
+stat_cases2 <- scoreboard |> 
+    filter(year>=ini_focus, member_state_3_letter_codes==MS,
+           amount_spent_aid_element_in_eur_million>0,
+           procedure_name!="Fisheries Block Exemption Regulation") |> 
+    group_by(year, procedure_name) |> 
+    summarise(expenditure=sum(amount_spent_aid_element_in_eur_million/1e3),
+              expenditure_deflated=sum(aid_element_eur_adj/1e3)
+              ) |> 
+    ungroup() 
+
+
+
+stat_cases3 <- scoreboard |> 
+    filter(year>=ini_focus, member_state_3_letter_codes==MS,
+           amount_spent_aid_element_in_eur_million>0) |> 
+    ## mutate(procedure2=if_else(procedure_name=="General Block Exemption Regulation", "(G)BER", procedure_name)) %>% 
+    group_by( procedure_name) |> 
+    summarise(expenditure=sum(amount_spent_aid_element_in_eur_million)/1e3)  |> 
+    ungroup()  |> 
+    mutate(expenditure=round_preserve_sum(expenditure, 1)) |> 
+    mutate(percent=expenditure/sum(expenditure)*100)  |> 
+    mutate(percent=round_preserve_sum(percent,1))  |> 
+    add_total()  |> 
+    mutate(percent=format_col(percent,1),
+           expenditure=format_col(expenditure,1)) |> 
+    mutate(percent=paste(percent,"%", sep=""))  |> 
+    mutate(expenditure=if_else(expenditure!="0.0", expenditure,"less than 0.1"),
+           percent=if_else(percent!="0.0%", percent, "less than 0.1%"))
+
+
+
+
+
+
+top5 <- scoreboard_ms  |> 
+    filter(year==max(year)) |> 
+    group_by(case_no) |> 
+    summarise(expenditure=sum(amount_spent_aid_element_in_eur_million)) %>%
+    ungroup()  |> 
+    arrange(desc(expenditure))  |> 
+    mutate(share=expenditure/sum(expenditure))  |> 
+    slice(1:5)  |> 
+    mutate(cum_share=cumsum(share),
+           cum_expenditure=cumsum(expenditure))  |> 
+    filter(expenditure>0)
+
+
+
+
+top5_share <- top5  |> 
+    pull(cum_share)  |> 
+    max()  |> 
+    multiply_by(100)  |> 
+    round(1)
+
+
+top5_expenditure <- top5 |> 
+    pull(cum_expenditure) |> 
+    max()
+
+if (top5_expenditure>1e3){
+
+    top5_expenditure_print <- top5_expenditure |>
+        divide_by(1e3) |>
+        round(1) |> 
+        paste("EUR ", x=_, " billion", sep="")
+
+
+} else{
+    
+    top5_expenditure_print <- top5_expenditure |>
+        ## divide_by(1e3) |>
+        round(1) |> 
+        paste("EUR ", x=_, "million", sep="")
+
+    
+}
+
+
+total_expenditure <- scoreboard_ms |> 
+    filter(year>=ini_focus) |> 
+    pull(amount_spent_aid_element_in_eur_million) |> 
+    sum(na.rm=T)  ## |>  divide_by(1e3)  |> 
+    ## round(1)
+
+if (total_expenditure>1e3) { total_expenditure_temp <- total_expenditure |>
+                                 divide_by(1e3) |>
+                                 round(1)
+total_expenditure_print <- paste("EUR", total_expenditure_temp, "billion", sep=" ")
+                                 
+} else { total_expenditure_temp <- total_expenditure |>
+           round(1)
+
+           total_expenditure_print <- paste("EUR", total_expenditure_temp, "million", sep=" ")
+          
+
+}
+
+
+
+total_expenditure_last <- scoreboard_ms |> 
+    filter(year==year_focus) |> 
+    pull(amount_spent_aid_element_in_eur_million) |> 
+    sum(na.rm=T)
+
+if (total_expenditure>1e3){
+    total_expenditure_last_print <- total_expenditure_last |>
+        divide_by(1e3) |>
+        round(1) |> 
+        paste("EUR", x=_ ,"million", sep=" " )
+    
+} else { 
+
+    total_expenditure_last_print <- total_expenditure_last |>
+        round(1) |>
+        paste("EUR", x=_ ,"million", sep=" " )
+
+    
+}
+
+
+cofinancing <- scoreboard_ms |> 
+    filter( year==max(year),
+          ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0
+           )  |> 
+    mutate(expenditure=amount_spent_aid_element_in_eur_million*
+               co_financed)
+
+
+
+
+
+cofinancing_expenditure <- cofinancing |> 
+    pull(expenditure) |> 
+    sum()
+
+if (cofinancing_expenditure>1e3){
+
+    cofinancing_expenditure_temp <- cofinancing_expenditure |>
+        divide_by(1e3) |>
+        round(1)
+
+    cofinancing_expenditure_print <- paste("EUR",cofinancing_expenditure_temp,
+                                           "billion", sep=" ")
+    
+} else { cofinancing_expenditure_temp <- cofinancing_expenditure |>
+        round(1)
+
+        cofinancing_expenditure_print <- paste("EUR",cofinancing_expenditure_temp,
+                                           "million", sep=" ")
+
+        
+}
+
+
+
+## share_cofinancing <- round(cofinancing_expenditure/1e3/total_expenditure_last
+##                            *100,1)
+
+
+share_cofinancing <- round(cofinancing_expenditure/total_expenditure_last
+                           *100,1)
+
+
+
+cofinancing_objective <- cofinancing |> 
+    group_by(scoreboard_objective) |> 
+    summarise(expenditure_objective=sum(expenditure)) |> 
+    ungroup() |> 
+    arrange(desc(expenditure_objective)) |> 
+    mutate(share=expenditure_objective/sum(expenditure_objective)*100)  |> 
+    mutate(share=round_preserve_sum(share,1))
+
+## list_obj <- cofinancing_objective |>
+##     filter(share>=cutoff)
+
+
+cofinancing_objective_sel <- cofinancing_objective |>
+    mutate(top_objective = fct_lump_min(scoreboard_objective,cutoff,share )) |>
+    summarise(expenditure=sum(expenditure_objective), .by=c(top_objective)) |>
+    mutate(top_objective=fct_inorder(top_objective)) |>
+    mutate(top_objective=fct_relevel(top_objective, "Other", after = Inf))
+    
+
+expenditure_by_objective <- scoreboard_ms  |> 
+    filter(year==max(year),
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0
+           ) |> 
+    mutate(scoreboard_objective=recode(scoreboard_objective,
+             "Other"="Other policy objectives"       ))  |> 
+    group_by(scoreboard_objective)  |> 
+    summarise(expenditure_objective=sum(amount_spent_aid_element_in_eur_million)) |> 
+    ungroup() |> 
+    arrange(desc(expenditure_objective)) |> 
+    mutate(objectives_reduced=if_else(scoreboard_objective %in% scoreboard_objective[1:4], scoreboard_objective, "Other policy objectives" )) |> 
+    group_by(objectives_reduced)  |> 
+    summarise(expenditure2=sum(expenditure_objective))  |> 
+    ungroup() |> 
+    arrange(desc(expenditure2))  |> 
+    mutate(share=expenditure2/sum(expenditure2)) |> 
+    mutate(share2=round_preserve_sum(share*100,1))  |> 
+    mutate(share_label=paste(as.character(share2), "%", sep="")) |> 
+    (\(.) move_row(.,which(.$objectives_reduced=="Other policy objectives"), nrow(.)))() |> 
+    ## move_row(which(.$objectives_reduced=="Other policy objectives"), nrow(.))  |> 
+    ## mutate(objectives_reduced=wrapper1(objectives_reduced, 25)) %>% 
+    mutate(objectives_reduced=factor(objectives_reduced))  |> 
+    mutate(objectives_reduced=fct_inorder(objectives_reduced))
+
+expenditure_gber <- scoreboard_ms  |> 
+    filter(year==max(year),
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0
+           ) |> 
+    filter(!is.na(all_objective_names_gber_only))  |> 
+    group_by(all_objective_names_gber_only)  |> 
+    summarise(expenditure=sum(amount_spent_aid_element_in_eur_million)) |> 
+    ungroup() |> 
+    arrange(desc(expenditure))  |> 
+    mutate(share=expenditure/sum(expenditure))  |> 
+    mutate(share2=round_preserve_sum(share*100,1))
+
+expenditure_gber_print <- expenditure_gber |>
+    select(all_objective_names_gber_only, share) |>
+    mutate(share=round_preserve_sum(share*100,1)) |>
+    slice(1:4) |> 
+    add_total(name="Total top 4 GBER articles") |>
+    mutate(share=format_col_percentage(share,1))
+
+
+
+
+expenditure_aid <- scoreboard_ms  |> 
+    filter(year==max(year),
+           ## duration_end>=max(year),
+           amount_spent_aid_element_in_eur_million>0
+           ) |> 
+    filter(!is.na(harmonised_aid_instrument))  |> 
+    group_by(harmonised_aid_instrument)  |> 
+    summarise(expenditure=sum(amount_spent_aid_element_in_eur_million))  |> 
+    ungroup() |> 
+    arrange(desc(expenditure))  |> 
+    mutate(share=expenditure/sum(expenditure)) |> 
+    mutate(share2=round_preserve_sum(share*100,1)) |> 
+    mutate(expenditure2=round(expenditure,0)) |>
+    mutate(expenditure_print=ifelse(expenditure>1e3, expenditure/1e3,
+           expenditure)) |>
+    mutate(expenditure_print=round(expenditure_print, 1)) |>
+    mutate(expenditure_print=ifelse(expenditure>1e3,
+           paste("EUR",expenditure_print, "billion", sep=" " ),
+           paste("EUR",expenditure_print, "million", sep=" " )
+           ))
+               
+
+    
+
+expenditure_aid_sel <- expenditure_aid |>
+    mutate(top_instrument=fct_lump_min(harmonised_aid_instrument,cutoff/100,share )) |>
+    summarise(expenditure=sum(expenditure), .by=c(top_instrument)) |>
+    arrange(desc(expenditure)) |>
+    mutate(top_instrument=fct_inorder(top_instrument)) |>
+    mutate(top_instrument=fct_relevel(top_instrument, "Other", after = Inf))
+
+    
+
+
+covid_eu27_year <- total_amount_eu27_covid_covid_years |>
+    mutate(share=  total/total_amount_eu27_covid_years$total) 
+
+
+ukraine_eu27_year <- total_amount_eu27_ukraine_ukraine_years |>
+    mutate(share=  total/total_amount_eu27_ukraine_years$total) 
+
+
+
+covid_years_ms <- scoreboard_ms |>
+    filter(year %in% covid_years) |>
+    summarise(total=sum(amount_spent_aid_element_in_eur_million),
+              .by=year)
+
+
+
+ukraine_years_ms <- scoreboard_ms |>
+    filter(year %in% ukraine_years) |>
+    summarise(total=sum(amount_spent_aid_element_in_eur_million),
+              .by=year)
+
+
+
+covid_years_ms_covid <- scoreboard_ms |>
+    filter(year %in% covid_years,
+           covid==T  ) |>
+    summarise(total=sum(amount_spent_aid_element_in_eur_million),
+              .by=year)
+
+
+
+
+ukraine_years_ms_ukraine <- scoreboard_ms |>
+    filter(year %in% ukraine_years,
+           ukraine==T  ) |>
+    summarise(total=sum(amount_spent_aid_element_in_eur_million),
+              .by=year)
+
+
+
+covid_ms_year <-    covid_years_ms_covid |>
+    mutate(share=total/covid_years_ms$total,
+           country=ms3)
+
+ukraine_ms_year <-    ukraine_years_ms_ukraine |>
+    mutate(share=total/ukraine_years_ms$total,
+           country=ms3)
+
+
+
+output_covid <- bind_rows(covid_eu27_year,
+                          covid_ms_year) |>
+    mutate(percent=format_col_preserve_sum(share*100,1)) |>
+    mutate(percent=paste(percent, "%", sep="")) |>
+    mutate(year=as.factor(year)) |>
+    mutate(country=factor(country)) |> 
+    mutate(country=fct_relevel(country, "EU27")) |>
+    mutate(total_print=ifelse(total>1e3,
+                              paste("EUR",round(total/1e3,1), "billion", sep=" "),
+                              paste("EUR", round(total,1), "million", sep=" "))) |>
+    mutate(total_print=ifelse(total>1e3,
+                              paste("EUR",round(total/1e3,1), "billion", sep=" "),
+                              paste("EUR", round(total,1), "million",
+							  sep=" ")))
+
+
+output_ukraine <- bind_rows(ukraine_eu27_year,
+                          ukraine_ms_year) |>
+    mutate(percent=format_col_preserve_sum(share*100,1)) |>
+    mutate(percent=paste(percent, "%", sep="")) |>
+    mutate(year=as.factor(year)) |>
+    mutate(country=factor(country)) |> 
+    mutate(country=fct_relevel(country, "EU27")) |>
+    mutate(total_print=ifelse(total>1e3,
+                              paste("EUR",round(total/1e3,1), "billion", sep=" "),
+                              paste("EUR", round(total,1), "million", sep=" "))) |>
+    mutate(total_print=ifelse(total>1e3,
+                              paste("EUR",round(total/1e3,1), "billion", sep=" "),
+                              paste("EUR", round(total,1), "million", sep=" ")))
+							  
+
+
+```
+
+---
+title: "Country Focus on `r ms3` in `r year_focus`"
+---
+
+In this document, unless otherwise stated, the reported State aid expenditure always
+excludes aid expenditure to railways and to financial institutions.
+
+# Case and Procedural Information
+The total number of active measures corresponded to `r n_cases` in
+`r year_focus` and the detailed breakdown by type of measure is provided
+in Table \@ref(tab:tab1).
+
+
+<!-- See https://ardata-fr.github.io/flextable-book/captions-and-cross-references.html -->
+
+```{r, tab.cap="Number and share of State aid measures by type of procedure.", tab.id='tab1', label='tab1' , echo=FALSE, eval=TRUE}
+
+
+
+ft <- stat_cases |> 
+    flextable()  |> 
+    ## add_header_row(values = c(paste("State Aid Measures in ", year_focus, sep="")) ) %>%
+    set_header_labels(procedure_name="Type of Procedure",
+                      n="Number of Active Measures",
+                      percent="Share of Total"
+                      )  |> 
+    theme_zebra()  |> 
+    fontsize(part = "all", size = 8)  |> 
+    font(part="all", fontname = "Verdana")  |> 
+    colformat_double(big.mark = " ")  |> 
+    ## autofit() ## %>%
+    width(width = c(2,2,2)) 
+ft <- add_header_row(
+  x = ft, values = paste("State Aid Measures in ",year_focus, sep=""),
+  colwidths = c(3))
+ft <- theme_box(ft)  |> 
+    align(align="center", part="header") |> 
+    align(align = "right",j=c(2,3), part="body") ## |>
+    ## set_caption("htftyhfghyfgh")
+ft
+```
+
+In `r year_focus`, the number of GBER measures in `r ms3` reached 
+`r stat_cases[stat_cases$procedure_name=="General Block Exemption Regulation",]$percent` of the total number of measures, with 
+`r stat_cases_new[stat_cases_new$procedure_name=="General Block Exemption Regulation",]$percent`% of all newly implemented measured falling
+under GBER.
+
+# State aid spending - Overview
+
+Figure \@ref(fig:spending) illustrates the evolution of  the
+State aid expenditure, broken down by type of procedure, for `r ms3` during the period 
+`r ini_focus`-`r year_focus` in constant prices adjusted by the yearly
+value of GDP(with the exclusion of aid under the fisheries block exemption
+regulation because it is negligible compared to the aid given under
+the other type of procedures).
+
+
+
+```{r spending, echo=FALSE,fig.cap="State aid expenditure at constant prices by type of procedure.",fig.height = 6, fig.width = 12}
+
+
+## my_pal <- viridis(length(stat_cases2 %>% pull(procedure_name) %>%
+##                         unique)+1)[1:4]
+
+
+
+my_pal <- pal_npg("nrc")(4)
+
+ggplot(data = stat_cases2, aes(x  = year, y=expenditure_deflated,
+                                      fill=procedure_name)) +
+    geom_bar(position=position_dodge2(preserve="single"), stat="identity", alpha=1, color="black")+
+    ## scale_fill_viridis("Vehicle Brand\nOrigin",breaks=mybreaks, labels= mylabels, discrete=T)+
+    ##     scale_colour_viridis("Vehicle Brand\nOrigin",breaks=mybreaks, labels= mylabels, discrete=T)+
+
+    scale_fill_manual(NULL, ## labels=c("Inward Stocks","Outward Stocks" ),
+                         values=my_pal)+
+
+
+    ## coord_cartesian(ylim = c(0, 20)) +
+    my_ggplot_theme2("top")+
+  ## theme(legend.position = 'top', 
+  ##       legend.spacing.x = unit(1.0, 'cm'))+
+## theme(axis.text.x = element_text(size=15,angle=90, colour="black", vjust=1))+
+
+    labs(title=paste("State aid spending in billion EUR ",ini_focus, "-",
+                     year_focus, " (constant prices)", sep=""))+
+    scale_x_continuous(breaks=seq(ini_focus, year_focus))+
+## coord_cartesian(ylim=c(0,8000))+ 
+##     scale_y_continuous(sec.axis = sec_axis(~./norm_in, labels=mypercentlatex,
+##                      name="Percentage of\nTotal Extra EU28",
+##                                            breaks=seq(0, 1, by=0.2)))+
+## scale_y_continuous(labels=mypercentlatex)+
+    
+    xlab(NULL)+
+    ylab("State aid expenditure (EUR bn)")+
+    guides(fill=guide_legend(nrow=1,byrow=TRUE))
+```
+
+
+In `r year_focus`, the total State aid spending for `r ms3` amounted
+ to `r total_expenditure_last_print`.  The 5 biggest State aid
+ measures in  absorbed `r top5_share`% of the total spending i.e. `r top5_expenditure_print`.
+ 
+ Finally, the amount of aid co-financed with EU funds in `r ms3` corresponded to
+ `r cofinancing_expenditure_print` <!-- million EUR --> (around
+ `r share_cofinancing`% of the total spending) and
+ was mostly concentrated in
+ `r cofinancing_objective$scoreboard_objective[1]`
+ (`r cofinancing_objective$share[1]`%),
+  `r cofinancing_objective$scoreboard_objective[2]`
+ (`r cofinancing_objective$share[2]`%) and
+  `r cofinancing_objective$scoreboard_objective[3]`
+ (`r cofinancing_objective$share[3]`%).
+
+Figure \@ref(fig:topobjectives) illustrates the actual amount of the co-financed
+ aid for the most important co-financed objectives. 
+
+```{r topobjectives, fig.cap="State expenditure for the main co-financed State aid objectives.", echo=FALSE,fig.height = 7, fig.width = 12}
+ggplot(data = cofinancing_objective_sel,
+              aes(x  = expenditure, y=fct_rev(top_objective))) +
+    geom_bar(position="dodge", stat="identity", alpha=1, color="black") +
+    ## geom_text(aes(label=share_label), position=position_dodge(width=0.9),
+    ##           hjust=-.1,
+    ##           vjust=-0.1,
+    ##           size=5)+
+    ## coord_cartesian(xlim = c(0, 1)) +
+    my_ggplot_theme2("top")+
+    ## scale_x_continuous(labels = label_percent())+
+    scale_y_discrete(labels = wrap_format(20))+    
+    xlab("State aid expenditure (million EUR)")+
+    ylab(NULL)+
+    labs(title=paste("Top State aid objectives for co-financed aid in ",
+                     year_focus, sep=""))
+```
+
+
+ 
+# State aid spending: top objectives and instruments
+
+ Around `r sum(expenditure_by_objective$share2[1:2])`% of State aid
+ spending in `r ms3` was concentrated in two main policy objectives. Around
+`r expenditure_by_objective$share2[1]`% was directed towards
+"`r expenditure_by_objective$objectives_reduced[1]`" while
+`r expenditure_by_objective$share2[2]`%  to
+"`r expenditure_by_objective$objectives_reduced[2]`".
+
+Furthermore, `r ms3` devoted around
+`r expenditure_by_objective$share2[3]`% towards
+"`r expenditure_by_objective$objectives_reduced[3]`" and
+`r expenditure_by_objective$share2[4]`% to
+"`r expenditure_by_objective$objectives_reduced[4]`".
+
+The data is also illustrated in the Figure \@ref(fig:topobjectives2).
+
+```{r topobjectives2,fig.cap="Share of State aid expenditure for the main objectives.", echo=FALSE,fig.height = 6, fig.width = 12}
+ggplot(data = expenditure_by_objective,
+              aes(x  = share, y=fct_rev(objectives_reduced))) +
+    geom_bar(position="dodge", stat="identity", alpha=1, color="black") +
+    geom_text(aes(label=share_label), position=position_dodge(width=0.9),
+              hjust=-.1,
+              vjust=-0.1,
+              size=5)+
+    coord_cartesian(xlim = c(0, 1)) +
+    my_ggplot_theme2("top")+
+    scale_x_continuous(labels = label_percent())+
+    scale_y_discrete(labels = wrap_format(20))+    
+    xlab("Share of State aid expenditure")+
+    ylab(NULL)+
+    labs(title=paste("Top State aid objectives in ",
+                     year_focus, sep=""))
+```
+
+
+As to GBER, the top 4 key articles absorbe about
+`r sum(expenditure_gber$share2[1:4])`%
+of the total GBER spending, as illustrated in Table
+\@ref(tab:tab3). 
+
+
+```{r, tab.cap="Contribution of the top 4 GBER articles to the total GBER expenditure.", tab.id='tab3', label='tab3' , echo=FALSE, eval=TRUE}
+
+
+
+ft <- expenditure_gber_print |> 
+    flextable()  |> 
+    ## add_header_row(values = c(paste("State Aid Measures in ", year_focus, sep="")) ) %>%
+    set_header_labels(all_objective_names_gber_only="GBER Article",
+                      ## n="Number of Active Measures",
+                      share="Share of Total"
+                      )  |> 
+    theme_zebra()  |> 
+    fontsize(part = "all", size = 8)  |> 
+    font(part="all", fontname = "Verdana")  |> 
+    colformat_double(big.mark = " ")  |> 
+    ## autofit() ## %>%
+    width(width = c(3,3)) 
+ft <- add_header_row(
+  x = ft, values = paste("Top GBER Articles in ",year_focus, sep=""),
+  colwidths = c(2))
+ft <- theme_box(ft)  |> 
+    align(align="center", part="header") |> 
+    align(align = "right",j=c(2), part="body") ## |>
+##     ## set_caption("htftyhfghyfgh")
+ft
+```
+
+
+
+
+
+<!-- ```{r topgber, fig.cap="Top GBER articles.", echo=FALSE,fig.height = 8, fig.width = 12} -->
+
+<!-- treemap(expenditure_gber,index= "all_objective_names_gber_only", "expenditure" -->
+<!--       , title=paste("Top GBER Articles in ", year_focus, sep=""), -->
+<!--         fontsize.title = 22,fontfamily.title =c("bold"), draw=T) -->
+<!-- ``` -->
+
+
+
+
+
+
+In terms of State aid instruments, `r ms3` privileged the use of 
+"`r expenditure_aid$harmonised_aid_instrument[1]`" (around 
+`r expenditure_aid$expenditure_print[1]`, 
+`r expenditure_aid$share2[1]`% of total State aid spending), 
+followed by
+"`r expenditure_aid$harmonised_aid_instrument[2]`" 
+(`r expenditure_aid$expenditure_print[2]`, 
+`r expenditure_aid$share2[2]`% of total State aid spending), 
+and 
+"`r expenditure_aid$harmonised_aid_instrument[3]`" (around 
+`r expenditure_aid$expenditure_print[3]`, 
+`r expenditure_aid$share2[3]`% of total State aid spending).
+
+In Figure \@ref(fig:topinstruments) we illustrate the actual spending for the top
+instruments in  `r year_focus`.
+
+```{r topinstruments, fig.cap="State aid expenditure for the main instruments.", echo=FALSE,fig.height = 6, fig.width = 12}
+ggplot(data = expenditure_aid_sel,
+              aes(x  = expenditure, y=fct_rev(top_instrument))) +
+    geom_bar(position="dodge", stat="identity", alpha=1, color="black") +
+    ## geom_text(aes(label=share_label), position=position_dodge(width=0.9),
+    ##           hjust=-.1,
+    ##           vjust=-0.1,
+    ##           size=5)+
+    ## coord_cartesian(xlim = c(0, 1)) +
+    my_ggplot_theme2("top")+
+    ## scale_x_continuous(labels = label_percent())+
+    scale_y_discrete(labels = wrap_format(20))+    
+    xlab("State aid expenditure (million EUR)")+
+    ylab(NULL)+
+    labs(title=paste("Top State aid instruments in ",
+                     year_focus, sep=""))
+```
+
+
+
+# State aid expenditure for COVID-19 measures
+ 
+ In `r max(covid_years)` the Covid19-related expenditure for `r ms3` amounted to
+ `r output_covid |> filter(year==max(covid_years),
+ country!="EU27")|>pull(total_print)` i.e.
+ `r output_covid |> filter(year==max(covid_years),
+ country!="EU27")|>pull(percent)`
+  of the total State aid expenditure.  In `r min(covid_years)` this
+ amounted to `r output_covid |> filter(year==min(covid_years),
+ country!="EU27")|>pull(total_print)`, i.e.  `r output_covid |> filter(year==min(covid_years),
+ country!="EU27")|>pull(percent)` of the total. 
+  We compare this figure to the 
+ share of Covid19 State aid
+ expenditure at the EU27 level 
+ (`r output_covid |> filter(year==max(covid_years),
+ country=="EU27")|>pull(percent)` in `r max(covid_years)` and `r output_covid |> filter(year==min(covid_years),
+ country=="EU27")|>pull(percent)` in `r min(covid_years)`).
+ In Figure \@ref(fig:covid19)  we plot these values for ease of
+ comparison including also the data for `r covid_years[2]`. 
+
+
+
+```{r covid19, fig.cap="Covid19 expenditure as a share of total State aid expenditure.", echo=FALSE,fig.height = 6, fig.width = 12}
+
+my_pal <- pal_npg("nrc")(3)
+
+
+ggplot(data = output_covid,
+       aes(x  = share,
+           y=country, fill=fct_rev(year))) +
+    geom_bar(position="dodge", stat="identity", alpha=1, color="black") +
+    geom_text(aes(label=percent), position=position_dodge(width=0.9),
+              hjust=-.1,
+              vjust=-0.1,
+              size=5)+
+    scale_fill_manual(NULL, ## labels=c("Inward Stocks","Outward Stocks" ),
+                        breaks=c(2020, 2021, 2022) ,values=my_pal)+
+    coord_cartesian(xlim = c(0, 1)) +
+    my_ggplot_theme2("top")+
+    scale_x_continuous(labels = label_percent())+
+    scale_y_discrete(labels =c("EU27", ms3) )+    
+    xlab("Share of Covid19 State Aid Expenditure")+
+    ylab(NULL)
+
+```
+
+
+# State aid expenditure for measures related to the Russian invasion of Ukraine
+
+ In `r max(ukraine_years)` the expenditure to counterbalance the
+ negative effects of the Russian invasion of Ukraine, under the
+ Temporary Crisis Framework or based on its principles, for `r ms3` amounted to
+ `r output_ukraine |> filter(year==max(ukraine_years),
+ country!="EU27")|>pull(total_print)` i.e.
+ `r output_ukraine |> filter(year==max(ukraine_years),
+ country!="EU27")|>pull(percent)`
+  of the total State aid expenditure.
+  
+  We compare this figure to the 
+ share of TCF-related State aid
+ expenditure at the EU27 level 
+  `r output_ukraine |> filter(year==max(ukraine_years),
+ country=="EU27")|>pull(percent)` in `r max(ukraine_years)` in Figure \@ref(fig:tcf).
+  
+  
+```{r tcf, fig.cap="TCF-related expenditure as a share of total State aid expenditure.", echo=FALSE,fig.height = 6, fig.width = 12}
+
+my_pal <- pal_npg("nrc")(3)
+
+
+ggplot(data = output_ukraine,
+       aes(x  = share,
+           y=country, fill=fct_rev(year))) +
+    geom_bar(position="dodge", stat="identity", alpha=1, color="black") +
+    geom_text(aes(label=percent), position=position_dodge(width=0.9),
+              hjust=-.1,
+              vjust=-0.1,
+              size=5)+
+    scale_fill_manual(NULL, ## labels=c("Inward Stocks","Outward Stocks" ),
+                        breaks=c(2020, 2021, 2022) ,values=my_pal)+
+    coord_cartesian(xlim = c(0, 1)) +
+    my_ggplot_theme2("top")+
+    scale_x_continuous(labels = label_percent())+
+    scale_y_discrete(labels =c("EU27", ms3) )+    
+    xlab("Share of TCF-related State aid expenditure")+
+    ylab(NULL)
+
+```
+