In dplyr, how to use a value in a column to specify another column to index

Question

I have data in the form of a list of objects (settings from our analysis machine). Some objects in the list are times, stored as a list containing hours, minutes and seconds.

I then have a 'directory', which contains the names of the objects in the data list.

I would like to add a column to the 'directory', and when the data type is 'time' I'd like it to insert the time in hh:mm:ss.

Here is an example of the data table:

data.times <- list(RUNT.1 = list(hour = 17L, minute = 48L, second = 56L, hsecond = 0L), 
    RUNT.2 = list(hour = 19L, minute = 0L, second = 47L, hsecond = 0L), 
    RUNT.3 = list(hour = 18L, minute = 19L, second = 8L, hsecond = 0L), 
    RUNT.4 = list(hour = 19L, minute = 0L, second = 48L, hsecond = 0L))

Here is an example of the 'directory' table

directory.times <- structure(list(data_label = c("RUNT.1", "RUNT.2", "RUNT.3", "RUNT.4"
), abif_type = c("time", "time", "time", "time"), description = c("Run start time", 
"Run stop time", "Data Collection start time", "Data Collection stop time"
), value = list(RUNT.1 = list(hour = 17L, minute = 48L, second = 56L, 
    hsecond = 0L), RUNT.2 = list(hour = 19L, minute = 0L, second = 47L, 
    hsecond = 0L), RUNT.3 = list(hour = 18L, minute = 19L, second = 8L, 
    hsecond = 0L), RUNT.4 = list(hour = 19L, minute = 0L, second = 48L, 
    hsecond = 0L))), row.names = c(NA, -4L), class = "data.frame")

If I wanted to pull out one specific time, I could easily do this:

library(lubridate)
  hms(paste(data.times[["RUNT.1"]][["hour"]],
            data.times[["RUNT.1"]][["minute"]],
            data.times[["RUNT.1"]][["second"]],
            sep=":"))

But I want to do this using dplyr, but it keeps erroring:

time.entry <- directory.times %>%
    mutate(time = case_when(abif_type == "time" ~
                              hms(paste(data.times[[paste0(data_label)]]["hour"],
                                        data.times[[paste0(data_label)]]["minute"],
                                        data.times[[paste0(data_label)]]["second"],
                                        sep="-"))))

The error is:

no such index at level 2

score 0 · Accepted Answer · answered Sep 12 '22 at 15:01

I think it may all get much easier if we work with a data.frame and convert the time elements to hms beforehand. The example data can be transformed into data.frame without any loss, not so sure with the actual data.

library(dplyr)
library(tidyr)
libary(purrr)
library(lubridate)

data.times <- data.times %>%
              map_dfr(~.x) %>%
              mutate(index = names(data.times), .before = everything()) %>%
              unite(col = time, c(hour, minute, second), sep = ':') %>% 
              mutate(time = hms(time))
data.times

# A tibble: 4 x 3
  index  time        hsecond
  <chr>  <Period>      <int>
1 RUNT.1 17H 48M 56S       0
2 RUNT.2 19H 0M 47S        0
3 RUNT.3 18H 19M 8S        0
4 RUNT.4 19H 0M 48S        0

I think you may pick it up from here

In dplyr, how to use a value in a column to specify another column to index

1 Answers1