This article explains how to create Semi Sunburst chart in Tableau.
Every Sunday Tableau Zen Master Andy Kriebel and visual analytics expert Andy Cotgreave are sharing the week’s chart and data set on his blog, VizWiz. Their goal is to encourage people to cast a critical eye over the charts they see. Week 28 topic comes from The Washington Post where we are looking at politicians’ responses to Orlando shootings. Every tableau lovers submitted their entries and all were awesome but one chart caught my eyes and it was prepared by “Shivaraj-A very talented tableau lover”. I downloaded his workbook and started exploring his technique .Wanted to share the same with you guys as well.
As this week’s data contains four columns – 1) Topics 2) Party 3) Mentions 4) Didn’t Mention. So I started my analysis and opened a Wikipedia page which beautifully explained the US seats.
Even Wikipedia used a Semi Sunburst diagram for representing the seats.
Week 28 – Makeover Monday Data
Data initially obtained must be processed or organized for analysis. For instance, this may involve placing data into rows and columns in a table format in such a way so that we can able to generate a similar type of chart in Tableau which we are seeing in Wikipedia page. For getting the desired shape, we need do reshape our data in our Excel sheet.
Plotting points over the excel requires a little bit of geometry. So, to create the half circle we need to structure the points in Excel. In order to create these circles, we needed points along the circumference which would be our X (Row shelf in Tableau) and Y (Column shelf in Tableau) coordinates.
These X and Y coordinates were created with the following parametric equation in Excel which I have taken from Adam Website:
X = 1 + SQRT (9) * SIN (A* PI () / 180)
Y = 1 + SQRT (9) * COS (A * PI () / 180)
Where A represents an angle between 0 and 180 at intervals of 6 degrees. For this chart there are 31 angles between 0 and 180 at intervals of 6 degrees.0 and 180 are necessary to form the half circle.
Derivation of the Above Formula:
(X) = (Sqrt (2) * Sin (A * (Pi ()/180) —— (i)
(Y) = (Sqrt (2) * Cos (A * (Pi ()/180) —— (ii)
Squaring both sides of the equation (i) and (ii)
(X) 2 = 2 * Sin 2 (A * Pi ()/180)
Let (A * Pi ()/180) = Ө
(X) 2 =2 * Sin2 Ө ——- Equation 1
(Y) 2 =2 * Cos2 Ө ——- Equation 2
Adding Equation 1 and Equation 2,
(X) 2 + (Y) 2 = 2 * Sin2 Ө + 2 * Cos2 Ө
(X) 2 + (Y) 2 = 2 * ( Sin2 Ө + Cos2 Ө )
(X) 2 + (Y) 2 = 2
I chose 6 arbitrarily but you can set those intervals at whatever you want with the formula above.
Here, we need to plot 434 (Democrat 187 + Republicans 247) circle points, so our ID column will start from 1 and it will go till 434 and for this entire set (when angle covers the entire semi-circle path 0-180) our Row Column will contain a constant value 1. As we have 31 angles between 0 and 180 at intervals of 6 degrees, so for getting a new circle, “Angle” column will have to again restart from 0 degree but Row column will change from Value 1 to 2. So if we do some math’s, we will realize Row Id will go till 14 (31*14 =434)
Now if you observe the MOM week 28 data carefully, we have 9 unique Topics and how the political parties (Republic and Democrats) in the US responded to an Orlando mass shooting tragedy.So our next step is too arrange the data so we can generate viz similar to image present on the Wikipedia page.
Follow the below steps and reshape the data in Excel sheet.
1. We already have 5 columns Row, ID, Angle, X and Y .Now we need to add and Topic, Party ,Mentions and Didn't mention in our Excel sheet. By looking into the first row ,one can easily tell that the majority of lawmakers in both parties did use “Thoughts and prayers” topic in their statements/phrases. So here our main task is too arrange the data in our excel sheet.
2. Create a Separate column for each “Topic”. Now copy the word “Democrat” under “Party” Column to till Row no: 188 and “Republic” till Row no: 435 (187+247).In the above mentioned image, we can see “Thoughts and prayers” are being mentioned by 114 times by democrat and 199 times by Republicans respectively. So you need to copy the word “Mentions” 114 times under “Thoughts and prayers”. Similar you need to copy the word “Didn’t Mentions “73 times under the same column. Both Mentions and Didn’t mentions should map to Democrat party. Perform the same operation for “Republic” Party as well.
3. Do the similar thing for all the individual topic like Addressed LGBT Community, Mentioned Hate etc. The Final result will look like the below image.
Now import the excel sheet in the Tableau. Once the data is available inside tableau we need to combine “Party” and “Topic (Individual Topic e.g Thoughts and prayers)” so that we should get 4 legends as per the below image.
Select “Party” and “Thoughts and prayers” dimension simultaneously. Do the following steps
Right Click -> Create -> Combine Field.
Perform the same operation for other Topics as well.
To make data sources with many fields easier to work with, you can organize the Data pane items into folders. Right click anywhere on the data pane and select “Create Folder”. You can access “Group by Folder” option from the field context menu. Select all the dimensional fields which contain (Combined) as a suffix. It will look similar to the below image.
Now our next target is to find the no of “Mentions” and “Didn’t Mention” Count. So for getting the desired value, we need to create a LOD calculation.
Similarly create another 8 calculation for each individual topic .Just replace the dimension value with the appropriate value.
Put Y measure on Column shelf and X measure on Row Shelf. Add “Party & Thoughts & Prayers (Combined)” on the Color marks card and ID on detail shelf. ID dimension will generate the Path. Add “Thoughts & Prayers Count” in the tooltip. You can also change the background color of worksheet.
So our next job is to improve the Tooltip interactivity .As we all know that “Republican “is being represented by –Red- Color and “Democrat” is being represented by – Blue- Color. So what we want, when user hover over the Republican Party, count will show in Red color and in case of Democratic, count will show in Blue color. You can also create a similar calculation for individual Topics.
Similarly create other calculated fields for all the topics which are mentioned in the data.
Add the above two calculated fields side by side in the Tooltip and assigned the Proper color as per the below image.So the color will change as user hover over the party.
The Final result will look like the below image.
The above chart is ok but not good enough. This sort of viz is useful only when we will have only two color legends but here we are having 4 legends.
Now let’s create a viz in such way that should be easy to read because primary goal of data visualization is to communicate information clearly and efficiently and above chart is not easy to read.We wanted to create a chart which should look like the below image.
So for getting the above chart we need to reshape our data in such a way like 1st Quadrant should contain all the information related to “Republican” Party and 2nd Quadrant should contain all the information related to “Democrat”
As we know that we have 31 dots in every Semi-circle which contains an angle from 0 degree to 180 degree and there are 14 dotted Semi- Circle .So we need to divide 31 dotted points equally between both the parties. In other words we can say, 31 dots should divide in such a way which will form 2 quadrants.
Now our next motto is to divide 17 dots between Mentions and Didn’t Mention. But currently we don’t have any idea how many dots will fall under “Mentions” category?
So for solving the above issue we will calculate the percentage as below
= (Total no of mentions/ Total no of Democrats) * No. of allotted Democrats Dots
= (114 / 187) * 14
Democrat Didn’t Mention:
= 14- Democrat Mention
This is how you need to do the data arrangement in excel.
But there is another issue when you take 14 Dots (Democrat) and 17 Dots (Republic). Let explore the problem by applying some mathematics.
As we know that we need 187 dots for Democrat and 247 dots for Republic and we are creating 14 circles or in other way we can say, we have 14 unique ID’s for every 31 dots.
=14 (unique IDs) * 14 (Democrat Dots)
But we need only 187 dots for democrats, so we need to adjust extra 9 dots.
=14 (unique IDs) * 17 (Republic Dots)
Here we need 247 but we are only getting 238 dots, so we need to make some data adjustment with Republic party.
Let’s make some adjustment in our data. So here what we can do, from Row ID 1-5, we will follow 17(R) –14(D) combination and after Row ID 6-14, we will be using 18(R) –13(D) combination.
Now the new calculation will be
= 14*5 + 13*9
= 17*5 + 18*9
See the below image.
Now our data is ready. Open your tableau and select the newly created excel file and do the same operation which is mentioned in Step 8.The final result will look like the below image.