Parsing data from a string

Question

I think this is something that experienced programmers do all the time. But, given my limited programming experience, please bear with me.

I have an excel file which has particular cell entries that read

[[{"from": "4", "response": true, "value": 20}, {"from": "8", "response": true, "value": 20}, {"from": "9", "response": true, "value": 20}, {"from": "3", "response": true, "value": 20}], [{"from": "14", "response": false, "value": 20}, {"from": "15", "response": true, "value": 20}, {"from": "17", "response": false, "value": 20}, {"from": "13", "response": true, "value": 20}]]

Now, for each such entry I want to take the information in each of the curly brackets and make a row of data out of it. Each such row would have 3 columns. For example, the row formed from the first entry within curly brackets should have the entries "4" "true" and "20" respectively. The part I posted should give me 6 such rows, and for n such repetitions I should end up with a matrix of 6n rows, and 4 columns ( an identifier, plus the 3 columns mentioned).

What would be most efficient way to do this? By "doing this" I mean learning the trick, and then implementing it. I have access to quite a few software packages(Excel, Stata, Matlab, R) in my laboratory, so that should not be an issue.

Answer 1

That looks like a JSON style file. What you've got is actually a list of two data objects. We can turn them into R data frames with the jsonlite package. Try this:

txt <- '[[{"from": "4", "response": true, "value": 20}, {"from": "8", "response": true, "value": 20}, {"from": "9", "response": true, "value": 20}, {"from": "3", "response": true, "value": 20}], [{"from": "14", "response": false, "value": 20}, {"from": "15", "response": true, "value": 20}, {"from": "17", "response": false, "value": 20}, {"from": "13", "response": true, "value": 20}]]'

library(jsonlite)
rbind.pages(fromJSON(txt))   ## just fromJSON(txt) for the list
#   from response value
# 1    4     TRUE    20
# 2    8     TRUE    20
# 3    9     TRUE    20
# 4    3     TRUE    20
# 5   14    FALSE    20
# 6   15     TRUE    20
# 7   17    FALSE    20
# 8   13     TRUE    20

If this is in a file, and the file is called e.g. "new.txt", then you can use

rbind.pages(fromJSON(readLines("new.txt")))