How to convert JSON into xlsx file using R

Question

I want to convert a JSON into xlsx file using R. Here is the scenario that I am expecting:

Here is my R object.

    myObject <- {
  "Alabama" : { 
                "Hoover": {
                           "x":[33.40556, 33.40565, 33.40555],
                           "y":[-86.81111, -86.81314, -86.81343]
                          },
                "Dothan": {
                           "x":[31.22722],
                           "y":[-85.40722]
                          },
                  ...
              },
  "Alaska" : {
               "Chugiak" : {
                            "x":[61.38778],
                            "y":[-149.48139]
                           },
               ...
             }
  ...
}

Here is the result that I want in xlsx:

Alabama Hoover 33.40556 -86.81111
Alabama Hoover 33.40565 -86.81314
Alabama Hoover 33.40555 -86.81343
Alabama Dothan 31.22722 -85.40722
Alabama Gadsden 34.01028 -86.01028
Alaska Chugiak 61.38778 -149.48139
...

P.S : Can I get the same result from an R object that is auto_unboxed such as:

myObject <- {
  "Alabama" : { 
                "Hoover": {
                           "x":[33.40556, 33.40565, 33.40555],
                           "y":[-86.81111, -86.81314, -86.81343]
                          },
                "Dothan": {
                           "x":31.22722,
                           "y":-85.40722
                          },
                  ...
              },
  "Alaska" : {
               "Chugiak" : {
                            "x":61.38778,
                            "y":-149.48139
                           },
               ...
             }
  ...
}

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Please don't include `...` in the sample data. That makes it non-reproducible. This is really two steps. First [convert to data frame](https://stackoverflow.com/questions/45038891/json-to-data-frame-in-r) then [export to excel](https://stackoverflow.com/questions/19414605/export-data-from-r-to-excel) which of which have existing answers. — MrFlick, Mar 26 '20 at 21:07

score 0 · Accepted Answer · answered Mar 26 '20 at 21:21

Try this:

myfunc <- function(o) {
  if ("x" %in% names(o)) {
    as.data.frame(o)
  } else {
    x <- lapply(o, function(z) myfunc(z))
    do.call(rbind.data.frame, Map(cbind.data.frame, nm=names(x), x))
  }
}
out <- myfunc(obj)
names(out) <- make.names(names(out), unique = TRUE)
out
#                       nm    nm.1        x          y
# Alabama.Hoover.1 Alabama  Hoover 33.40556  -86.81111
# Alabama.Hoover.2 Alabama  Hoover 33.40565  -86.81314
# Alabama.Hoover.3 Alabama  Hoover 33.40555  -86.81343
# Alabama.Dothan   Alabama  Dothan 31.22722  -85.40722
# Alaska            Alaska Chugiak 61.38778 -149.48139

The row names (Alabama.Hoover.3) are disposable. The repeat name columns should be unique (that's what make.names is doing, otherwise they would all be named "nm").

Data:

obj <- jsonlite::fromJSON('{
  "Alabama" : { 
                "Hoover": {
                           "x":[33.40556, 33.40565, 33.40555],
                           "y":[-86.81111, -86.81314, -86.81343]
                          },
                "Dothan": {
                           "x":[31.22722],
                           "y":[-85.40722]
                          }
              },
  "Alaska" : {
               "Chugiak" : {
                            "x":[61.38778],
                            "y":[-149.48139]
                           }
             } }')

How to convert JSON into xlsx file using R

1 Answers1