transform dataframe to json structure-CodePudding

Need help with converting this df to exact JSON as an example using R. Already tried a lot of approaches but cant succeed. Please help.

Code for generating sample:

df <- data.frame(month = c(1, 1, 1, 2, 2, 2),
                      station = c('AE', 'DС', 'CL', 'AE', 'DC', 'CL'),
                      persons = c(3, 12, 21, 12, 10, 9),
                      results = c(10, 34, 57, 19, 16, 5)
)

Its needed to drop column "station" and add level "summary" which gather results to pairs "person" and "results" just as like example shows. Needed JSON output.

[
  {
    "month": "1",
    "summary": [
      {
        "persons": 3,
        "results": 10
      },
      {
        "persons": 12,
        "results": 34
      },
      {
        "persons": 21,
        "results": 57
      }
    ]
  },
  {
    "month": "2",
    "summary": [
      {
        "persons": 12,
        "results": 19
      },
      {
        "persons": 10,
        "results": 16
      },
      {
        "persons": 9,
        "results": 5
      }
    ]
  }
]

CodePudding user response：

You could use tidyr::nest and jsonlite::toJson like so:

library(tidyr)
library(dplyr)
library(jsonslite)

df |> 
  select(-station) |> 
  group_by(month) |>
  nest() |>
  rename(summary = data) |> 
  jsonlite::toJSON()
#> [{"month":1,"summary":[{"persons":3,"results":10},{"persons":12,"results":34},{"persons":21,"results":57}]},{"month":2,"summary":[{"persons":12,"results":19},{"persons":10,"results":16},{"persons":9,"results":5}]}]