myprojects-hg-reborn

Fork

---
output: 
  bookdown::word_document2:
  fig_caption: yes
---


```{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)


############################################################################
############################################################################
############################################################################
############################################################################
############################################################################

MS <- 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)


##  covid_qualifiers <- c( "Covid19-TF",
##  "Block-notification for existing schemes;Covid19-TF",
##  "Covid19-TF;EIB Pan-European Guarantee Fund"
## )


## defl_ini <- read_csv("GDP-deflators_2021.csv",show_col_types = FALSE,
##                      name_repair = "unique_quiet")

## defl <- defl_ini |>
##     pattern_to_na("-") |> 
##     mutate(across(where(is.character), ~as.numeric(.x))) |>
##     pivot_longer(cols=c(BE:UK)) |>
##     rename("year"="...1",
##            "country"="name")

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)



## %>%
    ## mutate(member_state=recode_many(member_state,iso_map$country,
    ##                                 iso_map$iso3 ))

## deflators <- scoreboard|>
##     select(year, deflator, member_state_2_letter_codes) |>
##     distinct() |>
##     complete_cases() |>
##     rename("deflator2"="deflator")

## scoreboard <- scoreboard |>
##     left_join(y=defl, by=c("year"="year",
##                                 "member_state_2_letter_codes"="country" )) |>
##     select(-deflator) |>
##     rename("deflator"="value") |>
##     mutate(deflated_amount_spent_mio_eur=amount_spent_aid_element_in_eur_million/deflator)


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="")) 


## stat_cases <- cases[1:10,] %>%
##     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)  %>%
##     mutate(procedure2=if_else(procedure_name=="General Block Exemption Regulation", "(G)BER", procedure_name)) %>% 
##     group_by( procedure2) %>%
##     summarise(expenditure=sum(amount_spent_aid_element_in_eur_million/1e3)) %>%
##     ungroup  %>%
##     mutate(expenditure=round_preserve_sum(expenditure, 1))

## stat_cases <- cases %>%
##     tabyl(procedure_name) %>%
##     mutate(percent=round_preserve_sum(percent*100,1)) %>%
##     mutate(percent=format_col(percent,1)) %>% 
##     mutate(percent=paste(percent, "%", sep="")) 


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 <- cases  %>%
##     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 <- 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=" ")
          

}


## if (total_expenditure>1e3) { total_expenditure <- total_expenditure |>
##                                  divide_by(1e3) |>
##                                  round(1)
## total_expenditure_print <- paste("EUR", total_expenditure, "billion", sep="")
                                 
## } else { total_expenditure <- total_expenditure |>
##            round(1)

##            total_expenditure_print <- paste("EUR", total_expenditure, "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
<!-- Between `r ini_focus` and  `r year_focus` `r ms3` spent  -->
<!-- <\!-- EUR -\->   `r total_expenditure_print` <\!-- billion -\->  and the detailed breakdown of the -->

<!-- <\!-- for non-agricultural State aid, of -\-> -->
<!-- <\!-- which around `r stat_cases3$expenditure[2]` billion EUR under notified -\-> -->
<!-- <\!-- measures and around `r stat_cases3$expenditure[1]` billion EUR under -\-> -->
<!-- <\!-- BER and the 2008 and 2014 GBER. -\-> -->

<!-- ```{r, tab.cap="State aid expenditure and share by type of procedure.", tab.id='tab2', label='tab2' , echo=FALSE, eval=TRUE} -->

<!-- ft <- stat_cases3 |>  -->
<!--     flextable() |>  -->
<!--     ## add_header_row(values = c(paste("State Aid Measures in ", year_focus, sep="")) ) %>% -->
<!--     set_header_labels(procedure_name="Type of Procedure", -->
<!--                       expenditure="Expenditure (Billion EUR)", -->
<!--                       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 Expenditure in Current Prices during ",ini_focus, -->
<!--                            "-",year_focus, sep=""), -->
<!--   colwidths = c(3)) -->
<!-- ft <- theme_box(ft) |>  -->
<!--     align(align="center", part="header") |>  -->
<!--     align(align = "right",j=c(2,3), part="body") -->
<!-- ft -->
<!-- ``` -->

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)

```