data.frame to json in R-CodePudding

I have a data.frame that looks like this:

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)

I would like to have like following:

[
{"id": 1,
  "CAT1": ['code1', 'code2','code3', 'code4'],
  "CAT1_dates": ['date1',  'date2','date3','date4'],
  "CAT2": ['code1', 'code2','code3', 'code4'],
  "CAT2_dates": ['date1',  'date2','date3','date4'],
}
{"id": 2,
  "CAT1": ['code1', 'code2','code3', 'code4'],
  "CAT1_dates": ['date1',  'date2','date3','date4'],
  "CAT2": ['code1', 'code2','code3', 'code4'],
  "CAT2_dates": ['date1',  'date2','date3','date4'],
}
]

I understood that i need to write a function to do that job. I was not successfull.

From dataFrame to grouped Json in R

convert date frame to json in R

CodePudding user response：

One method using dplyr (for nesting and pivoting) and jsonlite:

library(dplyr)
library(tidyr) # pivot_wider
# library(jsonlite)
test %>%
  group_by(ID, CAT) %>%
  summarize(x = list(CODE), x_dates = list(DATE)) %>%
  pivot_wider(ID, names_from = "CAT", values_from = c("x", "x_dates"),
              names_glue = "{CAT}{gsub('^x','',.value)}") %>%
  ungroup() %>%
  jsonlite::toJSON(pretty = TRUE)
# [
#   {
#     "ID": "1",
#     "CAT1": ["code1", "code2", "code3", "code4"],
#     "CAT2": ["code1", "code2", "code3", "code4"],
#     "CAT1_dates": ["date1", "date2", "date3", "date4"],
#     "CAT2_dates": ["date1", "date2", "date3", "date4"]
#   },
#   {
#     "ID": "2",
#     "CAT1": ["code1", "code2", "code3", "code4"],
#     "CAT2": ["code1", "code2", "code3", "code4"],
#     "CAT1_dates": ["date1", "date2", "date3", "date4"],
#     "CAT2_dates": ["date1", "date2", "date3", "date4"]
#   },
#   ...truncated...
# ]

It's certainly feasible to do this in base R or data.table if needed, though admittedly not as smoothly as that.