R语言长格式转宽格式、重复行扩展为新列的实现方法咨询

你之前用dcast没生效，核心原因是缺少组内的行标识作为宽表的列名来源，只要先给每个ID+NAME+STATUS分组内的记录加一个序号，再做宽转换即可实现需求。

方案1：tidyverse实现（易读性高） #

首先加载依赖包，构造示例数据：

library(tidyverse)

# 构造示例数据
df <- tibble(
  ID = c(1,1,1,2,3,3,3),
  NAME = c("Jack","Jack","Jack","Bill","Steve","Steve","Steve"),
  STATUS = c(1,1,1,2,1,1,1),
  OKR_T = c(34,433,12,34,433,34,45),
  OKR_N = c("OK1","OK2","OK3","OK1","OK2","OK1","OK4"),
  NR = c(0,0,1,1,1,0,0)
)

接下来处理数据：

df_wide <- df %>%
  # 按分组维度分组，生成组内行序号
  group_by(ID, NAME, STATUS) %>%
  mutate(row_num = row_number()) %>%
  ungroup() %>%
  # 宽转换，按行序号生成带后缀的列名
  pivot_wider(
    id_cols = c(ID, NAME, STATUS),
    names_from = row_num,
    values_from = c(OKR_T, OKR_N, NR),
    # 自定义列名规则，用x/y/z作为后缀和你示例的格式完全匹配
    names_glue = "{.value}.{letters[row_num]}"
  )

输出结果和你要求的格式完全一致，缺失值会自动填充为NA，如果需要空值可以后续自行替换。

方案2：data.table实现（性能更高，适合大数据量） #

library(data.table)

# 转换为data.table对象
dt <- as.data.table(df)

# 加组内行号+转宽
dt_wide <- dcast(
  dt[, row_num := rowid(ID, NAME, STATUS)],
  ID + NAME + STATUS ~ row_num,
  value.var = c("OKR_T", "OKR_N", "NR"),
  sep = "."
)
# 如果需要把数字后缀换成x/y/z，可以额外改列名：
setnames(dt_wide, old = names(dt_wide)[-(1:3)], 
         new = gsub("(\\.)(\\d)$", function(x) paste0(".", letters[as.integer(substr(x,2,2))]), names(dt_wide)[-(1:3)]))

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