#pivot-tables


#"bhmtrains"

e <- new.env(parent = emptyenv())
#load("path/to/file.rda", envir=e)
load("~/programmering/R/data/bhmtrains.rda", envir=e) 
ls(e)     # shows names of variables stored in it,
#[1] "bhmtrains"
ls.str(e) # shows a `str` presentation of all variables within it

datasets<-load("~/programmering/R/data/bhmtrains.rda") 
datasets #bhmtrains
head(bhmtrains,1)
names(bhmtrains)
'[1] "ServiceId"                "Status"                   "TOC"                     
 [4] "TrainCategory"            "PowerType"                "SchedSpeedMPH"  ..   '

#PivottabelMedPct--------
df<-bhmtrains
library(pivottabler)
pt <- PivotTable$new()
pt$addData(df )
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
#pt$defineCalculation(calculationName="Total",summariseExpression="n()") #antal rows
pt$defineCalculation(calculationName="Total%",summariseExpression="round(n()/nrow(df)*100,1)") #pct 
pt$renderPivot()
pt


library(openxlsx)
wb <- createWorkbook(creator = Sys.getenv("USERNAME"))
addWorksheet(wb, "Data")
pt$writeToExcelWorksheet(wb=wb, wsName="Data", 
                         topRowNumber=2, leftMostColumnNumber=2, applyStyles=TRUE) # upper left (2,2)
saveWorkbook(wb, file="./data/pivottabel.xlsx", overwrite = TRUE)   #, asTable = FALSE) # man skal ikke lave tables, for der mål ikke sorteres inde i Excel, så fucker pivot-tabellen op 


data(mtcars)
rpivotTable(mtcars,rows="gear", cols=c("cyl","carb"),subtotals=TRUE, width="100%", height="400px")
####Group by
library(dplyr)
by_cyl <- mtcars %>% group_by(cyl)
by_cyl 

#order dataframe by cyl-kolonne
library(dplyr)
order_by_cyl <- mtcars[order(mtcars$cyl),]  
order_by_cyl


TOC_Categories<-bhmtrains %>% count(TOC,TrainCategory) 
TOC_Categories  #df

#gemmer df i excelark
library(openxlsx)
write.xlsx(TOC_Categories, './data/TOC_Categories.xlsx', asTable = TRUE)
write.xlsx(pt, './data/pivot_tabel.xlsx', asTable = TRUE)

data(mtcars)
rpivotTable(mtcars,rows="gear", cols=c("cyl","carb"),
            subtotals=TRUE, width="100%", height="400px")