Expand periods to regularly occuring timestamps

问题:

---

Had to modify the original post to include a better example

I have a tibble with time based data with start time, end time and a class variable of the following general form:

Code to make the table:

library(lubridate)
st <- c(ymd_hms("2016-01-01 00:35:00"),
        ymd_hms("2016-01-01 00:39:00"),
        ymd_hms("2016-01-01 00:54:00"),
        ymd_hms("2016-01-01 00:56:00"),
        ymd_hms("2016-01-01 00:57:00"))

en <- c(ymd_hms("2016-01-01 00:36:00"),
        ymd_hms("2016-01-01 00:45:00"),
        ymd_hms("2016-01-01 00:55:00"),
        ymd_hms("2016-01-01 00:57:00"),
        ymd_hms("2016-01-01 00:58:00"))

cl <- c("a","a","a","b","b")

df <- tibble(st,en,cl)

The periods are inconsistent, and there is a hidden class in the data: essentially, the time not explicitly listed in the data belongs to a third class in this example.

I need a way to expand this table to have regular periods (1-min) so that I can assign the missing class to those periods; the goal is to get to:

I am sure this can be done with dplyr and lubridate, but have ot been able to accomplish it. Keep in mind that my data set is huge, so preferably a non loopy approach would be great.

Thanks in advance,

MR

回答1:

Try this:

df_exp <- tibble(st = seq.POSIXt(from = min(st), to = max(st), by = "min"),
                 en = st + 60)
merge(df_exp, df, all = T)

First, create all start times. End time is just start time plus 1 minute. Merge with the data frame containing the class info. BTW: your start and end times do overlap, which might be an issue for some Tasks...

Edit to match your updated requirements:

library(tidyr)
library(dplyr)
df_exp <- tibble(st = seq.POSIXt(from = min(st), to = max(en), by = "min"), en = st + 60)

# with tidyr 0.8
df_n <- df %>% 
  rowwise() %>% 
  mutate(st = list(as.character(seq.POSIXt(from = st, to = en, by = "min"))[-length(seq.POSIXt(from = st, to = en, by = "min"))])) %>% 
  unnest() %>% 
  select(-en) %>% 
  mutate(st = as.POSIXct(st))

df_exp %>% left_join(df_n)

# with tidyr 0.8.1 (untested)
df_n <- df %>% 
  rowwise() %>% 
  mutate(st = list(seq.POSIXt(from = st, to = en, by = "min")[-length(seq.POSIXt(from = st, to = en, by = "min"))])) %>% 
  unnest() %>% 
  select(-en)

df_exp %>% left_join(df_n)

回答2:

Ok, I managed to get a solution, but it is a bit on the "loopy" side. I think Tino's answer is better. For what is worth, here is my answer:

##################################################
#Regular period DF covering the entire period in the initial data
df_regular <- tibble(st = seq(min(df$st),max(df$en)-59,60),
                     en = st + 59)
##################################################
#Creates variable with number of 1-min periods per row in initial data
df$periods <- as.integer((df$en-df$st + 1)/60)

##################################################
#Scan each row
listDates <- list()
listClass <- list()

k <- 1
for (i in 1:nrow(df)) {
  for(j in 1:df$periods[i]) {
    listDates[k] <- c(df$st[i]+(j-1)*60)
    listClass[k] <- c(df$cl[i])

    k <- k+1
  }
}

#################################################
#create output table
df_out <- tibble(st = unlist(listDates) %>% as_datetime(),
                 cl = unlist(listClass)) %>%
  right_join(df_regular[1],by=c("st" ="st")) %>%
  mutate(en = st + 59) %>%
  select(st,en,cl)
#################################################

Also incorporated Tino's suggestion about avoiding dates overlapping.

Cheers,

MR