Visualizing 4 Years of Car Fuel Usage with Tableau (Appendix)

This post is an appendix to the "Visualizing 4 Years of Car Fuel Usage with Tableau" series. Here I want to outline how I've used Tableau Public to construct the plots and figures that I've used throughout these posts. If you're interested in reading those posts you can find them here:

• Part 1: Cleaning data
• Part 2: Visualizing trends in fuel prices
• Part 3: Seasonal trends in fuel prices
• Part 4: Bias in car-reported fuel efficiency

Storing the Data

The way I collected the data was rather straight forward. Every time I got gas from a gas station I would take my receipt (which conveniently had how much I paid, the city/date, the price per gallon, how many gallons) and wrote on it the information as reported by my car. Every so often when I had 4 or 5 of these receipts my wife would complain and I would take them inside and record the information to my spreadsheet.

I was using Google Sheets for keeping track of this data. I chose Google Sheets for three reasons:

1. It was free and I already had an account with Google (no need to buy a license for Microsoft Excel)
2. I could easily add information to the sheet from my home/work computer or even from my phone (the cloud is a powerful thing).
3. I could quickly make charts and graphs in Google Sheets to visualize the data

I hear you asking "wait... if you could make charts in Google Sheets, then why bother with Tableau at all?" It turns out, plotting in Google Sheets is fairly limited. I want to try out the tools that are used by members of the Data Science community. Tableau has come up a few times in my research as a useful tool, so I decided to poke around my fuel data using Tableau. Tableau also has a nifty feature for just loading Google Sheets, so that was another bonus.

Starting Slow

The first step in this short stroll through Tableau is to load the data. Fortunately, Tableau provides a quick import for loading Google Sheets data. On opening a new Tableau notebook, I'm greeted with the following screen, which allows me to load directly from a Google Sheet (identified by a red box):

A brand new Tableau book

Clicking the link takes me to a site where I need to give permission for Tableau to view my Google Docs. After that, I go back to Tableau and choose the sheet I want to load and I get the following screen:

Loaded my Google Sheet into Tableau

I'm going to preface the rest of this post with the fact that before this experience I have used Tableau exactly ZERO times. I didn't even bother trying to read a tutorial before diving head first into plotting and whatnot. So if you're reading this as a Tableau expert, I hope you enjoy my struggles.

Scatter plotting

In my humble opinion, the very basic, most rudimentary plot that can be made is a scatter plot. I have data points with two parameters, <X,Y>, put them on a plot. Simple right? So, let's take a look at Tableau and see if we can figure out how to do this. There are three different options for new tabs on the bottom. You can create a new "worksheet", "dashboard", or "story". The thing I want for creating a new plot is going to be a worksheet, so let's open up a new one of those. We can do that easily by clicking on the 'New Worksheet' button on the bottom of the screen. This opens a clean sheet for us to start playing around in:

New worksheet with the 'New Worksheet' button highlighted

