May 102011
 

Pavel in St. Petersburg asked me if it would be possible to produce bubble diagrams like this in Excel:

UK Public Spending

Excel has had bubble diagrams sine 2003 but they are just an X-Y plot with variable-sized nodes. What Pavel was after is an automatic layout, with lines joining the nodes, along these lines:

Bubble diagram with Graphviz

Not perfect, but you get the idea, and it’s produced automatically. To do this, you’ll need Excel, Visio, Graphvizio and this zip file which contains the sample XLS, GV, VSD and JPG files.

  • Open the XLS. Column A is the node’s title, B is the title and the amount separated by a newline. Columns C, D and E specify the node’s parent, colour and amount. Column F computes the diameter of the node, in inches, from the amount:

Column G just creates Graphviz DOT statements from the values. G1 and G2 are the prelude. Copying column G into a text file called bubble1.gv, we get:

graph  RootGraph {
node [fontname=Arial, fontsize=12, style=filled];

"Total\n620" [width="3.1", height="3.1", color="gray", fillcolor="gray", shape=circle];

"Children, schools, family\n63"--"Total\n620" [color="pink"]; "Children, schools, family\n63" [width="0.315", height="0.315", color="pink", fillcolor="pink", shape=circle];

"Schools\n42"--"Children, schools, family\n63" [color="pink"]; "Schools\n42" [width="0.21", height="0.21", color="pink", fillcolor="pink", shape=circle];

...snip...

}

  • Fire up Visio. Graph>Diagram->Import Graphviz
  • To get the circular layout, Graph->Settings->Diagram->Concentric
  • Graph->Layout
  • A little tweaking of the font sizes and line thickness and you’re on your way

Enjoy!