How to Write to JSON with Children from R

How to write to json with children from R

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)

R data frame to JSON Hierarchy

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%"
        }
      ]
    }
  ]
}

Writing JSON children in R

The following code should output what you want:

for (i in 1:nrow(df)){
  cat ("{company:{address:{adress:\t\"",df$address[i],
       "\",\n\t\tcity.x:\t\"", df$city.x[i],
       "\",\n\t\tstate.x:\t \"", df$state.x[i],
       "\"}}\n\t {geo:{\tlatitude: \"", df$latitude[i],
       "\",\n\t\tlongitude: \"", df$longitude[i],
       "\"}}},\n", sep="")
}

with df as your data frame.

Convert a relation SQL table into JSON using R

I'll start with a simple recursive solution that only gets parent/children with no other attributes.

#get some help from igraph
library(igraph)

df <- read.table(
  textConnection(
'
Parent     Children
a          b
b          c
c          d
b          e
e          f
' )
  , header = TRUE
  , stringsAsFactors = FALSE
)

el_in <- get.adjlist(graph.data.frame(df),mode="in")
# fill in name/id instead of number
el_in <- lapply(
  el_in,
  function(x){
    names(el_in)[x]
  }
)

get_children <- function( adjlist, node ){
  names(Filter(function(x) x==node,unlist(el_in)))
}

recurse_tree <- function( adjlist, node = NULL ){
  #  start at root if undefined
  #    root will be the node with no in
  if(is.null(node)) node <- names(Filter(function(x)length(x)==0,adjlist))

  children <- get_children( adjlist, node )
  if(length(children)>0){
    list(
      name = node
      ,children = lapply(
        children
        ,function(x){
          recurse_tree( adjlist, x )
        }
      )
    )
  } else {
    list(
      name = node
    )
  }
}


jsonlite::toJSON(
  recurse_tree( el_in ),
  auto_unbox=T
)

Probably not the most efficient, but here is a solution using data.tree and igraph to build our hierarchy.

#get some help from the relatively new data.tree
#devtools::install_github("gluc/data.tree")
library(data.tree)
#get some help from igraph
library(igraph)

df <- read.table(
  textConnection(
'
Parent     Children
a          b
b          c
c          d
b          e
e          f
' )
  , header = TRUE
  , stringsAsFactors = FALSE
)

#this will be our paths for data.tree
build_path <- function(df){
  g <- graph.data.frame(df)
  #get an adjacency list of all in
  el_in <- get.adjlist( g, mode="in" )
  tree <- lapply(el_in,function(x){""})
  lapply(
    1:length(el_in)
    ,function(n){
      id <- names(el_in)[n]
      x <- el_in[[n]]
      if(length(x)>0){
        tree[[id]] <<- paste0(
          tree[[el_in[[id]]]],
          "/",
          id
        )
      } else {
        tree[[id]] <<- id
      }
    }
  )
  return(unlist(tree))
}


tree <- as.Node(data.frame(
  pathString = build_path(df),
  # have the ability to specify values
  #  if not then just set NA
  value = NA,
  stringsAsFactors = F
))

jsonlite::toJSON(
  as.list( tree, mode="explicit", unname = TRUE),
  auto_unbox = TRUE
)

# as a test let's build a random tree with igraph
tree_grf <- graph.tree(n=10,children=3)
plot(tree_grf)
tree <- as.Node(data.frame(
  pathString = build_path(
    get.data.frame(tree_grf,what="edges")
  ),
  # have the ability to specify values
  #  if not then just set NA
  value = NA,
  stringsAsFactors = F
))

How to write a JSON object from R dataframe with grouping

You've got a little "non-standard" thing going with two keys of "value" (I don't know if this is legal json), as you can see here:

(js <- jsonlite::fromJSON('{"query":"1", "type":[{"name":"a", "values":[{"value":"x"}, {"value":"y"}]}, {"name":"c", "values":[{"value":"z"}]}]}'))
## $query
## [1] "1"
## 
## $type
##   name values
## 1    a   x, y
## 2    c      z

... with a data.frame cell containing a list of data.frames:

js$type$values[[1]]
##   value
## 1     x
## 2     y
class(js$type$values[[1]])
## [1] "data.frame"

If you can accept your "type" variable containing a vector instead of a named-list, then perhaps the following code will suffice:

jsonlite::toJSON(lapply(unique(dat[, 'A']), function(a1) {
    list(query = a1, 
         type = lapply(unique(dat[dat$A == a1, 'B']),  function(b2) {
             list(name = b2,
                  values = dat[(dat$A == a1) & (dat$B == b2), 'C'])
         }))
}))
## [{"query":[1],"type":[{"name":["a"],"values":["x","y"]},{"name":["c"],"values":["z"]}]},{"query":[2],"type":[{"name":["d"],"values":["p"]},{"name":["f"],"values":["q","r"]}]}] 

---

Related Topics