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

コミットメタ情報

• リビジョン: b0f8a88cf65c483105bda59b5b44f7d9711392e8 (tree)
• 日時: 2021-02-19 23:47:03
• 作者: Lorenzo Isella <lorenzo.isella@gmai...>
• コミッター: Lorenzo Isella

ログメッセージ

I added some extra stats and I commented most of the code.

変更サマリ

• modified: R-codes/process_covid_tracker.R

差分

diff -r 5657114b3a2e -r b0f8a88cf65c R-codes/process_covid_tracker.R
--- a/R-codes/process_covid_tracker.R	Wed Feb 17 22:48:55 2021 +0100
+++ b/R-codes/process_covid_tracker.R	Fri Feb 19 15:47:03 2021 +0100
@@ -1,6 +1,6 @@
 rm(list=ls())
 
-## last saved on  Time-stamp: "2021-02-15 11:06:30 lorenzo"
+## last saved on  Time-stamp: "2021-02-19 15:46:30 lorenzo"
 
 
 library(tidyverse)
@@ -9,135 +9,159 @@
 library(openxlsx)
 library(stringr)
 
-## source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
-
-remove_special_char <- function(x, new_pattern=""){
-
-## remove special characters from a column
-    
-res <-  str_replace_all(x, "[[:punct:]]", new_pattern)
-
-res <-      str_replace_all(x, "[^[:alnum:]]", new_pattern)
-
-    
-res <- str_trim(res, side ="both")
-
-    
-return(res)
-    
-}
-
+library(kableExtra)
 
-move_row <- function(df, ini_pos, fin_pos){
-
-ll <- nrow(df)
-    
-row_pick <- slice(df, ini_pos)
-
-    if (fin_pos=="last"){
-
-           res <- df %>%
-        slice(-ini_pos) %>% 
-        add_row(row_pick, .before = ll)    
- 
-        
-} else{
-    
-    res <- df %>%
-        slice(-ini_pos) %>% 
-        add_row(row_pick, .before = fin_pos)    
-}
-
-    return(res)
-}
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
 
 
 
-add_total <- function(x, pos=1, ...){
-    adorn_totals(x, ...) %>% as_tibble %>%
-    move_row(nrow(.), pos)    
-}
+## pattern_to_na <- function(df, pattern){
+
+## res <- df %>% na_if(.,  pattern)
+
+## return(res)    
+    
+## }
+
+
+## ### This function removes punctuation and special characters
+
+## remove_special_char <- function(x, new_pattern=""){
+
+## ## remove special characters from a column
+    
+## res <-  str_replace_all(x, "[[:punct:]]", new_pattern)
+
+## res <-      str_replace_all(x, "[^[:alnum:]]", new_pattern)
+
+    
+## res <- str_trim(res, side ="both")
+
+    
+## return(res)
+    
+## }
+
+
+## ## this function moves a given row of a table to the desired position
+
+## move_row <- function(df, ini_pos, fin_pos){
+
+## ll <- nrow(df)
+    
+## row_pick <- slice(df, ini_pos)
+
+##     if (fin_pos=="last"){
+
+##            res <- df %>%
+##         slice(-ini_pos) %>% 
+##         add_row(row_pick, .before = ll)    
+ 
+        
+## } else{
+    
+##     res <- df %>%
+##         slice(-ini_pos) %>% 
+##         add_row(row_pick, .before = fin_pos)    
+## }
+
+##     return(res)
+## }
+
+
+## ## this function adds a total row to a table
+
+## add_total <- function(x, pos=1, ...){
+##     adorn_totals(x, ...) %>% as_tibble %>%
+##     move_row(nrow(.), pos)    
+## }
 
 
 
 
-## a function to remove duplicated columns (see http://bit.ly/2s0q6cC )
-
-rem_dupl_cols <- function(df){
+## ## a function to remove duplicated columns (see http://bit.ly/2s0q6cC )
 
-res <- df[, !duplicated(t(df))]
+## rem_dupl_cols <- function(df){
 
-return(res)
+## res <- df[, !duplicated(t(df))]
 
-}
+## return(res)
+
+## }
 
 
-## a function to remove constant columns (see  http://bit.ly/2s1qqaY
-## and http://bit.ly/2s1Sb3q . The solution in the second link is better
-## because it works also with factors and characters  )
+## ## a function to remove constant columns (see  http://bit.ly/2s1qqaY
+## ## and http://bit.ly/2s1Sb3q . The solution in the second link is better
+## ## because it works also with factors and characters  )
 
