1

I am trying to extract the start and end time index separately for all the labels and store them separately.

EDIT

As suggested in the comment I prepared an example dataset

data <- rnorm(11)

dates1 <- as.POSIXct("2019-03-18 10:30:00", tz = "CET") + 0:6*60
dates2 <- as.POSIXct("2019-03-19 08:30:00", tz = "CET") + 0:3*60
dates <- append(dates1, dates2)

R <- xts(x = data, order.by = dates) 
colnames(R) <- "R"
R$Label[1:7] <- 1
R$Label[8:11] <- 2

Output:

                           R Label
2019-03-18 10:30:00  1.193363635     1
2019-03-18 10:31:00 -0.558021057     1
2019-03-18 10:32:00  0.670440862     1
2019-03-18 10:33:00  0.073794492     1
2019-03-18 10:34:00 -0.416108940     1
2019-03-18 10:35:00 -0.596981420     1
2019-03-18 10:36:00  0.002006772     1
2019-03-19 08:30:00 -1.245200719     2
2019-03-19 08:31:00  0.417944923     2
2019-03-19 08:32:00  1.699169683     2
2019-03-19 08:33:00  0.861448103     2

Class of R is xts, zoo.

Now I would like to store the start and end time index for label 1 and two separately. I have many more data with more labels, so it needs to be automated. I would really appreciate if you can help. Thank you

Community
  • 1
  • 1
Rel_Ai
  • 581
  • 2
  • 11
  • 1
    Looks like a simple group by with a min and max applied afterwards. If you post some data and an example of what you would like the outcome to be, it would be easier to help you. Please follow the guidelines here in https://stackoverflow.com/help/minimal-reproducible-example and see e.g. https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – coffeinjunky Dec 21 '19 at 15:16
  • Hi, I have just edited by providing a reproducible dataset. I tried with group_by as you mentioned but I was not able to take min or max of time indexes. – Rel_Ai Dec 21 '19 at 22:50

2 Answers2

0

If we split it into components and then use start and end on each component we can get the start and end times of each group.

s <- split(R, R$Label)
do.call("c", lapply(s, start)) # start of each group
do.call("c", lapply(s, end)) # end of each group

If you want the row numbers instead do the same thing but change the index to 1, 2, 3, ...

R2 <- zoo(coredata(R))
s <- split(R2, R2$Label)
do.call("c", lapply(s, start)) # start of each group
do.call("c", lapply(s, end)) # end of each group
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
0

Using the data you have posted:

library(xts)
library(dplyr)
library(tibble)
set.seed(42)

data <- rnorm(11)
dates1 <- as.POSIXct("2019-03-18 10:30:00", tz = "CET") + 0:6*60
dates2 <- as.POSIXct("2019-03-19 08:30:00", tz = "CET") + 0:3*60
dates <- append(dates1, dates2)

R <- xts(x = data, order.by = dates) 
colnames(R) <- "R"

R$Label <- 1        # note I have removed the indexing here
R$Label[8:11] <- 2

R %>% 
  as.data.frame() %>% 
  rownames_to_column() %>% 
  group_by(Label) %>% 
  summarise(min = min(rowname), max = max(rowname) )

# A tibble: 2 x 3
  Label min                 max                
  <dbl> <chr>               <chr>              
1     1 2019-03-18 09:30:00 2019-03-18 09:36:00
2     2 2019-03-19 07:30:00 2019-03-19 07:33:00
coffeinjunky
  • 11,254
  • 39
  • 57
  • This result seems to be very useful for my purpose. But when I am running this code, unlike your result, I am getting start as the start of label 1 and end as the end of label 2. 2019-03-18 10:30:00 2019-03-19 08:33:00 – Rel_Ai Dec 21 '19 at 23:27
  • Did you define the label column like I did? I needed to remove the subsetting from your code as it was not working to initialize the column. – coffeinjunky Dec 21 '19 at 23:32
  • Yeah, I copied the whole thing from you in order to avoid bringing any mistakes but same result. – Rel_Ai Dec 21 '19 at 23:35
  • I see. I can't reproduce your result - for me it shows as I have posted. My versions are `tibble_2.0.1 dplyr_0.8.0.1` from `sessionInfo()` if it helps. – coffeinjunky Dec 21 '19 at 23:39
  • In my case, the versions are dplyr_0.8.3 and tibble_2.1.3. But are these versions that critical to alter outputs? – Rel_Ai Dec 21 '19 at 23:45
  • Funny, even with your versions, I can't reproduce your error. Everything still works well for me. What is your full sessionInfo()? – coffeinjunky Dec 21 '19 at 23:52
  • I was using R from university server where your code is not working. But it's working fine in my installed Rstudio. That's super weird – Rel_Ai Dec 22 '19 at 00:11