Convert R data frame to custom json

Question

I want to change a data frame to a custom json format at the end. The labels are created from the Date and Pressure column, with name being the value and column name being the group name and need.

Below is a way to generate the data frame being used to replicate this question

    df<-setNames(
  data.frame(
    t(data.frame(c("",1,2,0,20,"2b9c5fb7-72a4-4482-baa1-34cc37522d24","Yes","210521"),c("",2,3,20,40,"7f615aea-330f-4c67-8101-0ae501c793cd","No","210522")))
    ,row.names = NULL,stringsAsFactors = FALSE
  ), 
  c("notes","instanceNum","modifyCount","startTime","endTime","uniqueId","Pressure","Date")
)

Here is is the custom json format required. Any guidance appreciated

                    {
                        "labels": [
                            {
                                "name": "Yes",
                                "group": "Pressure"
                            },
                            {
                                "name": "210521",
                                "group": "Date"
                            }
                        ],
                        "notes": "",
                        "instanceNum": 1,
                        "modifyCount": 2,
                        "startTime": 0,
                        "endTime": 20,
                        "uniqueId": "2b9c5fb7-72a4-4482-baa1-34cc37522d24"
                    },
                    {
                        "labels": [
                            {
                                "name": "No",
                                "group": "Pressure"
                            },
                            {
                                "name": "210522",
                                "group": "Date"
                            }
                        ],
                        "notes": "",
                        "instanceNum": 2,
                        "modifyCount": 3,
                        "startTime": 20,
                        "endTime": 40,
                        "uniqueId": "7f615aea-330f-4c67-8101-0ae501c793cd"
                    }

Answer 1

Try these options:

base R

melted <- reshape2::melt(df[,c("instanceNum","Pressure","Date")], 
                         id.vars="instanceNum", variable.name="group", value.name="name")
melted$group <- as.character(melted$group)
# "nest" the other columns against a single row of "instanceNum"`
melted <- by(melted, melted[,"instanceNum"], function(z) {
  out <- out <- z[1,1,drop=FALSE]
  out$labels <- list(z[,-1])
  out
})
df2 <- merge(df[,setdiff(names(df), c("Pressure", "Date"))],
             do.call(rbind, melted), by = "instanceNum")
df2
#   instanceNum notes modifyCount startTime endTime                             uniqueId                      labels
# 1           1                 2         0      20 2b9c5fb7-72a4-4482-baa1-34cc37522d24 Pressure, Date, Yes, 210521
# 2           2                 3        20      40 7f615aea-330f-4c67-8101-0ae501c793cd  Pressure, Date, No, 210522

And the output from jsonlite::toJSON(df2, pretty = TRUE) is:

[
  {
    "instanceNum": "1",
    "notes": "",
    "modifyCount": "2",
    "startTime": "0",
    "endTime": "20",
    "uniqueId": "2b9c5fb7-72a4-4482-baa1-34cc37522d24",
    "labels": [
      {
        "group": "Pressure",
        "name": "Yes"
      },
      {
        "group": "Date",
        "name": "210521"
      }
    ]
  },
  {
    "instanceNum": "2",
    "notes": "",
    "modifyCount": "3",
    "startTime": "20",
    "endTime": "40",
    "uniqueId": "7f615aea-330f-4c67-8101-0ae501c793cd",
    "labels": [
      {
        "group": "Pressure",
        "name": "No"
      },
      {
        "group": "Date",
        "name": "210522"
      }
    ]
  }
] 

dplyr/tidyr

library(dplyr)
# library(tidyr) # pivot_longer, nest
df %>%
  select(instanceNum, Pressure, Date) %>%
  tidyr::pivot_longer(-instanceNum, names_to = "group", values_to = "name") %>%
  tidyr::nest(labels = c("group", "name")) %>%
  full_join(df, ., by = "instanceNum") %>%
  select(-Pressure, -Date) %>%
  jsonlite::toJSON(., pretty = TRUE)