JSON irregular data to R dataframe

Question

I thought the question is simple but it got me. Say I have a json file be like:

{"key1":["value1"],
"key2":["value2", "value3", "value4"],
"key3":["value5", "value6"]}

I want to import them to a data frame like:

0 key value
1 key1 value1
2 key2 value2
3 key2 value3
4 key2 value4
5 key3 value5
6 key3 value6

Tried jsonlite and rjson fromJSON function with different arguments, it returns a few lists instead of dataframe

Tried several answers from other questions but still cannot figure it out. Help is appreicated!

Was trying unlist function and got an ugly approach (with inspiration from R unlist changes names):

json <- '{"key1":["value1"],
  "key2":["value2", "value3", "value4"],
  "key3":["value5", "value6"]}'

jsonR <- fromJSON(txt = json)

data.frame(key = rep(names(jsonR ), lengths(jsonR )), value = unlist(jsonR , use.names=F))

mgc mgc · Accepted Answer · 2017-08-03T08:17:10

I guess you can use the stack and the rev functions (respectively from utils and base packages) to achieve this :

> json <- '{"key1":["value1"],
+   "key2":["value2", "value3", "value4"],
+   "key3":["value5", "value6"]}'
> 
> df <- rev(stack(fromJSON(txt=json)))
> colnames(df) <- c('key', 'value')
> df
   key  value
1 key1 value1
2 key2 value2
3 key2 value3
4 key2 value4
5 key3 value5
6 key3 value6

JSON irregular data to R dataframe

1 Answers