2
$\begingroup$

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.

Improve this question
$\endgroup$
2
  • 1
    $\begingroup$ 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. $\endgroup$
    – ffriend
    Nov 14, 2014 at 23:46
  • 3
    $\begingroup$ I think this would be more appropriate at StackOverflow. It's not related directly to data science, but just parsing a common data format. $\endgroup$
    – Sean Owen
    Nov 15, 2014 at 14:20

2 Answers 2

Reset to default
2
$\begingroup$

If you have R is quite simple

  1. Copy the lines into a file, let's say: "mydata.json"

  2. Be sure you have installed the rjson package

     install.packages("rjson")

  3. Import your data

     library("rjson")
 json_data <- fromJSON(file="mydata.json")
Improve this answer
$\endgroup$
0
$\begingroup$

If these data are available in the actual excel spreadsheet cells (ie, before you export them to the JSON format provided in your question), you can use the following to get them into R:

  1. highlight the region of interest within excel
  2. copy it to the clipboard (eg. Ctrl-C)

  3. At an R prompt type:

    d <- read.delim('clipboard')

The data will now be available as a data.frame in R. 

d
  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
Improve this answer
$\endgroup$

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.