JSON file to R dataframe

Question

I have a JSON file. While the original file is quite large, I reduced to a much smaller reproducible example for the purposes of this question (I still get the same error no matter what size):

{
  "relationships_followers": [
    {
      "title": "",
      "media_list_data": [
        
      ],
      "string_list_data": [
        {
          "href": "https://www.instagram.com/testaccount1",
          "value": "testaccount1",
          "timestamp": 1669418204
        }
      ]
    },
    {
      "title": "",
      "media_list_data": [
        
      ],
      "string_list_data": [
        {
          "href": "https://www.instagram.com/testaccount2",
          "value": "testaccount2",
          "timestamp": 1660426426
        }
      ]
    },
    {
      "title": "",
      "media_list_data": [
        
      ],
      "string_list_data": [
        {
          "href": "https://www.instagram.com/testaccount3",
          "value": "testaccount3",
          "timestamp": 1648230499
        }
      ]
    },
       {
      "title": "",
      "media_list_data": [
        
      ],
      "string_list_data": [
        {
          "href": "https://www.instagram.com/testaccount4",
          "value": "testaccount4",
          "timestamp": 1379513403
        }
      ]
    }
  ]
}

I am attempting to convert it into a dataframe in R, which contains the values for href, value, and the timestamp variables:

But when I run the following, which I pulled from another SO answer about converting JSON to R:

library("rjson")

result <- fromJSON(file = "test_file.json")

json_data_frame <- as.data.frame(result)

I get met with this error about differing rows.

Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE,  : 
  arguments imply differing number of rows: 1, 0

How can I get what I have into the desired DF format?

If you use jsonlite rather than rjson with result <- jsonlite::fromJSON(file = "test_file.json") then you could do dplyr::bind_rows(result$relationships_followers$string_list_data) — MrFlick
– MrFlick, Commented Dec 2, 2022 at 20:59

cory · Accepted Answer · 2022-12-02 21:02:28Z

4

Looks like the data is nested...

Try this:

library("rjson")
library("dplyr")

result <- fromJSON(file = "test_file.json")
result_list <-sapply(result$relationships_followers,
                     "[[", "string_list_data")
json_data_frame <- bind_rows(result_list)

answered Dec 2, 2022 at 21:02

cory

6,6813 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ric · Accepted Answer · 2022-12-02 20:58:52Z

3

That is because there is nested data.

df<- as.data.frame(do.call(rbind, lapply(
  lapply(result$relationships_followers, "[[", "string_list_data"), "[[", 1)))

df
#>      href                                     value          timestamp 
#>  "https://www.instagram.com/testaccount1" "testaccount1" 1669418204
#>  "https://www.instagram.com/testaccount2" "testaccount2" 1660426426
#>  "https://www.instagram.com/testaccount3" "testaccount3" 1648230499
#>  "https://www.instagram.com/testaccount4" "testaccount4" 1379513403

NOTE: jsonlite package does a better job on parsing data.frame by default.

answered Dec 2, 2022 at 20:58

Ric

5,7311 gold badge13 silver badges24 bronze badges

Collectives™ on Stack Overflow

JSON file to R dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related