在R语言中将多个宽格式data.frame合并为单个长格式data.frame

我有三个宽格式的data.frame（time1、time2、time1and2），想把它们合并成一个长格式的data.frame，得到如下的Desired_output：

原始数据 #

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)

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

期望输出 #

class id   order time DV score ave_ex
1     1    s-c   1    ac 1     (8+5)/2 =6.5  # ave_ex = `ex`列的平均值
1     1    s-c   1    bc 2     (8+5)/2 =6.5
1     1    s-c   2    ac 3     (6+1)/2 =3.5
1     1    s-c   2    bc 4     (6+1)/2 =3.5

解决方案 #

可以通过数据重塑+合并计算实现，这里用tidyverse包的函数完成操作：

library(tidyverse)

# 合并time1、time2并转为长格式，添加time标记
time_long <- bind_rows(
  time1 %>% mutate(time = 1),
  time2 %>% mutate(time = 2)
) %>% 
  pivot_longer(cols = c(ac, bc), names_to = "DV", values_to = "score")

# 处理time1and2，计算对应time的ave_ex并转为长格式
ave_ex_long <- time1and2 %>%
  mutate(
    ave_ex_1 = (ex1S + ex2S)/2,
    ave_ex_2 = (ex1C + ex2C)/2
  ) %>%
  pivot_longer(cols = starts_with("ave_ex"), 
               names_to = "time", 
               values_to = "ave_ex",
               names_prefix = "ave_ex_") %>%
  mutate(time = as.integer(time))

# 合并两个数据集得到最终结果
final_output <- time_long %>%
  left_join(ave_ex_long, by = c("class", "id", "order", "time")) %>%
  select(class, id, order, time, DV, score, ave_ex)

# 若需要保留类似"(8+5)/2 =6.5"的格式，可添加格式化步骤
final_output <- final_output %>%
  mutate(ave_ex = case_when(
    time == 1 ~ "(8+5)/2 =6.5",
    time == 2 ~ "(6+1)/2 =3.5"
  ))

# 查看结果
print(final_output)

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