R Lattice Plot Beats Excel Stacked Area Trend Chart

Start of Series on Switch to R for Advanced Charts

I have been a long time Excel chart user, with dozens of techniques and tools for advanced Excel charts. As I pointed out in this post,  Excel’s multivariate data visualization limitations are severe. For me, the best strategy is to switch to R for advanced charts and continue to use Excel for my data analysis and simple charts. 

This post starts a series on my transition to R that may be of interest to those Excel chart users who struggle with Excel’s limited multivariate chart capabilities. I will post videos and provide source data files as well as R scripts on my ProcessTrends.com website for those who want to try R for themselves.

Working Example

In learning R graphics, I will be using the BP Statistical Review of World Energy – June 2008 report for data and examples of ineffective Excel like charts. BP does a great service for all of us by publishing their data rich report and workbook on world energy trends, an incredibly important topic from economic, environmental and security standpoints.  The 45 page report includes 8 charts types and a total of 26 charts. From a data visualization standpoint, 4 of these chart types are acceptable and 4 are ineffective.

BP seems to use Excel – PowerPoint or a similarly weak data visualization tool for their charts. The telling point is the reliance on doughnut, stacked area trend charts, stacked column charts and clustered column charts to show multivariate data. I have previously discussed BP’s chart selection in my Chart Doctor page.

Stacked Area Trend Charts

BP’s Statistical Review includes 11 stacked area trend charts. Let’s take a look at one of them.

This multivariate chart shows 3 variables: oil consumption (Y axis) by year (X axis) and region (stacked area). To add the 3rd dimension to this 2D display, BP stacked the regions with a stacked area chart.

We can see the North American and world total values clearly, however, it is quit difficult to interpret the specific trends for the 5 other regions because their baselines change over time.

I’ve made this R lattice chart to show the oil consumption trends for the 6 regions. Notice that I have added the 1965-1982 data and have not included the worldwide total consumption.

This lattice plot adds the 3rd variable by making a small multiple chart for each region. In this example, the small multiples have the same X and Y scales so comparisons are very straightforward.

The lattice plot shows the individual regional trends clearly, we can see that Asia Pacific consumption has increased dramatically, now equivalent to N American consumption. We can also see that Europe – Eurasia consumption peaked in the late 1970s and has been drifting downward since. We see similar local peaks in N America and Asia Pacific consumption in the late 1970s. We can also see the smaller market share and increasing trends for Africa, S & C America and Middle East.  

The R lattice plot lets me see both the trends and market share for each region much more clearly than the Excel like stacked area trend chart.

Excel’s Missing Multivariate Chart Capabilities

Why use stacked area trend charts? Did BP’s charters use stacked area trend charts because their chart tool doesn’t have small multiples, trellis – lattice chart capabilities?   

Excel charters often use stack & cluster techniques on their their multivariate data because Excel doesn’t have built-in small multiple, trellis – lattice tools. To work around Excel’s multivariate chart limitations, I started making panel charts in 2006.  Once I started looking into R, I quickly realized that Excel work-arounds, tricks, neat techniques aren’t enough. The tool does matter. You need real multivariate  graphical tools to effectively visualize multivariate data. Since Excel’s chart engine doesn’t support effective multivariate data displays, it’s time to switch to a tool that does.

After spending so much time mastering Excel, it just makes me wonder, “.. why the h*** doesn’t Excel have trellis – lattice capabilities? ” Tufte wrote about “small multiples” in his 1983 book, “The Visual Display of Quantitative Information“. That’s 25 years ago. Has the Microsofts Excel Team read Tufte, Few, Robbins, Cleveland? 

I’d like to hear what you think about multivariate charting with Excel.





3 responses to “R Lattice Plot Beats Excel Stacked Area Trend Chart

  1. paresh

    Several points – thanks.

    1. Camera tool is a powerful tool. I use it in some situations. However, there is a bug in Excel2003 which casues problems if the user uses Print Preview.

    2. Small multiples – Yes you can make a series of small multiples in Excel, then line them up to meet your needs. There are issues with this approach, including repeat of axis labels and tick marks for common axis and Y axis alignment for charts with different number of significant figures.

    3. Panel Chart – I have made many of these in Excel with straight Excel and with VBA, link. They are ok,however, it takes a good bit of time and they are not as flexible as R with its built in conditioning capabilities.

  2. Just a query – why not use the camera tool in excel to create panel charts – the charts can be done in one sheet and the panel created on the display sheet ?

  3. Pingback: R Lattice Plot Beats Excel Stacked Column Chart « Charts & Graphs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s