在R中按id分组计算各数值列after与before的差值

我有一个结构如下示例的数据框：

df <- data.frame(rbind(c("Sample1_x2", 10, 23, 6, 5, "Sample1", "after"),
            c("Sample2_x2", 8, 53, 22, 52, "Sample2", "after"),
            c("Sample1_x1", 12, 2, 44, 15, "Sample1", "before"),
            c("Sample3_x1", 27, 46, 16, 65, "Sample3", "before"),
            c("Sample2_x1", 41, 44, 27, 25, "Sample2", "before"),
            c("Sample3_x2", 5, 38, 9, 29, "Sample3", "after")))
colnames(df) <- c("name", "alpha", "beta", "gamma", "rho", "id", "group")
df <- tibble::column_to_rownames(df, var = "name")
df
           alpha beta gamma rho      id  group
Sample1_x2   10   23    6    5 Sample1  after
Sample2_x2    8   53   22   52 Sample2  after
Sample1_x1   12    2   44   15 Sample1 before
Sample3_x1   27   46   16   65 Sample3 before
Sample2_x1   41   44   27   25 Sample2 before
Sample3_x2    5   38    9   29 Sample3  after

我希望按id分组，对每个数值列计算after - before的差值，得到如下目标结果：

id   alpha  beta  gamma    rho       
Sample1      -2    21    -38    -10
Sample2     -33     9     -5     27    
Sample3     -22    -8     -7    -36

我尝试使用dplyr::group_by(id, group)，但在mutate()部分无法成功计算每个样本的差值，恳请提供帮助。

方法1：转宽格式后计算差值 #

先将group的before和after转为独立列，再直接做减法，逻辑直观清晰：

library(dplyr)
library(tidyr)

df %>%
  # 先把字符型数值列转为数值类型
  mutate(across(c(alpha, beta, gamma, rho), as.numeric)) %>%
  # 按id分组，将group转为列名，数值列作为对应值
  pivot_wider(names_from = group, values_from = c(alpha, beta, gamma, rho)) %>%
  # 计算after与before的差值
  mutate(
    alpha = alpha_after - alpha_before,
    beta = beta_after - beta_before,
    gamma = gamma_after - gamma_before,
    rho = rho_after - rho_before
  ) %>%
  # 保留目标列并设置id为行名
  select(id, alpha, beta, gamma, rho) %>%
  tibble::column_to_rownames(var = "id")

方法2：分组后直接汇总计算 #

无需转换格式，直接按id分组，在summarise中提取对应组的数值做减法：

library(dplyr)

df %>%
  mutate(across(c(alpha, beta, gamma, rho), as.numeric)) %>%
  group_by(id) %>%
  summarise(
    alpha = first(alpha[group == "after"]) - first(alpha[group == "before"]),
    beta = first(beta[group == "after"]) - first(beta[group == "before"]),
    gamma = first(gamma[group == "after"]) - first(gamma[group == "before"]),
    rho = first(rho[group == "after"]) - first(rho[group == "before"])
  ) %>%
  tibble::column_to_rownames(var = "id")

关键注意点 #

• 原数据框的数值列是字符类型，必须先转为数值类型才能进行算术运算，否则会得到错误的字符拼接结果。
• 两种方法均可得到目标结果，可根据个人习惯选择。

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