Question 385236 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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.

Tags:

3

Answers


  1. 0

    @SamR’s solution is straight-forward, but for fun here is a solution using the jsoncons C++ library made available through the CRAN packages rjsoncons and cpp11

    Start by creating a JSON string as in the question

    json <- jsonlite::toJSON(dataset, dataframe = "columns")

    Write a C++ function that takes this string and transforms it to the desired format, using jsoncons documentation. Here’s what I have, in a file called ‘reshape.cpp’:

    #include "cpp11.hpp"
#include <jsoncons/json.hpp>

using namespace jsoncons;

[[cpp11::linking_to("rjsoncons")]] // where to find the jsoncons headers
[[cpp11::register]]
std::string reshape(const std::string& data) {
    auto j = ojson::parse(data); // parse JSON string to jsoncons object
    ojson array(json_array_arg); // allocate outer array

    // create each inner array as key + values
    for (const auto& member : j.object_range()) {
        ojson elt(json_array_arg);   // allocate inner array
        elt.push_back(member.key()); // push back key & all values
        auto rng = member.value().array_range();
        elt.insert(elt.array_range().end(), rng.begin(), rng.end());
        array.push_back(elt);        // add element to array-of-arrays
    }

    // decode and return as string
    std::string result;
    array.dump(result);
    return result;
}

    Compile this in R with cpp11::cpp_source(), and use the result

    cpp11::cpp_source("reshape.cpp")
dataset |>
    jsonlite::toJSON(dataframe = "columns") |>
    reshape()

    The output is

    [["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]]
    Login or Signup to reply.
  2. 0

    Maybe you want this.

    > jsonlite::toJSON(list(source=t(rbind(names(dataset), dataset))), pretty=TRUE) |> 
+   {(.) htmltools::HTML(
+     sprintf('<script type="application/json" id="data">%s</script>', .)
+   )}()
<script type="application/json" id="data">{
  "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"]
  ]
}</script>
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search