0

I have a large data frame with dates and data. In every odd column I have the dates and every second column the corresponding stock price. I would like to convert every column pair to a separate xts object. The data frame looks like this:

...1        Stock Price
2021-11-09    72.5
2021-11-10    73.4
2021-11-15    72.9
etc           etc

when I call the following line it works fine: xts(df[,2],order.by=df$...1) However I want to use it in a loop so I rather use xts(df[,2],order.by=df[,1]), but this leads to an error. order.by requires an appropriate time-based object. When I try this : xts(df[,2],order.by=as.Date(df[,1])) it pops up do not know how to convert 'x' to class “Date”. I do not understand as originaly the first column is in posixct format. Could someone suggest me a solution? Thank you

Tamás F
  • 45
  • 4
  • Your last line of code should work, unless df[, 1] is not a Posixct column. Check that first. Otherwise add a bit of your data to the question. For a ample use `dput(your_df[10, 1:6])`. This code will output the first 10 rows and 6 columns of your data.frame that you can copy paste into your question. – phiver Dec 01 '21 at 11:25
  • dput(al[10,1:4]) structure(list(...1 = structure(790214400, tzone = "UTC", class = c("POSIXct", "POSIXt")), `BF/B UN Equity` = 1.7299, ...3 = structure(790214400, tzone = "UTC", class = c("POSIXct", "POSIXt")), `UN UN Equity` = 4.3911), row.names = c(NA, -1L), class = c("tbl_df", "tbl", "data.frame")) – Tamás F Dec 01 '21 at 11:31
  • yes df[,1] is a Posixct column – Tamás F Dec 01 '21 at 11:32

1 Answers1

1

In an lapply you may add 0 and 2 to the index, to loop over both initial data. Gives a list with the two time series.

lapply(c(0, 2), \(i) xts::xts(df[, 2 + i], order.by=df[, 1 + i]))
# [[1]]
# [,1]
# 1995-01-16 1.7299
# 1995-01-16 1.7299
# 1995-01-16 1.7299
# 
# [[2]]
# [,1]
# 1995-01-16 4.3911
# 1995-01-16 4.3911
# 1995-01-16 4.3911

Data

df <- structure(list(...1 = structure(c(790214400, 790214400, 790214400
), tzone = "UTC", class = c("POSIXct", "POSIXt")), `BF/B UN Equity` = c(1.7299, 
1.7299, 1.7299), ...3 = structure(c(790214400, 790214400, 790214400
), tzone = "UTC", class = c("POSIXct", "POSIXt")), `UN UN Equity` = c(4.3911, 
4.3911, 4.3911)), row.names = c(NA, -3L), class = c("tbl_df", 
"tbl", "data.frame"))
jay.sf
  • 60,139
  • 8
  • 53
  • 110