Transform the dataframe to xts object

Question

I have a dataframe (time series) similar to this dummy data:

df <- data.frame(stringsAsFactors=FALSE,
      symbol = c("N2", "NJ", "K-Kl", "K-P3", "K-N", "KP+", "K13", "KS",
                 "KTotal", "P500", "P800", "P23", "P55", "PA", "PKA"),
        date = c("2017-10-12", "2017-10-12", "2017-10-12", "2017-10-12",
                 "2017-10-12", "2017-10-12", "2017-10-12", "2017-10-12",
                 "2017-10-12", "2017-10-12", "2017-10-12", "2017-10-12", "2017-10-12",
                 "2017-10-12", "2017-10-12"),
     open_pr = c(10.2, 2.7, 0.5, 4.5, 2.9, 8.1, 2.3, 1, 43.2, 28.5, 5.8, 6.7,
                 5.7, 0.1, 10),
       gross = c(460L, 121L, 21L, 203L, 130L, 363L, 102L, 45L, 1946L, 1282L,
                 262L, 303L, 256L, 6L, 449L),
     avg_aud = c(19L, 3L, 0L, 5L, 5L, 21L, 4L, 1L, 153L, 92L, 10L, 14L, 6L, 0L,
                 27L),
          ts = c(59L, 32L, 31L, 34L, 57L, 83L, 59L, 28L, 113L, 103L, 53L, 69L,
                 33L, 4L, 87L),
          tv = c(6L, 1L, 0L, 2L, 2L, 7L, 1L, 0L, 49L, 29L, 3L, 5L, 2L, 0L, 9L)
)

head(df)

   symbol       date open_pr gross avg_aud  ts tv
1      N2 2017-10-12    10.2   460      19  59  6
2      NJ 2017-10-12     2.7   121       3  32  1
3    K-Kl 2017-10-12     0.5    21       0  31  0
4    K-P3 2017-10-12     4.5   203       5  34  2
5     K-N 2017-10-12     2.9   130       5  57  2

my snippet

df %>% 
  as.tbl() %>% 
  mutate(date = ymd(date)) %>% 
  as.xts(date_col = date)

error message

Error in as.POSIXlt.character(x, tz, ...) : 
  character string is not in a standard unambiguous format

I would like to convert this dataframe into xts object. something similar to stock market data

library(quamtmod)
x <- getSymbols("GOOG", auto.assign = FALSE)

result :

           GOOG.Open GOOG.High  GOOG.Low GOOG.Close GOOG.Volume GOOG.Adjusted
2007-01-03  231.4944  236.7899  229.0652   232.2842    15513200      232.2842
2007-01-04  232.9847  240.4114  232.6618   240.0686    15877700      240.0686
2007-01-05  239.6910  242.1749  237.5102   242.0209    13833500      242.0209
2007-01-08  242.2693  243.3522  239.5420   240.2276     9570600      240.2276
2007-01-09  241.1565  242.5475  239.0452   241.1814    10832700      241.1814

