Today we were given historical US consumer expenditure data, ranging from 1959 – 2019. As the previous days, we were asked to clean and prepare the data in Alteryx, find and visualize insights in Tableau. The biggest challenge Today was to normalize the data as the consumer expenditure was not adjusted for inflation or increase in population which naturally would increase the total spending over time. As there were many categories, it was also challenging to find out what to visualize in Tableau as there were so many possible angles.
Data Prep
As always, the first step was to clean and prepare the data. The dataset consisted of an 8 level consumer/product category hierarchy, one level in each column, a date column on month level and a column with the amount spent for that date. It took quite some time to go through and clean all the columns, as many of them had aggregated rows that were hard to spot. You can see the workflow in the snippet below.
The next step was to aggregate the data up to a year level and join in historical inflation data that we found online. We all spent some time today to figure out how to adjust the dollar value to today’s value using the inflation data, but great teamwork and many discussions got us there in the end. You can see the workflow for this part below.
When this was done I had a quick look at the data in Tableau but found soon out that I also needed to adjust the consumer spending data for the growth in population. I found population data for the US at the World Bank and joined this to the dataset. I also joined historical household income data, even though I did not end up using it.
Visualisation
After I was done with the data prep I started exploring the dataset properly in Tableau and look for insights. As there were so many consumer spending categories it was hard to know where to start. I created a few charts to discover trends in the data and also spent some time looking at the different categories and see if there were any, in particular, it would be interesting to explore in more detail. The ‘negative’ expenditure categories, like alcohol and gambling, got my attention as I skimmed through the categories. This trail got me to look into health expenditure as I already knew that it is very high in the US compared to other countries. After some more investigations, I found some interesting insights regarding prescription drugs and decided to create a dashboard showing these. You can see the final dashboard below or you can find it on my Tableau Public profile here.
