R creating a list of JSON's from a DF

user_6075349 0
Viewed 61

I have a data.frame like the following:

rnum  Field  Value  
1        Car   25.2
1        Bike 20.1
1        Taxi  100.1
2        Car   25.5   
2        Bike  19.1
2        Taxi  25.7

I want to create a list of JSON such that, each element of the list is a JSON object is grouped by rnum. Essentially one element of the list should look like this:

{"Car":25.2,"Bike":20.1,"Taxi":100.1,"rnum":"1"}

I tried df %>%group_by(rnum)%>%toJSON() but it yields 3 JSON entries for each row. Thanks!

r json
3 Answers

After group_split with 'rnum', loop over the list with map, create a named list, apply the toJSON and flatten it to a single character vector

library(dplyr)
library(purrr)
library(jsonlite)
library(tibble)
out <- df1 %>%
    group_split(rnum, .keep = FALSE) %>% 
    map_chr( ~ toJSON(as.list(deframe(.x)), auto_unbox = TRUE))

-output

cat(out, '\n')
#{"Car":25.2,"Bike":20.1,"Taxi":100.1} {"Car":25.5,"Bike":19.1,"Taxi":25.7}

Or using xtabs from base R

toJSON(as.data.frame.matrix(xtabs(Value ~ rnum + Field, df1)))
#[{"Bike":20.1,"Car":25.2,"Taxi":100.1},{"Bike":19.1,"Car":25.5,"Taxi":25.7}]

Or with data.table

library(data.table)
toJSON(dcast(setDT(df1), rnum ~ Field))

data

df1 <- structure(list(rnum = c(1L, 1L, 1L, 2L, 2L, 2L), Field = c("Car", 
"Bike", "Taxi", "Car", "Bike", "Taxi"), Value = c(25.2, 20.1, 
100.1, 25.5, 19.1, 25.7)), class = "data.frame", row.names = c(NA, 
-6L))
user_37322710

Widen it first:

df %>%
  tidyr::pivot_wider(rnum, names_from="Field", values_from="Value") %>%
  jsonlite::toJSON()
# [{"rnum":1,"Car":25.2,"Bike":20.1,"Taxi":100.1},{"rnum":2,"Car":25.5,"Bike":19.1,"Taxi":25.7}]
user_33582720

Here is another base R option using reshape, e.g.,

gsub(
  "Value\\.",
  "",
  toJSON(reshape(df, direction = "wide", idvar = "rnum", timevar = "Field"))
)

which gives

[{"rnum":1,"Car":25.2,"Bike":20.1,"Taxi":100.1},{"rnum":2,"Car":25.5,"Bike":19.1,"Taxi":25.7}]
user_121587570
Related