在R语言中基于参考行重新排列数据集列的方法

问题背景 #

我有一个每行代表受访者的数据集，列对应提供给受访者的人口统计特征组合。每个受访者会看到两组特征组合（opt1和opt2），每组包含四个属性（A、B、C、D）。不同受访者的属性顺序不一致（例如受访者1的顺序为age/place/gender/income，受访者2为place/age/income/gender），这给数据重塑操作带来了困难。需要重新排列列，使每行的属性顺序与第一行保持一致。

初始数据集 #

id <- c(1, 2, 3, 4)
opt1.A <- c("age", "place", "income", "gender")
res1.A <- c(18, "paris", 70000, "male")
opt1.B <- c("place", "age", "gender", "income")
res1.B <- c("london", 14, "female", 80000)
opt1.C <- c("gender", "income", "place", "age")
res1.C <- c("male", 10000, "milan", 24)
opt1.D <- c("income", "gender", "age", "place")
res1.D <- c(20000, "male", 24, "newyork")
res2.A <- c(23, "london", 75000, "male")
res2.B <- c("newyork", 34, "male", 15000)
res2.C <- c("female", 100000, "paris", 39)
res2.D <- c(30000, "female", 45, "rome")

beginning <- data.frame(id, opt1.A, res1.A, opt1.B, 
                   res1.B, opt1.C, res1.C, 
                   opt1.D, res1.D, res2.A, 
                   res2.B, res2.C, res2.D, stringsAsFactors = FALSE)

beginning

目标数据集 #

id <- c(1, 2, 3, 4)
opt1.A <- c("age", "age", "age", "age")
res1.A <- c(18, 14, 24, 24)
opt1.B <- c("place", "place", "place", "place")
res1.B <- c("london", "paris", "milan", "newyork")
opt1.C <- c("gender", "gender", "gender", "gender")
res1.C <- c("male","male",  "female", "male")
opt1.D <- c("income", "income", "income", "income")
res1.D <- c(20000,10000, 70000, 80000)
res2.A <- c(23, 34, 45, 39)
res2.B <- c("london", "newyork", "paris", "rome")
res2.C <- c("female", "female", "male", "male")
res2.D <- c(30000, 100000, 75000, 15000)

end <- data.frame(id, opt1.A, res1.A, opt1.B, 
                   res1.B, opt1.C, res1.C, 
                   opt1.D, res1.D, res2.A, 
                   res2.B, res2.C, res2.D, stringsAsFactors = FALSE)

end

解决方案代码 #

# 加载初始数据集（如果已加载可跳过）
id <- c(1, 2, 3, 4)
opt1.A <- c("age", "place", "income", "gender")
res1.A <- c(18, "paris", 70000, "male")
opt1.B <- c("place", "age", "gender", "income")
res1.B <- c("london", 14, "female", 80000)
opt1.C <- c("gender", "income", "place", "age")
res1.C <- c("male", 10000, "milan", 24)
opt1.D <- c("income", "gender", "age", "place")
res1.D <- c(20000, "male", 24, "newyork")
res2.A <- c(23, "london", 75000, "male")
res2.B <- c("newyork", 34, "male", 15000)
res2.C <- c("female", 100000, "paris", 39)
res2.D <- c(30000, "female", 45, "rome")

beginning <- data.frame(id, opt1.A, res1.A, opt1.B, 
                   res1.B, opt1.C, res1.C, 
                   opt1.D, res1.D, res2.A, 
                   res2.B, res2.C, res2.D, stringsAsFactors = FALSE)

# 提取第一行的目标属性顺序
target_order <- beginning[1, paste0("opt1.", c("A", "B", "C", "D"))]
# 保存原始的opt1属性顺序，用于后续映射
original_opt1 <- beginning[, paste0("opt1.", c("A", "B", "C", "D"))]

# 统一所有行的opt1属性标签为目标顺序
beginning[, paste0("opt1.", c("A", "B", "C", "D"))] <- rep(list(target_order), nrow(beginning))

# 重新排列res1列的值，匹配目标属性顺序
for (i in 1:nrow(beginning)) {
  # 找到当前行原始opt1顺序到目标顺序的索引映射
  map_idx <- match(target_order, original_opt1[i, ])
  # 按映射重新排列res1的值
  beginning[i, paste0("res1.", c("A", "B", "C", "D"))] <- beginning[i, paste0("res1.", c("A", "B", "C", "D"))][map_idx]
}

# 重新排列res2列的值，逻辑与res1一致
for (i in 1:nrow(beginning)) {
  map_idx <- match(target_order, original_opt1[i, ])
  beginning[i, paste0("res2.", c("A", "B", "C", "D"))] <- beginning[i, paste0("res2.", c("A", "B", "C", "D"))][map_idx]
}

# 查看处理后的数据集
print(beginning)

代码说明 #

1. 提取目标顺序：以第一行的opt1属性顺序作为统一标准。
2. 保存原始顺序：避免修改opt1列后丢失原始的属性映射关系。
3. 统一opt1标签：将所有行的opt1属性标签替换为目标顺序，确保属性名称一致。
4. 重排结果值：通过match函数找到原始顺序与目标顺序的索引对应关系，以此重新排列res1和res2的结果值，保证每个列对应正确的属性。

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