Category Archives: Multivariate Charts

Time Series Regression of Global Temperature, El Nino – LaNina, and Volcanic Events

In a previous post, I showed how to make a 3 panel chart displaying RSS temperature anomalies,  El Nino- La Nina (NINO34 index) and volcanic event (SATO Index) time series data.  While time series charts can give a visual impression of how time series are related, time series regression provides much more information, including leading – lagging relationships between the time series.

In this post, I analyze the RSS, NINO34 and SATO time series using a simple trend line and a multiple regression model with lag periods for the independent variables. The post demonstrates some of  R ‘s regression analysis and data lagging capabilities. Links to the R script and data files are provided.

Continue reading

Interesting Data Tools: Tinkerplots

There are many data analysis and charting tools that Excel users should check out to see what is available in the world outside Excel. I’ll be posting about interesting tools that I find and/or readers tell me about.  Here’s my first – Tinkerplots Continue reading

R Lattice Plot Beats Excel Stacked Column Chart – Update 3

What’s Wrong With Excel’s Stacked Column Chart  This is my 2nd post on BP Oil Statistical Review of World Energy – June 2008, post 1 is here. In this post, I discuss Excel’s stacked column charts, using BP’s Regional Consumption Pattern  2007 as an example.           First, the good news. While I’m not sure what chart package BP used, this chart looks a lot like an Excel stacked column chart with some color, Y axis position and label enhancements.  The bad news is that BP used a stacked column chart which  is the Excel way of showing three variables on a 2D display. In this case, the variables are: Region of World, Fuel type and percent of energy use by fuel in that region. It is difficult to interpret the values for the internal fuels on the stacked column chart because they do not have a common baseline. Here’s the same data in an R Lattice chart. To me, the R Lattice – trellis chart helps me to see the patterns more clearly than the stacked column chart. Notice how Asia Pacific use of coal sticks out! S & C America have the largest portion of hydroelectric use. Natural gas is used widely, with Middle East having the greatest portion. Nuclear is relatively small, with Europe leading and N America  close behind. Finally, look at oil use, Middle East is greatest user, followed by S & C America and Africa.  Could you see these details in BP’s stacked column chart? Why Are Excel Chart Users Still Using Excel for Multivariate Charts If R is so good and free, then why are Excel charters still using Excel for multivariate charts?  For me, there are two reasons: 

  1. At first, I didn’t know any better. I knew about small multiples from Tufte’s writings, however, I didn’t know there was a free tool that could do trellis – lattice type small multiples.
  2. R Learning Curve  – Naomi Robbins book, Creating More Effective Graphs, introduced me to R. I got excited about R’s capabilities, however, I found the learning curve daunting. It was easier to slip back into comfortable Excel charts rather than learn a new – better way to make charts. 

R Lattice Flexibility In a comment to this post, Tony said ” .. I  typically like to see all of the charts either in a row or column so it’s easier to compare.” Here the lattice plot the way Tony prefers. The original 2 x 3 matrix was the default. by adding a simple layout control, I changed it to a 1x 6 display.  

Update Hadley Wickham, an R heavyweight (that’s a good thing), suggested in his comment that “.. you might also want to use the reorder function to reorder the factor levels in terms of the highest use”. Since it sounded like a good idea from a really experienced R programmer, I decided to give it a try. Here’s my revised chart based on Hadley’s suggested reorder of both the fuel and region factors.

I like it! The fuel panels are now sorted by median percent energy use, with nuclear the lowest and oil the highest. The regions are sorted by magnitude of oil use, with Africa at low end and N America at high end.

Update 2 In my 1st update, I sorted the panels by magnitude of energy use, however, I was not able to sort by regional energy use within panels. I tried several sorting, ordering and reordering approaches to no avail. I finally asked the R Graphics expert, Paul Murrell, the author of R Graphics for help. Thanks again Paul.

Here’s the plot the way I really wanted it.


Update 3

Reader Jaanus had an interesting comment:

“Whilst the charts look nice isn’t the fact that the x-axis doesn’t begin at 0 confusing the message?

Percentage of nuclear energy usage in Middle-East is not that intuitive…I would recommend forcing the x-axis to begin at 0.” 

Here’s the chart with the x-axis starting at 0.


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 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.





Data Loss Aversion II – R Lattice Plot

Click to Enlarge

This post continues Jorges Camoes discussion on data loss aversion.  In my first post on this topic,  I used a dot plot to show the 1967 and 2005 values to summarize relative shifts in households by total household income bracket.   Derek, giving in to data loss aversion, used a logarithmic axis technique to show all data in intervening years between 1967 and 2005. Andreas Lipphardt used small multiples to show 1967 and 2005 values as well as the overall change by bracket. We can combine Dereks “giving into loss aversion” and Andreas’s “small multiples” approaches to show all data for the 9 series in a compact trellis chart by using the R Lattice package. I have a brief discussion on using R  for advanced charting here. This R lattice plot has several advantages:

  • Shows all data  
  • All plots share common X and Y axes, reducing axis labeling
  • Plot uses banking to 45 to enhance visualization
The terms trellis, lattice and panel charts seem to be used interchangably, depending on which software was used to develop the chart. Small multiples is a more generic term that applies to Tufte’s approach of making a series of small, similar charts.
Excel has limited tools for effective multivariate charts, no trellis or lattice charts and no built-in small multiples capabilities.  While we can use clever Excel workarounds like panel charts or manually generated small mutiples,  I find that it is wise to move beyond Excel’s chart limits for multivariate charts.
R, a powerful and free statistical analysis and graphing package, has excellent multivariate charting tools. The R learning curve is well worth it for those Excel charters who want to move beyond Excel to the wider world of advanced charting.