For dashboard week’s 2nd day challenge, we needed to download data from the American Time Use Survey (ATUS), and to create a viz from it. This is what the part of the website that we needed to web scrape look like.
The Alteryx Part
Today’s challenge felt like a cubic puzzle. There was a code for each column and the values associated with it. We needed to decode these by looking up the correct table which had the human-understandable terms. To do this, we used Alteryx. Mine looked like this:
The top two lines were to get all the data in one table. This used the “timeseries” table and the “Alldata” table. We needed to join both on Series ID to get them all together in the same table. The “timeseries” table was needed to get the years and the measures together.
The next 12 rows of Alteryx were needed to decode the headers and the corresponding values in the main table that we just built.
We needed to do this separately, as each table was stored in different webpages and so we needed to download each webpage and clean the data to a structure that we wanted. We then joined them together one by one to make sure that the correct values were being decoded.
When we got to the final join, we used the select tool to get rid of the codes and I renamed the fields with the values and headers that we wanted so I could easily identify them.
We then had to use the crosstab tool to get the headers from one field called “Stattype”.
We then outputted the data.
The Tableau Part
This is what I created in Tableau.
Click on the image to see the viz on Tableau Public.
I created a Likert Scale to see the breakdown of the top 10 activities by age group. I also put in a tooltip which measured the percent difference for that activity across all the years. This way you can see the breakdown of each year group over time.
To do this, you need to put your measure on the columns shelf and add a table calculation. It need to be configured like this:
You then need to put your “activities” on the rows marks card and put “age group” into your colours marks card.
On top of this, I also put in the average, which shows the average time spent on this activity across all the age groups. This would help to provide further context.
I also created a second chart which showed the activities ranked for those who are employed and unemployed, to see if there were any similar activities.
For this you need to create a table calculation which ranks the activities. Then I chose to filter the top 10 activities for both the employed and unemployed sections.
I then created a dual axis to have the lines and the bubbles.
And there you go! That’s the activities of today.
Until the next challenge!