Nested JSON to dataframe in R

Question

I have a nested JSON file, with this structure:

{"category1": {"town1": 8,"town2": 2},"category2": {"town1": 4,"town2": 3}}

I want to import the JSON in to R, in following structure:

categories  towns   number
category1   town1   8
category1   town2   2
category2   town1   4
category2   town2   3

I tried fromJSON, also with Flatten = TRUE, but that doesn't give me what I want. What can I do in R to get the structure that I want?

Answer 1

The trick is to use stack:

library(jsonlite)
lst = fromJSON(json)
transform(stack(lst), towns=c(sapply(lst, names)))

#  values       ind towns
#1      8 category1 town1
#2      2 category1 town2
#3      4 category2 town1
#4      3 category2 town2

Using plyr, a concise one liner is:

library(plyr)
ldply(fromJSON(json), stack)

Data:

json = '{"category1": {"town1": 8,"town2": 2},"category2": {"town1": 4,"town2": 3}}'