-2

I have a json format data and I need to extract that data in R, but I want the columns (fields) in json as the fields in a data frame and the corresponding data below the field.

Some of the json array:

[{"from": {"category": "k", "name": "p", "id": "12"}, "like_count": 0, "can_remove": false, "created_time": "2015-11-20T04:19:27+0000", "message": "Hello Aleks, we are sorry to read about your experience, please contact us via private message so we can better assist you.", "id": "10153685805887457_10153685807007457", "user_likes": false}, {"from": {"name": "Aleks Vujovic", "id": "524130559029"}, "like_count": 0, "can_remove": false, "created_time": "2015-11-20T04:23:31+0000", "message": "I would love to but noare available.", "id": "10153685805887457_10153685812162457", "user_likes": false}]
Phil
  • 4,344
  • 2
  • 23
  • 33
Manas Kumar
  • 13
  • 5
  • 1
    What have you already tried? Have you searched for similar questions (with answers) on SO? If you still need to ask please provide a [minimal reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) so that we have something to work with and test. – Phil Dec 15 '15 at 10:16
  • [{"from": {"category": "k", "name": "p", "id": "12"}, "like_count": 0, "can_remove": false, "created_time": "2015-11-20T04:19:27+0000", "message": "Hello Aleks, we are sorry to read about your experience, please contact us via private message so we can better assist you.", "id": "10153685805887457_10153685807007457", "user_likes": false}, {"from": {"name": "Aleks Vujovic", "id": "524130559029"}, "like_count": 0, "can_remove": false, "created_time": "2015-11-20T04:23:31+0000", "message": "I would love to but noare available.", "id": "10153685805887457_10153685812162457", "user_likes": false}] – Manas Kumar Dec 15 '15 at 10:24
  • from that data i want to extract columns as fields and save them in r – Manas Kumar Dec 15 '15 at 10:25

1 Answers1

0

I'm not sure if I understand your question well (some clarification would be good) but maybe that is the solution for your problem. This code converts JSON to data frame.

library(rjson)
library(plyr)

json = '[{"name":"John","age":20},
{"name":"Martin","age":30}]'

data = fromJSON(json)

frame = do.call("rbind.fill", lapply(data, as.data.frame))

According to problem clarification in comments:

At first it's important to preserve quotes in your CSV file to read JSON properly. Here I replace default setting to treat both ' and " as string delimiters:

c = read.csv2("C:\\Users\\Ardavel\\Desktop\\f.csv", header = T, quote="\'")

Then I get the column with JSON data:

d = c$series.data

Then I extract the data itself:

x = levels(d)[d]

Finally, I can use the same method as in the original answer:

data = fromJSON(x[1])
frame = do.call("rbind.fill", lapply(data, as.data.frame))

I leave it up to you to get results from all rows in your CSV together if you have more than one.

Ardavel
  • 1,445
  • 1
  • 12
  • 23
  • yes exactly but json in the code is not performing fromJSON and throwing me an error.And can we do it for a list and factor – Manas Kumar Dec 15 '15 at 11:04
  • When I copy your JSON to my code it works fine. Are you sure you have rjson package installed? – Ardavel Dec 15 '15 at 11:07
  • yes.when i am performing it for a list it is not accepting insetead it is showing an error that only for text it can be performed – Manas Kumar Dec 15 '15 at 11:12
  • Please show me some example so I could understand your problem precisely. I thought you have your data available as JSON, not as list. – Ardavel Dec 15 '15 at 11:16
  • Actually in one data frame one of the field is having this JSON data.So i extracted the field into another variable.Now that it is in factor format.So what i thought is to convert data into a dataframe same like your example.But it is not excepting.This is what my problem is... so please help me – Manas Kumar Dec 15 '15 at 11:55
  • Could you provide some reproducible example of code? – Ardavel Dec 15 '15 at 12:00
  • a=read.csv2(file = "users_3_months.csv",header = T) d=a$series.data f=as.data.frame(d) – Manas Kumar Dec 15 '15 at 12:06
  • Ardavel your suggestion is working..but do call is not working...Finally i am happy with the solution thank you i will try it again if it doesnot work then i will let you know. – Manas Kumar Dec 15 '15 at 13:22
  • In `do.call` you need plyr library so make sure you have this package. – Ardavel Dec 15 '15 at 13:24
  • 2041 observations are there with same fields so i want them in the frame in row manner.but some error is happening – Manas Kumar Dec 15 '15 at 13:29
  • "Some error" is not something other people can help you with here. – Ardavel Dec 15 '15 at 13:33
  • Yes now it is working thanks a lot Ardavel as i am very new to r so it takes some time to get info.Once again Thanks a lot Ardavel – Manas Kumar Dec 15 '15 at 13:37
  • I'm glad I helped. Please accept the answer if it solved the problem ;) – Ardavel Dec 15 '15 at 13:49