Creating a dataframe by extracting information from a xts object

Question

Here is a reproducible example-

data <- rnorm(6)

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

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

Output:

2019-03-18 10:30:00  0.8303556     1
2019-03-18 10:31:00 -1.5585603     1
2019-03-18 10:32:00 -0.1266884     1
2019-03-19 08:30:00  0.3562468     2
2019-03-19 08:31:00  1.0219780     2
2019-03-19 08:32:00  2.5127290     2

I am trying to create dataframe where the expected result is as following:

Label| start timestamp    |end timestamp      | start R  | end R 
1    | 2019-03-18 10:30:00|2019-03-18 10:32:00| 0.8303556|-0.1266884
.
.

Can you kindly help? I can separately call these values but unfortunately not being able to put them altogether.

@ Ronak Shah, updated as requested. – Rel_Ai Dec 26 '19 at 09:57 — Rel_Ai, Dec 26 '19 at 09:57

score 1 · Accepted Answer · answered Dec 26 '19 at 08:22

1

For such operations it would be easier if you convert the data into dataframe and then perform the manipulation.

library(dplyr)
library(zoo)

R %>%
  fortify.zoo() %>%
  group_by(Label) %>%
  summarise_at(vars(Index, R), list(start = first, end = last))

answered Dec 26 '19 at 08:22

Ronak Shah

377,200
20
156
213

Although your solution is perfectly working with the data that I provided but it's showing an error and not functioning with the original data which has the same data structure. I have added an image of the data structure. Please note: xts attribute portion is different in these data sets for some reason. But I am not sure if it is the source of problem – Rel_Ai Dec 26 '19 at 09:39
@user11841472 Can you update your post with `dput(head(test))` ? – Ronak Shah Dec 26 '19 at 09:46
@ Ronak Shah added as requested. however it's quite long, so I just added the top and bottom portion. – Rel_Ai Dec 26 '19 at 09:53
1

@user11841472 As per the `dput` shared you do not have column with `R` in it. You need to change the column name. Try `test %>% fortify.zoo() %>% group_by(opNum) %>% summarise_at(vars(Index, R.factor), list(start = first, end = last))` – Ronak Shah Dec 26 '19 at 09:58
Hi, it's working perfectly. Apparently I made a blunder by confusing the column name. Thank you for the solution, it's perfect. – Rel_Ai Dec 26 '19 at 10:02

Creating a dataframe by extracting information from a xts object

1 Answers1