Opensource Temperature Data Monitoring Spreadsheet Updating Methodology

Image Credit: Walter Dnes

By Walter Dnes – Edited and Comment by Just The Facts and Werner Brozek

In my recent WUWT post I introduced an Opensource Temperature Data Monitoring Spreadsheet. The intent of this spreadsheet is to provide some of the key global temperature data sets, in a standardized data format summary, so that you can easily leverage these data sets for research. In this post I explain how to update the spreadsheet, and at the end of this post is comment from Just The Facts on the base periods used for each of the data sets spreadsheet.

This thread will have two parallel tracks, i.e.;

• One for people who want to copy the online spreadsheet to their Google account. This will be referred to as “online”.
• One for people who want to download the spreadsheet to their hard drives. This will be referred to as “local”.

To download the spreadsheet to your local computer:

1. point your web browser to https://docs.google.com/spreadsheet/ccc?key=0AnTohu4oFUbcdEgzTkpEYTAwN1BiXzJXMXZ5RVJiOUE&usp=sharing
2. select “File ==> Download as” from the spreadsheet menu (highlighted in blue in the image below), not the browser menu, (highlighted in red in the image below)
3. choose the file type you want and download; .xlsx for excel; .ods for Openoffice/Libreoffice

The graphs in Google spreadsheet don’t always translate well during the download process. You may have to rebuild them yourself. Here are the specs if necessary…

All graphs are XY graphs, and refer to data in tab “temp_data”

Column A is the X axis for all graphs. The Y data for slope graphs are as follows…

• HadCRUT3 column I
• HadCRUT4 column J
• GISS column K
• UAH column L
• RSS column M
• NOAA column N

For the 12 month running means, the header row is cells P11 through U11.

And the Y data is columns P through U from row 12 on down.

IMPORTANT NOTES FOR ONLINE VERSION USERS.

• You will usually see 2 sets of menus, one from your web browser, and one from the online spreadsheet. Unless otherwise stated, all references to File/Edit/View/etc will be to the spreadsheet menu (highlighted in blue in the image above), not your web browser’s menu, (highlighted in red in the image above)
• The numbers in column A are as they were originally imported from my PC. Do not attempt to reformat column A. Google sticks in commas in the year values when you reformat the column. Years with commas don’t look right. This is bad enough… *BUT IT ALSO CARRIES THROUGH TO ANY GRAPHS WHICH USE THE YEAR VALUE*! I’ve seen posts on the internet claiming that you can format a number as “plain text” and use it for mathematical operations, while hiding the commas. That may be true, but it has been my experience that “plain text” formatted numbers do not work with graphs in Google’s spreadsheet.
• Just as important as being able to do stuff, is being able to undo stuff, especially when you do “the wrong type of oops programming”. There are 2 options in Google spreadsheets. First is the standard {CTRL-Z} key combo that undoes the previous step. You can undo several steps this way. If you want to go back further in time, go to the spreadsheet menu and click on “File ==> See revision history”. Note that this can take a minute or two to render on the screen. Don’t panic while waiting. Once you step back to an earlier version, I don’t know if you can revert to a later version.

Obtaining the temperature anomaly data sets.

The various data sets are updated at different times of the month. The following are approximations, and vary from month to month.

• UAH and RSS around the 7th of the following month
• GISS and NOAA/NCDC around the 15th of the following month
• HadCRUT (v3 and v4) near the end of the following month

The data can be downloaded for free from the appropriate website. The source URLs for the data may change from time to time. As of the time of this post (early June 2013), they are…