Let's try to create the plot from showing MPG over time. My first guess is that I need to put the 'Date' information along the x-axis (columns?) and 'Real MPG' into the y-axis (rows?). Let's see what happens when we drag those two values into there... nope. (At this point, I should note that a few days later I was able to drag measure values into the 'Rows' and 'Columns' fields. I have no idea why I wasn't able to do this when I originally wrote this.)

It won't let me drag and drop them into those places. So instead let's use the 'Show Me' button in the upper right to see what we need to make that plot. For a 'scatter plot' I need "2-4 measures" values. So how do I make the date a measure? Seriously, how do I do that? I couldn't figure it out, so I just went with a line plot, but even that gives us a weird result:

THIS IS NOT WHAT I WANTED!

Looking at the actual values that have been put into the 'columns' and 'rows' it's clear that it's actually summing all of the MPG values for each year and plotting that. We want each value though, so I click on 'SUM(Real MPG)' and change it from a 'measure' to 'discrete'. Now we have all the values but still grouped by year, so click on the 'YEAR(Date)' and change it to be strictly a 'Date' (of the form 'May 5, 2015'). Now everything is a line, but we want a scatter-plot-equivalent, so going into the 'Marks' box I select 'Shape'. A little more customization of the shape type and color and we get this beautiful mess:

Finally, it looks like what I wanted

Applying Filters

Now that we have a decent plot, let's apply some data selection by removing 'nulls' and data from outside my home town. Removing nulls is easy they're actually already gone. I'm just gonna click that '20 nulls' button in the lower right corner to get rid of it now. There, OCD satisfied. To apply the cuts on geographic location, we go back to the 'Data Source' tab and select 'filters | add' in the upper right. We'll add two filters, one for 'Latitude' and one for 'Longitude'.

Histograms

After a simple scatter plot, one of the types of plots I find most helpful is a histogram. It helps me to visualize the distribution of actual values in the data. In Tableau, this is pretty easy. 

1. We just select the measure we want (for this example I'll stick to 'Real MPG')
2. Select the 'histogram' option under 'Show Me'

Steps to create a histogram

This generates a histogram that looks like this:

Generated histogram of 'Real MPG' values

That wasn't so hard! Later we'll see how to color the histogram by data.

Adding a Moving Average

So we've reduced spurious points that are adding noise into our plot, but it's still really messy. We can add a moving average to help us better visualize what the trend in the points is over time. This was perhaps one of the hardest things I did in all parts of this experience. First, I wasn't able to just right-click the data in the 'Rows' field and select 'add moving average'. You can add a trend line, but that's not what we want.

What you need to do is to create an entirely new field under 'Measures' (on the far left). For the moving average, we create something that looks like this:

WINDOW_AVG(AVG[Real MPG], -2, 2)

and we'll call it 'Real MPG Moving Avg'. Now we want to plot it. Should be as simple as dragging it up to the 'Rows' place, but it doesn't let us do that. What we have to do is select again the 'Date', 'Real MPG', and our new 'Real MPG Moving Avg' variable and then we can plot them:

MPG and Moving Avg MPG on the same plot

The plot is ... okay. I mean, it's technically what we were going for. But what we would really like to do is to over plot the moving average with on top of the actual points. We can do this by right-clicking on the 'Moving Avg' item under 'Rows' and selecting 'Dual Axis'. This will still look a little weird because the two axes will have different ranges. So, right-click on the right axis and select 'Synchronize Axis' and voila!

Finally, the plot we were trying to make!

We have now made a plot with data points and an overplotted moving average of those points.

Adding color to the points

One of the things I do repeatedly to visualize the trend over time is to color the points by either exact date, or by financial quarter. I use these two different styles to convey different points:

• Color by exact date: This helps visualize trends that take place gradually over the entire study. For instance, in part 4, coloring the entries in the MPG histogram by date helps visualize how the distribution gradually shifts to lower MPG over the 4 years.
• Color by financial quarter: Most of the data doesn't necessarily have to do with financial information, however,  the financial quarters do line up with the seasons quite nicely. Coloring by quarter allows visualizing differences based on the season regardless of the year. For example, visualizing the changing cost of fuel and fuel efficiency by season helped me learn that there are seasonal trends in fuel prices.

For my purposes, I was very interested in seeing how various measures changed over time. Because of this, I used time information to color the data points in my plots. This is as simple as 

1. Drag the 'Date' dimension into the 'Marks' box for the item we want to color
2. Make sure the menu item just to the left of the dragged object is set to 'color'

I'm going to color the points by year:

Real MPG with points colored by year

And we can also make the same plot with our histogram above:

Histogram with values colored by year

Interesting to see that the distribution appears to shift lower year after year.

There's no requirement though to use time information and I could have just as easily used any measure or dimension value.

Summary

I hope you liked this experience of doing some basic plotting with Tableau. It was a very interesting experience for me. I think Tableau is a useful tool for data visualization and especially for doing quick data exploration. It will certainly be a tool that I'll keep in my back pocket for use in the future.