如何在R中简化含JSON列的数据框展开及合并操作

我有一个包含数千条记录的数据框，其中X9列为JSON格式数据。需要将X1-X8列与X9列中提取的name（作为列名）和value（作为对应值）展开后的宽格式数据合并为单行。

此前尝试过以下代码，但未得到预期结果：

l <- fromJSON(data$X9, simplifyDataFrame = FALSE)
df <- do.call(cbind, l)

之后编写了一段步骤繁琐的代码实现需求，现寻求无需循环、更简洁的实现方法。

数据结构 #

data <- structure(list(X1 = 3079, X2 = 15112, X3 = 0, X4 = 0, X5 = NA,X6 = NA, X7 = 0, X8 = 0, X9 = "\n{\r\n\"1\":{\"name\":\"Program Type\",\"value\":\"Internship\",\"value_raw\":\"Internship\",\"id\":1,\"type\":\"select\"},\r\n\"3\":{\"name\":\"Field Of Study\",\"value\":\"Medical Practitioner - Internship\",\"value_raw\":\"Medical Practitioner - Internship\",\"id\":3,\"type\":\"select\"},\r\n\"488\":{\"name\":\"Studied in South Africa?\",\"value\":\"Yes\",\"value_raw\":\"Yes\",\"id\":488,\"type\":\"select\"},\"732\":{\"name\":\"Facility Group 8 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":732,\"type\":\"select\",\"visible\":false},\"731\":{\"name\":\"Facility Group 9 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":731,\"type\":\"select\",\"visible\":false},\"730\":{\"name\":\"Facility Group 10 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":730,\"type\":\"select\",\"visible\":false},\"729\":{\"name\":\"Facility Group 11 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":729,\"type\":\"select\",\"visible\":false},\"728\":{\"name\":\"Facility Group 12 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":728,\"type\":\"select\",\"visible\":false},\"727\":{\"name\":\"Facility Group 13 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":727,\"type\":\"select\",\"visible\":false},\"726\":{\"name\":\"Facility Group 14 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":726,\"type\":\"select\",\"visible\":false},\"725\":{\"name\":\"Facility Group 15 - WC\",\"value\":\"\",\"value_raw\":\"\",\"id\":725,\"type\":\"select\",\"visible\":false},\"868\":{\"name\":\"Declarations and Consent\",\"value\":\"I declare that the information entered in this application form and any attached\\/uploaded documents are valid and true\\nI consent\",\"id\":868,\"type\":\"checkbox\"}}",X10 = NA, X11 = structure(1696441392, class = c("POSIXct","POSIXt"), tzone = "UTC"), X12 = structure(1696441392, class = c("POSIXct","POSIXt"), tzone = "UTC"), X13 = NA, X14 = "Mozilla",X15 = "9-ae45-6"), row.names = c(NA,-1L), class = "data.frame")

之前的参考代码 #

data[2,] <- data[1,]
dt <- jsonlite::fromJSON(data$X9[1]) |> rbindlist(idcol="ID", fill=TRUE, use.names=TRUE)
dt <- dt[,2:3]
dt <- as.data.frame(t(dt))
colnames(dt) <-  dt[1, ]
dt = dt[-1, ]
data <- data[,c(1:8,10:15)]
final <- cbind(data,dt)

简洁实现方案 #

可以结合jsonlite、dplyr和tidyr的函数，批量处理所有记录，一步完成解析、转宽和合并：

library(jsonlite)
library(dplyr)
library(tidyr)
library(purrr)

# 批量解析JSON并转换为宽格式
json_wide_df <- map_dfr(data$X9, function(json_str) {
  # 解析JSON为列表，提取每个元素的name和value
  fromJSON(json_str) %>%
    map_dfr(~ tibble(name = .x$name, value = .x$value)) %>%
    # 转为宽格式，name作列名，value作对应值
    pivot_wider(names_from = name, values_from = value)
})

# 合并原数据（移除X9列）与解析后的宽格式数据
final_data <- bind_cols(data %>% select(-X9), json_wide_df)

或者用管道流简化写法：

final_data <- data %>%
  mutate(json_parsed = map(X9, ~ fromJSON(.) %>%
                             map_dfr(~ tibble(name = .x$name, value = .x$value)) %>%
                             pivot_wider(names_from = name, values_from = value))) %>%
  unnest(json_parsed) %>%
  select(-X9)

说明：

• map_dfr实现批量处理每条JSON记录，自动按行合并结果
• pivot_wider直接将长格式的name-value对转为宽格式列
• bind_cols/unnest确保原数据与解析后的列一一对应，无需手动处理行匹配

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