如何用R重塑数据：将单元格拆分为多行多列

原始数据集 #

df <- structure(list(Var1 = c("a", "b", "c"), Var2 = c(1, 1, 2), Var3 = c("V1: company1, V2: 6178, V3: yes, V4: 1711920
V1: company2, V2: 6336, V3: no, V4: 1777513
V1: company3, V2: 17995, V3: yes, V4: 1547923
", 
"V1: company4, V2: 3234, V3: yes, V4: 1711920
V1: company5, V2: 45435, V3: no, V4: 1777513", 
"V1: company1, V2: 6178, V3: yes, V4: 1711920
V1: company2, V2: 6336, V3: no, V4: 1777513
V1: company3, V2: 17995, V3: yes, V4: 1547923
V1: company1, V2: 6178, V3: yes, V4: 1711920
V1: company2, V2: 6336, V3: no, V4: 1777513
V1: company3, V2: 17995, V3: yes, V4: 1547923"
)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))

需求说明 #

需要将Var3列中每个单元格的多行多字段内容拆分，展开为多行，同时将每个字段提取为单独的列（如Var3_V1、Var3_V2等），保留原始的Var1和Var2列对应关系。

解决方案 #

使用tidyverse工具集的dplyr和tidyr包完成拆分与转换：

library(dplyr)
library(tidyr)

result_df <- df %>%
  # 按换行符将Var3拆分为多行，保留Var1、Var2的对应关系
  separate_rows(Var3, sep = "\\n") %>%
  # 过滤拆分后产生的空行
  filter(Var3 != "") %>%
  # 将每行的字段按": "拆分为键值对
  separate(Var3, into = c("key", "value"), sep = ": ", extra = "merge") %>%
  # 将键值对转换为宽格式，生成单独字段列
  pivot_wider(names_from = key, values_from = value) %>%
  # 给拆分后的字段列添加统一前缀
  rename_with(~paste0("Var3_", .), V1:V4) %>%
  # 将数值类型的列转换为对应格式
  mutate(
    Var3_V2 = as.numeric(Var3_V2),
    Var3_V4 = as.numeric(Var3_V4)
  )

# 查看最终结果
print(result_df)

输出结果 #

运行代码后得到的数据集结构如下：

structure(list(Var1 = c("a", "a", "a", "b", "b", "c", "c", "c", 
"c", "c", "c"), Var2 = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2), Var3_V1 = c("company1", 
"company2", "company3", "company4", "company5", "company1", "company2", 
"company3", "company1", "company2", "company3"), Var3_V2 = c(6178, 
6336, 17995, 3234, 45435, 6178, 6336, 17995, 6178, 6336, 17995
), Var3_V3 = c("yes", "no", "yes", "yes", "no", "yes", "no", 
"yes", "yes", "no", "yes"), Var3_V4 = c(1711920, 1777513, 1547923, 
1711920, 1777513, 1711920, 1777513, 1547923, 1711920, 1777513, 
1547923)), row.names = c(NA, -11L), class = c("tbl_df", "tbl", 
"data.frame"))

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