How to create a tibble from list of tibbles with different number of rows?

user_11213712 0
Viewed 66

I have a list of tibbles that look like this:

> head(temp)
$AT
# A tibble: 8,784 × 2
   price_eur datetime           
       <dbl> <dttm>             
 1      50.9 2021-01-01 00:00:00
 2      48.2 2021-01-01 01:00:00
 3      44.7 2021-01-01 02:00:00
 4      42.9 2021-01-01 03:00:00
 5      40.4 2021-01-01 04:00:00
 6      40.2 2021-01-01 05:00:00
 7      39.6 2021-01-01 06:00:00
 8      40.1 2021-01-01 07:00:00
 9      41.3 2021-01-01 08:00:00
10      44.9 2021-01-01 09:00:00
# … with 8,774 more rows

$IE
# A tibble: 7,198 × 2
   price_eur datetime           
       <dbl> <dttm>             
 1      54.0 2021-01-01 01:00:00
 2      53   2021-01-01 02:00:00
 3      51.2 2021-01-01 03:00:00
 4      48.1 2021-01-01 04:00:00
 5      47.3 2021-01-01 05:00:00
 6      47.6 2021-01-01 06:00:00
 7      45.4 2021-01-01 07:00:00
 8      43.4 2021-01-01 08:00:00
 9      47.8 2021-01-01 09:00:00
10      51.8 2021-01-01 10:00:00
# … with 7,188 more rows

$`IT-Calabria`
# A tibble: 8,736 × 2
   price_eur datetime           
       <dbl> <dttm>             
 1      50.9 2021-01-01 00:00:00
 2      48.2 2021-01-01 01:00:00
 3      44.7 2021-01-01 02:00:00
 4      42.9 2021-01-01 03:00:00
 5      40.4 2021-01-01 04:00:00
 6      40.2 2021-01-01 05:00:00
 7      39.6 2021-01-01 06:00:00
 8      40.1 2021-01-01 07:00:00
 9      41.3 2021-01-01 08:00:00
10      41.7 2021-01-01 09:00:00
# … with 8,726 more rows

The number of rows is different because there are missing observations, usually one or several days.

Ideally I need a tibble with a single date time index and corresponding columns with NAs when there is missing data and I'm stuck here.

tibble r list
1 Answers

We can do a full join by 'datetime'

library(dplyr)
library(purrr)
reduce(temp, full_join, by = "datetime")

If we need to rename the column 'price_eur' before the join, loop over the list with imap, rename the 'price_eur' to the corresponding list name (.y) and do the join within reduce

imap(temp, ~ .x %>%
              rename(!! .y := price_eur)) %>%
          reduce(full_join, by = 'datetime')
user_37322710
Related