Converting JSON file to df in R

Question

I have never worked with json files before so sorry in advance if my language/explanation is difficult to understand. First time posting so big apologies if this is in the wrong format too!

I'm trying to convert a json file in R, so far I can upload the file into a list using:

jdata <- RJSONIO::fromJSON("./data.json")

The original raw file looks like:

[
    {
        "name": "11 bit studios",
        "website": "https://11bitstudios.com/",
        "headquarters": "Warsaw, ",
        "industry": [
            "Software",
            "Gaming"
        ],
        "side": "",
        "actions": {
            "support": [{
                "country": "",
                "measures": [
                    "All profits from \"This War of Mine\" for seven days after announcement went go to special fund which was donated to Ukrainian Red Cross. In summary, $850,000 were raised"
                ]
            }]
        },
        "links": [
            "https://twitter.com/11bitstudios/status/1496904408344449027",
            "https://twitter.com/11bitstudios/status/1499736525357129730"
        ]

And the list looks like:

enter image description here

What I'm trying to do is get a df that has the following headings:

$name $website $headquarters $industry $actions $country $measures

I hope that makes sense!

--edit for example-- See picture: example df

Maybe [this quesion](https://stackoverflow.com/questions/36454638/how-can-i-convert-json-to-data-frame-in-r) is helping you? — Jonas, Jul 02 '22 at 12:18
Or [this](https://www.educative.io/answers/how-to-read-json-files-in-r) post? — Jonas, Jul 02 '22 at 12:21
As seen in your example, there can be multiple industries, and 'actions' is a complete multidimensional list. A data frame only supports a single value per cell. Can you give an example of how you'd like the data frame to look for the example entry? — Caspar V., Jul 02 '22 at 13:58
just added an edit with an example of the df! Does it make sense? — Hazel, Jul 02 '22 at 17:16

score 0 · Accepted Answer · answered Jul 02 '22 at 22:00

The JSON allows each company to have multiple industries, multiple actions, multiple countries they support, and multiple measures to take. However, given your example desired result, we will only get the first of each per company.

jdata <- RJSONIO::fromJSON("./data.json")

# With `lapply()` we iterate over all items `x` in `jdata`
jdata_list <- lapply(jdata, function(x) {

  # For each `x`, we create a 1-dimensional list of variables we want to extract
  list(name = x$name,
       hq = x$headquarters,
       industry = x$industry[1],
       actions = names(x$actions)[1],
       support = x$actions[[1]][[1]]$country,
       measures = x$actions[[1]][[1]]$measures[1]
       )
  
})

# We bind the resulting list of lists into a data frame
df <- as.data.frame(do.call(rbind, jdata_list))

Using this example JSON, resulting in this data frame:

Thank you!!! V.much appreciate :') – Hazel Jul 02 '22 at 22:22 — Hazel, Jul 02 '22 at 22:22

Converting JSON file to df in R

1 Answers1