For Makeover Monday Week 11, challenge was to build re-build the visualisation on data provided for World’s Biggest Cash Crop. The data was beautifully re-visualised by Adrian Zinovei, checkout the visualisation here. Follow the blog below, where he illustrates on how he came up with the visualisation.
The most important process is the design and building of one tree like graph which will allow us to show a representation of each group of crops.
Cash Crop Visualisation
Before we explore on how to build the cash crop visualisation, there is another interesting read on how to build Extending Bar Chart in Tableau
For the cash crop visualisation, as you can see from the picture the tree is made from multiple ramifications of smaller trees. Basically, we need to start with building small trees and after composure them in one big tree. We have split the data in 11 groups of crops (make sure it is spread).
Let us try to find out the methodology of building a tree from small trees groups.
Refer the dataset for cash crop visualisation here
Basic Layout for the cash crop
We have 12 groups, and we create a simple design in Excel template and the sketch on where every small tree will be placed. Every blue square has 10 px size and the tree should be inside of a 150×200 rectangle.
Example of how to build Tree 3 and how to write the data for Tableau.
We start by building squares 50×50 columns x rows. These will be our coordinates for X (columns) and Y (rows). After, we start drawing the nodes by coloring cells and lines to connect them. Level 3 coming from the main Root and connecting them all till level 8 final leaves. We named all nodes starting with the number of the level.
Plot the tree in an Excel
Basically, we for drawing a line in Tableau we need a Start Point and End Point. So, for each line ID we’re going to need 2 lines of data.
For each Line, assign a Start Point ID and End Point ID, also assign the X,Y co-ordinate to the line ends.
Description of the fields:
Tree ID – Tree3 (all small tree needs to have an ID for identification).
Level – Describe the level on which the node is placed. We can use this in Tableau.
Row ID – we add 3000 for each incremental Node to have them all distinct.
From ID – where the line starts.
To ID – there the line Ends.
X, Y – we place the coordinates of each line (Start and End).
Order – 1 start of the line, 2 end of the line.
Line 81, here is how we write down the X and Y for the start and end line.
Add the coordinates of each line (Start Point ID and End Point ID).
The process is not simple, and you’ll have to do it for each node (I call it Monkey job).
PUTTING ALL TOGETHER
After designing each small Tree it is about the time to bring the all together. From the data perspective, all data have same number of column and we just need to put them all together. We add them together by adding to existent X and Y based on their position on the tree. So, we’re extending small 50×50 areas to a bigger spreadsheet. Here are each small tree and where it’ll be placed on the tree. To change the X and Y for bigger tree we can use the table below. This is representative by following the 0…150 and 0…200 axis from the picture (you can choose your own).
Tree Matrix
All data can be found and extrapolated in this Excel file attached below
Harested-Crops-TemplateDownload
After you have the data with corrected X and Y you’re going to get your first tree.
Important: It will look ugly and not as you expected. My steps after: I loaded this data into Tableau (live connected) and looked at the “ugly” tree and try to change all nodes I did not like by changing X and Y numbers. To some of them I add numbers or subtract. Be carefully, you need to change both start and end nodes of the line. Anyway, in the end it is a rewarding manual process.
Create 5 trees and place them on X and Y.
We create a bigger spreadsheet 550px by 250px and we add to X and Y for each tree to place each tree on a specific place. Example Americas keeps all X and Ys the same. For Africa we add X + 150 and Y+ 70. Important: for each tree apart, I had to add 100000 to each “Row ID” to differentiate each tree. “Row ID”: Americas: + 100,000; Africa: + 200,000 etc. You can create your own “Row ID”, just make sure this is an important field and it is DISTINCT across the trees.
BUILD THE TABLEAU DASHBOARD
In Tableau the process is amazingly simple:
X – to Columns;
Y – to Rows;
Row ID, Tree ID to Details. Inverse the Y axis. (you can see from the dashboard)
Voila, you have the tree built in Tableau. The rest are basic things and depend on your next design and needs.
Final comments: Most of the final corrections are done on live Excel source. One Excel document open in one screen and the dashboard open on another. We change few numbers and after update the report and so on 😊
コメント