Visualizing US Unemployment with Namara and Tableau
[For the feature snippet on the blog index page (usually it is cut around 20–25 words)]: We decided to take a peek at the most recent US unemployment statistics and share it with you
US Bureau of Labor Statistics publishes monthly numbers about the state of the labor force per city, county, and state, as well as the corresponding information about quarterly wages and location quotients by industry.
Nice folks at ThinkData Works took an effort of bringing together numerous files and refining the data to provide these two geocoded data sets — Local Area Unemployment Statistics and Quarterly Census of Employment and Wages — that can be easily queried using the Namara API.
So today we are going to visualize this data to make it easier to answer some of the following questions:
- What regions of the US are observing the highest unemployment rates?
- What are the historical country-wide trends?
- Does employment in the problematic states/counties demonstrate strong features of seasonality?
- What regions show high growth rate of unemployment (even if their annual rates are lower than the country-wide average)?
- Can we pinpoint the decrease in the labor force in a region to a few specific industries?
and so on.
We will be using Tableau Desktop — industry adopted tool for powerful data exploration — to create our visualizations. We will focus on Local Area Unemployment Statistics first. We have prepared two slightly different views of this data set to make it easier to work with in Tableau: monthly unemployment by state and by county. To export them, log in to your Namara account, go to the Download tab on the corresponding data set page, hit Prepare the whole data set and then click Download in the pop-up.
Create a Tableau workbook by connecting to a Text file (let’s use the state-wide view first), and then click Update now or Automatically Update, in case if the data does not appear in the preview.
You will see a data set preview. Let’s rename Administration Area to State and make sure to assign it a geographic role of State/Province to take advantage of Tableau internal mapping tools. Let’s also rename Month to Period to avoid a confusion when drilling down by year, month, etc. And we are ready to explore! 🚀
Let’s start with a historical heatmap of unemployment rate. First off we need to create a dynamic measure for the unemployment rate that would get updated correspondingly based on filters and aggregations applied to the period. To do so click on the menu option Analysis -> Create Calculated field, and create Unemployment Rate as
AVG([Unemployment]) / AVG([Labor Force])Then double-click on the State dimension, pick a Filled Map option on the Marks card, drag newly created Unemployment Rate measure to the Color card, drag Period dimension into the Pages card, change the format of Unemployment Rate to percentage, and voilà — you have an interactive map of the unemployment rate by state that can be paginated by a year (or any other period that you pick, for that matter)!
I have changed a color palette and a map background style, as well as added a few more metrics to the Tooltip card. This view allows to easily spot some problematic states (e.g. Illinois in 2013) as well as to see that the situation has significantly improved over time.
Now, if it’s still hard to grasp the difference between some of the states starting from 2015 and onwards, you might want to resort to a familiar tabular view. To create a crosstab view (Tableau equivalent of a spreadsheet), create a new sheet, then drag Period dimension to the Columns card, State to both Rows and Filters cards, and Unemployment Rate to both Text and Color cards. Update Unemployment Rate format to Percentage, change the color palette as you prefer — and you will find yourself with this beautiful table that you can filter by state, and drill down or roll up by year, quarter, month, etc.
Another useful way of displaying this data set is to focus only on a few problematic states and compare their numbers with a country-wide average. But before we do this, we need to create one more calculated measure to get a corresponding dynamic value for the latter, so head to Analysis -> Create Calculated Field and save the Yearly Country-wide Unemployment Rate as follows:
{ FIXED [Year] : AVG({ FIXED [Year], [Period] : SUM([Unemployment]) / SUM([Labor Force]) }) }FIXED is a Tableau alternative to SQL GROUP BY. Now you are ready to create this graph, so drag State to both Rows and Filters, Period to Filters, Unemployment Rate to Color, and our newly created measure to Detail. Use Unemployment Rate to create top 10 State filter and sort. To add a country-wide average to this plot, go to Analytics tab on the Data pane, select Reference Line, pick Line and average for Yearly Country-wide Unemployment Rate.
Use filter by year to see the states with the highest unemployment rate for a specific year or select all years to get the numbers for the whole period of 2013–2017.
If you want to take a look at the historical trends for the selected states, you might consider using the following view:
To recreate the plot above, bring Period to Columns and select Quarter(Period) as its value, State to Filters, Rows, and Colors, and Unemployment Rate to Rows. Also create a Quarterly Country-wide Unemployment Rate similarly to how we assigned a corresponding yearly measure, and add it to Rows. This graph allows to easily see an anomaly when a state doesn’t follow the same trend as the country and it has its unemployment growing rapidly, while the rest of the country observes a steady drop.
It is also useful to slightly change this view to use monthly statistics to reveal seasonality if any:
As you can observe from the chart above, Alaska usually experiences a significant growth in employment during the summer months due to the temporary workforce involved in seafood processing, which is a known fact. However, this layout makes it way easier to discover new insights. To create this view you need to drag State to Columns and Filters, Period to Columns, and Unemployment Rate to both Rows and Color.
While it is easy to determine the most problematic regions by unemployment rate alone, finding those that might be experiencing a small local crisis can be slightly trickier. It easy to overlook a region that is suffering from a sudden growth in the unemployment when it has both small absolute and relative unemployment compared to the rest of the country. To make such regions stand out, let’s revisit our first map and swap Unemployment Rate with the year-over-year growth: right-click on the Unemployment Rate -> Quick Table Calculation -> Year Over Year Growth. Make sure to add the Unemployment Rate to the Tooltip to provide some context.
As you can see from the graph above, Wyoming had a significant growth of 24% in the unemployment rate in 2016, even though its unemployment rate was not exceeding the US average.
You can alternatively use a bar chart to be able to compare the annual growth on a single view: just drag Period to Columns, State to Rows and Filters, and Unemployment Rate (Quick Table Calculation -> Year Over Year Growth) to Columns and Color.
Wyoming is clearly attracting attention on both layouts, so let’s drill down and try to see if we can further pinpoint this anomaly to individual counties. To do so let’s create a new sheet, select Data -> New Data Source from the menu, and load the second data set — monthly unemployment by county. Here you would have to assign corresponding geographic roles to both State and County (make sure to rename the columns just as we did it before). You also need to create a geographic hierarchy to allow for proper display and filtering: right-click on State dimension Hierarchy -> Create Hierarchy, and then drag County under the State in that hierarchy.
Then follow the same steps to recreate unemployment rate growth map, and apply a filter by State to keep only WY.
Now you can clearly see three counties with the highest unemployment rate growth in 2016: Campbell (81.6%), Converse (69.3%), and Natrona (45.9%). Now we can try to probe if there was a significant drop in employment in a certain industry in that area during 2016.
To answer this question we will use Namara UI to query and filter US QCEW data set. Apply the following filters:
- Place Name equals to Campbell (this is the county we will focus on)
- Administration Area equals WY
- Year equals to 2016
You can access this data set with all applied filters here. Now by sorting by monthly change in the employment level over the year (Jan Oty Emplvl [Pct] Chg, Feb Oty Emplvl [Pct] Chg, etc.), you can start seeing industries that have reported the most significant drop in the labor force — all in the private sector:
- Goods-producing
- Support activities for mining
- Heavy and civil engineering construction
- Oil and gas extraction
Indeed, three giant coal producers— Arch Coal, Alpha Natural Resources, Peabody Energy — went through the bankruptcy and massive layoffs in 2016.
This article is by no means an exhaustive analysis of US unemployment rate, but it should be a good starting point for you when exploring the data with Namara and Tableau.
***
Please feel free to reach out if you have any questions or suggestions. And make sure to check out more of our posts on analyzing the data with Tableau: exploring uber data to assess the success of King street pilot and taking a close look at governmental spendings.
