Excel Chart Misrepresents CO2 – Temperature Relationship

This post reviews an Excel chart that misrepresents the CO2 – temperature anomaly relationship. The chart developer shows 2 time series on a single chart using Excel’s double Y axis capability. This is a poor charting technique which distorts the data. The developer makes things worse by only showing 15 years of data from a data set that stretches back over 100 years.

I have extended the data period to 1880 – 2008 and prepared  2 R based charts, a trend chart and a scatter plot of CO2 versus temperature anomaly.  My charts provide a more complete picture of the CO2 – temperature relationship than the original Excel chart.

Google document links to the data and R script files are provided so that readers can prepare their own  CO2 – temperature charts in R, Excel or other tools to assess the situation for themselves.

Introduction

Australian Senator Steve Fielding wrote a post on Climate Change that included the chart below. I assume it was developed in Excel 2003 because of the tell-tale default gray fill.

Senator Fielding's CO2 - Temperature Anomaly Chart

Senator Fielding's CO2 - Temperature Anomaly Chart

The post states “.. global temperatures have remained steady over the past 10-15 years despite skyrocketing man made carbon emissions. “

Senator Fielding has focused on a short time period and has ignored the role of El Nino -La Nina events. 1998 saw a major El Nino event, raising temperatures while 2008 saw a significant La Nina, lowering temperatures.  I have covered El Nino -La Nina in previous posts, here, here and here.

Andrew Bolt, a columnist with the Herald Statesman, picked up on Senator Fielding chart and wrote a post, asking Al Gore to explain the chart. Bolt’s post generated 224 comments, obviously a lot of people are interested in the CO2 – temperature relationship.

Let’s Look at the Long Term Data Trend

Senator Fielding’s chart only shows the 1995 – 2009 period, the last 15 years.  Here’s an R based chart that looks at CO2 and temperature anomalies for the 1880 – 2008 period, 128 years.

CO2_temp_trend

This longer term chart gives a different perspective on the situation. It shows a fluctuating temperature pattern with a clearly rising trend for both temperature and CO2.

To build my CO2 – temperature chart, I augmented the Mauna Loa CO2 data series with Law Dome data and used the Hadley Centre temperature anomaly series (data link) to extend the same variables that Senator Fielding used. I have used annual time series in my analysis so that I could take advantage of the law Dome CO2 data in the 1880 – 1959 period before the Mauna Loa Observatory started collecting monthly CO2 measurements.

The Hadley temperature anomalies have fluctuated widely in the past, however, it is clear that there is a definite upward trend in the overall rate. The CO2 trend is also upward.

Showing parallel trend charts is one way to evaluate the CO2 – temperature relationship, a scatter plot of CO2 versus temperature anomalies provides another view that helps us to see the relationship. Here’s my scatter plot and linear regression of the CO2 versus temperature anomaly.

CO2_temp_scatter_regression.

I have color coded the data to distinguish the Law Dome and Mauna Loa CO2 observations as well as Senator Fielding’s study period (1995-2009). My plot includes the regression relationship which shows that temperature anomaly has increased 0.0092 C for each 1 ppmv increase in CO2.

The CO2 level in 1880 was 291 ppmv, in 2008 it increased to 385 ppmv, approximately 94 ppmv in 128 years. Based on the CO2 – temperature anomaly regression, the 94 ppmv increase in CO2 would generate a 0.86 C increase in temperature anomaly.

Google Documents Data and Scripts

Climate change is an important and controversial topic that warrants careful analysis. I encourage the charts & graphs, data visualization community to participate in the dialog. To help, I am building a series of CSV files to make it as easy as possible for readers to prepare your own climate related visualizations.

My data files are available as Google spreadsheets which can be copied and pasted or exported as CSV files. Here are the links:

My R scripts are available as Google documents here:

I encourage data visualization practitioners  to contribute their assessments to this complex and important global issue.

Please let me know if you have any difficulty accessing the Google document data or R script files. I’m looking for a way to make the data files and scripts easily accessible. Hopefully, Google documents works for everyone.

About these ads

