Parsing JSON into R Data Frame

Question

I have a JSON file which has a slightly different format:

{"review/profileName": "Carleen M. Amadio \"Lady Dragonfly\"", "product/price": "unknown", "review/time": "1314057600", "product/productId": "B000GKXY4S", "review/helpfulness": "2/2", "review/summary": "Fun for adults too!", "review/userId": "A1QA985ULVCQOB", "product/title": "Crazy Shape Scissor Set", "review/score": "5.0", "review/text": "I really enjoy these scissors for my inspiration books that I am making (like collage, but in books) and using these different textures these give is just wonderful, makes a great statement with the pictures and sayings. Want more, perfect for any need you have even for gifts as well. Pretty cool!"}{"review/profileName": "Barbara", "product/price": "unknown", "review/time": "1328659200", "product/productId": "B000GKXY4S", "review/helpfulness": "0/0", "review/summary": "Making the cut!", "review/userId": "ALCX2ELNHLQA7", "product/title": "Crazy Shape Scissor Set", "review/score": "5.0", "review/text": "Looked all over in art supply and other stores for \"crazy cutting\" scissors for my 4-year old grandson. These are exactly what I was looking for - fun, very well made, metal rather than plastic blades (so they actually do a good job of cutting paper), safe (\"blunt\") ends, etc. (These really are for age 4 and up, not younger.) Very high quality. Very pleased with the product."}{"review/profileName": "L. Heminway", "product/price": "unknown", "review/time": "1156636800", "product/productId": "B000140KIW", "review/helpfulness": "1/1", "review/summary": "Fiskars Softouch Multi-Purpose Scissors, 10\"", "review/userId": "A2M2M4R1KG5WOL", "product/title": "Fiskars Softouch Multi-Purpose Scissors 10\"", "review/score": "5.0", "review/text": "These are the BEST scissors I have ever owned. I am left-handed and take note that either a left or right-handed person can use these equally well.If you have arthritis, as I do, these scissors are amazing as well. Well worth the price. I now own three pairs of these and have convinced many other people in my quilting group that they NEED a pair as well!They cut through muli layers and difficult to cut items really well.Do buy them, you won't regret it!"}{"review/profileName": "R. GARCIA", "product/price": "unknown", "review/time": "1214784000", "product/productId": "B000140KIW", "review/helpfulness": "0/0", "review/summary": "Best scissors ever", "review/userId": "ARQAQ6ZYMFPCA", "product/title": "Fiskars Softouch Multi-Purpose Scissors 10\"", "review/score": "5.0", "review/text": "This Fiskars Scissors are the best i've bougth in time. It are excellent for delicated fabrics and silck ribbon embroidery, also work perfectly with paper."}{"review/profileName": "Dea Carey \"deacarey\"", "product/price": "unknown", "review/time": "1173484800", "product/productId": "B000140KIW", "review/helpfulness": "0/0", "review/summary": "A great tool to make your work easier", "review/userId": "A3FPG4LAJ1HOHZ", "product/title": "Fiskars Softouch Multi-Purpose Scissors 10\"", "review/score": "5.0", "review/text": "I finally gave in and bought these after years of wanting them. I'm so glad I did!I'm a book artist and crafter, and I usually do my creative work in marathon stretches. (You know, nothing for three weeks and then an all-day session...) These scissors make it so much easier. They cut easily and cleanly, with no hand fatigue. They also saved me when I did all my Christmas gift wrapping.I should have just bought these in the first place!"}

I'd like to get this into Data Frames which has ProfileName, Price, ReviewText etc. as column names. Is there a simple way to do this in R?

