Convert list of data frames to time series

Question

I have the following list of data frames consisting of stock prices. Each data frame can potentially be of different length, and not all of the dates are in each data frame. I would like to convert the list to a time series with the first column being the date and each column being the price of a stock, with column names the name of the stock. If a price is missing for a given date, take the previous price.

$`0P00012DY5`<br/>
              last<br/>
2017-08-21 9.49504<br/>
2017-08-22 9.53553<br/>
2017-08-23 9.52643<br/>
2017-08-24 9.53486<br/>
2017-08-25 9.53390<br/>
2017-08-28 9.48788<br/>
2017-08-29 9.44059<br/>
2017-08-30 9.49893<br/>

$`0P0000KY8J`<br/>
               last<br/>
2017-08-21 11.58276<br/>
2017-08-22 11.58278<br/>
2017-08-23 11.58275<br/>
2017-08-24 11.58263<br/>
2017-08-25 11.58260<br/>
2017-08-29 11.58250<br/>
2017-08-30 11.58246<br/>
2017-08-31 11.58237<br/>

Desired output

date    0P00012DY5      0P0000KY8J<br/>
2017-08-21  9.49504         11.58276<br/>
2017-08-22  9.53553         11.58278<br/>
2017-08-23  9.52643         11.58275<br/>
2017-08-24  9.53486         11.58263<br/>
2017-08-25  9.53390         11.58260<br/>
2017-08-28  9.48788         11.58260<br/>
2017-08-29  9.44059         11.58250<br/>
2017-08-30  9.49893         11.58246<br/>
2017-08-31  9.49893         11.58237<br/>

Structure of the list:

mod_ll <- structure(list(`0P00012DY5` = structure(list(last = c(9.49504, 
9.53553, 9.52643, 9.53486, 9.5339, 9.48788, 9.44059, 9.49893, 
9.542, 9.56865, 9.5417, 9.54301, 9.52942, 9.54246, 9.54653, 9.60182, 
9.63157, 9.62566, 9.64084, 9.62746, 9.65814, 9.6672, 9.66387)), .Names = "last", row.names = c("2017-08-21", 
"2017-08-22", "2017-08-23", "2017-08-24", "2017-08-25", "2017-08-28", 
"2017-08-29", "2017-08-30", "2017-08-31", "2017-09-01", "2017-09-04", 
"2017-09-05", "2017-09-06", "2017-09-07", "2017-09-08", "2017-09-11", 
"2017-09-12", "2017-09-13", "2017-09-14", "2017-09-15", "2017-09-18", 
"2017-09-19", "2017-09-20"), class = "data.frame"), `0P0000KY8J` = structure(list(
    last = c(11.58276, 11.58278, 11.58275, 11.58263, 11.5826, 
    11.58254, 11.5825, 11.58246, 11.58237, 11.58231, 11.58223, 
    11.58219, 11.58218, 11.58206, 11.58202, 11.58197, 11.58193, 
    11.58189, 11.58178, 11.58174, 11.58173, 11.58166, 11.58163
    )), .Names = "last", row.names = c("2017-08-21", "2017-08-22", 
"2017-08-23", "2017-08-24", "2017-08-25", "2017-08-28", "2017-08-29", 
"2017-08-30", "2017-08-31", "2017-09-01", "2017-09-04", "2017-09-05", 
"2017-09-06", "2017-09-07", "2017-09-08", "2017-09-11", "2017-09-12", 
"2017-09-13", "2017-09-14", "2017-09-15", "2017-09-18", "2017-09-19", 
"2017-09-20"), class = "data.frame")), .Names = c("0P00012DY5", 
"0P0000KY8J"))

https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — AidanGawronski, Jul 14 '18 at 07:14

MKR · Accepted Answer · 2018-07-14T18:02:20.413

A tidyverse based solution can be to combine all your data.frames from list using dplyr::bind_rows. Then separate Date and Value in 2 columns and change data in wide format using tidyr::spread.

Finally, for missing dates, use tidyr::fill to populate last available values.

library(tidyverse)

bind_rows(ll, .id="Name") %>% 
  separate(last, c("Date", "Value"), sep=" ") %>%
  mutate(Date = as.Date(Date)) %>%
  spread(Name, Value) %>%
  fill(2:3)

#         Date 0P0000KY8J 0P00012DY5
# 1 2017-08-21   11.58276    9.49504
# 2 2017-08-22   11.58278    9.53553
# 3 2017-08-23   11.58275    9.52643
# 4 2017-08-24   11.58263    9.53486
# 5 2017-08-25   11.58260    9.53390
# 6 2017-08-28   11.58260    9.48788
# 7 2017-08-29   11.58250    9.44059
# 8 2017-08-30   11.58246    9.49893
# 9 2017-08-31   11.58237    9.49893

Edited: Updated to include answer for modified data provided by OP. The Date is provided as rownames of data.frames. We need to move Date to column before merging data frames. One can use rownames_to_column with lapply on list to move rownames to column.

library(tidyverse)
bind_rows(lapply(mod_ll,rownames_to_column, var="Date"), .id="Name") %>% 
  mutate(Date = as.Date(Date)) %>%
  spread(Name, last) %>%
  fill(2:3)

#         Date 0P0000KY8J 0P00012DY5
# 1  2017-08-21   11.58276    9.49504
# 2  2017-08-22   11.58278    9.53553
# 3  2017-08-23   11.58275    9.52643
# 4  2017-08-24   11.58263    9.53486
# 5  2017-08-25   11.58260    9.53390
# 6  2017-08-28   11.58254    9.48788
#
#....so on

Data:

df1 <- read.table(text =  
"last
'2017-08-21 9.49504'
'2017-08-22 9.53553'
'2017-08-23 9.52643'
'2017-08-24 9.53486'
'2017-08-25 9.53390'
'2017-08-28 9.48788'
'2017-08-29 9.44059'
'2017-08-30 9.49893'",
header = TRUE, stringsAsFactors = FALSE)


df2 <- read.table(text = 
"last
'2017-08-21 11.58276'
'2017-08-22 11.58278'
'2017-08-23 11.58275'
'2017-08-24 11.58263'
'2017-08-25 11.58260'
'2017-08-29 11.58250'
'2017-08-30 11.58246'
'2017-08-31 11.58237'",
header = TRUE, stringsAsFactors = FALSE)


ll <- list(df1, df2)

names(ll) <- c('0P00012DY5', '0P0000KY8J')

@marcl Fantastic. Please remove the comments from above chat as those will not help future users. — MKR, Jul 14 '18 at 18:05

Convert list of data frames to time series

1 Answers1