Problems in converting JSON to CSV(data frame)

Question

I am trying to convert the below JSON file to CSV (data frame) using jsonlite package in R. I am not able to do so. I am looking for a generic method that could parse JSON of any complexity and nesting?

library(jsonlite)
fromJSON(json_file)

JSON file:

{
 "IRD": {
 "INTV": {
 "INTVStatus": "SERV_HST",
"RD": {
 "U": "kWh",
"BEV": "0.0379",
"Val": "0",
"RV": "0",
"port": "1" 
},
"RD": {
 "U": "kWh",
"BEV": "0.0379",
"Val": "0",
"RV": "0",
"port": "2" 
},
"RD": {
 "U": "Vrms",
"BEV": "231.0000",
"Val": "231.0000",
"RV": "231",
"port": "3" 
},
".attrs": {
 "GatewayCollectedTime": "2015-12-21T12:05:02.257-05:00",
"INTVSequenceNumber": "47112",
"BlockSequenceNumber": "0",
"EndTime": "2015-12-21T10:00:00.000-05:00" 
} 
},
"INTV": {
 "INTVStatus": "SERV_HST",
"RD": {
 "U": "kWh",
"BEV": "0.0379",
"Val": "0",
"RV": "0",
"port": "1" 
},
"RD": {
 "U": "kWh",
"BEV": "0.0379",
"Val": "0",
"RV": "0",
"port": "2" 
},
"RD": {
 "U": "Vrms",
"BEV": "231.0000",
"Val": "231.0000",
"RV": "231",
"port": "3" 
},
".attrs": {
 "GatewayCollectedTime": "2015-12-21T12:05:02.257-05:00",
"INTVSequenceNumber": "47113",
"BlockSequenceNumber": "0",
"EndTime": "2015-12-21T11:00:00.000-05:00" 
} 
},
"INTV": {
 "INTVStatus": "SERV_HST",
"RD": {
 "U": "kWh",
"BEV": "0.0379",
"Val": "0",
"RV": "0",
"port": "1" 
},
"RD": {
 "U": "kWh",
"BEV": "0.0379",
"Val": "0",
"RV": "0",
"port": "2" 
},
"RD": {
 "U": "Vrms",
"BEV": "231.0000",
"Val": "231.0000",
"RV": "231",
"port": "3" 
},
".attrs": {
 "GatewayCollectedTime": "2015-12-21T12:05:02.257-05:00",
"INTVSequenceNumber": "47114",
"BlockSequenceNumber": "0",
"EndTime": "2015-12-  21T12:00:00.000-05:00" 
} 
},
".attrs": {
 "NumberINTVs": "3",
"EndTime": "2015-12-21T12:00:00.000-05:00",
"StartTime": "2015-   12-21T09:00:00.000-05:00",
"INTVLength": "60" 
} 
},
".attrs": {
 "Version": "2.0",
"DocumentID": "aebjjjjd-59de-4405-ac0b-50e33b0b4f4b-1",
"JobID": "3354",
"ExportID": "aeb5bf7d-59de-4405-er0b-50e33b0b4f4b",
"RunID": "20430452",
"CreationTime": "2015-12-21T13:55:00.807-05:00",
"StartTime": "2015-12-21T09:55:00.000-  05:00",
"EndTime": "2015-12-21T13:55:00.000-05:00" 
} 
}

"I am looking for a generic method that could parse JSON of any complexity and nesting?" ...that's asking a lot, because sometimes JSON doesn't have an obvious tabular equivalent, at least without some decisions being made. — alistaire
– alistaire, Commented Mar 19, 2016 at 4:40
Can you suggest something in R that can help parsing the above JSON file — Subramanian T V
– Subramanian T V, Commented Mar 19, 2016 at 15:02

Parfait · Accepted Answer · 2016-03-19 22:36:00Z

Simply by its multiple nested structure, consider parsing the JSON level by level (job/document, INTV, RD, and attrs) and then binding them into a dataframe:

library(jsonlite)

# READ IN JSON FILE INTO NESTED LIST
ird <- do.call(rbind,
               lapply(paste(
                      readLines("JSONFile.json", warn=FALSE),
                      collapse=""), 
               jsonlite::fromJSON))    
# JOB
job <- list(ird[[2]])

# INVSTATUS
intvstatus <- lapply(1:3, function(i) ird[[1]][i]$INTV$INTVStatus)

# RDs (nested lapply for three RDs per three INTVs)
rds <- lapply(1:3, function(i)
                   do.call(rbind, lapply(2:4, 
                                  function(j) ird[[1]][i]$INTV[j]$RD)
                           )
              )    
# ATTRS
attrs <- lapply(1:3, function(i) ird[[1]][i]$INTV$.attrs)

# BINDING EACH LIST TO FINAL DF (rep() to repeat for each 9 RDs)
df <- data.frame(do.call(rbind, rep(job,9)),
                 INTVStatus = do.call(rbind, rep(intvstatus, 3)),
                 do.call(rbind, rds),
                 do.call(rbind, rep(attrs,3)),
                 stringsAsFactors=FALSE)
# TO FLATTEN LISTS OUTPUT FROM DO CALLS
df <- data.frame(lapply(df, as.character), stringsAsFactors=FALSE)

# OUTPUT TO CSV
write.csv(df, 'Output.csv')

Collectives™ on Stack Overflow

Problems in converting JSON to CSV(data frame)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related