The are packages like `rjson` or `jsonlite` which do a pretty good job and might help. — drmariod, Jul 20 '15 at 09:37
I have been trying with rjson for sometime now. Not doing the job, as far as I could understand. — user3766332, Jul 20 '15 at 09:40
Here is a different thread and also different package which might help [link](http://stackoverflow.com/questions/16947643/getting-imported-json-data-into-a-data-frame-in-r) — drmariod, Jul 20 '15 at 09:42
That was the first soution I tried. For me, it just puts into table the first JSON element instead of all of them. — user3766332, Jul 20 '15 at 09:43

score 1 · Answer 1 · answered Jul 20 '15 at 11:30

In your string there are multiple JSON objects. I guess you should split them before and then parse them. For your example string (call it x), you can try this:

 require(RJSONIO)
 do.call(rbind,lapply(strsplit(x,"(?<=\\})",perl=TRUE)[[1]],fromJSON))

You obtain a character matrix which can be easily converted to a data.frame if you need it.

score 0 · Answer 2 · answered Jul 20 '15 at 10:44

I played a little with your data and it seems like the last 3 datasets are a bit messy and I couldn't figure out why.

But for the first two, after adding , and [] to the string and removing single quotes, it is working as in the link I suggested.

t <- '[{"review/profileName": "Carleen M. Amadio \"Lady Dragonfly\"", "product/price": "unknown", "review/time": "1314057600", "product/productId": "B000GKXY4S", "review/helpfulness": "2/2", "review/summary": "Fun for adults too!", "review/userId": "A1QA985ULVCQOB", "product/title": "Crazy Shape Scissor Set", "review/score": "5.0", "review/text": "I really enjoy these scissors for my inspiration books that I am making (like collage, but in books) and using these different textures these give is just wonderful, makes a great statement with the pictures and sayings. Want more, perfect for any need you have even for gifts as well. Pretty cool!"},
{"review/profileName": "Barbara", "product/price": "unknown", "review/time": "1328659200", "product/productId": "B000GKXY4S", "review/helpfulness": "0/0", "review/summary": "Making the cut!", "review/userId": "ALCX2ELNHLQA7", "product/title": "Crazy Shape Scissor Set", "review/score": "5.0", "review/text": "Looked all over in art supply and other stores for \"crazy cutting\" scissors for my 4-year old grandson. These are exactly what I was looking for - fun, very well made, metal rather than plastic blades (so they actually do a good job of cutting paper), safe (\"blunt\") ends, etc. (These really are for age 4 and up, not younger.) Very high quality. Very pleased with the product."}]'
# {"review/profileName": "L. Heminway", "product/price": "unknown", "review/time": "1156636800", "product/productId": "B000140KIW", "review/helpfulness": "1/1", "review/summary": "Fiskars Softouch Multi-Purpose Scissors, 10\"", "review/userId": "A2M2M4R1KG5WOL", "product/title": "Fiskars Softouch Multi-Purpose Scissors 10\"", "review/score": "5.0", "review/text": "These are the BEST scissors I have ever owned. I am left-handed and take note that either a left or right-handed person can use these equally well.If you have arthritis, as I do, these scissors are amazing as well. Well worth the price. I now own three pairs of these and have convinced many other people in my quilting group that they NEED a pair as well!They cut through muli layers and difficult to cut items really well.Do buy them, you wont regret it!"},
# {"review/profileName": "R. GARCIA", "product/price": "unknown", "review/time": "1214784000", "product/productId": "B000140KIW", "review/helpfulness": "0/0", "review/summary": "Best scissors ever", "review/userId": "ARQAQ6ZYMFPCA", "product/title": "Fiskars Softouch Multi-Purpose Scissors 10\"", "review/score": "5.0", "review/text": "This Fiskars Scissors are the best ive bougth in time. It are excellent for delicated fabrics and silck ribbon embroidery, also work perfectly with paper."},
# {"review/profileName": "Dea Carey \"deacarey\"", "product/price": "unknown", "review/time": "1173484800", "product/productId": "B000140KIW", "review/helpfulness": "0/0", "review/summary": "A great tool to make your work easier", "review/userId": "A3FPG4LAJ1HOHZ", "product/title": "Fiskars Softouch Multi-Purpose Scissors 10\"", "review/score": "5.0", "review/text": "I finally gave in and bought these after years of wanting them. Im so glad I did!Im a book artist and crafter, and I usually do my creative work in marathon stretches. (You know, nothing for three weeks and then an all-day session...) These scissors make it so much easier. They cut easily and cleanly, with no hand fatigue. They also saved me when I did all my Christmas gift wrapping.I should have just bought these in the first place!"}]'
require(RJSONIO)  
json_file <- fromJSON(t)
json_file <- lapply(json_file, function(x) {
  x[sapply(x, is.null)] <- NA
  unlist(x)
})
do.call("rbind", json_file)

Parsing JSON into R Data Frame

2 Answers2