« Work with Excel sheets in R | Main | Creating a final version of the UMBS bibliography »

November 17, 2009

Format Water Profile Data

This is the code I'm using to format some of our water quality data. It was in an Excel workbook where it occupied 132 worksheets. Once I am done it will be in 5 .csv files (1 file per variable.)

Cool things to notice with this R code is the dateTimeToStr() translates the Excel date/time value to an actual date/time value.

It also took me a while to figure out the as.numeric(levels())[as.integer()] line which was necessary because R was reading the data in as a Factor rather than Numerically.

# To run from the R Console command line:
# source("C:/workspace/web/data/rScripts/welchProfilesPh.r")

library(xlsReadWrite)

dir = "C:/workspace/web/data/"
input = "welchsfbprofiles.xls"
output = "pH_Profile_Douglas.csv"

cNames <- paste("pH_", paste(0:21, "m", sep=""), sep="")

d<-matrix(nrow=133, ncol= 23 )
d[1,1] <- "Date"
d[1,2:23]<- cNames

for (i in 1:132) {

     dset = read.xls(paste(dir, input, sep=""), colNames = TRUE, sheet = i, dateTimeAs = "numeric")

     d[i+1,1] <- dateTimeToStr(dset[1,2])
     # This link is where the following code came from.
    & d[i+1,2:23] <-as.numeric(levels(dset[4:25,3]))[as.integer(dset[4:25,3])]

     }

write.table(d, file=paste(dir, output, sep=""), sep=",", col.names = FALSE, row.names = FALSE, quote = FALSE)

Posted by kkwaiser at November 17, 2009 02:19 PM

Comments

Login to leave a comment. Create a new account.