0

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?

7
  • The are packages like rjson or jsonlite which do a pretty good job and might help. Commented Jul 20, 2015 at 9:37
  • I have been trying with rjson for sometime now. Not doing the job, as far as I could understand. Commented Jul 20, 2015 at 9:40
  • Here is a different thread and also different package which might help link Commented Jul 20, 2015 at 9:42
  • That was the first soution I tried. For me, it just puts into table the first JSON element instead of all of them. Commented Jul 20, 2015 at 9:43
  • In these case, please provide some code you already tried. Commented Jul 20, 2015 at 9:44

2 Answers 2

1

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.

Sign up to request clarification or add additional context in comments.

Comments

0

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)

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.