javascript - Converting JSON format to CSV to upload data table in R to produce D3 bubble chart

Question

I am trying to use the D3 Bubble Chart in R to make my own bubbles chart with grouped bubble colours.

I have upload the index.html and the flare.json files from the D3 into R and it produced the bubble chart when run. But I didn't wanted to manually change this JSON code to create my own bubbles and groups (header below shows a set of 3 bubble groups with names for the different groups).

    {
     "name": "flare",
     "children": [
      {
       "name": "analytics",
       "children": [
        {
         "name": "cluster",
         "children": [
          {"name": "AgglomerativeCluster", "size": 3938},
          {"name": "CommunityStructure", "size": 3812},
          {"name": "HierarchicalCluster", "size": 6714},
          {"name": "MergeEdge", "size": 743}
         ]
        },
        {
         "name": "graph",
         "children": [
          {"name": "BetweennessCentrality", "size": 3534},
          {"name": "LinkDistance", "size": 5731},
          {"name": "MaxFlowMinCut", "size": 7840},
          {"name": "ShortestPaths", "size": 5914},
          {"name": "SpanningTree", "size": 3416}
         ]
        },
        {
         "name": "optimization",
         "children": [
          {"name": "AspectRatioBanker", "size": 7074}
         ]
        }
       ]

Using the jsonlite package (which from reading online can handle more complex json structures) I have converted to a dataframe.

 library(jsonlite)
 fromJSON("flare.json",simplifyDateframe = FALSE)

This is without the dataframe structure requested (example).

$children[[10]]$children[[6]]$children[[10]]
$children[[10]]$children[[6]]$children[[10]]$name
[1] "OperatorSwitch"

$children[[10]]$children[[6]]$children[[10]]$size
[1] 2581

This is with the dataframe structure requested (example).

 fromJSON("flare.json",simplifyDataFrame = TRUE)

However it produces a long concatenated list of data which I have been trying to untangle to automate with my data.

Arrays, Colors, Dates, Displays, Filter, Geometry, heap, IEvaluable,  IPredicate, IValueProxy, math, Maths, Orientation, palette, Property, Shapes, Sort, Stats, Strings, 8258, 10001, 8217, 12555, 2324, 10993, NA, 335, 383, 874, NA, 17705, 1486, NA, 5559, 19118, 6887, 6557, 22026, FibonacciHeap, HeapNode, 9354, 1233, DenseMatrix, IMatrix, SparseMatrix, 3165, 2815, 3366, ColorPalette, Palette, ShapePalette, SizePalette, 6367, 1229, 2059, 2291

Suggested solutions ...

FOR LOOPS (Time-restricted)

I have thought about writing multiple for loops to re-construct the JSON nest structure (which I am stronger at but I have a deadline and this may take a while). But I thought that someone who is more JSON savy might be able to help.

CSV CONVERTED FORMAT (doesn't work)

I also attempted to converted the flare.json file using JSON to CSV convertor to produce the CSV format needed to test whether I could update the content from the CSV directly to R but that didn't work (even with the addition of the flare.json header content that isn't automate from the jsonlite toJSON).

What I really need

A solution for converting the flare.json from JSON into a dataframe or table so I can upload my data with names, sizes and groups to convert back to JSON to produce my own bubble chart?

If possible it would be great to achieve this all in R, which I don't think is impossible but am happy to hear other suggestions.

I am quite stumped as what to do next. I normally deal with matrices in R so dealing with JSON lists and array is not my strong point.

Answer 1

This might provide us something else to think about. I'll put comments inline in the code. You can see a live example.

library(jsonlite)
library(dplyr)


flare_json <- rjson::fromJSON(  ## rjson just works better on these for me
    file = "http://bl.ocks.org/mbostock/raw/4063269/flare.json"
)

# let's have a look at the structure of flare.json
# listviewer htmlwidget might help us see what is happening
#   devtools::install_github("timelyportfolio/listviewer")
#   library(listviewer)
jsonedit(
  paste0(
    readLines("http://bl.ocks.org/mbostock/raw/4063269/flare.json")
    ,collapse=""
  )
)

# the interesting thing about Mike Bostock's Bubble Chart example
#   though is that the example removes the nested hierarchy
#    with a JavaScript function called classes
#// Returns a flattened hierarchy containing all leaf nodes under the root.
#function classes(root) {
#  var classes = [];
#  
#  function recurse(name, node) {
#    if (node.children) node.children.forEach(function(child) { recurse(node.name, child); });
#    else classes.push({packageName: name, className: node.name, value: node.size});
#  }
#  
#  recurse(null, root);
#  return {children: classes};
#}

# let's try to recreate this in R
classes <- function(root){
  classes <- data.frame()

  haschild <- function(node){
    (!is.null(node) && "children" %in% names(node))
  }

  recurse <- function(name,node){
    if(haschild(node)){
      lapply(
        1:length(node$children)
        ,function(n){
          recurse(node$name,node$children[[n]])
        }
      )
    } else {
      classes <<- bind_rows(
        classes,
        data.frame(
          "packageName"= name
          ,"className" = node[["name"]]
          ,"size" = node[["size"]]
          ,stringsAsFactors = F
        )
      )
    }
  }

  recurse(root$name,root)
  return(classes)
}

# now with a R flavor our class replica should work
flare_df <- classes(flare_json)


# so the example uses a data.frame with columns
#   packageName, className, size
# and feeds that to bubble.nodes where bubble = d3.layout.pack
# fortunately Joe Cheng has already made a htmlwidget called bubbles
#   https://github.com/jcheng5/bubbles
# that will produce a d3.layout.pack bubble chart

library(scales)

bubbles(
  flare_df$size
  ,flare_df$className
  ,color = col_factor(
    RColorBrewer::brewer.pal(9,"Set1")
    ,factor(flare_df$packageName)
  )(flare_df$packageName)
  ,height = 600
  ,width = 960
)

# it's not perfect with things such as text sizing
#    but it's a start

If you still think you want a nested d3 JSON hierarchy, here is some code.

#  convert this to nested d3 json format
#    this is example data provided in a comment to this post
df <- data.frame(
  "overallgroup" = "Online"
  ,"primarygroup" = c(rep("Social Media",3),rep("Web",2))
  ,"datasource" = c("Facebook","Twitter","Youtube","Website","Secondary Website")
  ,"size" = c(10000,5000,200,10000,2500)
  ,stringsAsFactors = FALSE
)


# recommend using data.tree to ease our pain here
#devtools::install_github("gluc/data.tree")
library(data.tree)

# the much easier way
df$pathString <- apply(df[,1:3],MARGIN=1, function(x){paste0(x,collapse="/")})
root <- as.Node(df[,4:5])    

# the harder manual way
root <- Node$new("root")
sapply(unique(df[,1]),root$AddChild)
apply(
  df[,1:ncol(df)]
  ,MARGIN = 1
  ,function(row){
    lapply(2:length(row),function(cellnum){
      cell <- row[cellnum]
      if( cellnum < ncol(df) ){ # assume last column is attribute
        parent <- Reduce(function(x,y){x$Climb(y)},as.character(row[1:(cellnum-1)]),root)
        if(is.null(parent$Climb(cell))){
          cellnode <- parent$AddChild( cell )
        }  
      } else{
        cellnode <- Reduce(function(x,y){x$Climb(y)},as.character(row[1:(cellnum-1)]),root)
        cellnode$Set( size = as.numeric(cell) )
      }
    })
  }
)


# now we should be able to supply root to networkD3
#   that expects a typical d3 nested JSON
#devtools::install_github("christophergandrud/networkD3")
library(networkD3)
treeNetwork( root$ToList(unname=TRUE) )

# or to get it in JSON
jsonlite::toJSON( root$ToList(unname=TRUE), auto_unbox=TRUE)