# plot the common id over the years

path <- "D:/public/yang/MIND_STEP/new_sample/fadn_data_common_id.xlsx"
sheetsnames <- excel_sheets(path)
length(sheetsnames)
lst <- lapply(1:28, function(i) read_excel(path, sheet = i))
lst_test <- Map(function(x, y) {names(x)[1] <- y; x},
                lst, sheetsnames)

names(lst_test) <- sheetsnames

new_colnames <- c("Years","Num_id")

new_lst_ = lapply(lst_test, setNames, new_colnames)
###
new_lst = new_lst_
for (i in 1:28){
  new_lst[[i]]$Length <- str_count(new_lst[[i]]$Years)
  new_lst[[i]]$group <- cut(new_lst[[i]]$Length, breaks=c(1,10,14,18,25,30,35,40,48,55,58,70,Inf))
  #new_lst[[i]]$group <- cut(new_lst[[i]]$Length, breaks=c(1,10,14,18,25,30,35,40,48,55,58,70,Inf))
}



label_facet <- function(original_var, custom_name){
  lev <- levels(as.factor(original_var))
  #lev <- c()
  lab <- paste0(custom_name, ": ", lev)
  names(lab) <- lev
  return(lab)
}

names <- as_labeller(
  c(`(1,10]` = "2 years", `(14,18]` = "3 years",`(18,25]` = "4 years",
    `(25,30]` = "5 years",`(30,35]` = "6 years", `(35,40]` = "7 years",
    `(40,48]` = "8 years", `(48,55]`= "9 years", `(55,58]` = "10 years",
    `(58,70]`="11 and 12 years", `(70,Inf]` = "more than 12"))

figure <- function(country, df, n){

  p = df %>%
    #ggplot( aes(x = reorder(Years, -Num_id) ,y=Num_id))
    ggplot( aes(x = Years ,y=Num_id))+
    geom_bar( stat="identity", position = position_dodge(width = 0.8),width=0.5, fill = rainbow(n=length(df$Num_id)) ) +
    coord_flip()+
    #labs(title = paste0("Plot of the Number of common ID for country: ", country ), fill = "Years") +
    xlab("Years") +
    ylab("Number of common ID") +
    geom_text(aes(label=Num_id), vjust=0.5, colour="black", size=3.5)+
    theme(axis.text.x=element_text(color = "black", size=6, angle=0, vjust=.8, hjust=0.8)) +
    scale_x_discrete(labels = function(x) str_wrap(x, width = n)) +
    facet_wrap( ~ group, scales="free",
                labeller=names
                  #labeller(group = label_facet(df$group, "grouping"))
                )+
    ggtitle(paste0("Number of common ID for country: ", country )) +
    theme_bw() +
    theme(plot.title = element_text(hjust = 0.5))
  p
}
for (i in 1:28){
  if (sheetsnames[i] =="NED"){
    p <- figure(sheetsnames[i],new_lst[[i]], 35)
  }
  else{
    p <- figure(sheetsnames[i],new_lst[[i]], 20)
  }

  ggsave(plot = p,
         filename = paste0("D:/public/data/fadn/lieferung_20210414/common_id_plots/",sheetsnames[i] ,"_plot.png"),
         width = 18, height = 8)
}

figure("BEL",new_lst$BEL, 35)