We often have rectangular dataframes in our daily data analyses. When we want to move to web based things (e.g. interactive data visualisation), we’ll likely have to have our data in JSON format.

This document explores the way the a dataframe can be converted to JSON. There 3 different JSON outputs from a dataframe in R.

Libraries

library(tidyverse)
library(jsonlite)

Dummy data

We’ll pick a few rows and columns from mtcars

mtcars2 <- 
  as_tibble(mtcars, rownames = 'model') %>% 
  slice(1:4) %>% 
  select(model, mpg, cyl, disp)

mtcars2
# A tibble: 4 x 4
  model            mpg   cyl  disp
  <chr>          <dbl> <dbl> <dbl>
1 Mazda RX4       21       6   160
2 Mazda RX4 Wag   21       6   160
3 Datsun 710      22.8     4   108
4 Hornet 4 Drive  21.4     6   258

Convert to JSON

Three different ways to parse to json, by;

  1. Values of the rows without any other information (names of columns, the ‘keys’)
  2. Columns of the data, the key the column name
  3. Rows of the data, where each value in the row has it’s associated column name.

Let’s have a look.

Values

An array with each row an array.

toJSON(x = mtcars2, dataframe = 'values', pretty = T)
[
  ["Mazda RX4", 21, 6, 160],
  ["Mazda RX4 Wag", 21, 6, 160],
  ["Datsun 710", 22.8, 4, 108],
  ["Hornet 4 Drive", 21.4, 6, 258]
]

Columns

An object, with key:value pairs, where the key is the column name and the values are in an array.

toJSON(x = mtcars2, dataframe = 'columns', pretty = T)
{
  "model": ["Mazda RX4", "Mazda RX4 Wag", "Datsun 710", "Hornet 4 Drive"],
  "mpg": [21, 21, 22.8, 21.4],
  "cyl": [6, 6, 4, 6],
  "disp": [160, 160, 108, 258]
}

Rows

An object with objects containing key:value pairs, where each key:value pair is a row from the dataframe and it’s associated column names. Seems to have a lot of repeated text.

I think most javascript packages expect this format

toJSON(x = mtcars2, dataframe = 'rows', pretty = T)
[
  {
    "model": "Mazda RX4",
    "mpg": 21,
    "cyl": 6,
    "disp": 160
  },
  {
    "model": "Mazda RX4 Wag",
    "mpg": 21,
    "cyl": 6,
    "disp": 160
  },
  {
    "model": "Datsun 710",
    "mpg": 22.8,
    "cyl": 4,
    "disp": 108
  },
  {
    "model": "Hornet 4 Drive",
    "mpg": 21.4,
    "cyl": 6,
    "disp": 258
  }
]