If you read the for `as.xts` it accepts `timeSeries`, `ts`, `matrix`, `data.frame`, and `zoo` objects, so your `mutate` on the `date` column won't work. — EJJ, Oct 18 '18 at 13:45
See [this](https://stackoverflow.com/questions/4297231/converting-a-data-frame-to-xts) — EJJ, Oct 18 '18 at 13:47

score 6 · Accepted Answer · answered Oct 18 '18 at 17:42

The code below will give you what you want with dplyr and piping. I'm not sure why everything needs to be done with piping as not every function is built for magrittr pipes. For as.xts you need to reference to the date column with .$ if you want to use piping.

But the outcome will not be useful. xts transforms the data in a matrix and since Symbol and date are in the matrix the whole matrix will be a character matrix.

library(xts)
library(dplyr)

df %>% 
  mutate(date = as.Date(date)) %>% 
  as.xts(order.by = .$date)

           symbol   date         open_pr gross  avg_aud ts    tv  
2017-10-12 "N2"     "2017-10-12" "10.2"  " 460" " 19"   " 59" " 6"
2017-10-12 "NJ"     "2017-10-12" " 2.7"  " 121" "  3"   " 32" " 1"
2017-10-12 "K-Kl"   "2017-10-12" " 0.5"  "  21" "  0"   " 31" " 0"
2017-10-12 "K-P3"   "2017-10-12" " 4.5"  " 203" "  5"   " 34" " 2"
2017-10-12 "K-N"    "2017-10-12" " 2.9"  " 130" "  5"   " 57" " 2"
2017-10-12 "KP+"    "2017-10-12" " 8.1"  " 363" " 21"   " 83" " 7"
2017-10-12 "K13"    "2017-10-12" " 2.3"  " 102" "  4"   " 59" " 1"
2017-10-12 "KS"     "2017-10-12" " 1.0"  "  45" "  1"   " 28" " 0"
2017-10-12 "KTotal" "2017-10-12" "43.2"  "1946" "153"   "113" "49"
2017-10-12 "P500"   "2017-10-12" "28.5"  "1282" " 92"   "103" "29"
2017-10-12 "P800"   "2017-10-12" " 5.8"  " 262" " 10"   " 53" " 3"
2017-10-12 "P23"    "2017-10-12" " 6.7"  " 303" " 14"   " 69" " 5"
2017-10-12 "P55"    "2017-10-12" " 5.7"  " 256" "  6"   " 33" " 2"
2017-10-12 "PA"     "2017-10-12" " 0.1"  "   6" "  0"   "  4" " 0"
2017-10-12 "PKA"    "2017-10-12" "10.0"  " 449" " 27"   " 87" " 9"

But if you want something like your example on the bottom with google, use something like below.

Step 1 is to create a function to create xts timeseries with the symbol in front of the column names. Step 2 split your original data by symbol and create a list to contain all the data in a named list. Step 3 is to use Map to apply the function to the data. After this you can access all the data in the my_data list.

my_func <- function(x, symbol){
  index <- as.Date(x[["date"]])
  x <- x[, setdiff(colnames(x), c("symbol", "date"))]
  x <- xts::as.xts(x, order.by = index)
  colnames(x) <- paste0(symbol, ".", colnames(x))
  return(x)
}

my_data <- split(df, df$symbol)

my_data <- Map(my_func, my_data, names(my_data))

head(my_data, 2)
$`K-Kl`
           K-Kl.open_pr K-Kl.gross K-Kl.avg_aud K-Kl.ts K-Kl.tv
2017-10-12          0.5         21            0      31       0

$`K-N`
           K-N.open_pr K-N.gross K-N.avg_aud K-N.ts K-N.tv
2017-10-12         2.9       130           5     57      2

Is there any way to have a dataframe instead of list at the end, I tried to replace Map with purrr::map_df but couldnot make it — Ann, Oct 18 '18 at 19:37
How would you envision the data.frame? Every xts object in the list has different column names. Do you want all those columns in one data.frame? — phiver, Oct 19 '18 at 09:30
you are right. I think he needs xts object. Your solution looks fine but, if he wants to use highchart to visualize his data for example(interactive chart), highchart is not supporting list. I had the same issue while ago actually. — Ann, Oct 19 '18 at 17:19
Combining the data from the list can be done by `Reduce(merge, my_data)` This merges all xts objects into one, keeps the dates aligned and has all the columns. Next you can always turn this into a data.frame if needed. — phiver, Oct 19 '18 at 17:52

score 2 · Answer 2 · answered Sep 12 '20 at 09:55

2

You can simply code the following

x <- xts(df[,c(1,3:7)],df$date)

Worked for me

answered Sep 12 '20 at 09:55

BatmanFan

105
5

user113156 · Answer 3 · 2018-10-18T15:15:51.527

0

Will this work?

library(lubridate)
df$date <- date(df$date)
library(timetk)
df <- tk_xts(df, date_col = date)

Keeping the pipe function:

library(lubridate)
dat <- df %>% 
  as.tbl() %>% 
  mutate(date = date(date)) %>% 
  tk_xts(date_col = date)

edited Oct 18 '18 at 15:15

answered Oct 18 '18 at 15:07

user113156

6,761
5
35
81

1

using `tk_xts` like this will also drop the symbol column. There will be no way to recognize which data belongs to which symbol. – phiver Oct 18 '18 at 17:15

Transform the dataframe to xts object

3 Answers3