How I Created This Spiral Chart…and Why I Won’t Do It Again




Two months ago, I shared my viz, TORNADO.  Although the entire viz was well received by the community, the spiral chart at the top garnered the most attention.  Many people commented that they had never seen this chart before, asked how to create it and if I planned to write a blog post on it.  I had previously seen people create spiral charts with circles (Neil Richards will be mentioned below), but not with bars and especially not with equal width bars (more to come on that).  

Since I posted this viz, others have created this chart and written about it as well.  However, in this blog post, I will talk about my original inspiration for this chart, the math, the challenge, the iterations, and the result.  And I will focus quite a bit of time on the biggest challenge - what made this different from other spiral charts - the equal width bars.  

I will say the one and only reason that I created this chart was for the challenge…and because I absolutely love playing with Tableau. The result was a beautiful chart that was fairly useless analytically. 

Why?

For Christmas, my lovely wife bought me the The Book of Circles.  This book included hundreds of beautiful circular visualizations and infographics.  Each page contained incredibly beautiful works of art and I spent an hour on Christmas morning paging through and studying each one.

One particular graphic caught my eye.  The chart was created by Christian Tominski and Heidrum Schumann and showed the temperature fluctuations in Rockstock, Germany between 2008 and 2014.  It was a spiral chart, which I’ve seen before (and created before), but this one appeared to have equidistant points on the spiral.  Unfortunately, I cannot find a good image of the chart, but the following PDF documents their work.  The middle chart at the top is very similar to the chart in the Book of Circles, but the chart in the book was much larger.  (As a side note, it wasn’t until I finished my tornado viz that I realized the points were not equidistant at all – more to come).  

The math behind this intrigued me and over the next couple of weeks, I couldn’t get it out of my brain (yeah, I’m weird).  Therefore, I challenged myself to create this spiraling bar chart with equidistant points (which means equal size bars) in Tableau.  To be honest, it was just a challenge and I really didn’t know if the chart would ever be useful in any situation. 

The Process

I started with a proof of concept.  I utilized Superstore data to plot weekly sales per year in a radial fashion.  To do this, I utilized trigonometry - generally the same steps I used to build a radial bar chart in the Flerlage Twins TC19 presentation (it would be good to watch the entire video to gain a full understanding of the math, but if you are interested in just the radial bar chart, it starts at 16:19).    Each week was represented by a point on a circle and each circle represented a separate year.  As you can see, each year utilized a different radius.


You can see how the points got further apart as the radius increased.  In fact, the points in the inner ring were almost on top of each other.

Rather than to have individual rings for each year, I next modified the chart to create a spiral by simply increasing the radius.  I had done this before…I actually did it one month after starting Tableau by structuring the data in Excel.  I created Kramer’s lollipop where each point on the lollipop was a word in the script.  Click on the image to go to the viz and hover the lollipop to read the entire script of this episode. 




