How to create a Matrix out of a list of timeseries

Question

i do have a list of timeseries and I'd like to create a matrix out of it. My first idea was to seperate the list with dplyr by "filter" and merge the list of list but i am not sure if this is really smart.

Would be great if you could give me an input how to tackle this issue!

Example
id <- c(1,1,1,1,1,2,2,2,2,2,2)
level <-  c(100,101,100.5,102,103,100,101,103,105,103,105)
date <- as.Date(c('2021-1-1','2021-1-2','2021-1-3','2021-1-5','2021-1-6','2021-1-1','2021-1-2','2021-1-3','2021-1-4','2021-1-5','2021-1-6'))


test <- data.frame(date,id,level)


        date id level
1  2021-01-01  1 100.0
2  2021-01-02  1 101.0
3  2021-01-03  1 100.5
4  2021-01-05  1 102.0
5  2021-01-06  1 103.0
6  2021-01-01  2 100.0
7  2021-01-02  2 101.0
8  2021-01-03  2 103.0
9  2021-01-04  2 105.0
10 2021-01-05  2 103.0
11 2021-01-06  2 105.0

And i would like to have the following

      date    1       2
  2021-01-01 100.0  100.0
  2021-01-02 101.0  101.0
  2021-01-03 100.5  103.0
  2021-01-04        105.0
  2021-01-05 102.0  103.0
  2021-01-06 103.0  105.0

Thank you very much!

Sandwichnick · Accepted Answer · 2021-06-15T10:30:54.617

A Solution using the tidyverse:

#load tidyverse
#library(tidyverse)

pivot_wider(test,
            names_sort = TRUE, # columns should be sortet by the numbers
            names_from=id,
            values_from = level,
) %>% 
arrange(date) # sort by date

# returns:
# A tibble: 6 x 3
  date         `1`   `2`
  <date>     <dbl> <dbl>
1 2021-01-01  100    100
2 2021-01-02  101    101
3 2021-01-03  100.   103
4 2021-01-04   NA    105
5 2021-01-05  102    103
6 2021-01-06  103    105

A solution using base R:

reshape(test,
        idvar="date",
        timevar = "id",
        direction = "wide"
)

# Solution is close to your desired output
        date level.1 level.2
1 2021-01-01   100.0     100
2 2021-01-02   101.0     101
3 2021-01-03   100.5     103
4 2021-01-05   102.0     103
5 2021-01-06   103.0     105
9 2021-01-04      NA     105

How to create a Matrix out of a list of timeseries

1 Answers1