4

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?

Jeroen Steen
  • 531
  • 8
  • 22

2 Answers2

5

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}}'
Colonel Beauvel
  • 30,423
  • 11
  • 47
  • 87
4
json <- fromJSON('{"category1": {"town1": 8,"town2": 2},"category2":{"town1": 4,"town2": 3}}') 

json <- data.frame(number = unlist(json))

json$rownames <- rownames(json) 

rownames(json) <- NULL

json$categories <- lapply(strsplit(as.character(json$rownames), "\\."), "[", 1)

json$town <- lapply(strsplit(as.character(json$rownames), "\\."), "[",2) 

json$rownames <- NULL
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
Berecht
  • 1,085
  • 9
  • 23
  • unlisting the json worked well for me. This does create issues with multiple entries though and I did need to use reshape to get my data in order. Thank you. – Phil_T Jul 20 '18 at 04:20