JSON to CSV conversion using R jsonlite package

Question

I am able to convert the following JSON file to CSV(dataframe) using jsonlite package R. But the problem that I am facing is that one of the columns in dataframe is a list. Any approach to overcome this?

library(jsonlite)
jsonfile <- fromJSON(jsonfile, flatten=TRUE,simplifyDataFrame=TRUE)
jsondataframe <- data.frame(jsonfile)

The sample JSON file is as below

{
  "SSE": {
    "-xmlns": "urn:com:ssn:schema:export:v2.0:SSNExportFormat.xsd",
    "-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",
    "IRD": {
      "-NumberINTVs": "3",
      "-EndTime": "2015-12-21T12:00:00.000-05:00",
      "-StartTime": "2015-   12-21T09:00:00.000-05:00",
      "-INTVLength": "60",
      "INTV": [
        {
          "-GatewayCollectedTime": "2015-12-21T12:05:02.257-05:00",
          "-INTVSequenceNumber": "47112",
          "-BlockSequenceNumber": "0",
          "-EndTime": "2015-12-21T10:00:00.000-05:00",
          "INTVStatus": "SERV_HST",
          "RD": [
            {
              "-U": "kWh",
              "-BEV": "0.0379",
              "-Val": "0",
              "-RV": "0",
              "-port": "1"
            },
            {
              "-U": "kWh",
              "-BEV": "0.0379",
              "-Val": "0",
              "-RV": "0",
              "-port": "2"
            },
            {
              "-U": "Vrms",
              "-BEV": "231.0000",
              "-Val": "231.0000",
              "-RV": "231",
              "-port": "3"
            }
          ]
        },
        {
          "-GatewayCollectedTime": "2015-12-21T12:05:02.257-05:00",
          "-INTVSequenceNumber": "47113",
          "-BlockSequenceNumber": "0",
          "-EndTime": "2015-12-21T11:00:00.000-05:00",
          "INTVStatus": "SERV_HST",
          "RD": [
            {
              "-U": "kWh",
              "-BEV": "0.0379",
              "-Val": "0",
              "-RV": "0",
              "-port": "1"
            },
            {
              "-U": "kWh",
              "-BEV": "0.0379",
              "-Val": "0",
              "-RV": "0",
              "-port": "2"
            },
            {
              "-U": "Vrms",
              "-BEV": "231.0000",
              "-Val": "231.0000",
              "-RV": "231",
              "-port": "3"
            }
          ]
        },
        {
          "-GatewayCollectedTime": "2015-12-21T12:05:02.257-05:00",
          "-INTVSequenceNumber": "47114",
          "-BlockSequenceNumber": "0",
          "-EndTime": "2015-12-  21T12:00:00.000-05:00",
          "INTVStatus": "SERV_HST",
          "RD": [
            {
              "-U": "kWh",
              "-BEV": "0.0379",
              "-Val": "0",
              "-RV": "0",
              "-port": "1"
            },
            {
              "-U": "kWh",
              "-BEV": "0.0379",
              "-Val": "0",
              "-RV": "0",
              "-port": "2"
            },
            {
              "-U": "Vrms",
              "-BEV": "231.0000",
              "-Val": "231.0000",
              "-RV": "231",
              "-port": "3"
            }
          ]
        }
      ]
    }
  }
}

Why did you re-ask nearly the same exact question? Please delete the former. — Parfait
– Parfait, Commented Mar 20, 2016 at 13:26

lukeA · Accepted Answer · 2016-03-20 13:20:06Z

This task is often asked and often causing problems. However, in your case, unlisting should be easy, because jsondataframe$SSE.IRD.INTV.RD is a list with equal lengths:

df <- cbind(jsondataframe[, -ncol(jsondataframe)], 
            do.call(cbind, jsondataframe$SSE.IRD.INTV.RD))
