Converting JSON to Data Frame in R

Tags:

Here’s an example to load json objects into R’s data frame.

> library(rjson)
> # This gives you list.
> x <- fromJSON('[{"name": "foo", "score": 95},{"name": "bar", "score": 87}]')
> str(x)
List of 2
 $ :List of 2
  ..$ name : chr "foo"
  ..$ score: num 95
 $ :List of 2
  ..$ name : chr "bar"
  ..$ score: num 87
> x
[[1]]
[[1]]$name
[1] "foo"

[[1]]$score
[1] 95


[[2]]
[[2]]$name
[1] "bar"

[[2]]$score
[1] 87

> # Convert to data frame.
> y = as.data.frame(do.call(rbind, x))
> class(y)
[1] "data.frame"
> y
  name score
1  foo    95
2  bar    87

You need some formatting of the json file contents if your file is not formatted nicely:

$ cat data.json 
{"name": "foo", "score": 95}
{"name": "bar", "score": 87}

Here’s how to for loading the file contents in such case:

> l <- paste("[", paste(readLines("data.json"), collapse=","), "]")
> l
[1] "[ {\"name\": \"foo\", \"score\": 95},{\"name\": \"bar\", \"score\": 87} ]"

It takes a bit more effort if json object has some nested elements. This is one of the ways to get data frame.

> # See that x is list of list because of score field.
> (x <- fromJSON(
+ '[{"name": "foo", "score": {"a": 95, "b": 97} },{"name": "bar", "score": {"a": 87, "b": 98}}]'))
[[1]]
[[1]]$name
[1] "foo"

[[1]]$score
[[1]]$score$a
[1] 95

[[1]]$score$b
[1] 97


[[2]]
[[2]]$name
[1] "bar"

[[2]]$score
[[2]]$score$a
[1] 87

[[2]]$score$b
[1] 98

> # Thanks to as.data.frame, score$a and score$b becomes score.a and score.b.
> (x <- lapply(x, function(x) { as.data.frame(x) }))
[[1]]
  name score.a score.b
1  foo      95      97

[[2]]
  name score.a score.b
1  bar      87      98

> # It's now trivial to convert to data frame.
> (x <- do.call(rbind, x))
  name score.a score.b
1  foo      95      97
2  bar      87      98
> class(x)
[1] "data.frame"
> str(x)
'data.frame':	2 obs. of  3 variables:
 $ name   : Factor w/ 2 levels "foo","bar": 1 2
 $ score.a: num  95 87
 $ score.b: num  97 98

Above is just preliminary example of conversion. See https://github.com/minkooseo/ml/tree/master/json for more code and examples.