-rem_const_cols <- function(df){
-## res <- df[,apply(df, 2, var, na.rm=TRUE) != 0]
+## rem_const_cols <- function(df){
+## ## res <- df[,apply(df, 2, var, na.rm=TRUE) != 0]
 
 
-## res <- df[sapply(df, function(x) length(unique(na.omit(x)))) > 1]
+## ## res <- df[sapply(df, function(x) length(unique(na.omit(x)))) > 1]
 
-    res <- df %>%
-        select(where(~length(unique(na.omit(.x))) > 1))
+##     res <- df %>%
+##         select(where(~length(unique(na.omit(.x))) > 1))
 
     
 
-return(res)
+## return(res)
 
-}
-
+## }
 
 
 
 
-clean_data <- function(x){
+## ## this function makes the column names machine-friendly and removes
+## ## duplicated rows, duplicated columns and constant rows
 
-    res <- x %>%
-        clean_names() %>%
-        remove_empty() %>%
-        distinct() %>%
-        rem_dupl_cols() %>%
-        rem_const_cols()
+## clean_data <- function(x){
 
-    return(res)
+##     res <- x %>%
+##         clean_names() %>%
+##         remove_empty() %>%
+##         distinct() %>%
+##         rem_dupl_cols() %>%
+##         rem_const_cols()
+
+##     return(res)
 
     
-}
+## }
     
 
 
-na_to_pattern <- function(df, pattern){
+## ## this function changes all the missing data in a table to the desired pattern
 
-res <- df %>% replace(., is.na(.), pattern)
+## na_to_pattern <- function(df, pattern){
 
-return(res)    
+## res <- df %>% replace(., is.na(.), pattern)
+
+## return(res)    
     
-}
+## }
 
 
 
-save_excel <- function(output, fileName, sheetName="data", na_yes = TRUE,...){
-tryCatch({
+## ### This function is used to save the output of the calculations as an excel file
 
-    wb <- loadWorkbook(fileName)
-    addWorksheet(wb = wb, sheet = sheetName)
-  writeData(wb = wb, sheet = sheetName, x = output, colNames = T, rowNames = F,
-       keepNA=na_yes    ,...)
-   saveWorkbook(wb = wb, file = fileName, overwrite = T)
-}, 
-error = function(err){
+## save_excel <- function(output, fileName, sheetName="data", na_yes = TRUE,...){
+## tryCatch({
 
-  wb <- createWorkbook(fileName)
-  addWorksheet(wb = wb, sheet = sheetName)
-    writeData(wb = wb, sheet = sheetName, x = output, colNames = T, rowNames = F, keepNA=na_yes ,  ... )
-  saveWorkbook(wb = wb, file = fileName, overwrite = T)  
-})
-}
+##     wb <- loadWorkbook(fileName)
+##     addWorksheet(wb = wb, sheet = sheetName)
+##   writeData(wb = wb, sheet = sheetName, x = output, colNames = T, rowNames = F,
+##        keepNA=na_yes    ,...)
+##    saveWorkbook(wb = wb, file = fileName, overwrite = T)
+## }, 
+## error = function(err){
+
+##   wb <- createWorkbook(fileName)
+##   addWorksheet(wb = wb, sheet = sheetName)
+##     writeData(wb = wb, sheet = sheetName, x = output, colNames = T, rowNames = F, keepNA=na_yes ,  ... )
+##   saveWorkbook(wb = wb, file = fileName, overwrite = T)  
+## })
+## }
 
 
 
@@ -149,6 +173,10 @@
 
 
 
+## tf_list and tf_list2 are two lists of the same temporary framework
+## chapters. They are written in a different way because they need to be
+## looked for (grepped) in different places
+
 tf_list <- c("TF1:", "TF2:", "TF3:", ## "TF4",
              ## "TF5",
              "TF6:", "TF7:", "TF8:",
@@ -161,33 +189,37 @@
              "TF9", "TF10", "TF11", "TF12")
 
 
+
+### import the raw data about the amendments
 df_am_ini <- read_csv("amendments.csv")
 
-
+## import the raw data about the decisions
 df_dec_ini <- read_csv("decisions.csv", , locale = readr::locale(encoding = "latin1")   )
 
 
-    ## read.csv("decisions.csv", fileEncoding="latin1")
-
 
 df_am <- df_am_ini %>%
-    clean_data()
+    clean_data()    ### clean the amendments data
 
 df_dec <- df_dec_ini %>%
-    clean_data()
+    clean_data() ### clean the decision data
 
-rep1_ini <- read_csv("report1_detailed.csv")
+rep1_ini <- read_csv("report1_detailed.csv") ## import the raw data from the
+## full report
 
 
 
 rep1 <- rep1_ini %>%
