Squeeze it or Crack it !
I participated last week to the feeder 1 for the Global Ironviz contest 2019. This time the competition was a little different instead of having a theme and be free to pick up as many datasets as we would like, we were limited to ONLY one.
And of course my first reaction as a French person, was to complain :) I have to say, that the data analysis is my speciality, and my strengths consist on digging on the data to find the answers at my questions, and add more and more data during my thinking process ( and I am very curious).
I even recalled having collected manually at several occasions my own data to create some vizzes :
- My Tintin Analysis during which I could manually pages per pages and album per album the number of occurence of the main character viz
- My IronViz Analysis where I checked all the battles localisations viz
- My Tour de France Analysis where I collected and prepped the data year by year for the one hundred something editions.viz
But this time, the rules were clear no addition to any datasets were allowed. I have to say that I was disoriented, usually I begin from an idea and not the dataset.. or should I say that this time this Ironviz looks like to my daily work ! 😱
The basic : the data !
So I began with the part that is familar : the data preparation, which in my point of view is also part of the challenge, and should be count in the ranking ... but that is probably another topic.
The data was about the agricultural census in 2012 and consists on different tables :
- a county table having a county code and county name
- a variable lookup table having all the measure code and their respective name
- a crop and plant table having the county code and the value of every measure code of the category crop and plant
- an economic table having the county code and the value of every measure code of this category
- a farm table (idem then above)
- a livestock and animal table ( idem then above)
So if you connect to Tableau any of measures tables ( economic, farm, livestock or crop), it will be difficult for you to analyse because you will see something like only code name on the measure pane
not very useful... So I prepared the data so it can be easily used in Tableau. With some steps, you can easily do it in Tableau prep :
- join the county name source with the measure table
- in order to be able to cross reference the the coded measure names with the variable look up table, you will have to pivot the result table
- join the pivoted table with the variable lookup table
- do some cleaning and keep only the variable that you interested in
- pivot it again to have the measures as a column and dont forget to aggregate by counties
With those steps I was able to do almost all the visuals in my dashboard. I will not explain here how I estimated the areas of the different cropland in Tableau, because they are explained in the dashboard itself.
But what I wanted to do a square viz displaying the area of the orange and almond lands and be able to see how much they represent respectively to the farms and cropland.
It looks like a treemap but I wanted to be able to control the disposition of culture types.
To do it I manipulate the data ( almost the same steps than below, but I didn't proceed to the last pivot, because I wanted to have a column with all the fields name. For example : I rename the field "land dedicated to almonds as a percentage of Orchards " to a simple "almonds" :)
First sketch and idea : area chart !
Once I have my datasource, my first curiosity was to know how much represent the oranges and almonds field on the total agricultural area. And it was my first sketch on my drawing notebook ( if you are nice maybe I will send pictures of my notebook someday). To do it, I took inspiration of the quadrant visuals created by @Rody Zakovich link to his post ( and for the ones who already wonder... yes I gave him credits on my submission). I adapt his formulas in order to serve my purpose.
I also added a parameter to be able to swap between orange and almonds culture.
width depending parameter :
if [orange or almond]="orange" then [width orange] else [width almond] END
height depending parameter :
if [orange or almond]="orange" then [height orange] else [height almond] END
with width almond being
CASE MIN([type]) WHEN 'farms' THEN -SQRT(SUM([value])) when 'cropland' then -SQRT(SUM([value])) when 'orchards' then -SQRT(SUM([value])) when 'almond' then -SQRT(SUM([value])) END
and height almond being
CASE MIN([type]) WHEN 'farms' THEN SQRT(SUM([value])) when 'cropland' then SQRT(SUM([value])) when 'orchards' then SQRT(SUM([value])) when 'almond' then SQRT(SUM([value])) END
to create the view drag and drop :
- in column : width
- in rows : height
- in color : type of culture
- in size : - width
- in text : type and estimated area
et voila :)
Give some life to this viz : color and design !
During this iron viz contest, because I was limited on how deep I could go, I decided to focus more on the analysis and the story. So I told a real childhood story on how I remember picking up oranges from a garden's tree in Spain and how my father smashed almonds when we were walking on the countryside together.
The colors used are essentially orange for oranges ( how original) and a light pale green for the almonds ( when they are still on the tree, they are surrendered by a green shell).
I usually used the grey when these two fruits are represented in order to contrast with these two main elements.
I always tried to add some funny touches, for instance with an orange slices when you hover on the map to see the distinction of the Valencia orange variety and the other variety. I also add some home made drawings to give a more personal touch.
And Actions !
I also played with the Set actions feature, to be able to highlight on the map a county ( I am bad at geography !) when clicking on a it. On the example below, when clicking on "Butte" a circle appear on the map to indicate its position.
Be a legend !
I also took care of the tooltip and provide additional but crucial information for the analysis. For instance on the chloropleth map relative to the usage of chemicals, I gave the information about the top 5 cultures on the tooltip, so when you are hovering on a specific county having a huge consumption of chemicals, you see which cultures are produced there.
By the way Sarah Battersby has a very good blog on how to create this kind of map link
I am a huge fan !
Check the data!
What I didn't mention yet is that I checked the Data Quality. As an analyst, I always had the tendency not to trust the data. So I proceeded to some testing, and even if I dont consider that is part of the visual competition, I still added it on my entry. I found some data inconsistencies, or some missing data. In the real life, I would have reached the data provider, here I had to deal with it :) I decided to keep it but I could have decided to filter it out too. Here some examples of my findings.
The rest are on my viz "what about the data" link to my viz
If not already done, go and check all the amazing #ironviz entries done by the Tableau community https://public.tableau.com/en-us/s/blog/2019/05/2019-iron-viz-agriculture-entries