CODEWITHSUNDEEP
rpurrrxts
tomas hujo
tomas hujo

Reputation: 153

How to create a column containing nested xts object - yahoo data?

Hi I have data from yahoo in data frame for different stocks (columns symbol) . I want to create dataframe(tibble) with 1 row per stock with a column which would contain nested stock data as xts object. THe picture of result and reproducible example added. any help appreciated

the result I get

library(purrr)
library(tidyverse)
library(tidyr)




  df<-structure(list(symbol = c("AAPL", "AAPL", "AAPL", "AAPL", "AAPL", 
                                      "AMZN", "AMZN", "AMZN", "AMZN", "AMZN", "MSFT", "MSFT", "MSFT", 
                                      "MSFT", "MSFT"), date = structure(c(18295, 16700, 17571, 18305, 
                                                                          18086, 17834, 17696, 17438, 16850, 18016, 18376, 17935, 18085, 
                                                                          17626, 17724), class = "Date"), adjusted = c(76.636299, 26.198639, 
                                                                                                                       37.847511, 80.852463, 49.627182, 1530.420044, 1723.859985, 961.349976, 
                                                                                                                       534.900024, 1926.52002, 173.645462, 103.308533, 134.968887, 89.195686, 
                                                                                                                       101.034645)), row.names = c(NA, -15L), class = c("tbl_df", "tbl", 
                                                                                                                                                                        "data.frame"))

df%>%group_by(symbol)%>%
  nest()%>%
  mutate(xts_obj=map(data,~as.xts(order_by=.$date)))

Upvotes: 2

Views: 154

Answers (1)

akrun
akrun

Reputation: 887098

Here, we may need to remove the 'date' column by going into xts object

library(dplyr)
library(xts)
library(purrr)
ndf <- df %>% 
     group_by(symbol) %>%
     nest %>% 
     mutate(xts_obj = map(data, ~ xts(.x %>% select(-date), order.by = .x$date)))
ndf
# A tibble: 3 x 3
# Groups:   symbol [3]
#  symbol data             xts_obj      
#  <chr>  <list>           <list>       
#1 AAPL   <tibble [5 × 2]> <xts [5 × 1]>
#2 AMZN   <tibble [5 × 2]> <xts [5 × 1]>
#3 MSFT   <tibble [5 × 2]> <xts [5 × 1]>

Or make it more dynamic by selecting only numeric columns

df %>% 
    group_by(symbol) %>% 
    nest %>%
    mutate(xts_obj = map(data, ~ .x %>% 
                select(where(is.numeric)) %>% 
                xts(., order.by = .x$date)))

ndf$xts_obj[[1]]
#           adjusted
#2015-09-22 26.19864
#2018-02-09 37.84751
#2019-07-09 49.62718
#2020-02-03 76.63630
#2020-02-13 80.85246

Based on the OP's image, it is showing the xts_obj column as a list with 0 elements

ndf0$xts_obj[[1]]
#Data:
#numeric(0)

Upvotes: 2

Related Questions