14 responses to “Excel Chart Misrepresents CO2 – Temperature Relationship

  1. In your 1880 – 2008 CO2 vs temp plot, the left and right axes should be scaled so that they match up with the relationship shown by your linear regression equation.

  2. Hi
    Senator Fielding is still brandishing his ridiculous graph. Unaware that you had already checked this out, I loaded the data into some R programs and when the two sets of data are plotted on comparable scales, the result is nothing like the distortion in Fieldings graph.
    With his different scales, even if the co2 and temperature anomalies plotted over time had exactly the same slope, in Fieldings coordinate system, they would appear as different graphs.
    Fielding often complains about data being distorted by climate scientists, but his graph is one of the worst pieces of deception and manipulation I have seen in this debate
    Thanks for your work and interesting posts from others!

  3. Pingback: CO2 and Temperature Trends « Charts & Graphs with R

  4. Hi Kelly,

    I agree with you that normalization of the trends in the actual data is much more sensible since it facilitates comparison of trends with similar scale along the one axis.

    I should have been more selective in my criticism of the earlier method making it clear that the difficulty was not with the normalization as such but with the combination of normalization and the dual ‘y-axes’. That seems to me (and to you, I think) to invite potentially misleading comparisons.

    My second point was that the comparison of the actual CO2 record and the temperature anomaly record (based on an arbitrary baseline) could also mislead: it is better to compare the raw data in each case since the theory that we are trying to assess relates to the raw data rather than the anomaly data. We know from your wonderful graph showing the swings in the decadal trends in the GISS record that an arbitrary baseline is unlikely to capture the full story about the temperature trend.

    Your representation of the woodfortrees.org data is much more informative than mine (where differences in scale make the trends disappear). But I think it’s important that your (normalized) plot of trends in the actual data does not show anything resembling the close correlation that your ‘dual axis’ reconstruction based on the temperature anomalies suggests.

    Best wishes,

    Peter

  5. Hi Kelly,

    The comments in your R code are right, I think. It’s a bad idea to use double Y-axes..

    What in fact your chart does is to allow R to ‘normalize’ the data by situating the plot of each series in the centre of the plot area. Thereby, the chart invites us to make correlations where they may or may not make any sense.

    I think this is a case where they don’t make sense even if they appear to endorse one view of the relationship between ‘globally averaged’ temperature (whatever that is) and measured CO2 concentrations. There’s physical justification for a CO2/Radiative-Forcing link, and the latter implies at least some surface temperature impact (how much is the $64k question).But there’s no underlying justification for a relationship between CO2 concentrations and an arbitrary temperature ANOMALY based on average 1961-1990 ‘globally averaged’ measurements. That’s not even the theory that we’re trying to evaluate.

    If you were to plot the ACTUAL temperature changes against the ACTUAL CO2 concentrations you display, using a neutral (e.g. zero-based) scale your chart would look very different of course (just as it looks different from Steven Fielding’s chart which uses a zero-based anomaly scale). The slopes of the two statistical series are in no way similar. Here’s the Hadcrut Global Temp trend (OLS) versus the Mauna Loa trend (OLS) since 1959 when the ML series starts — plotted the lazy way using woodfortrees.org.

    Best wishes,

    Peter

  6. “This longer term chart gives a different perspective on the situation.” –

    It does! however:

    1. The graph only shows that CO2 tends to rise and fall in line with temperature changes.

    2. A Change in temperature of 1 degree per century is well within the standard deviation of 2.5 per experienced in the current holocene period.

  7. By the way, your comments are posted in a confusing order. Most blogs with a normal amount of comments (less than hundreds per post) just list them oldest first, and the threading works in the same order as the initial posts.

  8. Kelly –

    Thanks for the link to Hadley’s book. I know at some point I’ll branch into something like R for better graphical displays.

    My work is pretty much all in Excel, and not all of that is graphical work. Much less than half, in fact. With this in mind, it’s not likely that too many of my clients will be interested in an add-on like R. Especially considering how hard some IT departments make installing a small Excel add-in.

    RExcel looks like a promising bridge between R and Excel. We will have to rely on third party tools like this, because I don’t think it’s likely that Microsoft will build something like this by themselves.

  9. I wasn’t necessarily sticking up for Excel. I was just pointing out that someone who wants to distort the truth can do so using a wide variety of media.

    Excel is so handy, even senators have access to it (and they’re only lying if their lips are moving). People who are interested in misrepresenting data are not likely to have fancier tools on hand, nor to have the background and experience to use them effectively, either. Therefore you’re less likely to find a bad chart in R or in, say, Tableau, than in PowerPoint or Excel. The distance between casual users and these packages helps to prevent their misuse, while at the same time, hinders dissemination of best practices.

    And of course, Excel does make it too easy to do it wrong. Bad color defaults and poor options to improve the formatting. Too easy access to pie charts and other bad types. Too easy to add meaningless 3D perspectives and gratuitous visual effects. Too hard to make it look right, unless you’re obsessed like me, so why bother.

    • Jon

      I know you are never going to leave Excel charting, however, I suggest that you take a look at Hadley Wickham’s book. You can still get it free on-line.

      The subtitle says it all for me.. “Elegant Graphics for Data Analysis”.

      Hadley has implemented Leland Wilkerson’s Grammar of Graphics.

      If you casually glance through Hadley’s e-book, you’ll get an idea of how powerful the grammar is and how easy it to developed sophisticated panel charts, loess fits, other advanced techniques in a R with ggplot2.

      Here’s a link to an on-line graphics tool that uses ggplot2 as the graphics engine. Just select a compaany and start playing with the menus.

      The long term solution is to use a combination of R and Excel. Just like the Stockplot example above, users should be able to push an Excel button and get high quality R graphics without having o know or learn R.

      You may know that SAS and SPSS have built bridges to R. There is an RExcel tool and book. Some day, Microsoft will see the light and build their own bridge to R.

  10. Your title should be “Politician’s Chart Misrepresents CO2 – Temperature Relationship”. You can lie with charts using any graphics tool.

    • Jon

      A good statistical charting package should help users avoid ineffective/ improper techniques. R makes it difficult to use a double axis, not because no one thought of it, rather, it is not considered a good technique.

      Hadley Wickham, in his ggplot2 package, has deliberately not provided a double axis capability. Here’s a link to a discussion on ggplot2’s missing double axis capability on the the ggplot2 forum.

      It’s much harder to go wrong with ggplot2 than with Excel.

      To be fair to Excel, I’ll post about the next improper R chart that I see.

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