2

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.

1

This format is called "JSON". Curly braces form objects, while square ones represent arrays. Most programming languages have libraries for parsing JSON (e.g. for R see this question), so parsing it becomes trivial task.

– ffriend – 2014-11-14T23:46:31.9133I think this would be more appropriate at StackOverflow. It's not related directly to data science, but just parsing a common data format. – Sean Owen – 2014-11-15T14:20:32.547