R语言实现含重复项与计数的长表转宽表需求

原始数据 #

数据预览 #

V1 V2 V3 V4 V5
1  10  a  K  2 90
2  10  b  K  1 90
3  10  c  L  1 50
4  10  c  Q  1 70
5  10  d  Q  2 70
6  10  e  K  3 90
7  10  e  L  2 50
8  10  e  Q  4 70
9  20  f  K  1 75
10 20  g  K  2 75
11 20  g  Q  1 80
12 20  h  L  1 30

数据结构（dput输出） #

structure(list(V1 = c(10, 10, 10, 10, 10, 10, 10, 10, 20, 20, 
20, 20), V2 = structure(c(1L, 2L, 3L, 3L, 4L, 5L, 5L, 5L, 6L, 
7L, 7L, 8L), .Label = c("a", "b", "c", "d", "e", "f", "g", "h"
), class = "factor"), V3 = structure(c(1L, 1L, 2L, 3L, 3L, 1L, 
2L, 3L, 1L, 1L, 3L, 2L), .Label = c("K", "L", "Q"), class = "factor"), 
    V4 = c(2, 1, 1, 1, 2, 3, 2, 4, 1, 2, 1, 1), V5 = c(90, 90, 
    50, 70, 70, 90, 50, 70, 75, 75, 80, 30)), row.names = c(NA, 
-12L), class = "data.frame")

目标转换格式 #

10, a, 2, 0, 0, 90, 50, 70
10, b, 1, 0, 0, 90, 50, 70
10, c, 0, 1, 1, 90, 50, 70
10, d, 0, 0, 2, 90, 50, 70
10, e, 3, 2, 4, 90, 50, 70
20, f, 1, 0, 0, 75, 30, 80
20, g, 2, 0, 1, 75, 30, 80
20, h, 0, 1, 0, 75, 30, 80

注：示例中的K=等前缀仅作标注，实际转换可省略

转换规则 #

• V2列无重复值，作为新表的行标识
• 新表列数 = 2（V1、V2） + 2×V3唯一值数量（V4对应列 + V5对应列）
• V3唯一值对应的列填充匹配的V4值，无匹配则填0
• 新增的V5对应列，填充同V1分组下V3对应的唯一V5值

当前困境 #

已明确目标矩阵维度，但循环方法无法处理长度差异，按V1分组的尝试也未正确处理重复项，未找到合适的转换方法。

解决方案 #

方案一：仅使用R基础函数（高效原生实现） #

# 加载数据
df <- structure(list(V1 = c(10, 10, 10, 10, 10, 10, 10, 10, 20, 20, 20, 20), V2 = structure(c(1L, 2L, 3L, 3L, 4L, 5L, 5L, 5L, 6L, 7L, 7L, 8L), .Label = c("a", "b", "c", "d", "e", "f", "g", "h"), class = "factor"), V3 = structure(c(1L, 1L, 2L, 3L, 3L, 1L, 2L, 3L, 1L, 1L, 3L, 2L), .Label = c("K", "L", "Q"), class = "factor"), V4 = c(2, 1, 1, 1, 2, 3, 2, 4, 1, 2, 1, 1), V5 = c(90, 90, 50, 70, 70, 90, 50, 70, 75, 75, 80, 30)), row.names = c(NA, -12L), class = "data.frame")

# 1. 转换V4为宽表，缺失值填0
wide_v4 <- reshape(df, idvar = c("V1", "V2"), timevar = "V3", direction = "wide", v.names = "V4")
wide_v4[is.na(wide_v4)] <- 0
colnames(wide_v4) <- gsub("V4\\.", "", colnames(wide_v4))

# 2. 提取V1分组下V3对应的唯一V5值，转换为宽表
v5_map <- unique(df[, c("V1", "V3", "V5")])
wide_v5 <- reshape(v5_map, idvar = "V1", timevar = "V3", direction = "wide", v.names = "V5")
# 重命名V5对应列为P1/P2/P3（按V3顺序）
v3_unique <- unique(df$V3)
colnames(wide_v5) <- paste0("P", match(gsub("V5\\.", "", colnames(wide_v5)), v3_unique))

# 3. 合并并调整列顺序
result <- merge(wide_v4, wide_v5, by = "V1")
col_order <- c("V1", "V2", as.character(v3_unique), paste0("P", 1:length(v3_unique)))
result <- result[, col_order]

# 4. 输出为目标格式的文本
write.table(result, sep = ",", row.names = FALSE, col.names = FALSE)

方案二：tidyverse优雅实现（代码简洁易读） #

library(dplyr)
library(tidyr)

# 加载数据
df <- structure(list(V1 = c(10, 10, 10, 10, 10, 10, 10, 10, 20, 20, 20, 20), V2 = structure(c(1L, 2L, 3L, 3L, 4L, 5L, 5L, 5L, 6L, 7L, 7L, 8L), .Label = c("a", "b", "c", "d", "e", "f", "g", "h"), class = "factor"), V3 = structure(c(1L, 1L, 2L, 3L, 3L, 1L, 2L, 3L, 1L, 1L, 3L, 2L), .Label = c("K", "L", "Q"), class = "factor"), V4 = c(2, 1, 1, 1, 2, 3, 2, 4, 1, 2, 1, 1), V5 = c(90, 90, 50, 70, 70, 90, 50, 70, 75, 75, 80, 30)), row.names = c(NA, -12L), class = "data.frame")

# 1. 处理V4宽表，缺失值填充0
wide_v4 <- df %>%
  select(V1, V2, V3, V4) %>%
  pivot_wider(names_from = V3, values_from = V4, values_fill = 0)

# 2. 生成V1分组下的V5映射表
v5_df <- df %>%
  distinct(V1, V3, V5) %>%
  mutate(P_col = paste0("P", match(V3, unique(df$V3)))) %>%
  pivot_wider(names_from = P_col, values_from = V5)

# 3. 合并并调整列顺序
final_result <- wide_v4 %>%
  left_join(v5_df, by = "V1") %>%
  select(V1, V2, all_of(unique(df$V3)), starts_with("P"))

# 4. 输出目标格式
write_delim(final_result, delim = ",", col_names = FALSE)

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