Day 4 of Dashboard week looked at food waste data from the OECD website and after the struggles of yesterday, Andy tried suggested that he wasn’t going to be so cruel to us...
The data on the OEDC website has a filter for each country, meaning that I can’t download the data for each country within one single download (at least I couldn’t work out how to). I had to go to the Export Tab towards the top left, select Related Files and from there you can do a quick download by clicking on each excel file – not productive if you have hundreds or thousands of files to download but in my case, I only had 33 so it was pretty fast.
I attempted a wildcard input into Alteryx to clean up the data but this failed. Why? Because of course, there is a slightly different format for each excel file, well at least Alteryx thought so. I decided to create a macro using the Australia file to clean it up ready for us. I could then use this macro on a few of the other nations and union the data together to create a single file including each country.
My macro looked like this:
Creating this was not too difficult. But then I realized that the field name “Variable” was different within some of the files. I created a second macro that changed the field name back to variable if it wasn’t originally. There’s probably a better way of doing it but our prep deadline was 10:30 and it was hitting that time already.
I brought selected countries through and operated the macro on each of those before doing a union to bring them all together. Again this could have been done in a much more efficient way but I wasn’t in a position to think of alternatives.
In my output file, I configured it to create a table in Snowflake which I could then bring into Tableau.
The data was problematic – there were missing values all over the place making it very difficult to draw any analysis straight off the bat. There was a clean version of the data on the website that I hoped had better, more populated data so I brought a list of countries from that file into alteryx and joined it with some population data and uploaded that to Snowflake as well. Turns out this data was pretty much the same as mine, just with more countries, but I used it anyway. My workflow for this section is seen below.
My idea behind bringing in the population data was so that I could look at how much waste is produced per human or per 100,000 people etc to draw a relative conclusion, not realizing that the data had units such as “per/capita” so this population data was not used within my final dashboard.
So for the dashboard, I connected tableau to my table created in Snowflake and begun to explore the data that I had available to me. You can see the table I created for the exploration below. There were many empty cells throughout the data so I knew I was going to have to be very specific with the data I was going to use. I finally landed myself looking at General waste produced from animal, mixed food and vegetal waste as this was one of the variables with the best recorded data (but only for the years 2008 and 2010). Therefore I created a number of datasource filters to filter down some of the fields to reduce the size of my data (also seen below).
For my dashboard, I first I wanted to create some BANs to compare the 2010 values of waste in kg/capita to 2008 and include an indicator to show whether waste has increased or decreased. Examples of these are shown below.
I also wanted to create a view of the best and worst performing countries in terms of food waste in kg/capita in the year 2010. I decided to create two diverging bar charts which also show a view of how the values had changed between 2008 to 2010.
See my final dashboard below and find it on my tableau public here: