Formatted JSON fro R dataframe

Question

I have a data frame like this one.

date	aid	x_axis	y_axis	z_axis
2018-12-02	10	1.0720000	9.462000	0.0830000
2018-12-05	4	-1.9322222	5.654278	6.7933333
2018-12-05	6	0.0380000	8.662714	3.9418571
2018-12-08	6	1.3677143	9.199286	0.2580000

If I transform it with jsonlite::toJSON() it gives output something like this one

[
  {
    "date": "2018-12-02",
    "aid": 10,
    "x_axis": 1.072,
    "y_axis": 9.462,
    "z_axis": 0.083
  },
  {
    "date": "2018-12-05",
    "aid": 4,
    "x_axis": -1.9322,
    "y_axis": 5.6543,
    "z_axis": 6.7933
  },
  {
    "date": "2018-12-05",
    "aid": 6,
    "x_axis": 0.038,
    "y_axis": 8.6627,
    "z_axis": 3.9419
  }
]

But I want to create a JSON file like the following format

[
    {
        "date": "2018-11-22",
        "1": {
            "mean": {
                "x_axis": 3.11,
                "y_axis": 3.22,
                "z_axis": 3.33
            }
        },
        "2": {
            "mean": {
                "x_axis": 2.11,
                "y_axis": 2.22,
                "z_axis": 2.33
            }
        }
    }
 ]

Here column aid is nested under date and only the value of the aid is reported. And there is an additional text "mean" above the mean value of the axes. Does anyone know how to create a formatted output like the following one?

Allan Cameron · Answer 1 · 2022-10-30T11:12:17.837

You need to reshape your data into a list with the appropriate structure:

result <- split(df, df$date) |>
  lapply(function(x) {
    split(x[-c(1:2)], x$aid) |> 
      lapply(function(y) list(mean = y))
    }) |>
  jsonlite::toJSON() |>
  jsonlite::prettify()

Which gives

result
#> {
#>     "2018-12-02": {
#>         "10": {
#>             "mean": [
#>                 {
#>                     "x_axis": 1.072,
#>                     "y_axis": 9.462,
#>                     "z_axis": 0.083
#>                 }
#>             ]
#>         }
#>     },
#>     "2018-12-05": {
#>         "4": {
#>             "mean": [
#>                 {
#>                     "x_axis": -1.9322,
#>                     "y_axis": 5.6543,
#>                     "z_axis": 6.7933
#>                 }
#>             ]
#>         },
#>         "6": {
#>             "mean": [
#>                 {
#>                     "x_axis": 0.038,
#>                     "y_axis": 8.6627,
#>                     "z_axis": 3.9419
#>                 }
#>             ]
#>         }
#>     },
#>     "2018-12-08": {
#>         "6": {
#>             "mean": [
#>                 {
#>                     "x_axis": 1.3677,
#>                     "y_axis": 9.1993,
#>                     "z_axis": 0.258
#>                 }
#>             ]
#>         }
#>     }
#> }
#>

^{Created on 2022-10-30 with reprex v2.0.2}

Data taken from question in reproducible format

df <- structure(list(date = c("2018-12-02", "2018-12-05", "2018-12-05", 
"2018-12-08"), aid = c(10L, 4L, 6L, 6L), x_axis = c(1.072, -1.9322222, 
0.038, 1.3677143), y_axis = c(9.462, 5.654278, 8.662714, 9.199286
), z_axis = c(0.083, 6.7933333, 3.9418571, 0.258)), class = "data.frame", 
row.names = c(NA, -4L))

Thanks mate for your reply. But in my desired output there are `[]` only at the beginning and at the end of the document and date is reported as `"date": "2018-11-22"` format. If I add an `lapply` to add `"date"` the whole output changes. — Nowfel Ahmed, Oct 30 '22 at 13:53

Formatted JSON fro R dataframe

1 Answers1