将JSON转换为CSV（数据框）时出现问题 [英] Problems in converting JSON to CSV(data frame)

问题描述

我试图使用将以下 JSON 文件转换为 CSV （数据框） > jsonlite package in R.我不能这样做。我正在寻找一个通用的方法，可以解析任何复杂性和嵌套的 JSON ？

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文件：

{
 "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" 
} 
}

推荐答案

只要使用多重嵌套结构， （作业/文档，INTV，RD和attrs），然后将它们绑定到一个数据框架中：

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')

这篇关于将JSON转换为CSV（数据框）时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！