-    clean_data() 
+    clean_data() ## clean the data of the full report 
 
 rep1_unique <- rep1 %>%
-    distinct(case_reference, .keep_all=T)
+    distinct(case_reference, .keep_all=T)  ## generates the equivalent of
+## report 1 no duplicates in the excel tracker. This is a subset of the full report without duplicated case references.
 
 
 
+### budget calculation. The report without duplicated is broken down (grouped_by) member states (MS) and for each MS we calculate the sum of the confirmed budget. For each MS we also calculate the share of the total budget.
+## A column with the totals is also added.
 
 budget_test <- rep1_unique %>%
     group_by(member_state_2_letter_code) %>%
@@ -195,151 +227,16 @@
     ungroup %>%
     mutate(share=budget/sum(budget)) %>%
     add_total(nrow(.)+1)
-    
-
-
-
-decisions_test <- rep1_unique %>%
-    filter(!is.na(legal_basis))%>%
-    group_by(member_state_2_letter_code) %>%
-    summarise(n_decisions=n()) %>%
-    ungroup() %>%
-    arrange(member_state_2_letter_code)%>%
-    add_total(nrow(.)+1)
-
-
-
-count_am <- df_am %>%
-    group_by(member_state_of_amendment) %>%
-    summarise(n_amendments=length(unique(amendment_case_number))) %>%
-    ungroup%>%
-    add_total(nrow(.)+1)
-
-
-
-duration_calc <- rep1 %>%
-    filter(register_code %in% c("N", "PN"),
-           decided=="YES") %>%
-    select(case_reference, member_state_2_letter_code, average_duration,
-           register_code, legal_basis, type_of_measure )%>%
-    pivot_wider(names_from="register_code", values_from="average_duration") %>%
-    ## na_to_pattern(0) %>%
-    mutate(type=if_else(is.na(PN), "N", "P+N")) %>%
-    rowwise() %>% 
-    mutate(total_duration=sum(N,PN, na.rm=T)) %>%
-    ungroup
-
-
-## save_excel(duration_calc, "duration_raw_data.xlsx")
-
-
-
-
-duration_stat_ms <- duration_calc %>%
-    group_by(member_state_2_letter_code) %>%
-    summarise(mean_duration=mean(total_duration, na.rm=T),
-              median_duration=median(total_duration, na.rm=T),
-              mean_PN_duration=mean(PN, na.rm=T),
-              median_PN_duration=median(PN, na.rm=T),
-              mean_N_duration=mean(N, na.rm=T),
-              median_N_duration=median(N, na.rm=T),
-              number_data_points_N=n()-sum(is.na(N)),
-              number_data_points_PN=n()-sum(is.na(PN))
-              )%>%
-    ungroup
-
-## save_excel(duration_stat_ms, "duration_by_MS.xlsx")
-
 
 
-duration_stat_eu <- duration_calc %>%
-    ## group_by(legal_basis) %>%
-    summarise(mean_duration=mean(total_duration, na.rm=T),
-              median_duration=median(total_duration, na.rm=T),
-              mean_PN_duration=mean(PN, na.rm=T),
-              median_PN_duration=median(PN, na.rm=T),
-              mean_N_duration=mean(N, na.rm=T),
-              median_N_duration=median(N, na.rm=T),
-              number_data_points_N=n()-sum(is.na(N)),
-              number_data_points_PN=n()-sum(is.na(PN))) %>%
-    ungroup
-
-## save_excel(duration_stat_eu, "duration_all_eu.xlsx")
-
-duration_stat_basis <- duration_calc %>%
-    group_by(legal_basis) %>%
-    summarise(mean_duration=mean(total_duration, na.rm=T),
-              median_duration=median(total_duration, na.rm=T),
-              mean_PN_duration=mean(PN, na.rm=T),
-              median_PN_duration=median(PN, na.rm=T),
-              mean_N_duration=mean(N, na.rm=T),
-              median_N_duration=median(N, na.rm=T),
-              number_data_points_N=n()-sum(is.na(N)),
-              number_data_points_PN=n()-sum(is.na(PN)))%>%
-    ungroup
-
-## save_excel(duration_stat_basis, "duration_by_legal_basis.xlsx")
-
-
-
-
-duration_stat_basis_ms <- duration_calc %>%
-    group_by(member_state_2_letter_code,legal_basis) %>%
-    summarise(mean_duration=mean(total_duration, na.rm=T),
-              median_duration=median(total_duration, na.rm=T),
-              mean_PN_duration=mean(PN, na.rm=T),
-              median_PN_duration=median(PN, na.rm=T),
-              mean_N_duration=mean(N, na.rm=T),
-              median_N_duration=median(N, na.rm=T),
-              number_data_points_N=n()-sum(is.na(N)),
-              number_data_points_PN=n()-sum(is.na(PN)))%>%
-    ungroup %>%
-    arrange(member_state_2_letter_code)
-
-
-
-
-
-
-
-duration_stat_measure <- duration_calc %>%
-    group_by(type_of_measure) %>%
-    summarise(mean_duration=mean(total_duration, na.rm=T),
-              median_duration=median(total_duration, na.rm=T),
-              mean_PN_duration=mean(PN, na.rm=T),
-              median_PN_duration=median(PN, na.rm=T),
-              mean_N_duration=mean(N, na.rm=T),
-              median_N_duration=median(N, na.rm=T),
-              number_data_points_N=n()-sum(is.na(N)),
-              number_data_points_PN=n()-sum(is.na(PN)))%>%
-    ungroup
-
-
-
-
-duration_stat_measure_ms <- duration_calc %>%
-    group_by(member_state_2_letter_code,type_of_measure) %>%
-    summarise(mean_duration=mean(total_duration, na.rm=T),
-              median_duration=median(total_duration, na.rm=T),
-              mean_PN_duration=mean(PN, na.rm=T),
-              median_PN_duration=median(PN, na.rm=T),
-              mean_N_duration=mean(N, na.rm=T),
-              median_N_duration=median(N, na.rm=T),
-              number_data_points_N=n()-sum(is.na(N)),
-              number_data_points_PN=n()-sum(is.na(PN)))%>%
-    ungroup %>%
-    arrange(member_state_2_letter_code)
-
-
-
-
-## save_excel(duration_stat_measure, "duration_by_legal_measure.xlsx")
 
 
 aid_type <- tibble(code=c("AH", "IA", "S"),
                    name=c("Ad hoc", "Individual application within a scheme",
                           "Schemes"))
 
