How to write to json with children from R

Question

I want to turn an R data.frame into a JSON object in order to use it for preparing data visualizations with d3.js. I found a lot of questions that asked how to get JSON into R, but very few on how to write data from R to JSON.

A particular problem is that the JSON file needs to be nested by using factors, i.e. columns of the data.frame. I think that writing from nested lists could be a solution, but I already failed to create a nested list from a data.frame :(

I have preprared an example:

this represents my data.frame (called "MyData").

ID  Location Station   Size Percentage
1     Alpha    Zeta    Big       0.63
2     Alpha    Zeta Medium       0.43
3     Alpha    Zeta  small       0.47
4     Alpha    Yota    Big       0.85
5     Alpha    Yota Medium       0.19
6     Alpha    Yota  small       0.89
7      Beta   Theta    Big       0.09
8      Beta   Theta Medium       0.33
9      Beta   Theta  small       0.79
10     Beta    Meta    Big       0.89
11     Beta    Meta Medium       0.71
12     Beta    Meta  small       0.59

now, I want to turn it into something like this valid json format, including the children nodes:

   {
 "name":"MyData",
 "children":[
   {
     "name":"Alpha",
     "children":[
        {
           "name":"Zeta",
           "children":[
              {
                 "name":"Big",
                 "Percentage":0.63
              },
              {
                 "name":"Medium",
                 "Percentage":0.43
              },
              {
                 "name":"Small",
                 "Percentage":0.47
              }
           ]
        },
        {
           "name":"Yota",
           "children":[
              {
                 "name":"Big",
                 "Percentage":0.85
              },
              {
                 "name":"Medium",
                 "Percentage":0.19
              },
              {
                 "name":"Small",
                 "Percentage":0.89
              }
           ]
        }
    ]   
},
    {
     "name":"Zeta",
     "children":[
        {
           "name":"Big",
           "Percentage":0.63
        },
        {
           "name":"Medium",
           "Percentage":0.43
        },
        {
           "name":"Small",
           "Percentage":0.47
        }
     ]
  },
  {
     "name":"Yota",
     "children":[
        {
           "name":"Big",
           "Percentage":0.85
        },
        {
           "name":"Medium",
           "Percentage":0.19
        },
        {
           "name":"Small",
           "Percentage":0.89
        }
     ]
  }
  ]
 }

If anyone could help me out I would be very much grateful! thank you

IMO, this has nothing to do with JSON per se, just resemble this structure within R, and you're good to go. — aL3xa, Oct 10 '12 at 12:09
you are right, this is nothing JSON specifc. my question was mainly about how to get this specific structure. — Jens, Oct 10 '12 at 13:59

user1609452 · Accepted Answer · 2012-10-10T17:27:15.080

25

This is a recursive approach which is cleaner:

require(RJSONIO)

makeList<-function(x){
  if(ncol(x)>2){
    listSplit<-split(x[-1],x[1],drop=T)
    lapply(names(listSplit),function(y){list(name=y,children=makeList(listSplit[[y]]))})
  }else{
    lapply(seq(nrow(x[1])),function(y){list(name=x[,1][y],Percentage=x[,2][y])})
  }
}


jsonOut<-toJSON(list(name="MyData",children=makeList(MyData[-1])))
cat(jsonOut)

edited Oct 10 '12 at 17:27

answered Oct 10 '12 at 16:15

user1609452

4,406
1
15
20

awesome! Now it looks perfect. 100% valid json. In order to make d3js work with the output I had to change the "Percentage" to "size". But your approach is absolutely brilliant! I hope I can adjust / extend it. Billion thanks ! – Jens Oct 11 '12 at 12:57
2
This can be adapted to non regular files hierarchies (ie when the number of hierarchies is not constant) by adding a simple filter :
```
x2  <- dplyr::filter(x, x[1] != "") ; listSplit <- split(x2[-1], x2[1], drop = TRUE)
```
– PAC Aug 11 '14 at 16:31
@PAC exactly where does one drop in `x2 <- filter(x, x[1] != ""); listSplit <- split(x2[-1], x2[1], drop = TRUE)` right after `if(ncol(x)>2){` and leave everything else the same? – ben_says Feb 26 '16 at 19:59
would like to add that this approach works if the types of columns (except the last one) are not integer. If it is, the split based on `listSplit[[y]]` will likely be based on indices instead of column name. – chengvt Sep 14 '18 at 05:24

user1609452 · Answer 2 · 2012-10-10T14:43:48.310

Using a combination of split and subset may get what you want. For example

library(RJSONIO)
list1<-split(subset(MyData,select=c(-Location)),Mydata$Location)
list2<-lapply(list1,function(x){split(subset(x,select=c(-Station)),x$Station,drop=TRUE)})
list3<-lapply(list2,function(x){lapply(x,function(y){split(subset(y,select=c(-Size,-ID)),y$Size,drop=TRUE)})})
jsonOut<-toJSON(list(MyData=list3))
jsonOut1<-gsub('([^\n]*?): \\{\n "Percentage"','\\{"name":\\1,"Percentage"',jsonOut)
jsonOut2<-gsub('"([^"]*?)": \\{','"name":"\\1","children":\\{',jsonOut1)

cat(jsonOut2)
{
 "name":"MyData","children":{
 "name":"Alpha","children":{
 "name":"Yota","children":{
{"name": "Big","Percentage":   0.85 
},
{"name":"Medium","Percentage":   0.19 
},
{"name":"small","Percentage":   0.89 
} 
},
"name":"Zeta","children":{
{"name": "Big","Percentage":   0.63 
},
{"name":"Medium","Percentage":   0.43 
},
{"name":"small","Percentage":   0.47 
} 
} 
},
"name":"Beta","children":{
 "name":"Meta","children":{
{"name": "Big","Percentage":   0.89 
},
{"name":"Medium","Percentage":   0.71 
},
{"name":"small","Percentage":   0.59 
} 
},
"name":"Theta","children":{
{"name": "Big","Percentage":   0.09 
},
{"name":"Medium","Percentage":   0.33 
},
{"name":"small","Percentage":   0.79 
} 
} 
} 
} 
}

yes interesting! but here the variables `"Size"` and `"Percentage"` are separated. Each state of the variable, i.e. Big, should be linked to its Percentage value in a single row. Also the `"children"` agrument is missing. I will try to fiddle around with your idea of splitting lists. thx — Jens, Oct 10 '12 at 14:15
I added a split on `Size` and some ugly regex. Its not exactly what you want but it maybe close. — user1609452, Oct 10 '12 at 14:45
aaah now I understand how that works. Thank you. Although it is still not completely working I will vote you up :) The json validator is now complaining. I think the [] brackets are missing. I will try to fix this by adjusting your regex examples. — Jens, Oct 10 '12 at 15:42

score 0 · Answer 3 · answered May 04 '16 at 17:46

I am pigging backing off of user1609452's answer and answering the question about non regular file hierarchies. If you have a column where some data have children and some do not, use the following:

makeList<-function(x){ 
if(ncol(x)>2){
    listSplit<-split(x[-1],x[1],drop=T)
    lapply(names(listSplit),function(y){
        if(as.character(listSplit[[y]][1,1]) > 0){
            list(name=y,children=makeList(listSplit[[y]]))
        } else {
            list(name=y,size=listSplit[[y]][1,2])
        }
        })
}else{
    lapply(seq(nrow(x[1])),function(y){list(name=x[,1][y],size=x[,2][y])})
}
}

Basically we check if the current row has more children or if it simply needs to have size appended to it.

How to write to json with children from R

3 Answers3

Linked