• HadCRUT3 http://hadobs.metoffice.com/hadcrut3/diagnostics/global/nh+sh/monthly
• HadCRUT4 http://www.metoffice.gov.uk/hadobs/hadcrut4/data/current/time_series/HadCRUT.4.2.0.0.monthly_ns_avg.txt
• GISS http://data.giss.nasa.gov/gistemp/tabledata_v3/GLB.Ts+dSST.txt
• UAH http://vortex.nsstc.uah.edu/public/msu/t2lt/tltglhmam_5.5
• RSS ftp://ftp.ssmi.com/msu/monthly_time_series/rss_monthly_msu_amsu_channel_tlt_anomalies_land_and_ocean_v03_3.txt
• NOAA/NCDC ftp://ftp.ncdc.noaa.gov/pub/data/anomalies/monthly.land_ocean.90S.90N.df_1901-2000mean.dat

The data are all text files. Due to filename extensions and/or mime-type declarations by the webmaster, some of the data sets may not be directly viewable with a web browser. You can still download them to disk and view with a plaintext editor.

Preprocessing into normalized/column format (GISS only)

All of the sites listed above, except GISS, download in column format, which is suitable for importing directly into a spreadsheet. The GISS download must first be reformatted into column format. That is easy with a bash script under linux or a Powershell script under Windows. The scripts will be covered later in this post. In addition, GISS data is integers, representing anomaly * 100. To get the actual monthly anomaly, divide by 100.

Overwriting the appropriate column in tab “temp_data”

The next step is to copy updated data to its column in tab “temp_data”.

IMPORTANT NOTES!!!

You can *NOT* get away with appending the latest month’s data to the columns in tab “temp_data”. HadCRUT data changes 6 months or so back, apprently late data coming in, and/or corrections. NOAA and GISS use a weird averaging algorithm for old missing data, which results in numbers changing all the way back to day 1 (i.e. January 1880) from month to month. To ensure a completely correct spreadsheet, you must overwrite the entire column with the numbers from this month’s download.

The imported data is *NOT* always copied starting in row 1. HadCRUT data (v3 and v4) is the oldest, going back to January 1850 (1850.083). That is arbitrarily assigned as row 1. NOAA and GISS start in January 1880 (1880.083), i.e. Row 361. UAH starts December 1978 (1979.000), ie row 1548. RSS starts in January 1979 (1979.083), i.e. row 1549. If you wish to add other data sets to your copy of the spreadsheet, be certain that the dates align properly.

The procedure is…

• get the monthly temperature anomaly download
• import it INTO A SEPARATE SPREADSHEET
• select and copy the range of data cells in the separate spreadsheet, from the anomaly data column. Copy the range from the top row of the column of data to the bottom row of the column of data.

At this point, instructions diverge for local versus online users.

For local spreadsheet users

paste the copied range into “temps_raw”, such that the start of the paste range is the start of data in the column. That’s it for the monthly anomaly.

For online spreadsheet users

• paste the copied range at the top of a separate page, or a separate

  spreadsheet. The important thing is that only 1 column of data is

  visible on the page.

• export that page (with 1 column of data) as a CSV file. Remember the

  name of the file you exported to. Don’t worry if your spreadsheet

  complains that it’ll only save one page. That’s exactly what you want.

• log in to your copy of the online Google spreadsheet.
• go to row 1 in the right-most column of the online spreadsheet. This

  is cell V1 in the original version of the spreadsheet. It will change if you insert or delete columns (adding or removing data sets). Note that it is *VERY IMPORTANT* that you be in the rightmost column. The import process blanks 4 columns to the right of the one you’re importing. This includes wiping existing data if you import anywhere except at the far right… OUCH!

• from the spreadsheet menu select “File ==> Import…”. This brings up an “Import file” dialogue.
• click the “Replace data starting at selected cell” radio button.
• click on the “Browse…” button, and select the CSV file that you exported a few steps ago.
• this brings up a preview, and a “Separator character” option. You can ignore it, because you only have one column of data.
• click on the “Import” button. When the import is finished, you should

  see data in column V

