Transform dataframe into JSON object with jsonlite using R

Question

I am trying to transform a data.frame into a JSON object, I include the structure of the data table, the R code that uses the toJSON () function of the jsonlite package, the obtained result and the expected result. I have done various joins in the function, but I can't find the solution.

The data.frame has this structure, I have summarized it so as not to dump all the data:


structure(list(`Código Municipio INE` = c("02003", "03014"), Municipio = c("Albacete", "Alicante/Alacant"), `Esfuerzo Social por Habitante` = c(66.255518296917, 
55.4505971732305), `Relevancia Esfuerzo Social` = c(0.0779438999416286, 
0.075567624299619), `Umbral Pobreza` = c(21, 
28.8), `Umbral Pobreza Hombres` = c(20.5, 29), `Umbral Pobreza Mujeres` = c(21.5, 28.6)), class = "data.frame", row.names = c(NA, 
-2L))

I use jsonlite for the transformation:


toJSON( 
 list(
  list(
   name = dfon$Municipio,
   data = list(
    dfon[, which(colnames(dfon) != "Municipio")],
    x = dfon$`Esfuerzo Social por Habitante`,
    y = dfon$`Umbral Pobreza`,
    size = dfon$`Relevancia Esfuerzo Social`
   )
  )
 ), pretty=TRUE)

The result I get is this:


[
  {
    "name": ["Albacete", "Alicante/Alacant"],
    "data": {
      "1": [
        {
          "Código Municipio INE": "02003",
          "Esfuerzo Social por Habitante": 66.2555,
          "Relevancia Esfuerzo Social": 0.0779,
          "Umbral Pobreza": 21,
          "Umbral Pobreza Hombres": 20.5,
          "Umbral Pobreza Mujeres": 21.5
        },
        {
          "Código Municipio INE": "03014",
          "Esfuerzo Social por Habitante": 55.4506,
          "Relevancia Esfuerzo Social": 0.0756,
          "Umbral Pobreza": 28.8,
          "Umbral Pobreza Hombres": 29,
          "Umbral Pobreza Mujeres": 28.6
        }
      ],
      "x": [66.2555, 55.4506],
      "y": [21, 28.8],
      "size": [0.0779, 0.0756]
    }
  }
]

But I need a format like this, that is, for each record there must be two keys, name and data:


[
  {
    "name":"Albacete",
    "data":[
      {
        "Código Municipio INE":"02003",
        "Municipio":"Albacete",
        "Esfuerzo Social por Habitante":66.255518296917,
        "Relevancia Esfuerzo Social":0.0779438999416286,
        "Umbral Pobreza":21,
        "Umbral Pobreza Hombres":20.5,
        "Umbral PobrezaMujeres":21.5,
        "x":66.255518296917,
        "y":21,
        "size":7.79438999416286,
      }
    ]
  },

  {
    "name":"Alicante/Alacant",
    "data":[
      {
        "Código Municipio INE":"03014",
        "Municipio":"Alicante/Alacant",
        "Esfuerzo Social por Habitante":55.4505971732305,
        "Relevancia Esfuerzo Social":0.075567624299619,
        "Umbral Pobreza":28.8,
        "Umbral Pobreza Hombres":29,
        "Umbral Pobreza Mujeres":28.6,
        "x":55.4505971732305,
        "y":28.8,
        "size":7.5567624299619,
      }
    ]
  }
]

Would it better for you to first split your dataframe into lists by name, and then turn those into JSON? — mhovd
– mhovd, Commented Apr 16, 2021 at 15:54

Onyambu · Accepted Answer · 2021-04-16 16:03:49Z

2

You could do:

df<- data.frame(name = d$Municipio)
df$data<- split(d, d$Municipio)
jsonlite::toJSON(df)

answered Apr 16, 2021 at 16:03

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

akrun · Accepted Answer · 2021-04-16 17:25:33Z

0

We can use group_split

library(dplyr)
library(jsonlite)
df %>%
    group_split(Municipio) %>%
    toJSON

answered Apr 16, 2021 at 17:25

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Transform dataframe into JSON object with jsonlite using R

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related