0

I am trying to convert a json file in R. The format of the data is as follows: 

{
"id": "xyz",
"root": {
"author": {
"name": "xyz",
"email": "xyx@xyz.org",
"date": "2014-10-08T00:10:30Z"
},
"authorer": {
"name": "xyz",
"email": "xyx@xyz.org",
"date": "2014-10-08T00:11:30Z"
 },
"message": "This a test json",
"root": {
"id": "xyz1",
"url": "xyz"
},
"url": "xyz",
"message_count": 0
},
"url": "xyz",
"html_url": "xyz",
"comments_url": "abc",
"author": null,
"authorer": null,
"parent": [
{
"id": "xyz3",
"url": "xyz",
"html_url": "xyz"
}
]
}

After this a similar row begins, with {having the same formatted text }This is the code I wrote in R 

install.packages("rjson")
library(rjson)
df <- fromJSON(paste(readLines("file.json"), collapse=""))
View(df)

I was wondering how do make this file readable in R? I wanted to see them as columns like this: 

id  root/author/name    root/author/email   root/author/date    root/authorer/name

Refer to here: http://konklone.io/json/?id=dfeae96a607c7541b8fe (of how the input and output should look like).

I have provided a new link here for two rows: http://konklone.io/json/?id=3b01a02e17ec4fde3357

Thanks a lot

user3570187
  • 1,743
  • 3
  • 17
  • 34
  • 2
    you give a bad formated json text and a bad expected output! what do you expect from us to do with this? – agstudy Nov 12 '14 at 17:15
  • What do you mean by bad format?This is how I got the text. ( I removed some of the hyperlinks from the output, do you want that to put in the text as well, let me know). I was expecting if anyone has worked on converting nested json structures to R objects. – user3570187 Nov 12 '14 at 17:20
  • 1
    There are five open brackets in your sample json text, but only 3 close brackets. Also what do you want with screen, author and root? Should those be flattened? – John Paul Nov 12 '14 at 17:38
  • 1
    Try [validating your JSON data](http://jsonlint.com/) before posting. This will make it easier to help. – MrFlick Nov 12 '14 at 18:37
  • Here is the json text with appropriate brackets. I want the json text to be converted to R objects (columns) (like a R dataset) so that i can analyze the data. – user3570187 Nov 12 '14 at 18:42
  • I have edited the text and also validated. You can also find the json to csv link which i used. – user3570187 Nov 12 '14 at 18:47

1 Answers1

1

Is this what you want:

json <- '{
  "id": "xyz",
  "root": {
    "author": {
      "name": "xyz",
      "email": "xyx@xyz.org",
      "date": "2014-10-08T00:10:30Z"
    },
    "authorer": {
      "name": "xyz",
      "email": "xyx@xyz.org",
      "date": "2014-10-08T00:11:30Z"
    },
    "message": "This a test json",
    "root": {
      "id": "xyz1",
      "url": "xyz"
    },
    "url": "xyz",
    "message_count": 0
  },
  "url": "xyz",
  "html_url": "xyz",
  "comments_url": "abc",
  "author": null,
  "authorer": null,
  "parent": [
    {
      "id": "xyz3",
      "url": "xyz",
      "html_url": "xyz"
    }
  ]
}'

out <- jsonlite::fromJSON(json)
out[vapply(out, is.null, logical(1))] <- "none"
data.frame(out, stringsAsFactors = FALSE)[,1:5]

   id root.author.name root.author.email     root.author.date root.authorer.name
1 xyz              xyz       xyx@xyz.org 2014-10-08T00:10:30Z                xyz
sckott
  • 5,755
  • 2
  • 26
  • 42