georg23 georg23 - 14 days ago 7
JSON Question

R convert data.frame to json

I'm trying to convert a data.frame into json format

my data.frame has the following structure

a <- rep(c("Mario", "Luigi"), each = 3)
b <- sample(34:57, size = length(a))
df <- data.frame(a,b)
> df
a b
1 Mario 43
2 Mario 34
3 Mario 36
4 Luigi 45
5 Luigi 52
6 Luigi 35


What I want to create is something like this (to finally print it to a .json file)

[
{
"a": "Mario",
"b": [43, 34, 36]
},
{
"a": "Luigi",
"b": [45, 52, 35]
}
]


I've tried different packages handling json format but so far failed to produce this kind of output. I usually end up with something like this

[
{
"a":"Mario",
"b":43
},
{
"a":"Mario",
"b":34
},
{
"a":"Mario",
"b":36
},
{
"a":"Luigi",
"b":45
},
{
"a":"Luigi",
"b":52
},
{
"a":"Luigi",
"b":35
}
]

Answer

To get the required JSON structure you will want your data in a list, something like:

l <- list(list(a = "Mario",
               b = c(43,34,36)),
          list(a = "Luigi",
               b = c(45,52,35)))

## then can use the library(jsonlite) to convert to JSON

library(jsonlite)

toJSON(l, pretty = T)

[
  {
    "a": ["Mario"],
    "b": [43, 34, 36]
  },
  {
    "a": ["Luigi"],
    "b": [45, 52, 35]
  }
]

So to split your data into this format, you can do

l <- lapply(unique(df$a), function(x) list(a = x, b = df[a == x,"b"])   )

## and then the conversion works
toJSON(l, pretty = T)

[
  {
    "a": ["Mario"],
    "b": [44, 49, 50]
  },
  {
    "a": ["Luigi"],
    "b": [39, 57, 35]
  }
]

This works for the simple case, but if it gets more complex it might be better to re-design how you create your data.frame, and instead create a list(s) to begin with.


Reference

The jsonlite vignette is a very good resource.

Comments