I'm having trouble using the pivot_longer
on blocks of variables. Suppose I have this:
and I want this:
dfwide <- structure(list(date = structure(c(1577836800, 1577923200, 1578009600,
1578096000, 1578182400, 1578268800), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), x1_a = c(20, 15, 12, NA, 25, 27), x1_b = c(33,
44, 85, 10, 12, 3), x1_c = c(70, 20, 87, 11, 20, 5), x2_a = c(85,
65, 33, 46, 82, 9), x2_b = c(87, 25, 55, 64, 98, 5), x2_c = c(77,
51, 92, 20, 37, 98)), row.names = c(NA, -6L), class = c("tbl_df",
"tbl", "data.frame"))
##Tried:
dfwide %>%
pivot_longer(cols = -date,
names_sep = c("x1", "x2"),
names_to = c("a", "b", "c"),
values_to = "value")
This line is taking advantage of the name separation option of the pivot_longer
function.
pivot_longer(dfwide, -date, names_sep = "_",
names_to=c("which", ".value")) %>%
arrange(which)
# A tibble: 12 x 5
date which a b c
<dttm> <chr> <dbl> <dbl> <dbl>
1 2020-01-01 00:00:00 x1 20 33 70
2 2020-01-02 00:00:00 x1 15 44 20
3 2020-01-03 00:00:00 x1 12 85 87
4 2020-01-04 00:00:00 x1 NA 10 11
5 2020-01-05 00:00:00 x1 25 12 20
6 2020-01-06 00:00:00 x1 27 3 5
7 2020-01-01 00:00:00 x2 85 87 77
8 2020-01-02 00:00:00 x2 65 25 51
9 2020-01-03 00:00:00 x2 33 55 92
10 2020-01-04 00:00:00 x2 46 64 20
11 2020-01-05 00:00:00 x2 82 98 37
12 2020-01-06 00:00:00 x2 9 5 98