R/load.R

In EBlonkowski/timeseries:

#-----------------------------------------------------------------------------#
# Loading from file


load_sge <- function(file) {
  read.csv(file) %>% 
    parse_time(time_i = 2) %>%
    select(time.local, sge = system_demand_actual) %>%
    tse(period = 'period')
}

load_weather <- function(file) {
  read.csv(file) %>% 
    parse_time(time_i = 2, time_format = '%Y-%m-%d %H:%M:%S') %>%
    select(time.local, drybulb, dewpoint, relative_humidity) %>%
    tse(period = 'hour')
}

load_gefload <- function(file) {
  data <-
    read.csv(file, dec = ',', as.is = TRUE) %>%  # keep strings as strings
    # put hours in columns
    melt(id.vars = c('zone_id', 'year', 'month', 'day')) %>% 
    mutate(variable = gsub('h', '', variable)) %>% # remove h from hour name
    mutate(time = paste(year, month, day, sep = '/')) %>% # format the time
    mutate(time = paste(time, variable)) %>%
    select(time, zone_id, load = value) %>% # select the columns
    dcast('time ~ zone_id', value.var = 'load') # put each zone in a column
  
  colnames(data) <- c('time', colnames(data) %>% tail(-1) %>%
                        paste('zone', ., sep = '_')) # change colnames
    
  data %>%
    # parse the time information
    parse_time(time_i = 1, time_format = '%Y/%m/%d %H') %>% 
    tse(period = 'hour')
}

load1_gef2017 <- function(file) {
  read.csv(file, as.is = TRUE) %>%
    mutate(time = paste(Date, Hour)) %>%
    select(time, load = DEMAND, drybulb = DryBulb, dewpoint = DewPnt) %>%
    parse_time(time_i = 1, time_format = '%d/%m/%Y %H') %>%
    tse(period = 'hour')
}

load_sensor <- function(file) {
  message('[load sensor] reading from file')
  data <- 
    read.csv(file, header = TRUE, skip = 1, as.is = TRUE) %>%
    select(X,
           Uptime,
           # meter 002
           temp2 = TemperatureERIAN.SEN.TEST.002, pressure2 = BarometricPressureERIAN.SEN.TEST.002, 
           humidity2 = HumidityERIAN.SEN.TEST.002, light2 = LightERIAN.SEN.TEST.002,
           # meter 1101
           temp1101 = TemperatureTHPL..SENSOR.1101, pressure1101 = BarometricPressureTHPL..SENSOR.1101,
           humidity1101 = HumidityTHPL..SENSOR.1101, light1101 = LightTHPL..SENSOR.1101,
           # meter 1103
           temp1103 = TemperatureTHPL.SENSOR.1103, pressure1103 = BarometricPressureTHPL.SENSOR.1103,
           humidity1103 = HumidityTHPL.SENSOR.1103, light1103 = LightTHPL.SENSOR.1103,
           # meter 0004
           temp4 = TemperatureERIAN.CAG.SA.0004, pressure4 = BarometricPressureERIAN.CAG.SA.0004, 
           humidity4 = HumidityERIAN.CAG.SA.0004, light4 = LightERIAN.CAG.SA.0004
           )
  
  # old way
  #data %>%parse_time(time_i = 1, time_format = '%d.%m.%y_%H:%M:%S')
  
  message('[load sensor] parsing uptime column')
  # parse the uptime value
  delta_t <- vapply(data$Uptime, FUN=parse_sensor_uptime, FUN.VALUE = c(1))
  
  message('[load sensor] formatting data')
  # compute the date/time of the origin of the uptime value
  date_start <- as.POSIXct(strptime(data$X[1], format = '%d.%m.%y_%H:%M:%S'), timezone = 'Asia/Singapore') +
    dhours(12) # because the data is pm but it's not in the file
  date_start <- date_start - delta_t[1]
  
  # compute the time indexes
  data$time.local <- date_start + delta_t
  
  data$time.minute_round <- as.POSIXct(round(data$time.local, 'mins')) # add a minute index
  # average all ther measurements per minute
  data %>%
    select(-X, -Uptime) %>% # remove the useless columns
    melt(id = c('time.local', 'time.minute_round')) %>%
    dcast(time.minute_round ~ variable, mean) %>%
    fill_time('time.minute_round', 'time.minute') %>%
    rename(time.local = time.minute_round) %>%
    tse(period = 'minute') %>% 
    rename(s2_temp = temp2, s2_humidity = humidity2,    # change the names
           s2_pressure = pressure2, s2_light = light2, 
           s1_temp = temp1101, s1_pressure = pressure1101, 
           s1_humidity = humidity1101, s1_light = light1101, 
           s3_temp = temp1103, s3_pressure = pressure1103, 
           s3_humidity = humidity1103, s3_light = light1103, 
           s4_temp = temp4, s4_pressure = pressure4, 
           s4_humidity = humidity4, s4_light = light4)
}

parse_sensor_uptime <- function(str) {
  comps <- strsplit(str, '[:.]')[[1]] %>% as.numeric
  # if the format is not correct
  if(length(comps) != 4) {
    warning('incorrect format')
    return(NA)
  }
  ddays(comps[1]) + dhours(comps[2]) + dminutes(comps[3]) + dseconds(comps[4])
}

load_shss <- function(file) {
  read.csv(file) %>%
    select(time.local = local.time, power = power.kw, drybulb, dewpoint, humidity = relative.humidity) %>%
    parse_time(time_i = 1, time_format = '%Y-%m-%d %H:%M:%S') %>%
    fill_time('time.local', 'time.hour') %>%
    tse(period = 'hour')
}

#-----------------------------------------------------------------------------#
# Loading from database

#' Loads from SMES database
#' 
#' @description Loads a single data source from SMES database as a tse object
#' @usage tse <- load_db(driver = 'SMES-db-driver', uid="sa", pwd="admin098@")
load_db <- function(driver, uid, pwd, data_id = 1) {
  # query string
  query <- "
    SELECT f.[DateID]
          ,f.[TimeID]
          ,d.[StandardDate]
          ,t.[StandardTime]
          ,[AveragePower]
    FROM [smes_external].[smes].[FactMeterData] AS f
      LEFT JOIN [smes_external].[smes].DimDate AS d
      ON f.DateID = d.ID
      LEFT JOIN [smes_external].[smes].DimTime AS t
      ON f.TimeID = t.ID" %>%
  # select only one data source
    paste("WHERE f.DataID = ", data_id)

  # connect to the database
  myconn <-odbcConnect(driver, uid=uid, pwd=pwd)
  # get the data
  data <- sqlQuery(myconn, query, stringsAsFactors = FALSE)
  # close connection to the database
  close(myconn)
  
  # convert the raw data to tse
  data %>% db2tse
}

#' This function convert data from the database to the internal tse format
#' 
#' @param data Raw data from the database, should only contain 1 source of value
#' for now
#' @return The data cast into a tse object
db2tse <- function(data) {
  data %>% 
    select(DateID, TimeID, StandardDate, StandardTime, power = AveragePower) %>%
    # create a column containing the datetime as a string
    mutate(datetime = paste(StandardDate, StandardTime)) %>%
    # parse the timestamp
    parse_time(6, time_format = '%m/%d/%Y %I:%M:%S %p') %>%
    # keep only the interesting columns
    select(time.local, power) %>%
    # fill the missing data
    fill_time('time.local', 'time.period') %>%
    # convert to tse
    tse(period = 'period') %>%
    clamp_tse # remove empty rows at the start and end
}