#
#
###### Exporting Tables, Graphs, and Time series data into Excel.
#
# This material is also available in RSS Matters (article) format.
# http://web3.unt.edu/benchmarks/issues/2013/09/rss-matters
# and as an Adobe.pdf (printer friendly):
# http://researchsupport.unt.edu/class/Jon/Benchmarks/ExportExcel_L_JDS_Sep2013.pdf
#
# First, import the raw text (.txt) comma delimited data.
ts.df <- read.table(
"http://researchsupport.unt.edu/class/Jon/Benchmarks/ExcelFiles/time_series_001.txt",
header=TRUE, sep=",", na.strings="NA", dec=".", strip.white=TRUE)
nrow(ts.df)
ncol(ts.df)
# Next, set the working directory to the location where you want to
# save the final Excel file when we're done.
setwd("C:/Users/jds0282/Desktop/")
# Next, create an empty table in which the descriptive statistics will go.
table.1 <- data.frame(matrix(rep(NA,36), nrow = 4))
names(table.1) <- c("stats",names(ts.df[,2:9]))
table.1[,1] <- factor(c("n","mean","sd","mean.fd"))
table.1
# Next, get some summary statistics (i.e. n, mean, standard deviation).
table.1[1,2:9] <- rep(nrow(ts.df), 8)
table.1[2:3,2:9] <- data.frame(matrix(c(apply(ts.df[,2:9], 2, mean),
apply(ts.df[,2:9], 2, sd)), byrow = T, nrow = 2))
table.1
# Next, compute the mean fractal dimension of each ts and put those
# into the table (fractal dimension is used as a 'complexity' measure).
library(abind)
library(fractaldim)
w.s <- .1*nrow(ts.df); w.s
for(i in 1:8){
q <- fd.estimate(data = ts.df[,i+1], methods = "madogram",
window.size = w.s, step.size = w.s, trim = TRUE,
keep.data = FALSE, keep.loglog = FALSE, parallel = FALSE,
nr.nodes = NULL, plot.loglog = FALSE)
table.1[4,i+1] <- mean(q$fd)
}; rm(i,q,w.s)
detach("package:fractaldim")
detach("package:abind")
table.1
# Next, we're going to create a couple graphs of the time series'.
# Keep in mind, you are not going to 'see' the graphs, they will
# be written out of R as 'png' files into the working directory.
jpeg('graph1.png')
par(mfrow = c(4,1))
plot(ts.df[,1],ts.df[,2], type = "l", col = "darkblue", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
par(new = T)
plot(ts.df[,1],ts.df[,3], type = "l", col = "blue", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
plot(ts.df[,1],ts.df[,4], type = "l", col = "darkgreen", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
par(new = T)
plot(ts.df[,1],ts.df[,5], type = "l", col = "green", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
plot(ts.df[,1],ts.df[,6], type = "l", col = "red", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
par(new = T)
plot(ts.df[,1],ts.df[,7], type = "l", col = "brown", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
plot(ts.df[,1],ts.df[,8], type = "l", col = "black", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
par(new = T)
plot(ts.df[,1],ts.df[,9], type = "l", col = "grey", xlab = "Time",
ylim = c(-5,5), ylab = "Y")
dev.off()
################################################################################
#
# Now, the hard part....exporting the table, graphs, and data frame into
# the same Excel file.
# Now, load the packages which will allow us to export Excel files.
library(rJava)
library(xlsxjars)
library(xlsx) # This is the one, but it requires the two above.
# Next, create an Excel file (workbook = wb), then create the first
# sheet (table.1 & graphs), then create the second sheet (data);
# then, export the workbook using the 'saveWorkbook' function which
# saves the 'wb' as a finished Excel file.
my.wb <- createWorkbook(type = "xls")
sheet.1 <- createSheet(my.wb, sheetName = "time.series.tables.and.graphs")
addDataFrame(table.1, sheet = sheet.1, startRow = 3, startColumn = 1)
my.file <- "C:/Users/jds0282/Desktop/graph1.png"
addPicture(file = my.file, sheet = sheet.1, scale = 2, # scale = 1 by default.
startRow = 10, startColumn = 2)
my.file <- "C:/Users/jds0282/Desktop/graph2.png"
addPicture(file = my.file, sheet = sheet.1, scale = 2, # scale = 1 by default.
startRow = 75, startColumn = 2)
sheet.2 <- createSheet(my.wb, sheetName = "time.series.data")
addDataFrame(ts.df, sheet = sheet.2, startRow = 1, startColumn = 1)
saveWorkbook(my.wb, "TimeSeries.001.xls")
# Now, check your working directory to make sure the Excel file
# has been created. If it has, then proceed to the next few lines
# and clean up the workspace.
# See any of these help files to clarify:
# help(createWorkbook)
# help(createSheet)
# help(addDataFrame)
# help(addPicture)
# help(saveWorkbook)
search()
detach("package:xlsx")
detach("package:xlsxjars")
detach("package:rJava")
search()
ls()
rm(my.wb, my.file, sheet.1, sheet.2, table.1)
ls()
# End script.