This time around, I adjusted my Superstore weekly sales chart to be a spiral (and of course, did all the calculations within Tableau).  I incrementally increased the radius with each week, rather than use a constant radius for each year like before.  I then set each year to be its own color.  (As a side note, after creating this visualization, I learned that Neil Richards had written about how to create this chart last year.  It's a great blog post - check it out here).  
Okay, I was making progress, but I wanted bars, not points.  In order to do so, I would need four points for each week…one for each corner of each bar.  I’m not going to go into a ton of detail here, but the math behind creating these bars is the same as creating a radial bar chart like in our TC19 presentation, except that you do it twice.  So I calculated the points on the circle (referred to as inner ring at TC19), then I duplicated my data and calculated the second point (on the outer ring) which extends out from the inner point based on my measure.  Then I connected the two points with a line.  Below is an image from our TC19 presentation that shows the inner and outer points.

However, in my case, I needed four points, so I essentially did the same thing a second time, except this time, I offset the points slightly.  I did two more unions (for a total of four sets of my data).  I then calculated the second set of points at the same angle as the next point on the circle, but added a buffer parameter value to back it off so it wasn’t right on top of that next point.  The two new points were identical to the first two points, but they were offset by some small angle.  Below is a visual example.

First, the original radial bar chart which consisted of two points and a line (I’ve purposely made it very light in color):

Now I added in the second set of points connected by a line – again, these were the same as the original two points, but offset by some angle (the line connecting the additional points are in blue):
In the above image, the points connected by gray lines along with the next set of points connected by blue lines all correspond to one value.  Here is what the four points looked like with the my actual data: 
Then connected the dots with a line.
You can see that each week consisted of four points.  These four points were essentially two radial bars for each week, but connected together.  From here, I simply changed them to polygons.
When I looked at the chart, I saw exactly what I was trying to avoid.  There were 52 bars (each representing a week) on each year’s ring (full 360 degrees).  Each week was at the same angle on the ring as previous years, which was a good thing because you could easily see seasonality.  However, if you looked at the image, you can see that the bars on the outer ring (2019) were much wider than the bars on the inner ring (2016).  In my opinion, this was quite deceiving as the length represents sales, but the thinner inner bars gave the perception that they were also shorter.  I knew that creating bars of the same width would have its own set of problems, but I was determined to figure it out how to do it.

Equidistant Marks

Okay, so how to would I create a spiral with points that were of equal distant between them?  Well, let’s review what I did above.

I created 52 points on a circle, which I turned into bars.  Each of the 52 bars would account for an angle of 360/52 or ~7 degrees.  No matter the radius of the circle, each bar would cover 7 degrees. 

However, what happens when the radius of the circle increases?  Well, the circumference of a circle is 2πR.  Let’s assume the radius of a circle is 100.  That means its circumference is about 628.  With a circumference of 628, the width of each of our 52 bars is 628/52 or ~12.1.  Now let’s double the radius for the second circle to 200.  The circumference of that circle is 1256 and the width of each bar is 1256/52 or ~24.2 – double the first ring.  As we increased the radius, we increased the circumference and we also increased the width of each bar. 

In the above example, doubling the radius also doubled the width of the bar.  My goal was to have equidistant points on the spiral, which is the same as having equal width bars.  In order to do that, I would need to adjust the angle of each mark as I increased the radius.  Remember that as I doubled my radius, I doubled my width.  In order to keep the width the same, I would have to divide the angle by 2 (in this case).  So that is the math that I applied – I compared the length of the current point’s radius to the first point’s radius and took a ratio.  In the example above, it would be 200/100 or 2.  I then divided the increment of each angle by that ratio.  In the above example, our radius was 100 and the total angle it covered was 7 degrees.  In the second circle, the radius was 200 and our angle would be adjusted down to 3.5 degrees.  This would create equidistant points on the spiral and equal width bars.

I applied this logic to a different data set (crude oil prices - I needed more values) and set it up with just two points like a radial bar chart.  It looked like the following: 


That was pretty cool and as you could see, the points were equidistant from each other.  This was exactly what I wanted to accomplish, but the spiral…well, it spiraled out of control.  I wanted the spirals to be tighter; the radius seemed to increase far too quickly.

What Now?

Well, I was in a bit of a conundrum.  The angle used between each point was being adjusted based on the radius, yet when doing so, the radius increased too quickly.  I was adjusting the radius in a linear fashion – I was just adding a constant value to the radius with each mark.  Perhaps I needed some other way to incrementally increase the radius…but I wasn’t sure how.   

When I encountered this problem, it was Christmas time and Ken had just came into town.  I mentioned my dilemma to him.  There were kids everywhere and he was quite distracted.  He thought about it for a few minutes, but didn’t have any solution come to mind.  I forgot about it and figured I come back to it when I had more time.

The next day, Ken and I talked about it again at a time where there were less distractions…and I saw his eyes light up.  “Oh, I’ve helped someone do this before”.  As soon as he said it, I knew what he was talking about and in my head, I saw Soha Elgany’s incredible Journalist Death viz.  (It’s a fantastic viz and I recommend you check it out yourself).  When Soha was building this, she reached out to Ken about the very same issue I had.  I don’t know all the details, but he had found some preexisting code that on StackOverflow that showed how one might go about creating a spiral with equidistant points.  Ken had modified that code using VBA and brought into this template (when you open the file, you can click File then choose Download).

I’ll explain the template in more detail later, but this allowed me to generate a list of x, y coordinates for equidistant points on a spiral…much like the image shows on StackOverflow:



The Actual Build

I used the template to generate 30,000 points on spiral, far more than I actually needed.  In Excel, I lined up those points with my crude oil prices data set.  I brought that into Tableau.  I then replicated the steps above to duplicate my data so that I had two sets, and then created a spiral radial bar char that looked like the following:

I then did two more unions so that I had four sets of my data, offset the angle, plotted all four points, and connected them with polygons that looked like bars.  I ended up inverting the bars over a gray background and boom!  It was exactly what I was trying to do!

Iterations

Unfortunately, I found crude oil prices over time to be quite boring.  Sure, oil prices have gone up, but for the most part, it was just a factor of inflation.  I just didn’t like the data, so I tried to find something else.  The challenge was finding something with a lot of history.

I had previously fooled around with the daily Dow Jones averages so I tried it using the same technique as above.  Unfortunately, it showed much the same - an increase over time.  Therefore, I adjusted the measure value to look at daily changes, whether up or down.  This was the result:


The chart looked pretty darn cool, but it was far to sparse for my liking.

I wracked my brain for something that might work for this chart, but couldn't com up with anything exciting.  I then mentioned it to my coworker and friend, Dinushki De Livera (we call her Dee).  She said very plainly,  "What about tornado data?  Dang! Tornado data!  That was the perfect idea!  The spiral would fit nicely with the spiraling winds of a tornado.  Now all I needed to do was find the data.

A quick Google search led me to a website that showed tornado data, but I couldn’t find any actual data that could be downloaded.  I messaged the contact email on the page and within an hour or so, they replied!  They sent me a direct link to the data - and the data stretched back as far as 1950!  Woohoo!  I found the data I needed.

Once again, I replicated the steps above, colored each decade differently, added a background to provide some context and the result was as follows: 


As I looked at this chart, I quickly realized a major weakness.  The larger rings contained more bars than smaller rings, which meant that seasons did not appear in the same areas on each ring.  It was very difficult to see seasonality.  I decided to create a separate bar chart with a set action to allow users to select specific months of the year to see trends.  I also modified the colors to highlight those selected months:


I sent this to Ken to get his opinion.  He absolutely loved the chart, but then said, “Are you going with those colors?”  Personally, I loved the colors, but clearly, he did not.  Do you want to know the truth?  I didn’t care.  What does Ken know about data visualization after all?!!  I loved the colors and I was going to keep them. 

The next day, I showed it to Dee in order to get her thoughts.  She also commented on how cool the chart was, but then said, “Why did you choose those colors?”  Darnit!  She hated them too.  I guess I had to at least investigate my color usage.

I searched the web for “good color combinations” and using those combos, I built six more versions of the spiral chart. 







I sent these examples out to a handful of people and the choice was unanimous…they all loved the yellow and orange (above).  I trusted their instincts over mine and went with it.  I’m glad I did.  Thanks Dee, Kate, Brian, Lindsay, and Ken.

Problems with the Chart

I admit, I thought the chart was super cool, but there were a heck of a lot of problems with it.  Here is the full chart:


1) It is hard to compare any sort of seasonality.  In the image above, Apr, May, and June are highlighted.  You can see they are larger than most other months, but it’s difficult to see due to the differing locations on each ring.  2) It is very difficult to compare a year to another year.  I think you can see that there were less reported tornados in the 50’s (inner part) than recently, but it’s difficult to really compare them like you could with a standard bar chart.  I added the gray background (built the same way as the orange and yellow chart was) in order to add some additional context and allow for easier comparisons, but it falls short.  And there are many other weaknesses that I could discuss as well.

