How to access HomeAssistant’s InfluxDB from R
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
I’m running a HomeAssistant instance at home. I’ve configured it to log data into an InfluxDB database, so I can retrieve historical data for analysis later on. In default mode HomeAssistant would aggregate historical data for storage reasons.
So now I want to access the InfluxDB database from R to perform custom analyses.
HomeAssistant is still using InfluxDB version 1. To connect to InfluxDB from R,
I thought I can use the influxdbr package. But I got some errors because
this package seems to be outdated.
Here’s what I get, when fetching data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
library(influxdbr)
library(tidyverse)
influx_con <- influx_connection(
scheme = "http", host = "my_influx_host", port = 8086,
user = "my_influx_user", pass = "my_influx_pass"
)
query <- 'SELECT entity_id, value FROM "autogen"."°C" WHERE time > now() - 5d'
influx_query(influx_con,
db = "my_influx_db",
query = query,
)
|
The error message is:
1 2 3 4 5 6 7 8 9 10 11 |
Error in `purrr::map()`: ℹ In index: 1. Caused by error in `purrr::map()`: ℹ In index: 1. ℹ With name: results. Caused by error in `purrr::map()`: ℹ In index: 1. Caused by error: ! The `validate` argument of `as_tibble()` was deprecated in tibble 2.0.0 and is now defunct. ℹ Please use the `.name_repair` argument instead. Run `rlang::last_trace()` to see where the error occurred. |
It seems that the influxdbr package is not compatible with the current version
of the tibble package. The latest commit is 5 years old.
So I decided to use the httr package to send HTTP requests directly to the InfluxDB API.
Here’s how to do it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
library(tidyverse)
library(httr)
library(jsonlite)
query <- 'SELECT entity_id, value FROM "autogen"."°C" WHERE time > now() - 5d'
response <- GET(
url = paste0("my_influx_host", "/query"),
query = list(
db = "my_influx_db",
q = query,
u = "my_influx_user",
p = "my_influx_pass"
),
config = config(ssl_verifypeer = TRUE)
)
if (response$status_code == 200) {
data_raw <- content(response, as = "text", encoding = "UTF-8") %>% fromJSON(flatten = TRUE)
}
|
The data structure looks a little bit complicated:
1 |
data_raw %>% str() |
1 2 3 4 5 6 7 8 9 10 |
## List of 1 ## $ results:'data.frame': 1 obs. of 2 variables: ## ..$ statement_id: int 0 ## ..$ series :List of 1 ## .. ..$ :'data.frame': 1 obs. of 3 variables: ## .. .. ..$ name : chr "°C" ## .. .. ..$ columns:List of 1 ## .. .. .. ..$ : chr [1:3] "time" "entity_id" "value" ## .. .. ..$ values :List of 1 ## .. .. .. ..$ : chr [1:44176, 1:3] "2025-11-11T13:20:31.171165Z" "2025-11-11T13:20:43.741052Z" "2025-11-11T13:20:43.741159Z" "2025-11-11T13:20:44.552422Z" ... |
But here starts the magic of tidyverse. The interesting data is stored in
data_raw$results[[1]]$series[[1]]$values[[1]]. We can get it using pluck()
and convert it to a data.frame:
1 2 3 |
data_raw_df <- data_raw %>%
pluck("results", "series", 1, "values", 1) %>%
as.data.frame(stringsAsFactors = FALSE)
|
1 2 |
data_raw_df %>% head(10) |
1 2 3 4 5 6 7 8 9 10 11 |
## V1 V2 V3 ## 1 2025-11-11T13:20:31.171165Z Temp_5 25.875 ## 2 2025-11-11T13:20:43.741052Z Temp_11 30.625 ## 3 2025-11-11T13:20:43.741159Z Temp_10 48.75 ## 4 2025-11-11T13:20:44.552422Z Temp_8 28.25 ## 5 2025-11-11T13:20:44.552522Z Temp_9 28.25 ## 6 2025-11-11T13:20:50.225514Z Temp_2 19.8 ## 7 2025-11-11T13:21:07.61594Z Temp_15 26.5625 ## 8 2025-11-11T13:21:07.616039Z Temp_14 22.9375 ## 9 2025-11-11T13:21:10.101543Z Temp_13 26.75 ## 10 2025-11-11T13:21:10.911298Z Temp_12 23.5 |
Now we can change the column names (they are stored in
data_raw %>% pluck("results", "series", 1, "columns", 1)) and convert the time column to POSIXct:
1 2 3 4 5 6 7 |
data_raw_df %>%
set_names(data_raw %>% pluck("results", "series", 1, "columns", 1)) %>%
mutate(
time = as.POSIXct(time, format = "%Y-%m-%dT%H:%M:%OSZ", tz = "UTC"),
value = as.numeric(value)
) %>%
str()
|
1 2 3 4 |
## 'data.frame': 44176 obs. of 3 variables: ## $ time : POSIXct, format: "2025-11-11 13:20:31" "2025-11-11 13:20:43" ... ## $ entity_id: chr "Temp_5" "Temp_11" "Temp_10" "Temp_8" ... ## $ value : num 25.9 30.6 48.8 28.2 28.2 ... |
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.