Hourly Weather Data for Times Gone By (Plus Opening a Delimited File with Excel)

In my previous post, I linked you up with resources for climate data, including a way to get hourly data from the National Weather Service for the past 3 to 7 days for many locations.  Frequently, however, I need data for a time gone by;  weeks or even years in the past.  In those situations, I turn to two other resources that I will share in this post.  In the course of the discussion, I will also illustrate how Excel can open a delimited text file so the data ends up in cells and can be manipulated.  This is a useful technique for handling any type of delimited data, not just weather data.

In any case, if I only need a couple of days, or maybe a week of hourly weather data from the past, I usually turn to the Weather Underground.

The Weather Underground web site started out in 1991 as a TELNET interface, developed by University of Michigan PhD candidate Jeff Masters that displayed real time weather from around the world.  He is interviewed in a recent issue of Weatherwise if you are interested in a bit more about him.

The present day site contains a plethora of useful weather information, including hourly historical data for thousands of locations.  To access it, hover your mouse over the “Local Weather” weather tab, which gives you a drop down that includes “History Data”.   Clicking that link takes you to a page where you can pick a location and a date. If you start typing in a location name, a list of matches appear and you can pick one. In this screen shot, I have picked Berkeley for a day last April.

When you select “Submit” you get a page that looks like this.

Note a couple of things in this particular shot;

  • You can toggle to the previous or next day directly from here.
  • There are tabs for weekly, monthly, and custom date ranges.

What you are looking at is the summary for the day. But if you page down, you will find this …

This is a graph for the day. Notice how if you hover over it, you can right click and then copy it to your clipboard. You can then paste it into things. This is a great resource if you just want to show folks the data but don’t need to actually work with it.  For instance, I might paste a chart like this into a report to illustrate what was going on during a test.

But if you want the data to work with and load into a spreadsheet, you need to page down to the table that is below the chart image.

This is the actual hourly data. Note that this is something you can export directly to Excel if you are working in Internet Explorer by hovering over it, right clicking, and then selecting the “Export to Microsoft Excel” option, just as I described in the post about getting recent hourly data from the National Weather Service.

If you are in Firefox, if you page down a bit more, you will notice a “ Comma Delimited File” option.

If you click that, a new page will open up with the table in CSV format.

CSV stands for Comma Separated Value and basically means that a comma is placed between each piece of data to act as an identifier or “delimiter” for the data.  Spaces, semicolons, and tabs are other common delimiters.  The delimiter allows a program like Excel to recognize where when piece of data stops and the next piece of data starts;  thus, it can place data in cells as it loads the information.

To use the data on the web page, copy and paste the it into a text file (file extension *.txt).  There are a number of ways to do this, including pasting the information into Word and then saving it as a .txt. file type.  But the easiest is probably to open a Notepad file, which is .txt by default.  Notepad is a standard Microsoft Windows feature and can be accessed by right clicking on your desktop, then selecting “New” and “Text Document”.

I suspect Macs have a similar feature but don’t know that for sure.

Once you have created your text file, save it and then open with Excel.  Note that you need to do this by opening Excel and then using the “Open” option vs. highlighting the text file on your desktop or in a Windows Explorer window, right clicking, and selecting “Open With”.  For some reason, the latter method does not cause the Excel Text Import Wizard to run and you just end up with a spreadsheet that has all of the data for a row in one cell instead of parsed out so each piece of data is in its own cell.

Also, be sure to use “All Files (*.*)” in the “Files of type” drop down list to see your text file listed in the selection window.  There has been more than one occasion where I thought I had misplaced a text file that I was trying to open with Excel, only to realize that my “view” was being restricted by the default file type selection.

In the course of opening a text file, Excel asks you if the file is delimited …

… and what the delimiter or delimiters are.

Once you have answered those questions, you are given the option of setting the numerical formats for the different columns.

Typically, I make sure the columns with date information in them are formated for “Date” and accept the defaults for everything else.  When you click the “Finish” button, Excel thinks about it for a minute and opens a spreadsheet with your data parsed out into individual cells.

You can now save the file as an Excel file type and work with the data just like you would any other data set.  Note that the first column is time only while the last column is date and time, the latter being useful if you are using this technique to combine multiple days.

And, as I said at the beginning of the post, the steps I just outlined can be used to bring any text type file into Excel.  For instance, I often cut and paste data from a .pdf file into a text file so I can import it into Excel and make a table or graph out of it.

Returning to the Weather Underground discussion, you will discover that the “Weekly” and “Monthly” tabs have similar features to what I have described for the “Daily” tab.

You can even create a custom date range.  But there is one important point with regard to tabs with more than one day’s worth of data.  That is that while the graphs seem to show the hourly data for whatever range is associated with the tab, the table is just the nominal values for each day of the week or month.

So, to build up, say a week of hourly data from the past, you have to select each day and then put them together in a spreadsheet. For me, if I need more than a week of data, it’s probably more cost effective and faster to go to the NWS and spend the $20-25 that it costs to get a year’s worth of hourly data for a nearby location. Here is the link to the web site that will provide you access to the data.

http://cdo.ncdc.noaa.gov/pls/plclimprod/poemain.accessrouter?datasetabbv=DS3505

Following the link gets you to this page …

I have only used the “SIMPLIFIED” option, although I looked at the “ADVANCED” option once, which is what causes me continue to select the “SIMPLIFIED” option. Picking “SIMPLIFIED” gets you get to here after agreeing to a legal disclaimer.

Once you select a country, a couple of more fairly self explanatory screens allow you to pick your state …

… specific location …

… and date range.

Once you do that, you put your request in a shopping cart, which sends you to a different site where you can pay with a credit card.  Once you have paid for the data, after a slight delay – usually about 5-10 minutes – you will get a link to an FPT site and your files will be sitting there.

Bottom line is that you can open the CSV file that you will obtain using Excel and end up with something like this.

Since its all spreadsheet data, you can graph it and manipulate it any way that you like.  In fact, if you have the Get Psyched plug in for Excel (about $50 from K-W Engineering;  follow the link), you can use the temperature and dew point data to calculate other parameters like relative humidity, humidity ratio, specific volume, and wet bulb temperature.

Its just like a psych chart;  if you know any two parameters you can come up with the rest of them.  In fact, the plug-in will draw a psych chart for your, right there in your Excel file.

Or, you can use standard Excel functions like “COUNTIFS” to create bin data or degree day data.  Here is an example of creating a bin that contains the number of hours in the data set between 45 and 50°F.

In the example, the formula in cell AB:11868 looks at the data in the range Z:2 through Z:11865 and counts it if the value is greater than or equal to 45 and less than 50.   Since the data is hourly, each cell counted represents one hour with in the specified range of conditions.  By dividing the climate into temperature “bins” and repeating this calculation for each of them, you can create your own bin data from the hourly weather data you download.

So there  you have it, a few more options for getting hourly climate data and working with it. Hope they prove useful to you.


David Sellers
Senior Engineer – Facility Dynamics Engineering

Click here for an index to previous posts

This entry was posted in Weather and Climate Resources. Bookmark the permalink.

2 Responses to Hourly Weather Data for Times Gone By (Plus Opening a Delimited File with Excel)

  1. Great information, thanks for that. 🙂

    • Thanks for the kind words. As I often say, I feel lucky to have the opportunity to share things others have taken the time to share with me and am glad it is helpful.

      Thanks for visiting and your comment,

      David

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