The truth is, I created this chart to test myself and because I love fooling around with new ideas in Tableau.  This chart turned out very cool looking but very weak analytically.  Trying new things in Tableau is a great thing, you will learn a ton, but it’s also good to admit when something simply becomes data art and provides little benefit outside of data art.  This is one of those cases.  Perhaps there is some incredible use case for this, but it general…it’s probably best to stick to a standard bar chart.

That said, if you want to do this yourself, you most certainly can.  Just download the Excel template from here. Download the file, then follow the instructions below.

1. Go to the Developer menu at the top.  If you don’t have the Developer menu, use these instructions to turn it on. 

2. In the Developer menu, click on Visual Basic – it will open a new window. 

3. While in the Visual Basic Editor, open the Immediate Window by going to View and selecting Immediate Window (or by pressing CTRL G). 

4. Finally, in the Immediate Window, type the function name and parameters.  In this case, type: CalculateCoordinates2 followed by open parentheses, the number and points you want to generate, and closed parentheses.  As an example, if you wanted 3000 points, you would type: CalculateCoordinates2(3000).  Then click Enter. 

5. Close the VBA window and you should see an Excel spreadsheet containing a list of points numbers, X value, Y value, and angle.  (Note, before generating the points, you can also modify your radius in the VBA code…just change it from 100 to whatever you like.  As a side note, I actually did my spiral chart as a ratio to the whole rather than a flat value so that I did not need to adjust this value). 

Once you generate the points, you can apply them to your data then use the steps above to duplicate your data, calculate points then create linear bars or polygonal bars.  I will, however, ask you to please use caution with this particular chart. 

So that’s how I created this spiral chart and that’s also why I won’t do it again.  I hope you enjoyed the blog post and the viz.  If you ever have any questions whatsoever, please feel free to reach out at any time.



Kevin Flerlage, March 16, 2020











10 comments:

  1. Hello Kevin,
    I have downloaded the excel file but in the excel file is completely blank. Could you please share the link?

    ReplyDelete
    Replies
    1. Yes, it will be blank when you first open it. If you follow the instructions in the blog post (right below the link to the file), it will populate the Excel sheet.

      Delete
  2. Hello Kevin,
    How did you created the field Next X and Next Y? How can we know the number of points we had in our data? On which basis should join the data? Thanks for the blog.

    ReplyDelete
    Replies
    1. In this case, the Next X and Next Y was calculated in the data source, within the spreadsheet, to avoid additional table calcs in Tableau. In regards to the number of points...I'm not sure how to answer that. I knew the number of points going in. And I don't apologize, but I don't understand your question about the join.

      Delete
  3. Can you please tell the calc for next x and next y? Thanks for help

    ReplyDelete
    Replies
    1. It's just done in Excel by setting the cell equal to the next row's data.

      Delete
    2. Thanks Kevin.

      Delete
  4. Thanks for the blog Kevin. Can you please tell us the formula of Next X and Next Y and how to calculate
    it in excel?

    ReplyDelete
    Replies
    1. For that part, I just made the NextX field equal to the X for the following line then copied it down. Very easy in Excel. In SQL, you could use LEAD and in Tableau, you'd have to use a Table Calc (like LOOKUP +1).

      Delete

Powered by Blogger.