0

I have a variable called data that has json formated data:

dput(data)

{\"data\": [{\"target\": \"in\", \"datapoints\": [[5, 1423825200000.0], [0, 1423825500000.0], [0, 1423825800000.0], [0, 1423826100000.0], [0, 1423826400000.0], [0, 1423826700000.0], [0, 1423827000000.0], [0, 1423827300000.0], [0, 1423827600000.0], [0, 1423827900000.0], [0, 1423828200000.0], [1, 1423828500000.0], [1, 1423828800000.0], [0, 1423829100000.0], [0, 1423829400000.0], [0, 1423829700000.0], [0, 1423830000000.0], [0, 1423830300000.0], [0, 1423830600000.0], [0, 1423830900000.0], [0, 1423831200000.0], [0, 1423831500000.0], [0, 1423831800000.0], [0, 1423832100000.0], [0, 1423832400000.0], [0, 1423832700000.0], [0, 1423833000000.0], [0, 1423833300000.0], [0, 1423833600000.0], [0, 1423833900000.0], [0, 1423834200000.0], [0, 1423834500000.0], [1, 1423834800000.0], [0, 1423835100000.0], [4, 1423835400000.0], [9, 1423835700000.0], [1, 1423836000000.0], [3, 1423836300000.0], [0, 1423836600000.0], [1, 1423836900000.0], [1, 1423837200000.0], [0, 1423837500000.0], [0, 1423837800000.0], [0, 1423838100000.0], [0, 1423838400000.0], [0, 1423838700000.0], [0, 1423839000000.0], [0, 1423839300000.0], [0, 1423839600000.0], [0, 1423839900000.0], [0, 1423840200000.0], [2, 1423840500000.0], [0, 1423840800000.0], [0, 1423841100000.0], [0, 1423841400000.0], [0, 1423841700000.0], [0, 1423842000000.0], [0, 1423842300000.0], [0, 1423842600000.0], [0, 1423842900000.0], [0, 1423843200000.0], [0, 1423843500000.0], [0, 1423843800000.0], [0, 1423844100000.0], [0, 1423844400000.0], [0, 1423844700000.0], [0, 1423845000000.0], [0, 1423845300000.0], [0, 1423845600000.0], [0, 1423845900000.0], [0, 1423846200000.0], [0, 1423846500000.0], [0, 1423846800000.0], [0, 1423847100000.0], [0, 1423847400000.0], [0, 1423847700000.0], [0, 1423848000000.0], [0, 1423848300000.0], [0, 1423848600000.0], [1, 1423848900000.0], [1, 1423849200000.0]]}, {\"target\": \"out\", \"datapoints\": [[5, 1423825200000.0], [0, 1423825500000.0], [0, 1423825800000.0], [0, 1423826100000.0], [0, 1423826400000.0], [0, 1423826700000.0], [0, 1423827000000.0], [0, 1423827300000.0], [0, 1423827600000.0], [0, 1423827900000.0], [0, 1423828200000.0], [1, 1423828500000.0], [1, 1423828800000.0], [0, 1423829100000.0], [0, 1423829400000.0], [0, 1423829700000.0], [0, 1423830000000.0], [0, 1423830300000.0], [0, 1423830600000.0], [0, 1423830900000.0], [0, 1423831200000.0], [0, 1423831500000.0], [0, 1423831800000.0], [0, 1423832100000.0], [0, 1423832400000.0], [0, 1423832700000.0], [0, 1423833000000.0], [0, 1423833300000.0], [0, 1423833600000.0], [0, 1423833900000.0], [0, 1423834200000.0], [0, 1423834500000.0], [1, 1423834800000.0], [0, 1423835100000.0], [4, 1423835400000.0], [9, 1423835700000.0], [1, 1423836000000.0], [3, 1423836300000.0], [0, 1423836600000.0], [1, 1423836900000.0], [1, 1423837200000.0], [0, 1423837500000.0], [0, 1423837800000.0], [0, 1423838100000.0], [0, 1423838400000.0], [0, 1423838700000.0], [0, 1423839000000.0], [0, 1423839300000.0], [0, 1423839600000.0], [0, 1423839900000.0], [0, 1423840200000.0], [2, 1423840500000.0], [0, 1423840800000.0], [0, 1423841100000.0], [0, 1423841400000.0], [0, 1423841700000.0], [0, 1423842000000.0], [0, 1423842300000.0], [0, 1423842600000.0], [0, 1423842900000.0], [0, 1423843200000.0], [0, 1423843500000.0], [0, 1423843800000.0], [0, 1423844100000.0], [0, 1423844400000.0], [0, 1423844700000.0], [0, 1423845000000.0], [0, 1423845300000.0], [0, 1423845600000.0], [0, 1423845900000.0], [0, 1423846200000.0], [0, 1423846500000.0], [0, 1423846800000.0], [0, 1423847100000.0], [0, 1423847400000.0], [0, 1423847700000.0], [0, 1423848000000.0], [0, 1423848300000.0], [0, 1423848600000.0], [1, 1423848900000.0], [1, 1423849200000.0]]}]}

I need to convert this data to a data frame in R.

DateTime In Out
1423848600000.0  N/A  0
1423848300000.0  N/A  4
etc

I tried using this:

require(jsonlite)
dat <- fromJSON(data)

dat looks like this:

$at
  arge
1   NA
2   NA
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    atapoint
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NULL
2 5.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 1.000000e+00, 1.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 1.000000e+00, 0.000000e+00, 4.000000e+00, 9.000000e+00, 1.000000e+00, 3.000000e+00, 0.000000e+00, 1.000000e+00, 1.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 2.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 0.000000e+00, 1.000000e+00, 1.000000e+00, 1.423825e+12, 1.423825e+12, 1.423826e+12, 1.423826e+12, 1.423826e+12, 1.423827e+12, 1.423827e+12, 1.423827e+12, 1.423828e+12, 1.423828e+12, 1.423828e+12, 1.423829e+12, 1.423829e+12, 1.423829e+12, 1.423829e+12, 1.423830e+12, 1.423830e+12, 1.423830e+12, 1.423831e+12, 1.423831e+12, 1.423831e+12, 1.423831e+12, 1.423832e+12, 1.423832e+12, 1.423832e+12, 1.423833e+12, 1.423833e+12, 1.423833e+12, 1.423834e+12, 1.423834e+12, 1.423834e+12, 1.423834e+12, 1.423835e+12, 1.423835e+12, 1.423835e+12, 1.423836e+12, 1.423836e+12, 1.423836e+12, 1.423837e+12, 1.423837e+12, 1.423837e+12, 1.423838e+12, 1.423838e+12, 1.423838e+12, 1.423838e+12, 1.423839e+12, 1.423839e+12, 1.423839e+12, 1.423840e+12, 1.423840e+12, 1.423840e+12, 1.423841e+12, 1.423841e+12, 1.423841e+12, 1.423841e+12, 1.423842e+12, 1.423842e+12, 1.423842e+12, 1.423843e+12, 1.423843e+12, 1.423843e+12, 1.423843e+12, 1.423844e+12, 1.423844e+12, 1.423844e+12, 1.423845e+12, 1.423845e+12, 1.423845e+12, 1.423846e+12, 1.423846e+12, 1.423846e+12, 1.423847e+12, 1.423847e+12, 1.423847e+12, 1.423847e+12, 1.423848e+12, 1.423848e+12, 1.423848e+12, 1.423849e+12, 1.423849e+12, 1.423849e+12
user1471980
  • 10,127
  • 48
  • 136
  • 235
  • @Jthorpe, I dont get that error.I get the output put above. – user1471980 Feb 18 '15 at 16:19
  • Probably it was the formatting, I changed it and it works... – digEmAll Feb 18 '15 at 16:21
  • Your string doesn't look like a real json. The names of the objects and the string values are always quoted. For instance, your json should start with something like `{"data": [{"target": "in", "datapoints": []}` etc. etc. That's the reason why you have `at` instead of `data`: the parser skips the first and the last char expecting it to be a quote. – nicola Feb 18 '15 at 16:29
  • Yes incredibly fromJSON seems able to read it even if invalid, but doing strange things.... To be valid `data`, `in`, `out`, `target` and `datapoints` words should be wrapped in quotes e.g. `{ "data" : [ ...` – digEmAll Feb 18 '15 at 16:31
  • @digEmAll, yes, but I in order to show this, I couln't assign it to an object, quotes within quotes wont work in R for some reason. Can you assume that it is in quotes. – user1471980 Feb 18 '15 at 16:33
  • 1
    Lol at "quotes within quotes wont work in R for some reason". You have to escape them (try for instance `x<-"hello \"World\""`, or let R know that you are starting a string with `'` instead of `"`. – nicola Feb 18 '15 at 16:35
  • The `fromJSON` of the `RJSONIO` package seems to handle this object properly. Try for instance `res<-RJSONIO::fromJSON(data);do.call(rbind,res[[1]][[2]]$datapoints)` and you should be close to what you want. – nicola Feb 18 '15 at 16:55

1 Answers1

1

This is the general idea:

out <- NULL
for(i in seq.int(length(x$data))){

    mx <- do.call(rbind,x$data[[2]]$datapoints)

    #>       [,1]         [,2]
    #>  [1,]    5 1.423825e+12
    #>  [2,]    0 1.423825e+12
    #>  [3,]    0 1.423826e+12
    ...

    df <- as.data.frame(x$data[[2]]$target)
    names(df) <- c('out','date')

    out <- cbind(out,df)
}

Note that this won't actally run for your current example because x$data[[2]]$target if the length of this value is not consistent with the length of the values in x$data[[2]]$datapoints

Jthorpe
  • 9,756
  • 2
  • 49
  • 64
  • it needs to be dynamic where regardles of how many nodes in the json file, I need to be able create a data frame with having the column names. In this case yes, it works, there is only one valid node that has data (out). What if there were 10 nodes? column names? – user1471980 Feb 18 '15 at 17:51
  • added a bit more. your example is not well formed (length of names are not consistent with length of values in the datapoints list), but this should be a good starting point for your real data. – Jthorpe Feb 18 '15 at 18:10
  • I have updated the post. I need to be able to have mutliple columns in my data frame to test this. – user1471980 Feb 19 '15 at 18:21