Temperature Trends

This page focuses on collecting and analyzing European and USA  temperature data and trends.  Hands on Excel tools are used to analyze readily available climate data and investigate temperature trends. Readers are encouraged to download the Excel files to learn how to  investigate  temperature trends in your area of  the world.

The European Climate Assessment and Dataset (ECAD) site maintains files of continuous daily mean temperature data for 100's of European stations. The Stockholm, Sweden station has continuous daily mean temperature data from 1756. I have downloaded the daily data Zip file from  ECAD and analyzed the Stockholm dataset to demonstrate nine ways to analyze temperature trend data.

250 years of daily Stockholm mean temperature data were summarized into annual mean temperatures so that I could assess the long term climate trends. The Stockholm Temperature Trend Analyzer workbook (link) includes ten (10) trend analysis tools. The tools and charts are described below.

Technique	Example Chart (Click  thumbnail)
Annual Trend Chart - Trend charts are useful to get a sense of the long term trends. For Stockholm, annual temperatures have increased since about 1960, however, we can not make any definitive statements by just looking at this trend chart.
Moving Average - Since temperatures vary from year to year, a ten year moving average chart provide information on changes in the overall long term trend. Stockholm's 10 year moving average appears relatively stable in the 1760 - 1950, after 1950, there is a noticeable increase in the 10 year moving average.
Anomaly Chart - Temperature anomaly charts show the difference between each year and the average temperature during a baseline period. The red lines show those years when the mean temperature exceeded the 1951-1980 baseline average, the blue lines show those years when the mean temperature was less than the baseline average. Anomaly charts are particularly useful in comparing the magnitude of temperature changes among locations. Comparing the temperature anomalies of Miami and New York provides more information than just comparing their trends.
Step Chart - Step charts can be used to show the average over a user defined time period, in this case, decades seem appropriate. The long term average is included as well as the decadal average to provide both  a decade to decade comparison as well as decade to long term average comparison.
Box Plot - Box plots show the maximum, minimum, 25 and 75th percentiles as well as the mean. By combining a box plot and a trend chart, we can see decadal annual mean temperature distribution trends over each decade. The Stockholm decadal maximums, minimums, average and 25 and 75th percentile measures all increased since the 1960 decade.
Interactive Regression - Interactive regression allows the user to calculate the best fit for a user selected time span, in this case, 1950 - 2005. Stockholm has  experienced an increase of 0.039oC per year increase since 1950, equivalent to 3.9 oC per century, a high temperature increase rate.
CuSum Chart - Cumulative Sum (CuSum) charts shows "..the cumulative sum of differences between the values and the average. Because the average is subtracted from each value, the cumulative sum also ends at zero." The CUSUM chart trend shows how the individual values compare to the overall average. In periods when the measurements are below the overall average, the CuSum will decrease. In periods when the values are above the overall average, the CuSum will increase. The Stockholm CuSum chart shows a variable but continuous decline in the CuSum from 1756 until about 1930. It then begins to rise, with increasing rate of rise after about 1980.
Change Point Analysis (CPA) - Change Point Analysis (CPA),  based on techniques described by Dr. Wayne Taylor ( Variation.Com) combines the use of CuSum charts and a bootstrapping technique to compute 1,000 or more iterations of the CuSum chart.  This provides the ability to calculate the points where there has been a change or shift in the mean, called change points. The Stockholm temperature shows two change points, 1930 and 1989. The CuSum chart, with these change points, provides graphic evidence that there has been a change in Stockholm's annual average temperature.
Multiple Period Regression  The change point analysis shows that there are at least three distinct temperature periods, 1756-1929, 1930-1988, 1989-2006. The addition of regression lines for these three periods provides information about the rate of temperature change during these periods. Stockholm temperature remained relatively constant in the 1756-1929 period. In the 1930-1988 period, annual temperature rose at a rate of 0.009oC,  In the 1989-2006 period, the  rate doubled to 0.018oC per year.

Getting and Organizing European Temperature Data

The European Climate Assessment & Dataset  (ECAD) site provides daily temperature data for over 960 stations throughout Europe. Users can download a 10mb zipped file that includes text files of historical mean daily temperature data for these stations.

I have created a workbook that includes a list of the 960 European stations, their latitude and longitude as well as the start and end dates for the data records. A VBA procedure allows me to select a station and have it automatically opened in Excel.

To set up your own European temperature trend analysis system, download my workbook and the ECAD files, unzip and save them  in the same folder.

 Understanding the ECAD Text Files

Once you open an ECAD text files in Excel, you will find one column of text. The first 19 lines document the file. The actual temperature data starts on row 20.  There are 4 data items per row: 1) SOUID is the 6 digit station ID, 2) Date is 8 digits with the format (yyyymmdd), 3) TG is the 5 digit representation of the mean daily temperature in 0.1 ^oC units, 4) the Q_TG is a 5 digit quality code for the measurement.

Now's a good time to save your file with a useful name.

Converting Text Data to Usable Numeric Data

Now that we have our historical data in text format, we need to get it into a numeric format. Excel provides a powerful Text to Columns Wizard that let's us make this conversion quickly. 

• Select Data Column to Be converted

• Menu - Data > Text to Columns

• Follow 3 Step Wizard

Excel convert your single column text data into 4 columns of data.

We still need to make some data adjustments to get our temperature trend data into suitable form for charting and data analysis.

First, we need to convert the temperature data form 0.1°C units to °C units. We do this by dividing the column C values by 10.

Next, we need to do something with the date column so that we can summarize temperature data by year, month, or day. since 67 of the  European stations have temperature records before 1/1/1900, we'll need to develop a technique for handling these pre-1900 dates in Excel.

 Converting ECAD Date Format to Excel Format

To simplify calculation of annual mean temperatures, I have found that creating a new columns for the year, month and day with Left, Mid and Right formulas works well. the resulting table looks like this.

The daily data is now in a format that you can use to summarize temperature trends by month, year, decade or any other time period that you prefer.