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. 

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

This post is the next in a series where I'm looking at 4 years of data from tracking the fuel usage of my personal automobile. You can see the first parts here:

• Part 1: Cleaning the data

• Part 2: Visualizing trends in fuel economy

• Part 3: Cost efficiency of fuel in summer vs. winter

MPG: Car vs. Real

Over the years, I've been in cars that had a display on the dashboard showing the fuel efficiency (MPG) over the duration of a trip. I always wondered how accurate those numbers were. My own car had one of these displays. So I tracked 4 years of what this indicator said about my fuel economy every time I filled up (resetting it every time) and compared that number to the actual miles driven divided by the amount of gas I put in. Here's what those two distributions look like. And just because we have the information, we can also see how those numbers change over time:

Fuel efficiency between March 2013 - Feb. 2017

Right away, we can see that over time the fuel efficiency of my car got worse! Dropping from the high 30's to the lower-mid 30's. This trend shows up in both the car-reported MPG and my own calculated MPG. This really isn't that surprising. Over time the car is going to wear and it's going to be less efficient as a result. This begs the question "what impact did this drop have on my wallet?". 

So, for a "back of the envelope" calculation, let's assume:

• my MPG dropped from 38 to 33 between 2013 and 2017
• I traveled about 17,000 miles per year
• A flat $2.50/gallon price as well (see previous parts of this blog that show this number fluctuates A LOT over time). 

Based on these assumptions:

1. I used about 447 gallons in 2013 and ~515 gallons in 2017 (difference = 68 gallons)
2. I sent about $170 more on fuel in 2017 compared to 2013

So, strictly based on the natural reduction in my car's fuel efficiency, I saw an increase of $170 in my fuel bill over the course of 4 years! That seems like a lot, but it's about the cost of a Netflix subscription for one year, so it's not extraordinary. Also, if I lived near where I worked, the impact would probably be a few cups of coffee different (actually, I'd probably ride a bike or take the bus instead and SAVE money). 

Another thing I notice is that the numbers reported by the car at least appear to be slightly higher. In other words, my car seems to think I'm getting better gas mileage than I actually am. We can do a comparison to see the bias in how my car reports the fuel efficiency of my car. We do this by making a histogram of the difference between the two MPG measurements:

The difference in car-reported and real MPG 

It looks like my car (at least initially) had about a 2 miles per gallon bias in it's reporting of MPG. Well, that's not great. But it also looks like, over time, the car becomes more and more accurate... what gives? Why did the bias drop from about 2 MPG to less than 1 MPG? Perhaps we're looking at this in the wrong way. It's possible that the car wasn't strictly 2 MPG bias, but rather it overestimated by a given fraction of the true value? Let's plot that distribution as "(car - real) / real":

Distribution of percentage bias in car-reported MPG

Nope, still shows the same trend over time. I can't really think of any other reason why the value became more accurate. There were a couple times when I had to take my car into the dealership for a recall of some sort, so maybe one of those times they changed the software that reported the MPG? Do you have any thoughts?

Just for completeness sake, we can re-verify the point I made back in part 2, that the MPG is seasonally dependent. We can do this in Tableau by coloring the MPG distributions by financial quarter (Q1,Q4 = winter; Q2,Q3 = summer):

MPG measures colored by seasons (winter=Q1,Q4; summer=Q2,Q3)

As expected, there appears a slight bias towards higher efficiency in summer and lower efficiency in winter.

Thanks for reading these posts. I think this will be the last part of this series for now. Did I leave anything out? Let me know in the comments and I can always add additional parts.

Visualizing 4 Years of Car Fuel Usage with Tableau (Part 3)

This is part 3 in a series of articles where I'm analyzing 4 years of data about the fuel efficiency of my personal automobile. You can find parts 1 & 2 Here:

• Part 1: Cleaning the data
• Part 2: Seasonal variations in fuel economy

Cost Per Mile

