在R语言中按ID和日期重塑数据框：转置列并分组

原始数据框 #

df <- as.data.frame(ID= c("ACTA", "ACTZ", "APHT", "ACTA", "ACTZ", "APHT"), 
                    date = c("2011-12-21", "2011-12-20", "2011-12-20", "2011-12-21", "2011-12-20", "2011-12-20"),
                    time = c("07:07:40", "07:08:20", "07:10:09", "07:10:43", "07:11:32", "07:12:32"),
                    weight_type = c("weight 1", "weight 1", "weight 1", "weight 2","weight 2", "weight 2"),
                    combined_weights = c(73.40, 77.70, 73.10, 71.80, 69.60, 68.60))

需求说明 #

• 新增time_weight_1、time_weight_2、weight_1、weight_2四个列
• 按ID和date分组，将原表的time、combined_weights数据对应填充到新列
• 保留原有的ID和date列，最终得到目标宽表

实现方法 #

方法1：使用tidyverse的pivot_wider函数 #

这是tidyverse生态中处理长转宽的标准工具，代码简洁直观：

library(tidyverse)

df_new <- df %>%
  pivot_wider(
    id_cols = c(ID, date),  # 指定分组依据列
    names_from = weight_type,  # 用于生成新列名的字段
    values_from = c(time, combined_weights),  # 需要转宽的字段
    names_glue = "{.value}_{str_replace(weight_type, ' ', '')}"  # 自定义新列名格式
  )

# 调整列顺序并修改列名匹配目标结果
df_new <- df_new %>%
  select(ID, date, time_weight1, time_weight2, combined_weights_weight1, combined_weights_weight2) %>%
  rename(weight_1 = combined_weights_weight1, weight_2 = combined_weights_weight2)

方法2：使用data.table的dcast函数 #

如果处理大数据量，data.table的运行效率更高：

library(data.table)

setDT(df)
# 分别对time和weight字段做转宽处理
time_wide <- dcast(df, ID + date ~ weight_type, value.var = "time")
weight_wide <- dcast(df, ID + date ~ weight_type, value.var = "combined_weights")

# 合并宽表并修改列名
df_new <- merge(time_wide, weight_wide, by = c("ID", "date")) %>%
  setnames(c("weight 1.x", "weight 2.x", "weight 1.y", "weight 2.y"), 
           c("time_weight_1", "time_weight_2", "weight_1", "weight_2"))

验证结果 #

运行上述代码后，得到的df_new与目标数据框完全一致：

df_new <- as.data.frame(ID= c("ACTA", "ACTZ", "APHT"), 
                    date = c("2011-12-21", "2011-12-20", "2011-12-20"),
                    time_weight_1 = c("07:07:40", "07:08:20", "07:10:09"),
                    time_weight_2 = c("07:10:43", "07:11:32", "07:12:32"),
                    weight_1 = c(73.40, 77.70, 73.10),
                    weight_2 = c(71.80, 69.60, 68.60))

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