The US has 3 years of Oil in the ground: Oil exports, imports, and consumption

Many people are unaware of the massive amount of oil and refined petroleum that is shipped and consumed around the word. Every day, there are approximately 84 million barrels of oil used. Every day. 3.5 billion gallons, 5345 Olympic swimming pools, 13 Empire State Buildings. Quite unbelievable.

To understand who is using the oil, where it’s coming from, and what would happen if oil exports ground to a stop I retrieved data from US Energy Information Administration. The visualization is held on 3 tabs. The first allows you to explore where the oil is coming from, and where it goes. Sparklines and sorted tables allow for quick understanding of the data. The second concerns reserves of oil, and the third allows us to see how countries’ use is changing as their population changes over time. Some highlights:

  • Many countries decreased the amount of oil imported in the last year of complete data (2009), probably due to the global recession. China and India are among the top importers bucking this trend.
  • You can normalize imports/exports by population; this creates some interesting anomalies such as the Virgin Islands which have massive per capita imports of crude (and massive per capita exports of refined) due to the Hovensa oil refinery on St. Croix.
  • On the second tab – everyone knows about the Middle East, but Canada, Venezuela, Libya and Nigeria have very significant reserves – selecting just a specific region allows exploration of each country
  • If countries had to depend on their own reserves (assuming all oil is accessible, and consumption doesn’t change …) the results are surprising – the US has under three years of oil, whereas Chad has over 2050 left. Select the drop down to switch between years left and total reserves
  • The third tab shows how consumption has changed with time and population. You can use the page controls to cycle through the years. Note the general upwards trend. Select just Asia and look at the tracks – how China’s population hasn’t grown as quite quickly as India’s, but consumption has increased much more. The size of the the mark is proportional to the consumption per capita – notice how small China’s is compared to Singapore’s

You can interact with the smaller version of the dashboard below, or go the full size one here.

Methodology: Data was held in separate tables on the government website. I cleaned these in the excellent Data Wrangler, removing blank lines and  reshaping the data so that the year was not held in separate columns. The resulting data was brought into Tableau and the tables tied together by establishing joins between the countries and years. This was much more efficient than using data blending techniques and quicker than making a master table. Only a little cleanup was needed in Tableau, mostly around country names (South Korea, Korea, South). Fifteen or so calculations were created, and advanced visualization techniques like bar charts in the tooltips created – hover over Canada for example (thank you Andy Cotgreave).

15 Comments


  1. Richard Leeke
    May 28, 2011

    Nice job Alex.

    The tooltip bar charts don’t look right when you select normalisation by population. Does Andy’s technique come with a warranty?


    • Alex Kerin
      May 28, 2011

      Thank you Richard. The tooltips should be better now, though some missing data in 2009 is an issue for some countries.


  2. Alex Kerin
    May 28, 2011

    This graphic has been picked up nicely on Twitter – 23 retweets thanks to my followers (one in particular with 124,000 followers). Comments welcome.


  3. Andy Cotgreave
    May 28, 2011

    Great work Alex – this is a very interesting viz, and what I particularly like is the fact that, with your commentary, it really encourages exploration of the various filters. I really like the scatterplot – it demonstrates very clearly how Africa, while still small in terms of consumption, is rapidly growing.


  4. Serj
    May 29, 2011

    Great viz, Alex!
    Could you give me the direct link to download this data from eia.gov?
    I can’t find it myself :(
    Thank you.


  5. Alex Kerin
    May 29, 2011

    Serj, each separate table can be found from here: http://www.eia.gov/cfapps/ipdbproject/IEDIndex3.cfm?tid=5&pid=53&aid=1 If you wanted only the same datasets as I use, you could extract them from the workbook after downloading it.


    • Serj
      May 29, 2011

      Thank you, Alex!


  6. Alex Kerin
    May 29, 2011

    This is interesting: http://www.upi.com/Business_News/Energy-Resources/2011/05/03/US-becomes-net-fuel-exporter/UPI-38911304425703/ – The US is a net exporter of refined product (we import crude to the Gulf Coast refineries and then export. That said, my data from 2009 shows the US as a net exporter of refined.


  7. jerome cukier
    May 31, 2011

    Nice job Alex. I like the care in the execution, the choice of colors and the balance in the size of the various elements


    • Alex Kerin
      May 31, 2011

      Thank you Jerome, I learned a lot making this one.


  8. Amish
    Aug 16, 2011

    Would you be able to explain the “Up” and “Down” piece of the viz in a little more detail? I love the information it is conveying and would like to use something similar in a dashboard I’m building, but can’t quite figure out what you’ve done there. Any help or how-to on that would be IMMENSELY useful!


  9. General Ledger
    Sep 04, 2011

    Alex,
    Very interesting material.
    As you are someone who often creates graphic images to convey information, let me make a suggestion that might be of interest. Please keep in mind a portion of your audience likely will have at least some degree of color blindness. About 17% of the population is color blind.

    In your graph of Exporters and Importers, you used green and brown to depict two pieces of information. Green and brown are two colors that are difficult for many color blind people to easily distinguish. I often try to use formats other than colors or in addition to colors to communicate better to the user. I like using different shapes such as squares and circles, or up and down arrows. Different fill patterns such a solids and stripes are also a good option. Using formatting besides colors offers an additional benefit. If the user prints the material in black and white, the degree of usefulness of the colors is diminished. Shapes and patterns are not impacted by the color used on the output.


    • Alex Kerin
      Sep 04, 2011

      John, absolutely right – I’ve even written blog posts about it myself here. I don’t know why I didn’t use different colors.


  10. Thomas Laussermair
    Mar 05, 2012

    Great viz and useful description of methodology. Nifty little trick with the Alt 2-1-9 char by Andy Cotgreave.
    Speaking of tips & tools used: Has Data Wrangler become a product? I haven’t seen any announcement – only the initial research work at Stanford.


  11. admin
    Mar 05, 2012

    Thanks Thomas – if I were to change one thing, it would be the red/green coloring. Per Data Wrangler, their last post was on the blog was almost a year ago, so I guess we may be done with that project…

Leave a Reply