R Convert Dataframe to JSON

R convert dataframe to JSON

How about

library(rjson)
x <- toJSON(unname(split(res1, 1:nrow(res1))))
cat(x)
# [{"id":1,"value":"server1"},{"id":2,"value":"server2"},
# {"id":3,"value":"server3"},{"id":4,"value":"server4"},
# {"id":5,"value":"server5"}]

By using split() we are essentially breaking up the large data.frame into a separate data.frame for each row. And by removing the names from the resulting list, the toJSON function wraps the results in an array rather than a named object.

Convert data frame to json in R

I can give a very specific solution, but to generalise will take more work, as its a strange format to me. Try this out, and adapt to make more robust accordingly e.g. the year going from length 8 to length 4.

# I find data.table easier
library(data.table)
dt <- as.data.table(df)

# manually can do it, find levels of L, split data and name
L <- unique(df$ArtId)
ArtList <- lapply(L, function(x){
  x.dt <- dt[ArtId == x, .(IndexBV, SE)]
  x.dt
})
names(ArtList) <- L

# combine in to one list
X <- list(Year=unique(dt$Year),
          ArtId = ArtList)

# convert to Json, need either "null" or "string" for na, else dropped
jsonlite::toJSON(X, pretty = TRUE, na="null")

R convert data.frame to json

If you nest b as a list column, it will convert correctly:

library(jsonlite)

# converts b to nested list column
df2 <- aggregate(b ~ a, df, list)

df2
##       a          b
## 1 Luigi 49, 42, 37
## 2 Mario 46, 50, 45

toJSON(df2, pretty = TRUE)
## [
##   {
##     "a": "Luigi",
##     "b": [49, 42, 37]
##   },
##   {
##     "a": "Mario",
##     "b": [46, 50, 45]
##   }
## ] 

or if you prefer dplyr:

library(dplyr)

df %>% group_by(a) %>% 
    summarise(b = list(b)) %>% 
    toJSON(pretty = TRUE)

or data.table:

library(data.table)

toJSON(setDT(df)[, .(b = list(b)), by = a], pretty = TRUE)

which both return the same thing.

Converting R data frame to JSON while separating each row into a new JSON object

Here is a way:

L <- list(list(traits = as.list(df[1,])), 
          list(traits = as.list(df[2,])),
          list(traits = as.list(df[3,])))

---

> toJSON(L, pretty = TRUE, auto_unbox = TRUE)
[
  {
    "traits": {
      "color": "red",
      "fruit": "apple",
      "animal": "cat"
    }
  },
  {
    "traits": {
      "color": "blue",
      "fruit": "orange",
      "animal": "dog"
    }
  },
  {
    "traits": {
      "color": "green",
      "fruit": "grape",
      "animal": "chicken"
    }
  }
] 

To get this list L you can do

L <- apply(df, 1, function(x) list(traits = as.list(x)))

Another way is:

df2 <- purrr::transpose(lapply(df, function(x) as.character(x)))
L <- lapply(df2, function(x) list(traits = x))

or

df <- data.frame(color, fruit, animal, stringsAsFactors = FALSE)
L <- lapply(purrr::transpose(df), function(x) list(traits = x))

R convert dataframe to a nested json file/object grouped by column names

Here's a function that seems to work. The only glitch is if there is a possible collision, e.g., allergies.pet and allergies.pet.car; while it does not error, it may be non-standard.

New data:

