r - gather multiple columns in multiple key columns with tidyr

Question

Not sure if tidyr::gather can be used to take multiple columns and merge them in multiple key columns.

Similar questions have been asked but they all refer to gathering multiple columns in one key column.

I'm trying to gather 4 columns into 2 key and 2 value columns like in the following example:

Sample data:

df <- data.frame(
    subject = c("a", "b"),
    age1 = c(33, 35),
    age2 = c(43, 45),
    weight1 = c(90, 67),
    weight2 = c(70, 87)
)

  subject age1 age2 weight1 weight2
1       a   33   43      90      70
2       b   35   45      67      87

Desired result:

dfe <- data.frame(
    subject = c("a", "a", "b", "b"),
    age = c("age1", "age2", "age1", "age2"),
    age_values = c(33, 43, 35, 45),
    weight = c("weight1", "weight2", "weight1", "weight2"),
    weight_values = c(90, 70, 67, 87)
)

  subject  age age_values  weight weight_values
1       a age1         33 weight1            90
2       a age2         43 weight2            70
3       b age1         35 weight1            67
4       b age2         45 weight2            87

Possible duplicate of [Gather multiple sets of columns](https://stackoverflow.com/questions/25925556/gather-multiple-sets-of-columns) — camille, Nov 05 '18 at 01:52
Is there an update on this w.r.t. to accomplishing it with `pivot_longer()`, `pivot_wider()`? — Dunois, Jan 22 '20 at 21:27

Shree · Accepted Answer · 2018-11-05T01:02:19.387

Here's one way to do it -

df %>%
  gather(key = "age", value = "age_values", age1, age2) %>%
  gather(key = "weight", value = "weight_values", weight1, weight2) %>%
  filter(substring(age, 4) == substring(weight, 7))

  subject  age age_values  weight weight_values
1       a age1         33 weight1            90
2       b age1         35 weight1            67
3       a age2         43 weight2            70
4       b age2         45 weight2            87

score 2 · Answer 2 · answered Nov 05 '18 at 00:24

Here's one approach. The idea is to do the use gather, then split the resulting dataframe by variable (age and weight), do the mutate operations separately for each of the two dataframes, then merge the dataframes back together using subject and the variable number (1 or 2).

library(dplyr)
library(tidyr)
library(purrr)

df %>%
  gather(age1:weight2, key = key, value = value) %>%
  separate(key, sep = -1, into = c("var", "num")) %>%
  split(.$var) %>%
  map(~mutate(., !!.$var[1] := paste0(var, num), !!paste0(.$var[1], "_values") := value)) %>%
  map(~select(., -var, -value)) %>%
  Reduce(f = merge, x = .) %>%
  select(-num)

r - gather multiple columns in multiple key columns with tidyr

2 Answers2

Linked