str(df)
# 'data.frame': 3 obs. of  33 variables:
#  $ SSE..xmlns                        : Factor w/ 1 level "urn:com:ssn:schema:export:v2.0:SSNExportFormat.xsd": 1 1 1
# ...
#  $ SSE.IRD.INTV..EndTime             : chr  "2015-12-21T10:00:00.000-05:00" "2015-12-21T11:00:00.000-05:00" "2015-12-  21T12:00:00.000-05:00"
#  $ SSE.IRD.INTV.INTVStatus           : chr  "SERV_HST" "SERV_HST" "SERV_HST"
#  $ -U                                : chr  "kWh" "kWh" "Vrms"
#  $ -BEV                              : chr  "0.0379" "0.0379" "231.0000"
#  $ -Val                              : chr  "0" "0" "231.0000"
#  $ -RV                               : chr  "0" "0" "231"
#  $ -port                             : chr  "1" "2" "3"
#  $ -U                                : chr  "kWh" "kWh" "Vrms"
#  $ -BEV                              : chr  "0.0379" "0.0379" "231.0000"
#  $ -Val                              : chr  "0" "0" "231.0000"
#  $ -RV                               : chr  "0" "0" "231"
#  $ -port                             : chr  "1" "2" "3"
#  $ -U                                : chr  "kWh" "kWh" "Vrms"
#  $ -BEV                              : chr  "0.0379" "0.0379" "231.0000"
#  $ -Val                              : chr  "0" "0" "231.0000"
#  $ -RV                               : chr  "0" "0" "231"
#  $ -port                             : chr  "1" "2" "3"

write.csv(df, tf <- tempfile(fileext = ".csv"), row.names = FALSE)
head( read.csv(tf, check.names = FALSE) )
#                                           SSE..xmlns SSE..Version                        SSE..DocumentID SSE..JobID
# 1 urn:com:ssn:schema:export:v2.0:SSNExportFormat.xsd            2 aebjjjjd-59de-4405-ac0b-50e33b0b4f4b-1       3354
# 2 urn:com:ssn:schema:export:v2.0:SSNExportFormat.xsd            2 aebjjjjd-59de-4405-ac0b-50e33b0b4f4b-1       3354
# 3 urn:com:ssn:schema:export:v2.0:SSNExportFormat.xsd            2 aebjjjjd-59de-4405-ac0b-50e33b0b4f4b-1       3354
#                          SSE..ExportID SSE..RunID             SSE..CreationTime                  SSE..StartTime
# 1 aeb5bf7d-59de-4405-er0b-50e33b0b4f4b   20430452 2015-12-21T13:55:00.807-05:00 2015-12-21T09:55:00.000-  05:00
# 2 aeb5bf7d-59de-4405-er0b-50e33b0b4f4b   20430452 2015-12-21T13:55:00.807-05:00 2015-12-21T09:55:00.000-  05:00
# 3 aeb5bf7d-59de-4405-er0b-50e33b0b4f4b   20430452 2015-12-21T13:55:00.807-05:00 2015-12-21T09:55:00.000-  05:00
#                    SSE..EndTime SSE.IRD..NumberINTVs              SSE.IRD..EndTime               SSE.IRD..StartTime
# 1 2015-12-21T13:55:00.000-05:00                    3 2015-12-21T12:00:00.000-05:00 2015-   12-21T09:00:00.000-05:00
# 2 2015-12-21T13:55:00.000-05:00                    3 2015-12-21T12:00:00.000-05:00 2015-   12-21T09:00:00.000-05:00
# 3 2015-12-21T13:55:00.000-05:00                    3 2015-12-21T12:00:00.000-05:00 2015-   12-21T09:00:00.000-05:00
#   SSE.IRD..INTVLength SSE.IRD.INTV..GatewayCollectedTime SSE.IRD.INTV..INTVSequenceNumber SSE.IRD.INTV..BlockSequenceNumber
# 1                  60      2015-12-21T12:05:02.257-05:00                            47112                                 0
# 2                  60      2015-12-21T12:05:02.257-05:00                            47113                                 0
# 3                  60      2015-12-21T12:05:02.257-05:00                            47114                                 0
#             SSE.IRD.INTV..EndTime SSE.IRD.INTV.INTVStatus   -U     -BEV -Val -RV -port   -U     -BEV -Val -RV -port   -U
# 1   2015-12-21T10:00:00.000-05:00                SERV_HST  kWh   0.0379    0   0     1  kWh   0.0379    0   0     1  kWh
# 2   2015-12-21T11:00:00.000-05:00                SERV_HST  kWh   0.0379    0   0     2  kWh   0.0379    0   0     2  kWh
# 3 2015-12-  21T12:00:00.000-05:00                SERV_HST Vrms 231.0000  231 231     3 Vrms 231.0000  231 231     3 Vrms
#       -BEV -Val -RV -port
# 1   0.0379    0   0     1
# 2   0.0379    0   0     2
# 3 231.0000  231 231     3

Collectives™ on Stack Overflow

JSON to CSV conversion using R jsonlite package

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