• before copying over the data, the number of decimal places should agree with the destination column. The copy command copies the format, so you need to set the number of digits after the decimal. GISS uses 2 digits, NOAA uses 4, and the others use 3. If the number of decimal places displayed does not match…
  • click on the “V” at the top of column “V”. This will highlight the

    entire column.

  • click on “Format => Number” in the spreadsheet menu.
  • click on “Custom Decimals…” as show in the following image.
  • this will give you a dialogue box asking for the number of decimal

    places. Enter the number of decimal places you need.

Common instructions for both local and online versions

After the monthly temperatures have been updated, the slope and 12 month means need to be updated manually. The columns containing data sets are as follows…

Here is what I did to update NOAA for April 2013 when the data came in.

NOAA data for March extended down to G1959

NOAA data for April extends down to G1960

Slope

For March, cell N1681 had the formula

=slope(G1681:G$1959,$A1681:$A$1959)

and the slope data extended down to cell N1958

For April changed cell N1681 to =slope(G1681:G$1960,$A1681:$A$1960)

• goto cell N1681
• {CTRL}{INSERT}
• goto cell N1682
• extend select to N1959
• {SHIFT}{INSERT}

12 month running means

• goto cell U1959
• {CTRL}{INSERT}
• goto cell U1960
• {SHIFT}{INSERT}

The affected graphs will update automatically.

Converting GISS temperature downloads to column format

The GISS monthly anomaly data is downloadable as a textfile, at URL http://data.giss.nasa.gov/gistemp/tabledata_v3/GLB.Ts+dSST.txt The textfile looks nice, but is not suited for importing into a spreadsheet. I run linux, so I use a bash script to convert it to columnar format, for use in a spreadsheet. I have also put together a Windows Powershell script, to do the same conversion under Windows. In both examples below, assume that you have downloaded the February 2013 data as giss201302.txt and that you wish to save the column-reformatted data to g201302.txt. Both posix and Windows users need to remember to divide the reformatted temperatures by 100, to convert from the unusual GISS style to degrees C.

For posix (linux/unix/bsd/etc) users

Windows users, skip to the “For Windows users” section

The bash script is called “parsegiss” and is executed as follows

parsegiss input_file > output_file

e.g.

parsegiss giss201302.txt > g201302.txt

Here is the bash script. Copy the following to a text file, and chmod it

executable. The script is 19 lines long.

#!/bin/bash

rm temp1.txt

grep "^[12]" ${1} | sed "s/\*\*\*\*\*.*$/ /g" > temp0.txt

decim=(000 083 167 250 333 417 500 583 667 750 833 917 000)

wyear=`head -1 temp0.txt | cut -c1-4`

while read xyear m[1] m[2] m[3] m[4] m[5] m[6] m[7] m[8] m[9] m[10] m[11] m[12] year_avg

do

if [[ ${xyear} -ge ${wyear} ]]; then

   xmonth=1

   while [[ xmonth -le 11 ]]

   do

      echo "${xyear}.${decim[${xmonth}]}, ${m[${xmonth}]}" >> temp1.txt

      xmonth=$(( ${xmonth} + 1 ))

   done

   wyear=$((${wyear} + 1))

   echo "${wyear}.${decim[${xmonth}]}, ${m[${xmonth}]}" >> temp1.txt

fi

done a.txt

grep -v ", $" temp1.txt

For Windows users

Powershell comes built-in to Windows 8, and is available as a free

download, from microsoft.com, for some earlier versions of Windows. This

is the standard Windows scripting tool. It requires a bit of setup. I’ll

deal with that here, to save some questions. Note, if you are using a

work PC, or someone else’s PC, please get their permission before

editing the user profile.

• By default, Powershell comes up in restricted mode. I.e. it will

  execute only commands typed in at the command prompt. It will not

  execute Powershell scripts.

• The minimum necessary permissions to execute your own scripts are

  obtained by typing the command…

  Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

  …at the Powershell prompt each time you start a Powershell session.

  Needless to say, this is annoying grunt work.

