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