R: Spread Time Series Data Based on Event Time

user_10198988 0
Viewed 92

I have a large time series dataset that currently iterates through the data to change the time series data into events divided by time interval. I am looking for something more slick than iterating through, because this gets pretty slow with how large my data is. My starting dataframe looks similar to this simple one:

structure(list(Name = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L), .Label = c("a", 
"b", "c"), class = "factor"), datetime = structure(c(1597203000, 
1597201200, 1597199400, 1597186800, 1597185000, 1597183200, 1597197600, 
1597195800, 1597194000, 1597181400, 1597179600, 1597177800, 1597192200, 
1597190400, 1597188600, 1597176000, 1597174200, 1597172400), class = c("POSIXct", 
"POSIXt"), tzone = ""), percent = c(0, 0, 2, 1, 0, 0, 0, 0, 3, 
4, 0, 0, 0, 0, 0, 5, 0, 0)), class = "data.frame", row.names = c(NA, 
-18L))

The data is half-hourly, so if a Name variable has two consecutive half hourly datetime values, I consider it to be a part of the event. I would also give some leniency, so if the data doesn't show consecutive half hourly values, but there are consecutive hour values, that would work as well. So the goal is to return a dataframe that looks like so:

structure(list(Name = structure(c(1L, 2L, 3L, 1L, 2L, 3L), .Label = c("a", 
"b", "c"), class = "factor"), startdate = structure(c(1597203000, 
1597197600, 1597192200, 1597186800, 1597181400, 1597176000), class = c("POSIXct", 
"POSIXt"), tzone = ""), enddate = structure(c(1597199400, 1597194000, 
1597188600, 1597183200, 1597177800, 1597172400), class = c("POSIXct", 
"POSIXt"), tzone = "")), class = "data.frame", row.names = c(NA, 
-6L))

Thanks in advance for any snazzy solutions, I greatly appreciate it!

EDIT: The datetime values will not necessarily be in order going down the list.

time-series r
2 Answers

I'm not sure what your looping looks like, but if you use the following code you can put the looping off until late making things run possibly a little faster at least.

df= with(df, df[order(Name, datetime),]) %>% 
         mutate(dftime = difftime(lead(datetime),datetime, units = "mins")) %>%
         mutate(eventnum = 0)

i = 1
j = 1
for(i in 1:length(df$eventnum)){
  if(df$dftime[i] <= 60){          # accounting for your consecutive hours comment
    df$eventnum[i] = j
  } else{df$eventnum[i] = j
         j = j + 1}
  i = i + 1
}

Then you could use a summarise sort of set up like akrun's answer he shared here like the following:

df_lengths = df %>% group_by(eventnum, Name) %>% 
                     summarise(startdate = first(datetime), enddate = last(datetime)) %>% 
                     ungroup %>% select(-eventnum)

But this is only a better answer assuming that you do the looping earlier in the data organization, for example if you loop through the time difference calculation as well as the interval checks.

user_103871160

Create a grouping variable with rleid (from data.table) on the 'Name' column, then summarise the 'datetime' column by returning the first and last elements in two columns

library(data.table)
library(dplyr)
df1 %>%
   group_by(grp = rleid(Name), Name) %>% 
   summarise(startdate = first(datetime), enddate = last(datetime)) %>%
   ungroup %>%
   select(-grp)
# A tibble: 6 x 3
#  Name  startdate           enddate            
#  <fct> <dttm>              <dttm>             
#1 a     2020-08-11 22:30:00 2020-08-11 21:30:00
#2 b     2020-08-11 21:00:00 2020-08-11 20:00:00
#3 c     2020-08-11 19:30:00 2020-08-11 18:30:00
#4 a     2020-08-11 18:00:00 2020-08-11 17:00:00
#5 b     2020-08-11 16:30:00 2020-08-11 15:30:00
#6 c     2020-08-11 15:00:00 2020-08-11 14:00:00
user_37322710
Related