Mapping arrays from csv file for tree hierarchy visualization using NodeJS

Question

I'm trying to show the relationship between SQL tables in visualization. I have three columns in a csv sheet (columns: Target, Source, JoinSource).

Column Target has a table name in each cell, say A1, A2, A3....... An.

Column Source has arrays with multiple elements. The elements have an index as a prefix. A sample array would look like: (I've changed the actual data to dummy data and all these elements are actually SQL tables)

[P1 Apple, P2 Mango, P2.1 Pluto, P3.1.1 Earth... P10 Red, P10.1 Blue, P10.1.1 Copper]

The structure of Column JoinSource is similar to Source but with different elements. A sample array from JoinSource would like:

[P3 Orange, P2.2 Charlie, P1.1 Mushroom, P7 Cyclone, P7.1 Hurricane.... P10.2 Typhoon]

Every table has a alphanumerical prefix. The prefix P is just an arbitrary variable used for simplicity purpose, so we can safely ignore it.

The numerical prefixes 1, 2, 2.1, 10.1.1 denote the relationship between tables. If it's a whole number then it is directly connected to the table in column Target. If there's a decimal then it is directly connected to the table either in Source or JoinSource.

To put simply, A1 is the parent table - P1 Apple is the child of A1; and P1.1 Mushroom is the child of P1 Apple.

Similarly, P10 Red is the child of A1; P10.1 Blue is the child of P10 Red; P10.1.1 Copper is the child of P10.1 Blue.

The parent/child relationship depends on the number of decimal places in the index. If there's no decimal it's straight forward. If there's one decimal then it's connected to the table with the same whole number as prefix; if there are two decimals then it's connected to the table with same whole number plus one decimal as prefix.

I hope the above explanation is clear. Now I need to use some logic in NodeJS (for loop, if loop etc) and make the parent-child mapping of tables. Any help is much appreciated.

The data from csv sheet would look like this.

|------------|------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------|
|    Target  |                           Source                                 |           JoinSource                                                                                                |
|------------|------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------|
| Fenugreek  | P8 Sirocco, P8.1 Merlin, P9.1 Cancun, P10.1 Force, P11.2 Torque  | P1 Tiger, P2 Lion, P3 Train, P4 Giraffe, P5 Bear, P6 Javelin, P7 Mingo, P8 Mavue, P9 Violet, P10 Jupiter, P11 Pluto |
|------------|------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------| 
| Chernobyl  | P1 Moro, P2 Cher, P2.1 Rona, P2.2 Mason, P3 Tonga, P4.1 Nagatom  | P1.1 Eba, P2.3 Van, P3.1 Gomin, P4 Evaum, P4.2 Hun                                                                  |
|------------|------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------|

One thing to note from the above table is, there is a P1 in the first row and one more P1 in the second row. These two are different. Each row is independent of each other and the visualization is different for each row as well.

And I need the table names in the visualization, not the indexes. The indexes are only for mapping purpose. For instance, nodes in the tree diagram should be more like Apple, Pluto, Earth etc, not P1, P2.1, P3.1.1.

The final visualization output should be something like this.

My thinking is that may be we can check for number of decimal points in the prefix using a function and then loop it. — learningsql
– learningsql, Commented May 28, 2020 at 10:55
Would it be possible to share the three tables and the desired output? — Tanya Gupta
– Tanya Gupta, Commented May 30, 2020 at 13:11
Click here for a pictorial representation of the desired output. Once you have a look at the visualization image, it's easy to understand the problem. — learningsql
– learningsql, Commented May 30, 2020 at 16:14
You don't have to do the mapping manually, as d3 has functions to do it for you. I'm still not sure that this will work though, because your data appears to have "holes" in it from the samples you've shown. And you'd have to decide how you want to fill in those holes. — user120242
– user120242, Commented Jun 2, 2020 at 3:50

user120242 · Accepted Answer · 2020-06-02 03:32:27Z

Note: your original sample data had missing nodes in the tree hierarchy, which I've filled in manually.

I've transformed the data into the form {id, parentId, name} where P3.1.1 will emit {id: "P3.1.1", parentId: "P3.1", name: "Earth"}. This can be fed to d3.stratify that will build the hierarchy for you. I also a {id: "P", name: "Target"} for the root node.
d3-stratify:
https://github.com/d3/d3-hierarchy/blob/master/README.md#stratify
demo

I used this demo for hierarchy with a few adjustments to build the SVG tree: d3-hierarchy (demo)
label was changed to extract name property by default instead of id, and I've embedded the dimensions directly into the function.

It uses d3 Tidy Tree (demo) to render a d3-hierarchy.

data = `[P1 Apple, P2 Mango, P2.1 Pluto, P3.1.1 Earth, P10 Red, P10.1 Blue, P10.1.1 Copper, P3 PewPewPew, P3.1 Chopper, P3.2 Twenty, P3.1.2 Two]`
.slice(1,-1).split(', ')
.map(x=>x.match(/^((.*?)(?:\.)?(?:\d*)?) (.*)$/).slice(1))
.map(([id, parentId, name])=>({id, parentId, name}))

data.push({id: 'P', name:'Target'})

document.body.appendChild(graph(d3.stratify()(data)))

function graph(root, {
  label = d => d.data.name, 
  highlight = () => false,
  marginLeft = 40
} = {}) {
width=500;
dx=12;
dy=120;
treeLink = d3.linkHorizontal().x(d => d.y).y(d => d.x);
tree = d3.tree().nodeSize([dx, dy]);
  root = tree(root);

  let x0 = Infinity;
  let x1 = -x0;
  root.each(d => {
    if (d.x > x1) x1 = d.x;
    if (d.x < x0) x0 = d.x;
  });

  const svg = d3.create("svg")
      .attr("viewBox", [0, 0, width, x1 - x0 + dx * 2])
      .style("overflow", "visible");
  
  const g = svg.append("g")
      .attr("font-family", "sans-serif")
      .attr("font-size", 10)
      .attr("transform", `translate(${marginLeft},${dx - x0})`);
    
  const link = g.append("g")
    .attr("fill", "none")
    .attr("stroke", "#555")
    .attr("stroke-opacity", 0.4)
    .attr("stroke-width", 1.5)
  .selectAll("path")
    .data(root.links())
    .join("path")
      .attr("stroke", d => highlight(d.source) && highlight(d.target) ? "red" : null)
      .attr("stroke-opacity", d => highlight(d.source) && highlight(d.target) ? 1 : null)
      .attr("d", treeLink);
  
  const node = g.append("g")
      .attr("stroke-linejoin", "round")
      .attr("stroke-width", 3)
    .selectAll("g")
    .data(root.descendants())
    .join("g")
      .attr("transform", d => `translate(${d.y},${d.x})`);

  node.append("circle")
      .attr("fill", d => highlight(d) ? "red" : d.children ? "#555" : "#999")
      .attr("r", 2.5);

  node.append("text")
      .attr("fill", d => highlight(d) ? "red" : null)
      .attr("dy", "0.31em")
      .attr("x", d => d.children ? -6 : 6)
      .attr("text-anchor", d => d.children ? "end" : "start")
      .text(label)
    .clone(true).lower()
      .attr("stroke", "white");
  
  return svg.node();
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/5.16.0/d3.js" integrity="sha256-LHLWSn9RC2p119R1eT2pO3Om+Ir2G0kTZOJmWQ2//pw=" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/d3-array/1.2.2/d3-array.js" integrity="sha256-flJtpBHeLvoTQmeFnm0UuGrCFMGQbK6yrLhaNHyX8kk=" crossorigin="anonymous"></script>

Collectives™ on Stack Overflow

Mapping arrays from csv file for tree hierarchy visualization using NodeJS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related