convert xlsx to json using R

Question

I first pulled apart the xlsx file, which consisted of multiple sheets.

# install.packages("readxl")

library(readxl)
library("rjson")

# read_excel reads
df1 <- read_excel("C:/Users/Adminstrator/Downloads/file.xlsx", sheet = 1)
df2 <- read_excel("C:/Users/Adminstrator/Downloads/file.xlsx", sheet = 2)
df3 <- read_excel("C:/Users/Adminstrator/Downloads/file.xlsx", sheet = 3)

The contents of df are as follows.

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
...

I want to replace this xlsx file with json.

{
  "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 can I change the xlsx file to json? Please help me. thanks.

Your data is not an excel but a data.frame (or some variant of it). You can use `toJSON` function from `rjson` or `jsonlite`. e.g. https://stackoverflow.com/questions/25550711/convert-data-frame-to-json — Roman Luštrik, Nov 11 '17 at 09:28
that's not going to put it into the desired format @RomanLuštrik. The OP wants to have data put into a custom format without doing any work at all. From other q's I don't think R or python is their primary language. — hrbrmstr, Nov 11 '17 at 11:54
You can change you data frames to that desired format by programming. Grouping/iterating by unique values in the first column then second column and build JSON strings from them. You've not even shown an attempt and where you had issues. — hrbrmstr, Nov 11 '17 at 11:58

score 4 · Accepted Answer · answered Nov 11 '17 at 13:30

The split function of data.table is highly useful here.

dd <- data.frame(
  state = c("Alabama", "Alabama", "Alabama", "Alsaka"),
  city = c("Hoover", "Hoover", "Dothan", "Chugiak"),
  x = c(1, 2, 3, 4),
  y = c(5, 6, 7, 8), 
  stringsAsFactors=FALSE
)

library(data.table)
dt <- as.data.table(dd)
dt_split <- split(dt, by=c("state", "city"), keep.by=FALSE, flatten=FALSE)

You get:

> dt_split
$Alabama
$Alabama$Hoover
   x y
1: 1 5
2: 2 6

$Alabama$Dothan
   x y
1: 3 7


$Alsaka
$Alsaka$Chugiak
   x y
1: 4 8

Now use jsonlite:

> library(jsonlite)
> toJSON(dt_split, dataframe = "columns", pretty=TRUE)
{
  "Alabama": {
    "Hoover": {
      "x": [1, 2],
      "y": [5, 6]
    },
    "Dothan": {
      "x": [3],
      "y": [7]
    }
  },
  "Alsaka": {
    "Chugiak": {
      "x": [4],
      "y": [8]
    }
  }
}

I really appreciate JSON errors (dt_split, dataframe = "columns", pretty = TRUE). Unused arguments (dataframe = "columns", pretty = TRUE) This error has occurred. Why this? — Cian, Nov 12 '17 at 09:25

convert xlsx to json using R

1 Answers1