gistfile1.r

library(xlsx)
library(reshape2)
library(dplyr)
options(xlsx.datetime.format="yyyy-mm-dd")
df <- read.xlsx('SPLICE results full - xlsx.xlsx',sheetIndex=2,header=TRUE,stringsAsFactors=FALSE,startRow=3)
df <- df[-72,]
id <- df[,1:4]
sheet1 <- select(df,ends_with('Potential.Contribution.of.Energy.System.Components'))
sheet2 <- select(df,ends_with('Criticality.of.Energy.System.Components'))
sheet3 <- select(df,ends_with('Persistence.of.Impacts'))
sheet4 <- select(df,ends_with('Local.Impacts'))
sheet5 <- select(df,ends_with('Uncertainty.of.Impacts'))
sheet6 <- select(df,ends_with('Uncertainty.in.Decision.Making'))
newdf <- cbind(id,sheet1,sheet2,sheet3,sheet4,sheet5,sheet6)
df <- melt(newdf,id.vars=c('RespondentID','CollectorID','StartDate','EndDate'))
df <- df[df$variable!="Potential.Contribution.of.Energy.System.Components",]
df <- df[df$variable!="Criticality.of.Energy.System.Components",]
df <- df[df$variable!="Persistence.of.Impacts",]
df <- df[df$variable!="Local.Impacts",]
df <- df[df$variable!="Uncertainty.of.Impacts",]
df <- df[df$variable!="Uncertainty.in.Decision.Making",]
df$category <- sapply(strsplit(as.character(df$variable),'\\.\\.\\.'),"[[",1)
df$subcategory <- sapply(strsplit(as.character(df$variable),'\\.\\.\\.'),"[[",2)
df$variable <- NULL
rownames(df) <- NULL
sheet1 <- df[df$subcategory=="Potential.Contribution.of.Energy.System.Components",]
sheet2 <- df[df$subcategory=="Criticality.of.Energy.System.Components",]
sheet3 <- df[df$subcategory=="Persistence.of.Impacts",]
sheet4 <- df[df$subcategory=="Local.Impacts",]
sheet5 <- df[df$subcategory=="Uncertainty.of.Impacts",]
sheet6 <- df[df$subcategory=="Uncertainty.in.Decision.Making",]
sheets <- list(sheet1,sheet2,sheet3,sheet4,sheet5,sheet6)
cleanup <- function(x){
  x$subcategory <- NULL
  rownames(x) <- NULL
  x <- dcast(x, RespondentID + CollectorID + StartDate + EndDate ~ category)
  return(x)
}
sheetlist <- lapply(sheets,cleanup)
sheetnames <- c("Potential Contribution of Energy System Components","Criticality of Energy System Components","Persistence of Impacts","Local Impacts","Uncertainty of Impacts","Uncertainty in Decision Making")
for(i in seq(1:6)){
  if(i == 1){
    write.xlsx(sheetlist[[i]],'cleanSpliceData.xlsx',sheetName=sheetnames[i],row.names=FALSE,append=FALSE)
  } else {
    write.xlsx(sheetlist[[i]],'cleanSpliceData.xlsx',sheetName=sheetnames[i],row.names=FALSE,append=TRUE)
  }
}

## Tidy data
df$category <- gsub("\\."," ",df$category)
df$subcategory <- gsub("\\."," ",df$subcategory)
write.csv(df,'tidySplice.csv')