stu stu - 6 months ago 58
JSON Question

Simple formatting with tidyjson with R

I have a simple JSON file which I'm attempting to coerce into an R data.frame.

json = "
{ \"objects\":
{
\"object_one\": {
\"key1\" : \"value1\",
\"key2\" : \"value2\",
\"key3\" : \"0\",
\"key4\" : \"value3\",
\"key5\" : \"False\",
\"key6\" : \"False\"
},

\"object_two\": {
\"key1\" : \"0.5\",
\"key2\" : \"0\",
\"key3\" : \"343\",
\"key4\" : \"value4\",
\"key5\" : \"True\",
\"key6\" : \"True\"
}
}
}
"


and I simply want to extract the name of each object as a index key (or rowname), create column names from the keys and spread the values.

Unfortunately I've had no luck unpicking the syntax. Can anyone help?

Thanks

Stuart

Answer

Not entirely sure on your desired output, but you can use jsonlite::fromJSON to extract the data, and data.table::rbindlist to put it into a data.table

library(jsonlite)
library(data.table)

rbindlist(fromJSON(json))

#     object_one object_two
# 1:     value1        0.5
# 2:     value2          0
# 3:          0        343
# 4:     value3     value4
# 5:      False       True
# 6:      False       True

Based on your comment, another approach that involves some reshaping

library(jsonlite)
library(reshape2)

lst <- fromJSON(json)
lst <- lapply(lst[[1]], unlist)

df <- as.data.frame(lst)
df$key <- rownames(df)

df <- melt(df, id = "key")
df <- dcast(df, formula = variable ~ key)
df
#      variable   key1   key2 key3   key4  key5  key6
# 1 object_one value1 value2    0 value3 False False
# 2 object_two    0.5      0  343 value4  True  True