Even the experts can't agree on the best way to find long-term trends in air pollution data. But we had to figure something out for our "Last Gasp" project, which made the point that air pollution was worsening in parts of California's Central Valley even as it improved in Los Angeles.
Here's what we did. Our data came from California's Air Resources Board, which offers a free CD with data, maps and a nifty interactive program for displaying both. The board has a Web site (www.arb.ca.gov/aqd/aqdcd/aqdcd.htm) with downloadable hourly monitoring data back to 1980.
Most of the data is in dBASE files that can be imported into Microsoft Access with a couple of keystrokes. From there, you can construct queries to extract data for a single monitoring station (station ID numbers and locations are in a separate file) and export the resulting data to Excel.
The files have readings for a dozen pollutants, presented more than 20 different ways. Unfortunately, the raw data is just a jumble of numbers. Even if you scanned the entire file from top to bottom, it would be impossible to figure out the trend.
You can use the Excel Chart Wizard to create, for example, a line graph for the date and one-hour ozone levels, but even that doesn't help much.
Because ozone is a summertime pollutant, the levels are high in the summer and approach zero in the winter. If you eyeball the graph, it looks as if the summertime peaks are edging upward, but it's hard to say for sure.
What we needed was a way to boil down these peaks and valleys to a long-term trend.
One of the ways that statisticians find patterns in data is to do a linear regression, and Excel fortunately has a built-in way to add a regression line to the chart we built. After you've finished the chart, just right-click on the data line and click Add Trendline, and you can lay down a nice, clean regression line over the data.
Doing that may show what appears to be a long-term rising trend in the data. There's just one thing wrong: As any competent statistician will tell you (and lucky for us, one did), a simple linear regression doesn't work with data that involves a time series like this one. The seasonal pattern in this data is so strong that it overwhelms any other pattern that a regression analysis might show you.
This problem is not unique to air pollution data. Economists run into it when they're studying things like retail sales, which rise and fall with the calendar. And there are tools available for dealing with it, such as the Census Bureau's X-ARIMA model. But even if you have the intellectual capacity to use something like that, you're still left with a significant problem - explaining it to your readers. Just saying, "Trust me, my computer says there's a trend here" probably isn't the best approach.
Here's another alternative: A 365-day (or annual) moving average. It's not too hard to explain: for each day, it's just the average of the past year's daily values. That's why it's called a "moving" average. If today is June 5, 2003, then the 365-day moving average for today is the average of all daily values from June 6, 2002 through today. Tomorrow's 365-day moving average, in turn, would be the average of daily values from June 7, 2002 through June 6, 2003.
Here's how you write a formula to get that from Excel. Suppose you have date labels in column A and data for several years in column B. Calculate the first annual average in cell C366 with this formula: =average(b2:b366
Copy that formula down and graph the result, and you can say with some confidence that the 1990s saw one-hour daily ozone peaks decline edge upward or downward, or remain about the same, depending on the data. The above chart shows contrasting trends (using the 365-day moving average) at two stations - one in southern California that is declining and one in the San Joaquin Valley near Fresno that is rising somewhat.
At this point in our reporting, we are ready for quality control. When we did this in real life, we showed our graphs to various experts at the state and local air pollution control agencies. We also explained how we did the analysis and asked for their comments on it.
All agreed that the 365-day moving average was a valid way to show the long-term trend. But one expert went a step further. He encouraged us to throw out the winter values and look only at the summer data. His reasoning: Although no one knows for sure, it's considered likely that very low levels of ozone are not a health threat. The state's one-hour ozone standard of 0.09 parts per million is exceeded regularly in the summer. But in winter, typical levels are far less than that. And an increase from 0.02 to 0.03 is clearly not as important as an increase from 0.12 to 0.13. Yet our 365-day moving average would regard them both as important.
The expert suggested a number of alternative approaches. We finally chose one that was doubly elegant, being both easy to calculate and easy to explain. For each year, we calculated and plotted just one value - the average daily peak for the summer smog season, May 1 through Oct. 31.
You can read the "Last Gasp" project on the Web at www.valleyairquality.com