+### budget broken by type
+
 budget_type <- rep1_unique %>%
     group_by(## member_state_2_letter_code,
              case_type_code) %>%
@@ -352,6 +249,7 @@
     add_total(nrow(.)+1)
 
 
+### budget broken by type and MS
 
 budget_type_ms <- rep1_unique %>%
     group_by( member_state_2_letter_code,
@@ -370,7 +268,7 @@
 
 
 
-
+#### budget broken by measure
 
 
 budget_measure <- rep1_unique %>%
@@ -384,7 +282,7 @@
 
 
 
-
+#### budget broken by measure and MS
 
 budget_measure_ms <- rep1_unique %>%
     group_by( member_state_2_letter_code,
@@ -405,9 +303,107 @@
 
 
 
+### budget broken by beneficiary sector
 
 
 
+budget_by_beneficiary_sector <-  rep1_unique %>%
+    group_by(beneficary_sector) %>%
+    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
+    ungroup %>%
+    filter(complete.cases(.)) %>%
+    mutate(share=budget/sum(budget)) %>%
+    arrange(desc(budget))%>%
+    add_total(nrow(.)+1)
+
+
+### budget broken by MS and beneficiary sector
+
+budget_by_beneficiary_sector_ms <-  rep1_unique %>%
+    group_by(member_state_2_letter_code,beneficary_sector) %>%
+    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
+    na.omit %>%
+    mutate(share=budget/sum(budget)) %>%
+    group_modify(~ .x %>%
+                     adorn_totals("row"))  %>% 
+    ungroup%>%
+    complete(member_state_2_letter_code,beneficary_sector) %>%
+    na_to_pattern(0)
+
+
+
+
+### budget broken by beneficiary type
+
+
+budget_by_beneficiary_type <-  rep1_unique %>%
+    group_by(beneficary_type) %>%
+    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
+    ungroup %>%
+    filter(complete.cases(.)) %>%
+    mutate(share=budget/sum(budget)) %>%
+    arrange(desc(budget))%>%
+    add_total(nrow(.)+1)
+
+
+
+### budget broken by beneficiary type and MS
+
+
+budget_by_beneficiary_type_ms <-  rep1_unique %>%
+    group_by(member_state_2_letter_code,beneficary_type) %>%
+    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
+    na.omit %>%
+    mutate(share=budget/sum(budget)) %>%
+    group_modify(~ .x %>%
+                     adorn_totals("row"))  %>% 
+    ungroup%>%
+    complete(member_state_2_letter_code,beneficary_type) %>%
+    na_to_pattern(0)
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+### statistics on the number of decisions.
+## from the report file without duplicates, we remove those rows for which
+## the legal basis is missing. We break down the remaining data by MS and
+## we count the number of decisions for each MS (just the number of lines for each MS) and we add a total row
+
+decisions_test <- rep1_unique %>%
+    filter(!is.na(legal_basis))%>%
+    group_by(member_state_2_letter_code) %>%
+    summarise(n_decisions=n()) %>%
+    ungroup() %>%
+    arrange(member_state_2_letter_code)%>%
+    add_total(nrow(.)+1)
+
+
+
+### number of decisions by legal_basis
+
 decisions_eu <- rep1_unique %>%
     filter(!is.na(legal_basis))%>%
     group_by(legal_basis) %>%
@@ -418,6 +414,8 @@
     add_total(nrow(.)+1)
 
 
+### number of decisions by legal_basis and MS
+
 
 decisions_eu_ms <- rep1_unique %>%
     filter(!is.na(legal_basis))%>%
@@ -436,6 +434,300 @@
 
 
 
+
+## statistics on the number of amendements. We use the first two columns of
+## the amendments file. We break the amendment data by MS and we count the number of amendments for each MS as the number of *unique* amendment case numbers (in case some of them are repeated) 
+
+
+count_am <- df_am %>%
+    group_by(member_state_of_amendment) %>%
+    summarise(n_amendments=length(unique(amendment_case_number))) %>%
+    ungroup%>%
+    add_total(nrow(.)+1)
+
+
+### this the fundamental table for the duration calculations
+## we consider the full report and we filter the register code to be
+## either N of PN and the case needs to have been decided (i.e. decided = YES)
+## we select the columns case_reference, member_state_2_letter_code,
+## average_duration,       register_code, legal_basis, type_of_measure
+## and we pivot the table.
+
+## Finally we obtain a table with the structure
+
+## case_reference member_state_2_… legal_basis type_of_measure     N    PN type total_duration
+
+## in which we have the duration of the notification and prenotification phase of all the decided cases
+
+duration_calc <- rep1 %>%
+    filter(register_code %in% c("N", "PN"),
+           decided=="YES") %>%
+    select(case_reference, member_state_2_letter_code, average_duration,
+           register_code, legal_basis, type_of_measure )%>%
+    pivot_wider(names_from="register_code", values_from="average_duration") %>%
+    ## na_to_pattern(0) %>%
+    mutate(type=if_else(is.na(PN), "N", "P+N")) %>%
+    rowwise() %>% 
+    mutate(total_duration=sum(N,PN, na.rm=T)) %>%
+    ungroup
+
+
+## save_excel(duration_calc, "duration_decisions_raw_data.xlsx")
+
+
+
+
+
+
+
+
+## we break the duration_calc data by MS and we calculate statistics
+## on the duration of the N, PN and combined N+PN process.
+
+duration_stat_ms <- duration_calc %>%
+    group_by(member_state_2_letter_code) %>%
+    summarise(mean_duration=mean(total_duration, na.rm=T),
+              median_duration=median(total_duration, na.rm=T),
+              mean_PN_duration=mean(PN, na.rm=T),
+              median_PN_duration=median(PN, na.rm=T),
+              mean_N_duration=mean(N, na.rm=T),
+              median_N_duration=median(N, na.rm=T),
+              number_data_points_N=n()-sum(is.na(N)),
+              number_data_points_PN=n()-sum(is.na(PN))
+              )%>%
+    ungroup
+
+## save_excel(duration_stat_ms, "duration_by_MS.xlsx")
+
+
+### As above, but without breaking the data by MS ---> we calculate the stats for the whole EU.
+
+duration_stat_eu <- duration_calc %>%
+    ## group_by(legal_basis) %>%
+    summarise(mean_duration=mean(total_duration, na.rm=T),
+              median_duration=median(total_duration, na.rm=T),
+              mean_PN_duration=mean(PN, na.rm=T),
+              median_PN_duration=median(PN, na.rm=T),
+              mean_N_duration=mean(N, na.rm=T),
+              median_N_duration=median(N, na.rm=T),
+              number_data_points_N=n()-sum(is.na(N)),
+              number_data_points_PN=n()-sum(is.na(PN))) %>%
+    ungroup
+
+## save_excel(duration_stat_eu, "duration_all_eu.xlsx")
+
+
+#### Now the duration statistics is calculated by breaking the data according
+## to the legal basis
+
+duration_stat_basis <- duration_calc %>%
+    group_by(legal_basis) %>%
+    summarise(mean_duration=mean(total_duration, na.rm=T),
+              median_duration=median(total_duration, na.rm=T),
+              mean_PN_duration=mean(PN, na.rm=T),
+              median_PN_duration=median(PN, na.rm=T),
+              mean_N_duration=mean(N, na.rm=T),
+              median_N_duration=median(N, na.rm=T),
+              number_data_points_N=n()-sum(is.na(N)),
+              number_data_points_PN=n()-sum(is.na(PN)))%>%
+    ungroup
+
+## save_excel(duration_stat_basis, "duration_by_legal_basis.xlsx")
+
+
+## Now the data is broken both by legal basis and by duration statistics.
+
+duration_stat_basis_ms <- duration_calc %>%
+    group_by(member_state_2_letter_code,legal_basis) %>%
+    summarise(mean_duration=mean(total_duration, na.rm=T),
+              median_duration=median(total_duration, na.rm=T),
+              mean_PN_duration=mean(PN, na.rm=T),
+              median_PN_duration=median(PN, na.rm=T),
+              mean_N_duration=mean(N, na.rm=T),
+              median_N_duration=median(N, na.rm=T),
+              number_data_points_N=n()-sum(is.na(N)),
+              number_data_points_PN=n()-sum(is.na(PN)))%>%
+    ungroup %>%
+    arrange(member_state_2_letter_code)
+
+
+
+
+
+#### now the duration statistics is broken by type of measure.
+
+duration_stat_measure <- duration_calc %>%
+    group_by(type_of_measure) %>%
+    summarise(mean_duration=mean(total_duration, na.rm=T),
+              median_duration=median(total_duration, na.rm=T),
+              mean_PN_duration=mean(PN, na.rm=T),
+              median_PN_duration=median(PN, na.rm=T),
+              mean_N_duration=mean(N, na.rm=T),
+              median_N_duration=median(N, na.rm=T),
+              number_data_points_N=n()-sum(is.na(N)),
+              number_data_points_PN=n()-sum(is.na(PN)))%>%
+    ungroup
+
+
+### and now it is also further broken by MS.
+
+duration_stat_measure_ms <- duration_calc %>%
+    group_by(member_state_2_letter_code,type_of_measure) %>%
+    summarise(mean_duration=mean(total_duration, na.rm=T),
+              median_duration=median(total_duration, na.rm=T),
+              mean_PN_duration=mean(PN, na.rm=T),
+              median_PN_duration=median(PN, na.rm=T),
+              mean_N_duration=mean(N, na.rm=T),
+              median_N_duration=median(N, na.rm=T),
+              number_data_points_N=n()-sum(is.na(N)),
+              number_data_points_PN=n()-sum(is.na(PN)))%>%
+    ungroup %>%
+    arrange(member_state_2_letter_code)
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+#### I keep only some columns of the duration calc table and I rename them
+duration_calc_restricted <- duration_calc %>%
+    select(case_reference,member_state_2_letter_code, N, PN) %>%
+    na_to_pattern(0) %>%
+    mutate(total_duration=N+PN) %>%
+    select(-c(N, PN)) %>%
+    rename("MS"="member_state_2_letter_code") %>%
+    mutate(type="decision")
+
+
+
+
+###file with the raw data on the duration of the amendments.
+### it is important to know that when a PN notification has a duration of zero,
+## it actually means that it had never taken place and so the zeros should be
+## treated as missing data.
+
+## I also keep only the distinct amendment numbers to avoid double-counting
+## the amendments.
+
+duration_calc_am <- df_am %>%
+    distinct(amendment_case_number, .keep_all=T) %>% 
+    select(case_number_key , amendment_case_number,member_state_of_amendment,
+           duration_of_n_phase_calendar_days, duration_of_pn_phase_calendar_days,total_duration_pn_n_calendar_days
+           ) %>%
+    pattern_to_na(0) %>%
+    mutate(is_PN_plus_N=if_else(!is.na(duration_of_pn_phase_calendar_days),
+                                "yes", "no"))
+
+
+### I now select only some columns of the duration_calc table
+duration_calc_am_restricted <- duration_calc_am %>%
+    select(case_number_key,member_state_of_amendment,total_duration_pn_n_calendar_days ) %>%
+    rename("case_reference"="case_number_key",
+           "MS"="member_state_of_amendment",
+           "total_duration"="total_duration_pn_n_calendar_days") %>%
+    mutate(type="amendment")
+
+
+
+#### Here I create a table where for every case I list the time to take a decision and the duration of each amendment process.
+
+duration_am_plus_decisions <- bind_rows(duration_calc_am_restricted,
+                                        duration_calc_restricted) %>%
+    arrange(MS, case_reference)
+
+save_excel(duration_am_plus_decisions, "duration_decisions_amendments_raw_data.xlsx")
+
+
+## I calculate some statistics by MS on the collected data on durations and decisions.
+
+duration_am_plus_decisions_ms <- duration_am_plus_decisions %>%
+    group_by(MS) %>%
+    summarise(mean_duration=mean(total_duration),
+              median_duration=median(total_duration)) %>%
+    ungroup
+
+
+## and now the same stats at the EU level.
+duration_am_plus_decisions_eu <- duration_am_plus_decisions %>%
+    ## group_by(MS) %>%
+    summarise(mean_duration=mean(total_duration),
+              median_duration=median(total_duration)) ## %>%
+    ## ungroup
+
+
+
+### I now calculate the statistics on the duration of the amendments process
+## at the MS level
+
+duration_stat_am_ms <- duration_calc_am %>%
+    group_by(member_state_of_amendment) %>%
+    summarise(mean_duration=mean(total_duration_pn_n_calendar_days, na.rm=T),
+              median_duration=median(total_duration_pn_n_calendar_days, na.rm=T),
+              mean_PN_duration=mean(duration_of_pn_phase_calendar_days ,
+                                    na.rm=T),
+              median_PN_duration=median(duration_of_pn_phase_calendar_days ,
+                                        na.rm=T),
+              mean_N_duration=mean(duration_of_n_phase_calendar_days, na.rm=T),
+              median_N_duration=median(duration_of_n_phase_calendar_days, na.rm=T),
+              number_data_points_N=n()-sum(is.na(duration_of_n_phase_calendar_days)),
+              number_data_points_PN=n()-sum(is.na(duration_of_pn_phase_calendar_days))
+              )%>%
+    ungroup
+
+
+
+
+## and now the same stats at the EU level.
+
+duration_stat_am_eu <- duration_calc_am %>%
+    ## group_by(member_state_of_amendment) %>%
+    summarise(mean_duration=mean(total_duration_pn_n_calendar_days, na.rm=T),
+              median_duration=median(total_duration_pn_n_calendar_days, na.rm=T),
+              mean_PN_duration=mean(duration_of_pn_phase_calendar_days ,
+                                    na.rm=T),
+              median_PN_duration=median(duration_of_pn_phase_calendar_days ,
+                                        na.rm=T),
+              mean_N_duration=mean(duration_of_n_phase_calendar_days, na.rm=T),
+              median_N_duration=median(duration_of_n_phase_calendar_days, na.rm=T),
+              number_data_points_N=n()-sum(is.na(duration_of_n_phase_calendar_days)),
+              number_data_points_PN=n()-sum(is.na(duration_of_pn_phase_calendar_days))
+              )## %>%
+    ## ungroup
+
+
+
+
+
+
+
+
+## save_excel(duration_stat_measure, "duration_by_legal_measure.xlsx")
+
+
+
+
+
+
+
+
+
+
+### This time we just count the number of measures by legal basis
+
 measures_covid <- rep1_unique %>%
     filter(decided=="YES", !is.na(legal_basis))%>%
     group_by(legal_basis) %>%
@@ -445,7 +737,7 @@
     mutate(share=n_measures/sum(n_measures))%>%
     add_total(nrow(.)+1)
 
-
+### now number of measures by legal basis and MS
 
 measures_covid_ms <- rep1_unique %>%
     filter(decided=="YES", !is.na(legal_basis))%>%
@@ -464,6 +756,7 @@
 
 
 
+### Statistics on SA cases under assessment
 
 sa_n_pn <- rep1_unique %>%
     ## filter(decided=="YES", !is.na(legal_basis),
@@ -476,7 +769,7 @@
     add_total(nrow(.)+1)
 
 
-
+### as above, but also broken by MS
 sa_n_pn_ms <- rep1_unique %>%
     ## filter(decided=="YES", !is.na(legal_basis),
     ##        register_code %in% c("N", "PN"))%>%
@@ -494,7 +787,7 @@
 
 
 
-
+### and this time broken by Unit dealing with them
 sa_n_pn_unit <- rep1_unique %>%
     ## filter(decided=="YES", !is.na(legal_basis),
     ##        register_code %in% c("N", "PN"))%>%
@@ -509,6 +802,7 @@
 
 
 
+### now broken by both unit and MS
 
 sa_n_pn_unit_ms <- rep1_unique %>%
     ## filter(decided=="YES", !is.na(legal_basis),
@@ -529,65 +823,9 @@
 
 
 
-
-
-budget_by_beneficiary_sector <-  rep1_unique %>%
-    group_by(beneficary_sector) %>%
-    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
-    ungroup %>%
-    filter(complete.cases(.)) %>%
-    mutate(share=budget/sum(budget)) %>%
-    arrange(desc(budget))%>%
-    add_total(nrow(.)+1)
-
-
-
-
-budget_by_beneficiary_sector_ms <-  rep1_unique %>%
-    group_by(member_state_2_letter_code,beneficary_sector) %>%
-    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
-    na.omit %>%
-    mutate(share=budget/sum(budget)) %>%
-    group_modify(~ .x %>%
-                     adorn_totals("row"))  %>% 
-    ungroup%>%
-    complete(member_state_2_letter_code,beneficary_sector) %>%
-    na_to_pattern(0)
-
-
-
-
-
-
-
-budget_by_beneficiary_type <-  rep1_unique %>%
-    group_by(beneficary_type) %>%
-    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
-    ungroup %>%
-    filter(complete.cases(.)) %>%
-    mutate(share=budget/sum(budget)) %>%
-    arrange(desc(budget))%>%
-    add_total(nrow(.)+1)
-
-
-
-
-
-budget_by_beneficiary_type_ms <-  rep1_unique %>%
-    group_by(member_state_2_letter_code,beneficary_type) %>%
-    summarise(budget=sum(confirmed_budgets, na.rm=T)) %>%
-    na.omit %>%
-    mutate(share=budget/sum(budget)) %>%
-    group_modify(~ .x %>%
-                     adorn_totals("row"))  %>% 
-    ungroup%>%
-    complete(member_state_2_letter_code,beneficary_type) %>%
-    na_to_pattern(0)
-
-
-
-
-
+### from the rep1_unique table, we filter the decided cases with procedure
+## either N or PN. then we collapse all the text in type_of_measure column and we count how many times we find there the words in tf_list (i.e. the TF chapters)
+## we want to track.
 
 chapters_stat_test <- rep1_unique %>%
     filter(decided=="YES",
@@ -599,18 +837,19 @@
     mutate(share=value/sum(value))%>%
     mutate(TF_chapter=tf_list)
 
-## test1 <- rep1_unique %>%
-##     filter(decided=="YES",
-##            register_code %in% c("N", "PN"))%>%
-##     filter(grepl("TF3",type_of_measure) )
 
-## test1bis <- rep1_unique %>%
-##     filter(case_reference=="SA.57574")
+### The calculation above is correct but it does not allow one to see exactly
+## to which TF chapter each case is associated to.
+
 
 tf <- rep1_unique %>%
     filter(decided=="YES",
-           register_code %in% c("N", "PN")) 
-    
+           register_code %in% c("N", "PN")) ### I again take the decided
+                                 ### case either N or PN as register_code
+
+### here I use map_df to iterate the filtering of the tf dataframe according
+## to the tf_list of chapters
+
 tf_all <-   map_df(tf_list, function(x) {filter(tf,grepl(x, type_of_measure)) %>%
                                              mutate(TF=x)                                            })  %>%
     mutate(TF=remove_special_char(TF)) %>% 
@@ -623,6 +862,11 @@
 ## filter(grepl("TF1", type_of_measure))
 
 
+### I need to treat separately the cases associated to more than 3 chapters
+## because they are stored somewhere else. These calculations are along
+## the lines of chapter_stat_test, but I need tf_list2 because the TF chapters
+## are written a bit differently.
+
 chapters_stat_extra <- df_dec %>%
     pull(specify_tf_others_more_than_three_sections_combined) %>%
     paste0(collapse = " ") %>%
@@ -631,6 +875,7 @@
     mutate(share=value/sum(value))%>%
     mutate(TF_chapter=tf_list)
 
+### and once again, if I want to have the association between case and TF chapter, I run calculations similar to those for tf_all
 
 tf_all2 <- map_df(tf_list2, function(x) {filter(df_dec,
                                                grepl(x, specify_tf_others_more_than_three_sections_combined)) %>%
@@ -640,14 +885,17 @@
     rename("description"="specify_tf_others_more_than_three_sections_combined")
 
 
+### and now I have the detailed situation for all cases and chapters
+
 all_chapter_cases <- bind_rows(tf_all, tf_all2)
 
-## save_excel(all_chapter_cases, "chapters_detailed.xlsx")
-
 
 
 
 
+#### If I just need the statistics on all the TF chapters, I can add the count
+## data on chapter usage from chapter_stat_test and chapter_stat_extra
+
 
 chapters_stat_fin <- chapters_stat_test %>%
     select(TF_chapter, value) %>%
@@ -657,6 +905,8 @@
     mutate(TF_chapter=remove_special_char(TF_chapter))
 
 
+#### I can finally use the detailed statistics in all_chapter cases
+### to count the number of TF chapter per MS.
 
 chapters_stat_fin_ms <- all_chapter_cases %>%
     tabyl(member_state_code, TF) %>%