In R, I have a time series ts_big in long format as shown below, with observations of type A and B:
ts1<-tibble(dates=c("2023-01-01","2023-02-01","2023-03-01",
"2023-04-01"),
numbers_1=c(1.0, 2.8, 2.9, 2.0),
numbers_2=c(3.0, 5.0, 7.9, 0.9),
types=replicate(4, "A"))
ts2<-tibble(dates=c("2023-01-01","2023-02-01"),
numbers_1=c(0.3, 0.9),
numbers_2=c(3.0, 5.0),
types=replicate(2, "B"))
ts_big<-rbind(ts1, ts2)
Type A has observations at 4 times ("2023-01-01","2023-02-01","2023-03-01"), while type B has observations at only 2 of those same times ("2023-01-01","2023-02-01"). Is there a way to transform this tibble ts_big into a wide format with 5 columns: one single date column one column for type A numbers_1, one column for type A numbers_2, and one column for type B numbers_1, and one column for type B numbers_2 (perhaps with NA values in rows where type B doesn't have observation values for given dates)?
I understand this could be done manually by creating a bunch of vectors then compiling them into a tibble/dataframe but I was wondering if there was a function or simple process that could be used on much larger/complex examples than the small scale one I provided here.
Double-pivot:
library(dplyr) # mostly just for %>%, though it's very useful with tidyr
library(tidyr)
ts_big %>%
pivot_longer(cols = -c(dates, types)) %>%
pivot_wider(id_cols = dates, names_from = c(types, name), values_from = value)
# # A tibble: 4 × 5
# dates A_numbers_1 A_numbers_2 B_numbers_1 B_numbers_2
# <chr> <dbl> <dbl> <dbl> <dbl>
# 1 2023-01-01 1 3 0.3 3
# 2 2023-02-01 2.8 5 0.9 5
# 3 2023-03-01 2.9 7.9 NA NA
# 4 2023-04-01 2 0.9 NA NA