Convert R data.frame to a json array (source)

Question

I have this data.frame in R:

dataset <- data.frame(
  product = c('2012', '2013', '2014', '2015', '2016', '2017'),
  MilkTea = c(56.5, 82.1, 88.7, 70.1, 53.4, 85.1),
  MatchaLatte = c(51.1, 51.4, 55.1, 53.3, 73.8, 68.7),
  CheeseCocoa = c(40.1, 62.2, 69.5, 36.4, 45.2, 32.5),
  WalnutBrownie = c(25.2, 37.1, 41.2, 18, 33.9, 49.1)
)

I'm trying to leave it with this same (identical) structure:

dataset: {
  source: [
    ['product', '2012', '2013', '2014', '2015', '2016', '2017'],
    ['Milk Tea', 56.5, 82.1, 88.7, 70.1, 53.4, 85.1],
    ['Matcha Latte', 51.1, 51.4, 55.1, 53.3, 73.8, 68.7],
    ['Cheese Cocoa', 40.1, 62.2, 69.5, 36.4, 45.2, 32.5],
    ['Walnut Brownie', 25.2, 37.1, 41.2, 18, 33.9, 49.1]
  ]
}

I try this:

library(jsonlite)

toJSON(x = dataset, dataframe = "columns", pretty = TRUE)

"product": [2012, 2013, 2014, 2015, 2016, 2017],
"MilkTea": [56.5, 82.1, 88.7, 70.1, 53.4, 85.1],
"MatchaLatte": [51.1, 51.4, 55.1, 53.3, 73.8, 68.7],
"CheeseCocoa": [40.1, 62.2, 69.5, 36.4, 45.2, 32.5],
"WalnutBrownie": [25.2, 37.1, 41.2, 18, 33.9, 49.1]

But I would like to leave you with the first structure I put above. Also, how to insert this JSON created inside an HTML(...) something like:

HTML("<script>put JSON here</script>")

So that I can use it in a later analysis.

Answer 1

Your expected output is not valid json, which requires double rather than single quotes and also requires keys to appear in quotes (e.g. "source" rather than source).

I'll address the part of the question about how to create a json list which mixes strings and numeric values, e.g.:

["MilkTea",56.5,82.1,88.7,70.1,53.4,85.1]

This is valid json, though unusual. You will need to use an R list, rather than a vector, which if you mix strings and numeric type will coerce all values to character. For example:

lapply(
    names(dataset),
    \(nm, d = dataset) c(list(nm), d[[nm]])
) |>
    list(source = _) |>
    list(dataset = _) |>
    jsonlite::toJSON(auto_unbox = TRUE)

Output:

{
    "dataset":{
        "source":[
            ["product","2012","2013","2014","2015","2016","2017"],
            ["MilkTea",56.5,82.1,88.7,70.1,53.4,85.1],
            ["MatchaLatte",51.1,51.4,55.1,53.3,73.8,68.7],
            ["CheeseCocoa",40.1,62.2,69.5,36.4,45.2,32.5],
            ["WalnutBrownie",25.2,37.1,41.2,18,33.9,49.1]
        ]
    }
} 

The key is that a list is technically a list of vectors, so we must unbox the elements to ensure the final representation is an array of values, rather than an array of arrays of length one, when encoded into json.