myprojects-hg-reborn

Fork

rm(list=ls())

library(tidyverse)
library(janitor)
library(openxlsx)
library(RJSDMX)


source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")



df_ini <- read_csv("../input/spain/spain-de-minimis-stat.csv") |>
    mutate(regulation=case_match(regulation,
                                 c("2023/2831") ~ "General",
                                 c("2023/2832", "360/2012") ~ "SGEI",
                                 c("1408/2013") ~ "AGRI",
                                 c("717/2014") ~ "FISH",
                                 c( "1407/2013") ~ "General"
                                 )) |>
    group_by(year, regulation) |>
    summarise(number_cases=sum(number_cases)) |>
    ungroup() |> 
    group_by(year) |>
    add_total_by_group() |> 
        ## group_modify(~ .x  |> 
        ##                  adorn_totals("row", fill="Total")) |> 
    ungroup()


saveRDS(df_ini, "../input/spain/spanish_data.RDS")

query <- "DEMO_PJAN/A.NR.TOTAL.T.ES+EU27_2020"

df_demo <- estat_retrieval(query) |>
    clean_names() |>
    select(time_period, geo, obs_value) |>
    mutate(time_period=as.numeric(time_period)) |>
    pivot_wider(names_from=geo, values_from=obs_value)



query <- "NAMA_10_GDP/A.CP_MEUR.B1GQ.ES+EU27_2020"

df_gdp <- estat_retrieval(query) |>
    clean_names() |>
    select(time_period, geo, obs_value) |>
    mutate(time_period=as.numeric(time_period)) |>
    pivot_wider(names_from=geo, values_from=obs_value)


df_tot <- df_ini |>
    left_join(df_demo, by=c("year"="time_period")) |>
    rename("population_ES"="ES", "population_EU27"="EU27_2020") |>
    left_join(df_gdp, by=c("year"="time_period"))  |>
    rename("GDP_ES"="ES", "GDP_EU27"="EU27_2020") |>
    mutate(ratio_population=population_EU27/population_ES,
           ratio_GDP=GDP_EU27/GDP_ES) |>
    mutate(number_cases_EU27_GDP=number_cases*ratio_GDP,
           number_cases_EU27_population=number_cases*ratio_population) |>
    group_by(year) |>
    mutate(number_cases_EU27_GDP=round_preserve_sum(number_cases_EU27_GDP, 0),
           number_cases_EU27_population=
               round_preserve_sum(number_cases_EU27_population, 0)
           ) |>
    ungroup()

save_excel(df_tot, "../input/spain/number_de_minimis_ES_EU27.xlsx")

saveRDS(df_tot, "../input/spain/spain_final.RDS")


print("So far so good")