awavi awavi - 9 days ago 5
JSON Question

How to transform a nested json into a data frame in r?

I am using a third party response in json and I want to analyse it using r. To do so I need to transform the json into an r data frame. For instance my json might look like this:

{
"useless_info1": "trash1",
"useless_info2": "trash2",
"useless_info3": "trash3",
"usefull_info": [
{
"color": "red",
"sizes": [
"height": 128,
"width": 40
],
"flavour": "bitter"
},
{
"color": "blue",
"sizes": [
"height": 30,
"width": 10
],
"flavour": "sweet"
},
]
}


I am looking for something like this:

color sizes_height sizes_width flavor
-------- --------------- ------------- ---------
red 128 40 bitter
blue 30 10 sweet

Answer

Consider this:

library(jsonlite)

json = '{
    "useless_info1": "trash1",
    "useless_info2": "trash2",
    "useless_info3": "trash3",
    "usefull_info": [
        {
            "color": "red",
            "sizes": {
                "height": 128,
                "width": 40
            },
            "flavour": "bitter"
        },
        {
            "color": "blue",
            "sizes": {
                "height": 30,
                "width": 10
            },
            "flavour": "sweet"
        }
    ]
}'

fromJSON(json)$usefull_info
#>   color sizes.height sizes.width flavour
#> 1   red          128          40  bitter
#> 2  blue           30          10   sweet