{"id":408,"date":"2011-05-10T21:52:33","date_gmt":"2011-05-10T19:52:33","guid":{"rendered":"http:\/\/www.calvert.ch\/maurice\/?p=408"},"modified":"2011-05-10T22:13:39","modified_gmt":"2011-05-10T20:13:39","slug":"creating-bubble-diagrams-with-excel-visio-graphviz-and-graphvizio","status":"publish","type":"post","link":"https:\/\/www.calvert.ch\/maurice\/2011\/05\/10\/creating-bubble-diagrams-with-excel-visio-graphviz-and-graphvizio\/","title":{"rendered":"Creating Bubble Diagrams with Excel, Visio, Graphviz and Graphvizio"},"content":{"rendered":"<p>Pavel in <a href=\"http:\/\/en.wikipedia.org\/wiki\/St._Petersburg\">St. Petersburg<\/a> asked me if it would be possible to produce bubble diagrams like this in Excel:<\/p>\n<p><a href=\"http:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/2009124163835.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-409\" title=\"UK Public Spending\" src=\"http:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/2009124163835-300x196.jpg\" alt=\"UK Public Spending\" width=\"300\" height=\"196\" srcset=\"https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/2009124163835-300x196.jpg 300w, https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/2009124163835-1024x672.jpg 1024w, https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/2009124163835.jpg 1191w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>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:<\/p>\n<p><a href=\"http:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/Bubble1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-410\" title=\"Bubble diagram with Graphviz\" src=\"http:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/Bubble1-268x300.jpg\" alt=\"Bubble diagram with Graphviz\" width=\"268\" height=\"300\" srcset=\"https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/Bubble1-268x300.jpg 268w, https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/Bubble1-917x1024.jpg 917w, https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/Bubble1.jpg 1646w\" sizes=\"auto, (max-width: 268px) 100vw, 268px\" \/><\/a><\/p>\n<p>Not perfect, but you get the idea, and it&#8217;s produced <strong>automatically<\/strong>. To do this, you&#8217;ll need Excel, Visio,\u00a0<a href=\"http:\/\/www.calvert.ch\/graphvizio\">Graphvizio<\/a> and <a href=\"http:\/\/www.calvert.ch\/download\/bubbles.zip\">this zip file<\/a> which contains the sample XLS, GV, VSD and JPG files.<\/p>\n<ul>\n<li>Open the XLS. Column A is the node&#8217;s title, B is the title and the amount separated by a newline. Columns C, D and E specify the node&#8217;s parent, colour and amount. Column F computes the diameter of the node, in inches, from the amount:<\/li>\n<\/ul>\n<p><a href=\"http:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/bubbless1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-412\" title=\"bubbless1\" src=\"http:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/bubbless1.jpg\" alt=\"\" width=\"708\" height=\"189\" srcset=\"https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/bubbless1.jpg 708w, https:\/\/www.calvert.ch\/maurice\/files\/2011\/05\/bubbless1-300x80.jpg 300w\" sizes=\"auto, (max-width: 708px) 100vw, 708px\" \/><\/a><\/p>\n<p>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:<\/p>\n<pre class=\"qoate-code\">\r\ngraph  RootGraph {\r\nnode [fontname=Arial, fontsize=12, style=filled];\r\n\r\n\"Total\\n620\" [width=\"3.1\", height=\"3.1\", color=\"gray\", fillcolor=\"gray\", shape=circle];\r\n\r\n\"Children, schools, family\\n63\"--\"Total\\n620\" [color=\"pink\"]; \"Children, schools, family\\n63\" [width=\"0.315\", height=\"0.315\", color=\"pink\", fillcolor=\"pink\", shape=circle];\r\n\r\n\"Schools\\n42\"--\"Children, schools, family\\n63\" [color=\"pink\"]; \"Schools\\n42\" [width=\"0.21\", height=\"0.21\", color=\"pink\", fillcolor=\"pink\", shape=circle];\r\n\r\n...snip...\r\n\r\n}\r\n\r\n<\/pre>\n<ul>\n<li>Fire up Visio. Graph&gt;Diagram-&gt;Import Graphviz<\/li>\n<li>To get the circular layout, Graph-&gt;Settings-&gt;Diagram-&gt;Concentric<\/li>\n<li>Graph-&gt;Layout<\/li>\n<li>A little tweaking of the font sizes and line thickness and you&#8217;re on your way<\/li>\n<\/ul>\n<p>Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Pavel in St. Petersburg asked me if it would be possible to produce bubble diagrams like this in Excel: Excel has had bubble diagrams sine 2003 but they are just an X-Y plot with variable-sized nodes. What Pavel was after <a href='https:\/\/www.calvert.ch\/maurice\/2011\/05\/10\/creating-bubble-diagrams-with-excel-visio-graphviz-and-graphvizio\/' class='excerpt-more'>[&#8230;]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,11],"tags":[19,42,43,40],"class_list":["post-408","post","type-post","status-publish","format-standard","hentry","category-graphics","category-technology","tag-excel","tag-graphviz","tag-graphvizio","tag-visio","category-4-id","category-11-id","post-seq-1","post-parity-odd","meta-position-corners","fix"],"_links":{"self":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/408","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/comments?post=408"}],"version-history":[{"count":17,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/408\/revisions"}],"predecessor-version":[{"id":429,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/408\/revisions\/429"}],"wp:attachment":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/media?parent=408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/categories?post=408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/tags?post=408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}