#### Robust code for reading in excel data and preparing it for NAL #### Prepared by Jane Okalebo #### Install all the necessary packages #### Install packages # ipak function: install and load multiple R packages. # check to see if packages are installed. Install them if they are not, then load them into the R session. ipak <- function(pkg){ new.pkg <- pkg[!(pkg %in% installed.packages()[, "Package"])] if (length(new.pkg)) install.packages(new.pkg, dependencies = TRUE) sapply(pkg, require, character.only = TRUE) } # usage packages <- c("bigmemory", "readxl", "XLConnect", "xlsx","epitools") ipak(packages) ##read in csv file my_data <- (file.choose()) # read first sheet mdata<- read.csv(my_data, header=TRUE) colnames(mdata) data<-mdata data[data == 9999.0] <- NA data[data == -9999.0] <- NA ##data <- read.csv("Site3.April.csv", stringsAsFactors = FALSE) ##str (data) data$time2 <- strptime(as.character(data$DateTime),"%m/%d/%Y %H:%M") which(is.na(data$time2)) #head(data$time2) data$time3<-as.Date(data$time2) #head(data$time3) aw<-as.month(data$time3) data$month<-aw$mon unique(data$month) rm(aw) #str(data) #head(data) #### save files #### This code will be used for defining the name of the file and saving the files #### set the folder to save files #### setwd("P:/2015/LTAR/Met Data/4.13.2016_7.31.2016/PRHPA4.13.2016_7.16.2016") sitename<-data$StationID[1] for (j in 1:length(unique(data$month))) { data_temp <- data[which(data$month==j), ] if (j<10){ data_month<-paste0("0",j) }else { data_month<-j } data_temp$month<-NULL ####DateTime Stamp code data_temp$timeT<-"T" data_temp$timestamp<-"-6:00" data_temp$StationID<-paste0("00", data_temp$StationID) rm(df) newlist<-strsplit(as.character(data_temp$DateTime), "[ ]") length(newlist) #head(newlist) BOD<-unlist(newlist, use.names=FALSE) #head(BOD) df <- data.frame(matrix(BOD, nrow=(length(newlist)), byrow=T)) #head(df) #df[1] #df[2] #names(df) data_temp$DateTime4<-paste0(data_temp$time3,data_temp$timeT,df$X2,data_temp$timestamp) #head(data_temp$DateTime4) #str(data_temp) data_temp$LTARSiteAcronym<-"PRHP" data_temp$DateTime<-data_temp$DateTime4 data_temp$time2<-NULL data_temp$timestamp<-NULL data_temp$time3<-NULL data_temp$DateTime4<-NULL data_temp$timeT<-NULL #str(data_temp) ## set working directory to save files setwd("D:/LTAR/Automated Processing of DATA/test") filName<-paste0("prhpMET00",sitename,"B_01_2016",data_month,"00_00.csv") write.table(data_temp,filName, na="", row.names=FALSE, sep=",", quote=FALSE) }