Quick backstory:
I'm planning a trip to Alaska, with the main objective being to see the Northern Lights, and am trying to decide when to go. Given the objective, we need to go when it's darkest, but I want to balance that with comfort and go when it's not too cold.
A simple Google search should produce a graph of average historical temperatures, right? Well my 2-minute search didn't produce what I wanted. Weather Underground's graph is pretty but I can only see one month for a specific year at a given time. That's not going to work.
There were other options but none that I loved so my next question in my 5-minute quest was "Can I get the data to build my own graph?" The National Weather Service has all sorts of data on the subject.
With a few clicks I had some raw data.
Here's where the exercise begins:
I could copy and paste the data into Excel, but didn't want to add another technology to a very ad-hoc, one-off process.Did you know? You can copy data onto your clipboard and just paste into Tableau Desktop?
Here's how it goes:
1. CTRL-A (Selects All) then CTRL-C (Copy)
2. Open Tableau Desktop
3. CTRL-V (Paste)
The data gets dropped into Tableau and represented as a table. It's not in a great format yet so some minor data transformation needs to be done. Clicking on the Data Source tab allows us to do this.
First, let's rename the headers by double-clicking on the current name, typing, and then tabbing to the next one.
Second, we need to pivot the data so the months are represented in a single column.
Select the first month column, hold the shift key, and select the last month column. When you select the columns, you have to select the ribbon at the very top (the bit that's blue). Once selected, right-click in that same spot and select Pivot.
I got errors on all the monthly columns, but you can select them all (the same way we did above), and delete them (also using the right-click ability). Finally, rename the new pivoted columns.
Third, create a new sheet (or go back to Sheet 1 and clear it of all fields) and let's build a date field from the MONTH and YEAR columns.
In order to do this, we need the YEAR field to be a dimension. You can just click and drag it from the Measures section to the Dimensions section. Then we need both fields to be strings, which can easily be changed by clicking the "#" icon to the left of YEAR and selecting String.
We need to combine MONTH and YEAR into one date field, which we'll do with a calculated field. Right-click on either MONTH or YEAR and select Create > Calculated Field.
I created a date with the following formula, but there are many ways to produce the same results in Tableau so your formula could look different.
Finally, we VIZ!
Due to the data set used, I excluded records where YEAR = Click column heading..., Max, Mean, and Min and where ANNUAL is not null. I filtered the DATE to only give 1999-2019 since I could see the seasonal trends more clearly. Note to self: in another blog post, show off Tableau's forecasting capabilities and how it compares against an ARIMA model in R.
The ability to quickly drop some data into Tableau, pivot it, and build a graph on the fly is a powerful capability I use ALL. THE. TIME. Were you able to do this in 5 minutes?
Maybe not yet, but over time you will! Thanks for tuning in.
Maybe not yet, but over time you will! Thanks for tuning in.
