如何将多个宽格式data.frame转换为单一长格式data.frame以获取目标输出？

数据准备 #

首先加载所需工具包并读取原始数据：

library(tidyverse)

# 原始输入数据
time1 = read.table(text = "
class id   order ac bc
1     1    s-c   1  2
", header = TRUE)

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

time1and2 = read.table(text = "
class id   order ex1S ex2S ex1C ex2C  k1   k2   t1  t2
1     1    s-c   8    5    6     1    400  600  30  50
", header = TRUE)

步骤1：处理time1/time2，转换为长格式 #

给两个时间点的数据添加time标识，合并后将ac/bc列拆分为DV（变量名）和score（对应值）：

# 合并time1和time2并转长格式
time_long = bind_rows(
  time1 %>% mutate(time = 1),
  time2 %>% mutate(time = 2)
) %>%
  pivot_longer(cols = c(ac, bc), names_to = "DV", values_to = "score")

步骤2：处理time1and2，提取分组参数并计算平均值 #

将k1/k2、t1/t2按time拆分，同时计算对应时间点的ex列平均值并格式化为需求字符串：

# 处理time1and2，拆分k、t列并计算ave_ex
time1and2_processed = time1and2 %>%
  pivot_longer(
    cols = starts_with(c("k", "t")),
    names_to = c(".value", "time"),
    names_pattern = "(k|t)(\\d)"
  ) %>%
  mutate(
    time = as.integer(time),
    # 计算对应time的ex平均值并格式化
    ave_ex = case_when(
      time == 1 ~ str_glue("({ex1S}+{ex2S})/2 ={(ex1S + ex2S)/2}"),
      time == 2 ~ str_glue("({ex1C}+{ex2C})/2 ={(ex1C + ex2C)/2}")
    )
  ) %>%
  select(class, id, order, time, k, t, ave_ex)

步骤3：合并数据集得到最终结果 #

通过共同分组列合并两个处理后的数据集，整理为目标结构：

# 合并得到最终输出
Desired_output = time_long %>%
  left_join(time1and2_processed, by = c("class", "id", "order", "time")) %>%
  select(class, id, order, time, DV, score, k, t, ave_ex)

# 查看结果
print(Desired_output)

最终输出 #

运行代码后得到与需求完全一致的结果：

class id order time DV score   k  t          ave_ex
1     1  1   s-c    1 ac     1 400 30 (8+5)/2 =6.5
2     1  1   s-c    1 bc     2 400 30 (8+5)/2 =6.5
3     1  1   s-c    2 ac     3 600 50 (6+1)/2 =3.5
4     1  1   s-c    2 bc     4 600 50 (6+1)/2 =3.5

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