As a recap, the conclusion I reached in part 2 was that although the fuel sold in winter is cheaper, my car also uses it less efficiently. The question I want to answer in this post is whether or not the cost savings is enough to offset the reduced efficiency. Or, to put it another way, does the reduced price for winter fuel offset the fact that I have to refuel more often?

So what I want to get a feel for is the cost per mile in the summer vs. the winter. Plotting the distribution of "amount paid" per "miles driven" over time gives the "cost per mile", which I've shown in the following plot. Note, I've also plotted the market value of a barrel of oil over that same time.

Cost per mile and market price of a barrel of fuel

I can already hear you saying "well DUH, of course, the cost per mile is going to track the market value of a barrel of oil", but stick with me. The important take away from the above plot is that the cost per mile tends to be smaller in the winter than in the summer. So for the same amount of money, you can travel farther in the winter than in the summer. The difference looks like a 2-3 cents per mile, so that's probably about $10-$15 difference to fill up my 12-ish gallon tank in a car that gets about 30 miles per gallon. I had to fill up about once per week during this time, so that's around $40-$60 more per month in the summer time! 

So what leads to the fuel being cheaper in the winter and more expensive in the summer? This article provides a couple of ideas which I'll summarize here:

• The summer fuel blend is actually more expensive to produce:
  The summer blend uses certain additional fuel additives designed to reduce the amount of smog produced. Production companies also tend to shut down in the early spring for maintenance and to shift over to producing the new fuel blend.
• People travel more in summer than they do in winter:
  No surprise here. In the summer, kids are out of school and families go on vacation. This causes an increase in demand for fuel, which leads to an increase in price. Supply and demand, baby!

So in the end, the answer seems to be that the winter fuel is more cost-effective. But according to this 1999 study it's also potentially worse for your health and the environment.

Market Impacts on Cost

For the sake of argument, let's say I'm more concerned about the increased economic cost of fuel in the summer than its impact on the environment. Before I go to write my congress to change the EPA regulation, we might notice that the seasonal fluctuations are actually much smaller than the broader, more impactful market fluctuations. Or to visualize it another way, we can "bin" the data points by month. To better visualize "winter" vs. "summer" Tableau can also color the points by financial quarter: Q1 (yellow) and Q4 (green) are winter and Q2 (red) and Q3 (teal) are summer.

Cost per mile stacked by month and colored by financial quarter

It appears that the spread in the points over time is actually quite high. So what happens when we normalize the cost per mile by the market value of a barrel of oil? Well, we get the following image:

Cost per mile normalized by market price for a barrel of oil

"Why did the relative cost per mile increase, even though the market price for a barrel of oil went down?" The cost of fuel at the local gas station came down, but not by the same amount as a barrel of oil, hence the curve goes up instead of down. The peaks and valleys also appear to be sometimes in the summer, sometimes in the winter, and sometimes in between. So the market value of a barrel of oil probably has a more direct impact on the cost of fuel than the increased cost of summer fuel production.

Next Up

Do you have one of those dashboard displays that tracks your fuel efficiency and tells you your MPG? Have you ever wondered how accurate it is or whether it's slightly biased? In part 4 I'll start to look at the MPG reported directly by my car and see how that number compares to the raw MPG computed by <miles driven>/<gallons filled up>. I wonder how truthful my car is actually being?

• Part 4: Bias in car-reported fuel efficiency

Visualizing 4 Years of Car Fuel Usage with Tableau (Part 2)

This post is a continuation of a previous post. If you haven't read part 1, you can go back to it using the following link:

• Part 1: Cleaning the data

Gas Price Trends

As you may recall from part 1, we've cleaned up the data and plotted the price per gallon as a function of time over 4 years, specifically for my hometown.

Price per gallon for fuel restricted to only refuelings in my hometown

Here’s what I notice:

