R语言中将指定结构data.frame转换为分组嵌套JSON的实现方法

依赖包 #

需要用到tidyverse（数据整理）和jsonlite（JSON生成）两个工具包，提前安装加载即可：

# 安装包（首次使用执行）
install.packages(c("tidyverse", "jsonlite"))
# 加载包
library(tidyverse)
library(jsonlite)

完整转换代码 #

# 你提供的测试数据
test <- data.frame(ID = c('1','1','1','1','1','1','1','1','2','2','2','2','2','2','2','2',
                          '3','3','3','3','3','3','3','3','4','4','4','4','4','4','4','4',
                          '5','5','5','5','5','5','5','5','6','6','6','6','6','6','6','6'),
                   CAT = c('CAT1','CAT1','CAT1','CAT1','CAT2','CAT2','CAT2','CAT2',
                           'CAT1','CAT1','CAT1','CAT1','CAT2','CAT2','CAT2','CAT2',
                           'CAT1','CAT1','CAT1','CAT1','CAT2','CAT2','CAT2','CAT2',
                           'CAT1','CAT1','CAT1','CAT1','CAT2','CAT2','CAT2','CAT2',
                           'CAT1','CAT1','CAT1','CAT1','CAT2','CAT2','CAT2','CAT2',
                           'CAT1','CAT1','CAT1','CAT1','CAT2','CAT2','CAT2','CAT2'),
                   CODE = c('code1','code2','code3','code4','code1','code2','code3','code4',
                            'code1','code2','code3','code4','code1','code2','code3','code4',
                            'code1','code2','code3','code4','code1','code2','code3','code4',
                            'code1','code2','code3','code4','code1','code2','code3','code4',
                            'code1','code2','code3','code4','code1','code2','code3','code4',
                            'code1','code2','code3','code4','code1','code2','code3','code4'),
                   DATE = c('date1', 'date2', 'date3','date4','date1','date2','date3','date4',
                            'date1', 'date2', 'date3','date4','date1','date2','date3','date4',
                            'date1', 'date2', 'date3','date4','date1','date2','date3','date4',
                            'date1', 'date2', 'date3','date4','date1','date2','date3','date4',
                            'date1', 'date2', 'date3','date4','date1','date2','date3','date4',
                            'date1', 'date2', 'date3','date4','date1','date2','date3','date4'),
                   stringsAsFactors = F)

# 数据整理为目标结构
result_df <- test %>%
  # 将ID转为数值型，匹配JSON里数字id的要求
  mutate(ID = as.integer(ID)) %>%
  # 按ID和分类分组
  group_by(ID, CAT) %>%
  # 同组内的编码、日期分别汇总为列表
  summarise(
    code = list(CODE),
    date = list(DATE),
    .groups = "drop"
  ) %>%
  # 长表转宽表，生成CAT1、CAT1_dates这类目标列
  pivot_wider(
    id_cols = ID,
    names_from = CAT,
    values_from = c(code, date),
    # 自定义列名格式
    names_glue = "{CAT}{if_else(.value == 'date', '_dates', '')}"
  ) %>%
  # 重命名ID为小写id
  rename(id = ID)

# 转换为JSON格式
result_json <- toJSON(result_df, pretty = TRUE, auto_unbox = TRUE)

# 打印查看结果
cat(result_json)

# 如果需要写入文件，执行以下代码
# write_json(result_df, "output.json", pretty = TRUE, auto_unbox = TRUE)

说明 #

• 生成的JSON为标准JSON格式，使用双引号包裹字符串，和你给出的样例逻辑完全一致
• auto_unbox = TRUE参数保证id字段为单个数值，不会被包裹为数组
• 支持任意数量的CAT分类扩展，不需要手动调整代码逻辑

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