• This command can be included in your user profile, and will be automatically executed from there, each time you open Powershell. To do this…
  • open a Powershell window, and type the command…

    notepad $profile

  • if it opens up, add the line…

    Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

    …to the profile, and save.

  • if you get an error message, about no such file, create an empty

    profile file. At the Powershell prompt type the command…

    New-Item -path $profile -type file -force

  • in the Powershell window, type the command…

    notepad $profile

• Once you’ve set up the profile, close Powershell, and open it again, to have the profile take effect.

Now that you have Powershell scripts executing, copy the script below to file “parsegiss.ps1”. Using the February 2013 data example, type the following command at the Powershell prompt

parsegiss giss201302.txt | Out-File g201302.txt -encoding ascii

This example assumes your encoding locale is ASCII. Change as necessary if you’re in a part of the world that uses a different encoding. Here is the script powershell.ps1, which is 14 lines long.

findstr /B 1 $args[0] > a.txt

findstr /B 2 $args[0] >> a.txt

$linearray = $(type a.txt)

for ($i=0; $i -lt $linearray.Count; $i++)

{

   [double]$baseyear = $linearray[$i].Substring(0, 4)

   for ($j=1; $j -le 12; $j++)

   {

       $deciyear = $baseyear + $j / 12

       [string]$stringyear = "{0:####.000}" -f $deciyear

       $anomaly = $linearray[$i].Substring($($j * 5), 5)

       if($anomaly -ne "*****"){$stringyear + ", " + $anomaly}

   }

}

_ _ _

It is apparent from Walter’s graphs above that the temperature data sets use different base periods. “World Meteorological Organization (WMO) Policy” “suggests using the latest decade for the 30-year average”, i.e. a 1981–2010 base period. However, even the WMO doesn’t comply with it’s own policy. For reference, the base periods for the data sets in the Spreadsheet are as follows:

UAH 1981-2010

RSS 1979-1998

HadCRUT 1961-1990

NOAA 1901-2000

GISS 1951-1980

According to this WMO Management Report on Climate Normals one of the key arguments for using a “‘rolling’ set of 30 year Normals updated every 10 years (hereafter the 30/10 model)” is that;

The use of more up-to-date Normals under the 30/10 model provides a more realistic base period for climate services. For instance, design standards and climatologies would be based on a more representative standard that better reflects possible changes in climate. By contrast, basing design standards etc on climate Normals that are up to 30 years out of date might raise significant credibility problems with the users of services and products (“can’t we get more recent data than this?”)

Whereas in “the case against changing” to the 30/10 model and staying with a “30/30 model, i.e., a base period of 30 year normals, updated every 30

years, i.e retain 1961-90 as the base period until 2021, when 1991-2020 will become the new base period” the key argument for staying with the 30/30 model is:

Some NMHSs would face large increases in workload to revise products and services currently based on the 1961-90 period. Knowing that the base period will need to be updated in 2020 is a very different proposition to having to update within perhaps the next two to three years, and then (depending on what base period model is selected) having to face regular updates thereafter. This problem might be mitigated if, for instance, WMO provided software to regularly update normals (based on agreed common standards with regard to, e.g., missing data), which may be possible with CDMS. Alternatively, the normals could be calculated centrally by global agencies such as NCDC.

One might wonder why GISS fails to use 30/10 or 30/30, and instead uses 1951-1980. According to James Hansen:

“We calculate seasonal-mean temperature anomalies relative to average temperature in the base period 1951-1980. This is an appropriate base period because global temperature was relatively stable and still within the Holocene range to which humanity and other planetary life are adapted (note 1).”

“In contrast, we infer that current global temperature is above the Holocene range, as evidenced by the fact that the ice sheets in both hemispheres are now rapidly shedding mass (Rignot et al., 2011) and sea level is rising (Nerem et al., 2006) at a rate (more than 3 mm/year or 3 m/millennium) that is much higher than the rate of sea level change during the past several millennia.”

The key takeaway from this is to take care as some of the temperature data sets in the spreadsheet have intentionally arbitrary and knowingly outdated base periods.

JTF