1. Sometime in late summer of 2014, the cost of fuel took a huge dive that persisted for at least two years afterward.
2. There’s a seasonal fluctuation in the price of fuel with summer fuel costing more and winter fuel costing less.

The first point wasn’t surprising. Anyone listening to the news in the summer of 2014 is already aware that there were large price drops in gasoline around this time (just do a quick internet search and you’ll find lots of news articles about why this happened).

As for the second point, I was kind of already aware of this trend, but not really the reason. Apparently, this is caused by differences in the fuel blends produced in summer compared to winter. The reason behind these differences is a regulation by the EPA that restricts the type of fuel that can be sold in the summer as a means to reduce the level of smog in certain areas. The winter blend is cheaper, but it’s (supposedly) also less fuel efficient. Fortunately, I have the data to see if the second part of that statement is true.

Fuel Efficiency

Let's have a look at my MPG over this same time frame. No surprise, it's a total mess! What gives? I kept the same filters as I had in Part 1, but it’s clear that there’s quite a bit more spread in the points. To clean things up, let’s visualize a moving average where we average each value with the two before it and the two after it.

Miles per gallon (points) with moving average (line)

That cleans things up a little bit. And yes, there is a dip in fuel efficiency in the winter compared to the summer. So the fuel in winter is cheaper, but my car also uses it less efficiently. To be fair, there are many reasons why fuel efficiency might be worse in the winter compared to summer that are not directly tied to the quality of fuel, but I’m guessing quality plays some part. How big of a part? I can’t really judge from the above plot, but it looks like the efficiency doesn’t immediately change in September and May (when the fuel change-over happens). So I’m guessing the weather has a more dramatic impact on my fuel economy than the actual fuel itself.

Next Up

From a purely economic point of view, it’s worth asking whether the drop in price outweighs the drop in fuel economy. In part 3, I'll try to answer the question of whether the miles per dollar was significantly different in winter compared to summer.

• Part 3: Comparing summer and winter fuel costs

Visualizing 4 Years of Car Fuel Usage with Tableau (Part 1)

One day I was traveling with a friend. We stopped off at a gas station and after fueling up their car they got back in and started writing something down in a little notepad. They were keeping track of their mileage over time so they could notice if their car was losing fuel efficiency. I thought this was interesting and when I bought a car in 2013 I decided I would keep track of this information myself. Additionally, my car had some extra features, so I decided I would keep track of a lot more information:

• miles driven
• price/gallon at pump
• date
• where I filled up (city name)
• how much fuel my car said I used
• how much fuel the pump said I put in my car
• my average speed according to my car

I sold my car in 2017 and since then this spreadsheet has just been sitting there. I was traveling about 40 minutes one-way for work every day, so there are a lot of data points. Recently I’ve been looking into learning Tableau as a means of visualizing data and I figured this was as good a sample of data as any to cut my teeth on.

Cleaning the Data

The most obvious place to start is with the cost per gallon of fuel. So, plotting that up let's take a look:

Price per gallon for nearly every refueling over approximately 4 years.

I can hear you thinking “what’s that spike around the summer of 2014?” Going back to the data it’s immediately apparent that this was during a time I was traveling cross-country. Because the cost of fuel appears to fluctuate noticeably depending on where I bought it from, I can interpret this as if these prices are drawn from a different distribution than the values from my home town.

There are other instances of this (especially in the summer and around winter when I usually travel to visit family). Fortunately, I can place some constraints on the data and remove all points that aren't associated with my hometown. I did this by first computing the latitude and longitude of each city where I fueled up in. Then I applied filters to select only those latitudes and longitudes around my hometown.

Price per gallon for fuel restricted to only refuelings in my hometown

That clears things up nicely! There are a few features in this distribution, most noticeable is the difference between winter and summer. 

Next Up

In the next post, I'll dive a little deeper into the data and analyze the actual fuel trends that appear in the above image.

• Part 2: Seasonal trends in fuel prices