R data frame to JSON Hierarchy

Question

I have the following data frame:

fakedat<-data.frame(name=c("Holdingcompany","Holdingcompany","company1","company1","company2","company2"),children=c("company1","company2","company4","company3","company5","company6"),info=c("text1","text2","text3","text5","othertext","other_text"),percentage=c("100%","100%","60%","75%","80%","70%"))

The output I am hoping is the following:

  {"name": "Holdingcompany",
  "children": [
    {
      "name": "company1",
      "tooltip": "text1",
      "percentage": "100%",
      "children": [
        {
          "name": "company4",
          "tooltip": "text3",
          "percentage": "60%"
        },
        {
          "name": "company3",
          "tooltip": "text5",
          "percentage": "75%"
        }
      ]
    },
    {
      "name": "company2",
      "tooltip": "text2",
      "percentage": "100%",
      "children": [
        {
          "name": "company5",
          "tooltip": "othertext",
          "percentage": "80%"
        },
        {
          "name": "company6",
          "tooltip": "other_text",
          "percentage": "70%"
        }
      ]
    }
  ]
}

I have attempted a couple different methods of parsing including: How to write to json with children from R

But unfortunately I wasn't able to apply the above code to this situation properly to the the children in lists the way I was hoping.

Attempting to apply some of the solution from the below mentioned possible duplicate, I'm running into a recursion error: "C stack usage too close to limit" as the function appears to call itself.

##Adding in IDs
fakedat<-data.frame(id=c(1,2,3,4,5,6),name=c("Holdingcompany","Holdingcompany","company1","company1","company2","company2"),
           children=c("company1","company2","company4","company3","company5","company6"),
           info=c("text1","text2","text3","text5","othertext","other text"),
           percentage=c("100%","50%","60%","75%","80%","70%"))

 get_node <- function(df, id) {
 node <- as.list(df[df$id == id, c("name", "info", 
 "percentage","id")])
 names(node) = c("name", "info", "percentage","id")
 id1<-df[df$id==id,]$children
 if (!is.na(id1)){
 child1 <- get_node(df, id)
 if(child1$name == node$name){
 node$children <- list(child1)}
 node
 }
 }
    
 jsonlite::toJSON(get_node(fakedat,6), pretty = TRUE, auto_unbox = 
 TRUE)`

 Error: C stack usage  7972496 is too close to the limit

Answer 1

Consider preparing the relationships of parent to child with merge, then walk down each level of root / parent / child to build nested lists with nested lapply:

Data Preparation

### MERGE DATA
merge_df <- merge(fakedat, fakedat, by.x="children", by.y="name")
merge_df
#   children           name info.x percentage.x children.y     info.y percentage.y
# 1 company1 Holdingcompany  text1         100%   company4      text3          60%
# 2 company1 Holdingcompany  text1         100%   company3      text5          75%
# 3 company2 Holdingcompany  text2         100%   company5  othertext          80%
# 4 company2 Holdingcompany  text2         100%   company6 other_text          70%

nested_df <- unique(merge_df[c("children", "name", "info.x", "percentage.x")])
nested_df
#   children           name info.x percentage.x
# 1 company1 Holdingcompany  text1         100%
# 3 company2 Holdingcompany  text2         100%

top_level_val <- unique(merge_df$name)
top_level_val
# [1] "Holdingcompany"

JSON Build

output <- lapply(top_level_val, function(root) {  
  root_lst <- list(
    name = root
  )
  
  root_lst$children <- lapply(1:nrow(nested_df), function(i) {
    chld_mrg <- merge(nested_df[nested_df$children == nested_df$children[i],], merge_df)
    
    parent_lst <- list(
      name = nested_df$children[i][1],
      tooltip = nested_df$info.x[i][1],
      percentage = nested_df$percentage.x[i][1]
    )
    
    parent_lst$children <- lapply(1:nrow(chld_mrg), function(j) 
      list(
        name = merge_df$children.y[j][1],
        tooltip = merge_df$info.y[j][1],
        percentage = merge_df$percentage.y[j][1]
      )
    )
    
    return(parent_lst)
  })
  
  return(root_lst)
})

# CONVERT TO JSON STRING
jdata <- toJSON(output[[1]], pretty=TRUE, auto_unbox=TRUE)

# WRITE TO DISK
fileConn <- file("NestParentChildJSON.json")
  writeLines(jdata, fileConn)
close(fileConn)

Output

{
  "name": "Holdingcompany",
  "children": [
    {
      "name": "company1",
      "tooltip": "text1",
      "percentage": "100%",
      "children": [
        {
          "name": "company4",
          "tooltip": "text3",
          "percentage": "60%"
        },
        {
          "name": "company3",
          "tooltip": "text5",
          "percentage": "75%"
        }
      ]
    },
    {
      "name": "company2",
      "tooltip": "text2",
      "percentage": "100%",
      "children": [
        {
          "name": "company4",
          "tooltip": "text3",
          "percentage": "60%"
        },
        {
          "name": "company3",
          "tooltip": "text5",
          "percentage": "75%"
        }
      ]
    }
  ]
}