syntax for jsonlite::fromJSON() or malformed JSON data?

Question

I'm reading in a json formatted datasource and can't parse it to a dataframe that I'd like.

jsontxt <- '{"sitesEnergy":{"timeUnit":"DAY","unit":"Wh","count":2,"siteEnergyList":[{"siteId":159864,"energyValues":{"measuredBy":"METER","values":[{"date":"2015-09-01 00:00:00","value":2.0},{"date":"2015-09-02 00:00:00","value":2.0}]}},{"siteId":177606,"energyValues":{"measuredBy":"INVERTER","values":[{"date":"2015-09-01 00:00:00","value":null},{"date":"2015-09-02 00:00:00","value":0.0}]}}]}}'
fromJSON(jsontxt,flatten=TRUE)

yields:

$sitesEnergy
$sitesEnergy$timeUnit
[1] "DAY"

$sitesEnergy$unit
[1] "Wh"

$sitesEnergy$count
[1] 2

$sitesEnergy$siteEnergyList
  siteId energyValues.measuredBy                             energyValues.values
1 159864                   METER  2015-09-01 00:00:00, 2015-09-02 00:00:00, 2, 2
2 177606                INVERTER 2015-09-01 00:00:00, 2015-09-02 00:00:00, NA, 0

The first seven rows of output text look fine but the value for energyValues.values is a concatenated version of both dates and values. I'm expecting somthing like this:

  siteId energyValues.measuredBy    energyValues.values.date   energyValues.values.value
1 159864                   METER  2015-09-01 00:00:00                2
2 159864                   METER  2015-09-02 00:00:00                2
3 177606                INVERTER  2015-09-01 00:00:00                NA
2 177606                INVERTER  2015-09-02 00:00:00                0

so is myJSON packet malformed, am I using fromJSON inproperly, do I need to preprocess jsontxt, or is it somthing else altogether?

I tried:

fromJSON(jsontxt,simplifyVector = FALSE)

but it returns a list instead of a dataframe that I need. I also tried without using the flatten=TRUE parameter and that didn't affect the output.

score 1 · Accepted Answer · answered Nov 05 '15 at 15:35

Not sure if this is what you want...

    library(jsonlite)

    jsontxt <- '{"sitesEnergy":{"timeUnit":"DAY","unit":"Wh","count":2,"siteEnergyList":[{"siteId":159864,"energyValues":{"measuredBy":"METER","values":[{"date":"2015-09-01 00:00:00","value":2.0},{"date":"2015-09-02 00:00:00","value":2.0}]}},{"siteId":177606,"energyValues":{"measuredBy":"INVERTER","values":[{"date":"2015-09-01 00:00:00","value":null},{"date":"2015-09-02 00:00:00","value":0.0}]}}]}}'
    jsontxt<-fromJSON(jsontxt,flatten=TRUE)

    str(jsontxt[[1]][4])

    mydf<- jsontxt[[1]][4][[1]]

    library(tidyr)

    unnest(mydf, energyValues.values)

It works on the sample dataset I provided but, in general,it doesn't handle "real" data well with intermittent NULLs. I have a bit more work to do before I can understand the problem better and move forward, but this solution got me started in the right direction, so thanks! — Jonathan, Nov 12 '15 at 14:29

syntax for jsonlite::fromJSON() or malformed JSON data?

1 Answers1