rm(list = ls())

#install.packages("readxl")
library("readxl")
library("openxlsx")
library(dplyr)
library(raster)
library(raster)
library(ggplot2)


######### INFORMATION TO MODIFY ########

folder_to_upload <- ""                                       # Name of the folder to find the files
folder_to_save <- ""                                         # Name of the folder to save the files/figures

N_research_question <- 57
weight_criteria <- c(0.8, 0.84, 0.88, 0.80) # (Impact, Answerability, Relevancy, Potential for translation)

demographic_criteria_to_analyze<-'Status'                 # Write 'Status' to not divide by demographics. Otherwise: e.g. 'Q5bis_5' / 'Q2' / 
demographic_criteria_for_final_score <-'0'    # Write '0' to not divide by demographics. 

N_digits<-2                                           # Number of digits you want for the outputs

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

### IMPORT SURVEY
results_specific_qualtrics<-read_excel(paste0(folder_to_save,'count_criteria_RQ_',demographic_criteria_to_analyze,'_',demographic_criteria_for_final_score,'.xlsx'))        # Upload the output of Qualtrics (xlsx format)
question_list<-read_excel(paste0(folder_to_upload,'question_list.xlsx'))                                                                                                         # Upload the list of the research questions (xlsx format)


### 2.CALCULATE NUMBER OF ANSWERS PER RESEARCH QUESTION

score.RQ <- data.frame(matrix(ncol=21,nrow=N_research_question))

