CODEWITHSUNDEEP
arraysjsonr
georg23
georg23

Reputation: 53

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
  }
]

Upvotes: 3

Views: 5998

Answers (2)

alistaire
alistaire

Reputation: 43354

If you nest b as a list column, it will convert correctly:

library(jsonlite)

# converts b to nested list column
df2 <- aggregate(b ~ a, df, list)

df2
##       a          b
## 1 Luigi 49, 42, 37
## 2 Mario 46, 50, 45

toJSON(df2, pretty = TRUE)
## [
##   {
##     "a": "Luigi",
##     "b": [49, 42, 37]
##   },
##   {
##     "a": "Mario",
##     "b": [46, 50, 45]
##   }
## ]

or if you prefer dplyr:

library(dplyr)

df %>% group_by(a) %>% 
    summarise(b = list(b)) %>% 
    toJSON(pretty = TRUE)

or data.table:

library(data.table)

toJSON(setDT(df)[, .(b = list(b)), by = a], pretty = TRUE)

which both return the same thing.

Upvotes: 9

SymbolixAU
SymbolixAU

Reputation: 26258

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.

Upvotes: 2

Related Questions