R pivot longer multiple columns

Question

have already tried quite a lot and even chatgpt could not help me :D

The following case:

library(tidyr)

# create the output dataset
data <-  data.frame(
  id = 1:12,
  f51a_01 = c(1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3),
  f51a_02 = c(1, 2, 3, 6, 1, 2, 3, 4, 5, 6, 1, 6),
  f51b_01 = c(1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 3, 3),
  f51b_02 = c(1, 2, 2, 6, 6, 6, 6, 6, 6, 6, 1, 6)
)

expected output:

id  var var_01  var_02
1   f51a    1   1
1   f51b    1   1
2   f51a    1   2
2   f51b    1   2
3   f51a    1   3
3   f51b    2   2
4   f51a    1   6
4   f51b    1   6
5   f51a    2   1
5   f51b    1   6
6   f51a    2   2
6   f51b    1   6
7   f51a    2   3
7   f51b    1   6
8   f51a    2   4
8   f51b    1   6
9   f51a    2   5
9   f51b    1   6
10  f51a    2   6
10  f51b    1   6
11  f51a    3   1
11  f51b    3   1
12  f51a    3   6
12  f51b    3   6

My solution so far works, but is not optimal.

data%>%
  select(id, matches("f51[a-z]_01"))%>%
  pivot_longer(cols = -id, names_to = "var", values_to = "var_01")%>%
  mutate(var = str_extract(var, "f51[a-z]"))%>%
  left_join(data2%>%
              select(id, matches("f51[a-z]_02"))%>%
              pivot_longer(cols = -id, names_to = "var", values_to = "var_02")%>%
              mutate(var = str_extract(var, "f51[a-z]")))

Glad about every hint. Thanks

Your data is not reproducible. Check your "data" object – Maël Jun 01 '23 at 08:59 — Maël, Jun 01 '23 at 08:59

score 0 · Accepted Answer · answered Jun 01 '23 at 09:21

When you want to simultaneously put multiple variables in long format, like here, you should combine names_to and names_pattern or names_sep. In names_to, where you define new column names, .value refers to:

".value" indicates that the corresponding component of the column name defines the name of the output column containing the cell values, overriding values_to entirely.

pivot_longer(data, -id, names_sep = "_", names_to = c("var", ".value"))

#       id var    `01`  `02`
#  1     1 f51a      1     1
#  2     1 f51b      1     1
#  3     2 f51a      1     2
#  4     2 f51b      1     2
#  5     3 f51a      1     3
#  6     3 f51b      2     2
#  7     4 f51a      1     6
#  8     4 f51b      1     6
#  9     5 f51a      2     1
# 10     5 f51b      1     6
# 11     6 f51a      2     2
# 12     6 f51b      1     6
# 13     7 f51a      2     3
# 14     7 f51b      1     6
# 15     8 f51a      2     4
# 16     8 f51b      1     6
# 17     9 f51a      2     5
# 18     9 f51b      1     6
# 19    10 f51a      2     6
# 20    10 f51b      1     6
# 21    11 f51a      3     1
# 22    11 f51b      3     1
# 23    12 f51a      3     6
# 24    12 f51b      3     6

works great, thank you very much. will try to understand this logic — Waschi Waschoi, Jun 01 '23 at 09:26

R pivot longer multiple columns

1 Answers1