How to read Several JSON files to a dataframe in R?

Question

I have a folder with 30,000 plus JSON file. A sample file with contents is posted below.

{
"name": null, "release_date_local": null, "title": "3 (2011)", 
"opening_weekend_take": 1234, "year": 2011,
"release_date_wide": "2011-09-16", "gross": 59954
}

However, I need the data in a df in a structure as given below:

name relase_date_local title    opening_weekend_take year release_date gross
NA   NA                3 (2011) 1234                 2011 2011-09-16   5994

Here is my code snippet to get all the files as a list:

path = "./Week1/jsonfiles"
temp = list.files(path, pattern = "*.json")
filename = paste(path, temp, sep = "/")
movies = c()
for (i in filename){
  movie = fromJSON(file = i)
  movies = c(movies, movie)
}

Please advise, how can I read all 30,000 files as rows of a df?

Answer 1

First, you can use the full.names parameter to list.files() to get the full path added to each file.

temp <- list.files(path, pattern="*.json", full.names=TRUE)

Next, there are issues with the data since they contain NULL values which throws off a quick-and-dirty solution. So, we have to take each list element and convert any NULL to NA.

Finally, we can use the handy purrr::map_df() to take the whole list of lists and turn them into a data.frame:

movies <- purrr::map_df(temp, function(x) { 
  purrr::map(jsonlite::fromJSON(x), function(y) ifelse(is.null(y), NA, y)) 
})