3

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.

Joe
  • 183
  • 5
  • 16
  • 3
    Possible duplicate of [Getting imported json data into a data frame in R](http://stackoverflow.com/questions/16947643/getting-imported-json-data-into-a-data-frame-in-r) – Psidom Jul 12 '16 at 18:52
  • it would be appropriate to provide a sample `json` file, however I don't see how this is different from all of the examples where `.json` file is downloaded from a URL then parsed. You just substitute your file in place of the one that was downloaded. – Hack-R Jul 12 '16 at 18:54
  • Can you tell the syntax by which i can read that data from the json file and parse it in csv in the ways we have an excel? I am using
    treview <- "filepath"
    json_data<- fromJSON(treview)     – Joe Jul 12 '16 at 19:06
  • Sample data in that json file {"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} – Joe Jul 12 '16 at 19:11
  • You need to explicitly specify the `file` parameter in `fromJSON`. `fromJSON(file = treview)` should work. – Psidom Jul 12 '16 at 19:15
  • @Psidom, Hi, I tried using that but got the error- Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘fromJSON’ for signature ‘"missing", "missing"’ ....
    Also, once we have the data in the dataframe, how can i parse it into excel format?     – Joe Jul 12 '16 at 19:18
  • What json packages are your using? – Psidom Jul 12 '16 at 19:20
  • I tried using both rjson and RJSONIO – Joe Jul 12 '16 at 19:21
  • Just made a show case using `rjson` package in the answer. Check it out. – Psidom Jul 12 '16 at 19:31

1 Answers1

4

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}]
Psidom
  • 209,562
  • 33
  • 339
  • 356
  • Explicit calling worked. Thanks!. Can you also tell how can i parse this data to be useful in excel. – Joe Jul 12 '16 at 20:08
  • It's a little bit tricky to make your data fit with excel since you have comma in your data. But check the answer, it gives some clue to make it work. – Psidom Jul 12 '16 at 20:21
  • Thanks Psidom. I received an error-- Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, : arguments imply differing number of rows: 1, 0

    Not sure what am i doing wrong. I wrote this only- do.call(rbind, lapply(myList, data.frame))     – Joe Jul 12 '16 at 20:35
  • I am not sure what I can tell you. That command should have no problem to produce a data.frame assuming `myList` is exactly what we have in the first stage. – Psidom Jul 12 '16 at 20:48