Joe Joe - 4 months ago 8
JSON Question

Importing Data from a json file in R

I have a json data file from which I want to import in R. I tried searching for similar blogs but either they are getting data from URLs or the syntax gave errors.

Let's say the name of the json file is "Jsdata.json"

How can i get the data from Jsdata.json to R and convert it into the excel/csv format for a better picture.

Answer

To confirm, this is the output using rjson package. The file parameter has to be explicitly specified here, otherwise the function will treat it as a json string and throw an error.

myList = rjson::fromJSON(file = "JsData.json")
myList
# [[1]]
# [[1]]$key
# [1] "type1|new york, ny|NYC|hit"
# 
# [[1]]$doc_count
# [1] 12


# [[2]]
# [[2]]$key
# [1] "type1|omaha, ne|Omaha|hit"

# [[2]]$doc_count
# [1] 8


# [[3]]
# [[3]]$key
# [1] "type2|yuba city, ca|Yuba|hit"

# [[3]]$doc_count
# [1] 9

In order to convert this to data frame, you can do:

do.call(rbind, lapply(myList, data.frame))

#                            key doc_count
# 1   type1|new york, ny|NYC|hit        12
# 2    type1|omaha, ne|Omaha|hit         8
# 3 type2|yuba city, ca|Yuba|hit         9

Write the data frame as csv using write.csv(..., sep = "\t") and configure your excel so that the delimiter matches your sep here should work.

And the JsData.json data looks like this:

[{"key":"type1|new york, ny|NYC|hit","doc_count":12},
 {"key":"type1|omaha, ne|Omaha|hit","doc_count":8}, 
 {"key":"type2|yuba city, ca|Yuba|hit","doc_count":9}]
Comments