df <- read.csv(textConnection(
"id,name,allergies.pollen,allergies.pet,attributes.height,attributes.gender,allergies.pet.cat
x,alice,no,yes,175,female,quux
y,bob,yes,yes,180,male,unk"))

The function:

func <- function(x) {
  grps <- split(names(x), gsub("[.].*", "", names(x)))
  for (nm in names(grps)) {
    if (length(grps[[nm]]) > 1 || !nm %in% grps[[nm]]) {
      x[[nm]] <- setNames(subset(x, select = grps[[nm]]),
                          gsub("^[^.]+[.]", "", grps[[nm]]))
      x[,setdiff(grps[[nm]], nm)] <- NULL
    }
  }
  for (nm in names(x)) {
    if (is.data.frame(x[[nm]])) {
      x[[nm]] <- func(x[[nm]])
    }
  }
  if (any(grepl("[.]", names(x)))) func(x) else x
}

See how this nests all .-delimited columns into frames:

str(df)
# 'data.frame': 2 obs. of  7 variables:
#  $ id               : chr  "x" "y"
#  $ name             : chr  "alice" "bob"
#  $ allergies.pollen : chr  "no" "yes"
#  $ allergies.pet    : chr  "yes" "yes"
#  $ attributes.height: int  175 180
#  $ attributes.gender: chr  "female" "male"
#  $ allergies.pet.cat: chr  "quux" "unk"
newdf <- func(df)
str(newdf)
# 'data.frame': 2 obs. of  4 variables:
#  $ id        : chr  "x" "y"
#  $ name      : chr  "alice" "bob"
#  $ allergies :'data.frame':   2 obs. of  2 variables:
#   ..$ pollen: chr  "no" "yes"
#   ..$ pet   :'data.frame':    2 obs. of  2 variables:
#   .. ..$ pet: chr  "yes" "yes"
#   .. ..$ cat: chr  "quux" "unk"
#  $ attributes:'data.frame':   2 obs. of  2 variables:
#   ..$ height: int  175 180
#   ..$ gender: chr  "female" "male"

From here, it's straight-forward to jsonify:

jsonlite::toJSON(newdf, pretty = TRUE)
# [
#   {
#     "id": "x",
#     "name": "alice",
#     "allergies": {
#       "pollen": "no",
#       "pet": {
#         "pet": "yes",
#         "cat": "quux"
#       }
#     },
#     "attributes": {
#       "height": 175,
#       "gender": "female"
#     }
#   },
#   {
#     "id": "y",
#     "name": "bob",
#     "allergies": {
#       "pollen": "yes",
#       "pet": {
#         "pet": "yes",
#         "cat": "unk"
#       }
#     },
#     "attributes": {
#       "height": 180,
#       "gender": "male"
#     }
#   }
# ] 

How to convert R dataframe to Json in name/value pair?

Solution using rjson:

df <- data.frame(Values = c(123, 4565, 7812))
rownames(df) <- paste0("Type", 1:3)

library(rjson)
toJSON(setNames(df$Values, rownames(df)))

[1] "{\"Type1\":123,\"Type2\":4565,\"Type3\":7812}"

Convert columns of R dataframe to JSON

This solution ultimately worked for me. Thanks to everyone who contributed.

df_1 <- df %>%
  rowwise() %>%
  dplyr::mutate(
    options_json = ifelse(!is.na(option_value_3),
      paste(toJSON(setNames(list(option_label_1 = paste(option_value_1), option_label_2 = paste(option_value_2), option_label_3 = paste(option_value_3)), c(option_label_1, option_label_2, option_label_3)), auto_unbox = T)),
      ifelse(!is.na(option_value_2),
        paste(toJSON(setNames(list(option_label_1 = paste(option_value_1), option_label_2 = paste(option_value_2)), c(option_label_1, option_label_2)), auto_unbox = T)),
        paste(toJSON(setNames(list(option_label_1 = paste(option_value_1)), option_label_1), auto_unbox = T))
      )
    )
  )

Convert DataFrame to JSON with single array

You can do:

library(jsonlite)
toJSON(list(nameofdata = df), pretty = TRUE)

which gives:

{
  "nameofdata": [
    {
      "areaId": "abc123",
      "time": "2022-01-30 00:00:00",
      "value": 10
    },
    {
      "areaId": "abc123",
      "time": "2022-01-30 00:05:00",
      "value": 10
    },
    {
      "areaId": "abc123",
      "time": "2022-01-30 00:10:00",
      "value": 10
    }
…
  ]
}

---

Related Topics