for (i in 1:N_research_question){
  
  score.RQ.weight.RPS.C1 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_1')))%>%filter(row_number()==10),digits=N_digits)
  score.RQ.unweight.RPS.C1 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_1')))%>%filter(row_number()==9),digits=N_digits)
  score.RQ.AEA.C1 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_1')))%>%filter(row_number()==11),digits=N_digits)
  
  score.RQ.weight.RPS.C2 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_2')))%>%filter(row_number()==10),digits=N_digits)
  score.RQ.unweight.RPS.C2 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_2')))%>%filter(row_number()==9),digits=N_digits)
  score.RQ.AEA.C2 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_2')))%>%filter(row_number()==11),digits=N_digits)
  
  score.RQ.weight.RPS.C3 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_3')))%>%filter(row_number()==10),digits=N_digits)
  score.RQ.unweight.RPS.C3 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_3')))%>%filter(row_number()==9),digits=N_digits)
  score.RQ.AEA.C3 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_3')))%>%filter(row_number()==11),digits=N_digits)
  
  score.RQ.weight.RPS.C4 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_4')))%>%filter(row_number()==10),digits=N_digits)
  score.RQ.unweight.RPS.C4 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_4')))%>%filter(row_number()==9),digits=N_digits)
  score.RQ.AEA.C4 <- round(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_4')))%>%filter(row_number()==11),digits=N_digits)
  
  all.score.RQ.weight.RPS <- round((score.RQ.weight.RPS.C1+score.RQ.weight.RPS.C2+score.RQ.weight.RPS.C3+score.RQ.weight.RPS.C4)/4,digits=N_digits)
  all.score.RQ.unweight.RPS <- round((score.RQ.unweight.RPS.C1+score.RQ.unweight.RPS.C2+score.RQ.unweight.RPS.C3+score.RQ.unweight.RPS.C4)/4,digits=N_digits)
  all.score.RQ.AEA <- round((score.RQ.AEA.C1+score.RQ.AEA.C2+score.RQ.AEA.C3+score.RQ.AEA.C4)/4,digits=N_digits)
  
  all_count <- (results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_1')))%>%filter(row_number()==7)+
                  results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_2')))%>%filter(row_number()==7)+
                  results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_3')))%>%filter(row_number()==7)+
                  results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_4')))%>%filter(row_number()==7))/4
  
  all.data.RQ <- c(i,
                   pull(all.score.RQ.weight.RPS),pull(all.score.RQ.unweight.RPS),pull(all.score.RQ.AEA),pull(all_count),
                   pull(score.RQ.weight.RPS.C1),pull(score.RQ.unweight.RPS.C1),pull(score.RQ.AEA.C1),pull(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_1')))%>%filter(row_number()==7)),
                   pull(score.RQ.weight.RPS.C2),pull(score.RQ.unweight.RPS.C2),pull(score.RQ.AEA.C2),pull(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_2')))%>%filter(row_number()==7)),
                   pull(score.RQ.weight.RPS.C3),pull(score.RQ.unweight.RPS.C3),pull(score.RQ.AEA.C3),pull(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_3')))%>%filter(row_number()==7)),
                   pull(score.RQ.weight.RPS.C4),pull(score.RQ.unweight.RPS.C4),pull(score.RQ.AEA.C4),pull(results_specific_qualtrics%>%dplyr::select(c(paste0('#',i,'_4')))%>%filter(row_number()==7)))
  # pull(score.RQ.weight.RPS.C5),pull(score.RQ.unweight.RPS.C5),pull(score.RQ.AEA.C5),pull(all.count.criteria%>%dplyr::select(c(paste0('#',i,'_5')))%>%filter(row_number()==7)))
  
  score.RQ[i,]<-all.data.RQ
  
}

colnames(score.RQ)<-c("#RQ",
                      "Weighted RPS - RQ","Unweighted RPS - RQ","AEA - RQ", "Averaged Count",
                      "Weighted RPS - Impact","Unweighted RPS - Impact","AEA - Impact","Count - Impact",
                      "Weighted RPS - Answerability","Unweighted RPS - Answerability","AEA - Answerability","Count - Answerability",
                      "Weighted RPS - Relevancy","Unweighted RPS - Relevancy","AEA - Relevancy","Count - Relevancy",
                      "Weighted RPS - Potential for translation","Unweighted RPS - Potential for translation","AEA - Potential for translation","Count - Potential for translation")


min.weight.RPS <- score.RQ%>%dplyr::select(`Weighted RPS - RQ`)%>%summarize(min(`Weighted RPS - RQ`))
max.weight.RPS <- score.RQ%>%dplyr::select(`Weighted RPS - RQ`)%>%summarize(max(`Weighted RPS - RQ`))
range.weight.RPS <- max.weight.RPS-min.weight.RPS

min.unweight.RPS <- score.RQ%>%dplyr::select(`Unweighted RPS - RQ`)%>%summarize(min(`Unweighted RPS - RQ`))
max.unweight.RPS <- score.RQ%>%dplyr::select(`Unweighted RPS - RQ`)%>%summarize(max(`Unweighted RPS - RQ`))
range.unweight.RPS <- max.unweight.RPS-min.unweight.RPS

min.AEA <- score.RQ%>%dplyr::select(`AEA - RQ`)%>%summarize(min(`AEA - RQ`))
max.AEA <- score.RQ%>%dplyr::select(`AEA - RQ`)%>%summarize(max(`AEA - RQ`))
range.AEA <- max.AEA-min.AEA

score.weight.RPS <- data.frame(matrix(ncol=3,nrow=N_research_question))
colnames(score.weight.RPS)<-c("Scaled Weighted RPS - RQ","Scaled Unweighted RPS - RQ","Scaled AEA - RQ")

for (i in 1:N_research_question){
  score.weight.RPS_1 <- round(((score.RQ$`Weighted RPS - RQ`[i])-(min.weight.RPS))/range.weight.RPS,digits=N_digits)
  score.weight.RPS[i,1] <- round(score.weight.RPS_1,digits=N_digits)
  
  score.unweight.RPS_2<- round(((score.RQ$`Unweighted RPS - RQ`[i])-(min.unweight.RPS))/range.unweight.RPS,digits=N_digits)
  score.weight.RPS[i,2] <- round(score.unweight.RPS_2,digits=N_digits)
  
  score.AEA<- round(((score.RQ$`AEA - RQ`[i])-(min.AEA))/range.AEA,digits=N_digits)
  score.weight.RPS[i,3] <- round(score.AEA,digits=N_digits)
  
}

data.all <- cbind(score.weight.RPS,score.RQ)
score.all.RQ <- cbind(question_list,data.all)
score.all.RQ <- score.all.RQ[, !is.na(colnames(score.all.RQ))]
score.all.RQ_b <- as_tibble(score.all.RQ)

score.all.RQ_select<-score.all.RQ_b%>%dplyr::select(c(`Climate Hazards`,
                                                      `Research Question #`,
                                                      `Research Question`,
                                                      `Scaled Weighted RPS - RQ`,
                                                      `Scaled Unweighted RPS - RQ`,
                                                      `Scaled AEA - RQ`,
                                                      `#RQ`,
                                                      `Weighted RPS - RQ`,
                                                      `Unweighted RPS - RQ`,
                                                      `AEA - RQ`,
                                                      `Averaged Count`))

write.xlsx(score.all.RQ, paste0(folder_to_save,'score_all_RQdate_',demographic_criteria_to_analyze,'_',demographic_criteria_for_final_score,'.xlsx'))
write.xlsx(score.all.RQ_select, paste0(folder_to_save,'score_RQdate_',demographic_criteria_to_analyze,'_',demographic_criteria_for_final_score,'.xlsx'))