2

We have compiled a 2 column spreadsheet of SQL and Web servers that all talk to one other daily:

Source,Target  
sql1,web1  
sql1,web2  
sql2,web2  
sql2,sql1  
sql2,web3  
web2,web3  
web3,web2
web3,sql2

I want to create a 'pretty' and simple D3 graph, (not necessarily that one, even a static graph might do) from above data. Direction of flow is not that important. Each group (SQL or WEB) needs to be in a different color though.

However, D3 requires the data to be in json format.

How do I make the D3 code accept my 2 column data ?
or How do I convert my 2 column data into the JSON format that D3 code requires ? I'm comfortable writing T-SQL code btw if someone can guide.

d-_-b
  • 761
  • 2
  • 11
  • 26
  • the example you provided is consuming JSON Data. it has groups and links. can you please provide your JSON format that you require ? so conversion can be done. – murli2308 Feb 21 '16 at 05:46
  • that *is* my question - how do I convert my 2 column data into the JSON format which the example requires, or how do I make the example accept my 2 column data – d-_-b Feb 21 '16 at 06:00
  • the final JSON is not an issue that can engineered. How are you going to give the above data is it a csv or tsv ...or anything else? – Cyril Cherian Feb 21 '16 at 06:01
  • I can do any format - csv, tab separated, excel, sql table. I just added commas in my data above – d-_-b Feb 21 '16 at 06:02

1 Answers1

2

Our goal is to load your CSV and convert into a JSON structure expected by force Directed graph d3.

To do this:

  //load your CSV via ajax as below
  d3.csv("my.csv", function(json) {
  //load the csv as json
  //array of nodes
  var nodes = [];
  /array of links
  var links = [];
  json.forEach(function(d){
    //check if node is present if not add that in the array to get unique nodes.
    if (nodes.indexOf(d.Source.trim()) <0){
      //sorce node not there so add
      nodes.push(d.Source.trim())
    }
    if (nodes.indexOf(d.Target.trim()) <0){
      //target node not there so add
      nodes.push(d.Target.trim())
    }
    //link to map the nodes with its index.
    links.push({source:nodes.indexOf(d.Source.trim()), target:nodes.indexOf(d.Target.trim())})
  });
  nodes = nodes.map(function(n){
    return {name:n}
  })
  //now the usual code for force graph.

Now feed the nodes and links to the Force directed graph.

working code here

Cyril Cherian
  • 32,177
  • 7
  • 46
  • 55
  • 1
    Works great ! Do you know if there is a way to add color to each group in D3, like SQL in red and WEB in blue ? I tried looking at similar [question](http://stackoverflow.com/questions/17837463/d3-js-force-directed-graph-each-group-different-color), but couldn't figure it out – d-_-b Feb 22 '16 at 13:19
  • 1
    Like the example you referring, you don't have a group. So I am using node name to color the text . `node.append("text") .attr("dx", 12) .attr("dy", ".35em") .attr("fill", function(d){return color(d.name)})` fiddle here http://plnkr.co/edit/N4zUwNJOLptops23gWTd?p=preview – Cyril Cherian Feb 23 '16 at 06:59
  • where would i need to add the group info ? If I created another csv with server & group, would that work? or to make life easier, could I customize the 'color' function you're calling, that when passed the name parameter, would check if it is like '%sql%', then return 'red', if like 'web' then 'blue' ? – d-_-b Feb 24 '16 at 16:47
  • Yes the later part _would check if it is like '%sql%', then return 'red', if like 'web' then 'blue'_ is also a good idea , yes you may do that – Cyril Cherian Feb 25 '16 at 02:05