如何基于其他行的条件值为数据集创建计算差值的新列

前置说明 #

你当前提供的数据集实际为长结构，每个唯一组（SUBJECT、CLUSTER、SESSION、FEATURE）下对应不同处理组的响应值，我们可以通过宽转换或分组匹配的方式计算指定条件水平的配对差值。 #

方法1：tidyverse 套件实现（推荐） #

步骤1：构造原始数据集 #

library(tidyverse)

df <- structure(
  list(
    SUBJECT = c(
      "101","101","101",
      "102","102","102"
    ),
    CLUSTER = c(
      "G1","G1","G1",
      "G1","G1","G1"
    ),
    SESSION = c(
      "SessionA","SessionA","SessionA",
      "SessionA","SessionA","SessionA"
    ),
    TREATMENT = c(
      "TRT-A","TRT-B","CTRL",
      "TRT-A","TRT-B","CTRL"
    ),
    FEATURE = c(
      "FeatureA_Loc1","FeatureA_Loc1","FeatureA_Loc1",
      "FeatureA_Loc1","FeatureA_Loc1","FeatureA_Loc1"
    ),
    RESPONSE = c(
      5.2, 7.1, 3.8,
      6.4, 8.0, 4.5
    )
  ),
  row.names = c(NA, -6L),
  class = c("tbl_df", "tbl", "data.frame")
)

步骤2：转宽后计算差值 #

如果需要生成独立的差值数据集，可先转宽格式再新增差值列，可按需调整对比的条件水平：

diff_df <- df %>%
  # 固定分组维度，将处理项转为列、响应值作为列值
  pivot_wider(
    id_cols = c(SUBJECT, CLUSTER, SESSION, FEATURE),
    names_from = TREATMENT,
    values_from = RESPONSE
  ) %>%
  # 自定义计算任意两个水平的差值
  mutate(
    delta_TRT_A_vs_CTRL = `TRT-A` - CTRL,
    delta_TRT_B_vs_CTRL = `TRT-B` - CTRL,
    delta_TRT_B_vs_TRT_A = `TRT-B` - `TRT-A`
  )

方法2：Base R 实现 #

不需要加载第三方包的实现方式：

# 转宽格式
wide_df <- reshape(
  df,
  idvar = c("SUBJECT", "CLUSTER", "SESSION", "FEATURE"),
  timevar = "TREATMENT",
  direction = "wide"
)
# 新增差值列
wide_df$delta_TRT_A_vs_CTRL <- wide_df$RESPONSE.TRT.A - wide_df$RESPONSE.CTRL
wide_df$delta_TRT_B_vs_CTRL <- wide_df$RESPONSE.TRT.B - wide_df$RESPONSE.CTRL

长格式新增差值列方案 #

如果不需要转宽，要在原始长表基础上直接新增差值列，可直接分组匹配参考值计算：

df_with_diff <- df %>%
  group_by(SUBJECT, CLUSTER, SESSION, FEATURE) %>%
  mutate(
    # 以CTRL为参考值，可替换为你需要的基准条件水平
    ref_value = RESPONSE[TREATMENT == "CTRL"],
    delta_vs_ref = RESPONSE - ref_value
  ) %>%
  ungroup()

内容的提问来源于stack exchange，提问作者12666727b9