将多个列按多个键列进行聚合 - 使用tidyr的r函数

Question

不确定是否可以使用tidyr :: gather将多个列合并到多个关键列中。

类似的问题已经被问过，但它们都是指在一个关键列中收集多个列。

我正在尝试将4个列收集到2个关键列和2个值列中，就像以下示例中所示：

示例数据：

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

期望结果：

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

- adl

---

可能是Gather multiple sets of columns的重复问题。 - camille

关于使用 pivot_longer() 和 pivot_wider() 完成此操作，是否有更新？ - Dunois

Answer 1

以下是一种实现方式 -

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

- Shree

Answer 2

这里有一种方法。思路是使用gather，然后按变量（年龄和体重）split结果数据框，分别对两个数据框进行mutate操作，然后使用subject和变量号码（1或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)

- Weihuang Wong