Visualizing Energy Data from Influx DB

show_energy.R

# Visualizing Energy Data from Influx DB
#
# - Loads energy data from an influx database filled with vzlogger
# - Creates a visualization for the electric power of the last 12 hours
#
library(dplyr)
library(tidyr)
library(ggplot2)
requireNamespace("glue")
requireNamespace("plotly")
requireNamespace("lubridate")
requireNamespace("influxdbr")
requireNamespace("assertthat")

# Converts a date/time value into the Influx QL format
influx_time <- function(x) {
  assertthat::is.date(x)
  options(digits.secs = 3)
  return(base::format(x, format = '%Y-%m-%dT%H:%M:%OSZ', tz = 'GMT'))
}

# Converts a lubridate::Period into Influx QL format used in GROUP BY
influx_period <- function(x) {
  assertthat::assert_that(lubridate::is.period(x))
  assertthat::are_equal(lubridate::year(x), 0)
  glue::glue("{lubridate::day(x)}d{lubridate::hour(x)}h{lubridate::minute(x)}m{lubridate::second(x)}s")
}

# Converts a lubridate::Duration into floating point hours
as_hours <- function(x) {
  assertthat::assert_that(lubridate::is.duration(x))
  return(as.numeric(x) / as.numeric(lubridate::hours(1)))
}

# 1. Connect to influx db
# connection parameters are in ~/.influxdb.cnf (MacOS/Linux)
conn <- influxdbr::influx_connection(
  group = "vzlogger",
  curl_options = httr::config(verbose = FALSE, timeout = 10, ssl_verifypeer = FALSE))

# 2. Query electric enery data in 5 minutes resolution (values are in [kWh])
end_time <- lubridate::now()
start_time <-  end_time - lubridate::hours(12)
group_by <- lubridate::minutes(5)
query <- glue::glue("SELECT MAX(value) AS value FROM electric WHERE time >= '{influx_time(start_time)}' AND time < '{influx_time(end_time)}' GROUP BY *, time({influx_period(group_by)})")
energy <- influxdbr::influx_query(conn, db = "vzlogger", query = query, return_xts = FALSE)[[1]]
View(energy)

# 3. Compute power by computing difference ratios value / deltaT
# Unit of power is then in [W]
x <- energy %>%
  select(time, identifier, value) %>% 
  drop_na() %>%
  group_by(identifier) %>%
  mutate(diffValue = c(0, diff(value))) %>%
  mutate(diffTimeHours = c(0, as_hours(lubridate::as.duration(diff(time))))) %>%
  mutate(power = diffValue / diffTimeHours) %>% 
  ungroup() %>% drop_na()

# 4. Create a graph using ggplot
p <- x %>% ggplot(aes(x = time, y = power, color = identifier)) +
  theme_bw() +
  geom_line(group = 1) + ylab("power [W]")
print(p)

# 5. Make it interactive using plotly
q <- plotly::ggplotly(p, dynamicTicks = TRUE)
print(q)