如何将多个宽格式data.frame合并为单个长格式data.frame并生成指定输出

需要将多个宽格式data.frame（time1、time2、time3、time4、time1and2、time3and4）合并为单个长格式data.frame，得到指定的期望输出。尝试了以下代码但未成功：

library(tidyverse)
rbind(cbind(time1, time = 1),cbind(time2, time = 2),
      cbind(time3, time = 3),cbind(time4, time = 4)) %>%
  pivot_longer(ac:bc,names_to = "DV", values_to = "score") %>%
  right_join(rbind(time1and2,time3and4))

数据集定义 #

time1 = read.table(text="
class id   order ac bc
1     1    s-c   1  2
", h=TRUE)

time2 = read.table(text="
class id   order ac bc
1     1    s-c   3  4
", h=TRUE)

time3 = read.table(text="
class id   order ac bc
1     1    s-c   5  6
", h=TRUE)

time4 = read.table(text="
class id   order ac bc
1     1    s-c   7  8
", h=TRUE)

time1and2 = read.table(text="
class id   order ex1S ex2S ex1C ex2C
1     1    s-c   8    5    6     1
", h=TRUE)

time3and4 = read.table(text="
class id   order ex1S ex2S ex1C ex2C
1     1    s-c   7    9    2     6
", h=TRUE)

期望输出 #

class id   order time DV score ave_ex
1     1    s-c   1    ac 1     6.5  # (8+5)/2
1     1    s-c   1    bc 2     6.5
1     1    s-c   2    ac 3     3.5  # (6+1)/2
1     1    s-c   2    bc 4     3.5
1     1    s-c   3    ac 5     8    # (7+9)/2
1     1    s-c   3    bc 6     8
1     1    s-c   4    ac 7     4    # (2+6)/2
1     1    s-c   4    bc 8     4

解决方案 #

可以通过分步骤处理两个类型的数据集，再合并得到结果：

library(tidyverse)

# 1. 处理time1-time4，转为长格式并标记时间
time_long <- bind_rows(
  mutate(time1, time = 1),
  mutate(time2, time = 2),
  mutate(time3, time = 3),
  mutate(time4, time = 4)
) %>%
  pivot_longer(cols = ac:bc, names_to = "DV", values_to = "score")

# 2. 处理time1and2/time3and4，计算每个时间对应的ex均值
ex_ave <- bind_rows(
  # 处理time1and2：对应time1和time2的均值
  time1and2 %>%
    mutate(
      time = list(1:2),
      ave_ex = list(c((ex1S + ex2S)/2, (ex1C + ex2C)/2))
    ) %>%
    unnest(c(time, ave_ex)),
  # 处理time3and4：对应time3和time4的均值
  time3and4 %>%
    mutate(
      time = list(3:4),
      ave_ex = list(c((ex1S + ex2S)/2, (ex1C + ex2C)/2))
    ) %>%
    unnest(c(time, ave_ex))
) %>%
  select(class, id, order, time, ave_ex)

# 3. 合并两个结果表
final_output <- time_long %>%
  left_join(ex_ave, by = c("class", "id", "order", "time"))

# 查看最终输出
print(final_output)

代码说明 #

• 第一步将time1-time4合并并添加时间标记，再转成长格式，得到每个时间点、每个指标（ac/bc）的分数。
• 第二步针对time1and2和time3and4，分别拆分出对应时间点，计算每组（S组/C组）的均值并匹配到对应时间。
• 最后通过left_join按共同字段（class、id、order、time）合并两个表，得到符合需求的长格式结果。

内容的提问来源于stack exchange，提问作者Simon Harmel