Charting NOAA data

[RP]

I cobbled together a method to chart wind data from NOAA (National Oceanographic and Atmospheric Administration).

Below is a map of the wind speed throughout the year for 2003 at the airport in Vichy Missouri.  It reads like a calendar wheel with January - March in the upper right quadrant, April - June in the lower left, etc.  The radius at any given point is the wind speed in mph.  This tells me what to expect in terms of available energy throughout the year.

The next one shows wind speeds by compass direction.  Each data point is plotted by compass direction and speed telling me where most of the energy comes from.  This would be usefull in selection a tower site to get a clear shot at the wind.

I haven't done it yet but it would be a simple matter to convert to data to energy units taking the cube law into account.

If anyone's interested I can post the spreadsheet file and/or instructions for converting the text data from the NOAA website into these charts.

rp

[Aelric]

would love to find out more about how to chart windspeed/direction.  Oklahoma is a pretty windy state and I want to setup a wind turbine one day (just gotta get some land hehehehe and a bit of spare cash)  Your charts are very interesting, and are a great idea.  Finding out average windspeed is very important to setting up a windmill that can consistantly put out good power.  Anyway yes I'd love to find out more hehehehe.

[RP]

Well...

The first step is to get the data from NOAA such as from here:

http://www.crh.noaa.gov/sgf/climate/vih/vihf6.shtml

Or here (for Tulsa):

http://www.srh.noaa.gov/tulsa/cgi-bin/get_f6.pl?stn_name=Tulsa+International+Airport&month=Jan&a
mp;year=2004

The NOAA website isn't orgainized very well so you have to hunt around to find the climate data for your area.

In the end NOAA gives tables of data in text by month and the raw data looks like this:

PRELIMINARY LOCAL CLIMATOLOGICAL DATA (WS FORM: F-6)

                                          STATION:   VICHY-ROLLA

                                          MONTH:     JANUARY

                                          YEAR:      2003

                                          LATITUDE:   38  7 N                  

                                          LONGITUDE:  91 46 W                  

  TEMPERATURE IN F:       :PCPN:    SNOW:  WIND      :SUNSHINE: SKY     :PK WND

===========================

1   2   3   4   5  6A  6B    7    8   9   10  11  12  13   14  15   16   17  18

                                          AVG MX 2MIN

DY MAX MIN AVG DEP HDD CDD  WTR  SNW DPTH SPD SPD DIR MIN PSBL S-S WX    SPD DR

===========================

 1  36  26  31   1  34   0 0.23    M    M  9.4 17  70   M    M   9 168    21  40

 2  30  26  28  -2  37   0 0.02    M    M  0.0  0 320   M    M  10 1       0 310

 3  33  17  25  -5  40   0 0.00    M    M  3.4 10 280   M    M   1 1      12 280

 4  46  26  36   6  29   0 0.15    M    M 10.3 17 310   M    M   2        21 310

 5  40  30  35   5  30   0 0.00    M    M  9.2 17 300   M    M   7 1      22 270

 6  39  20  30   0  35   0 0.00    M    M  8.1 16  20   M    M   6 18     21  20

 7  46  20  33   3  32   0 0.00    M    M 13.2 18 260   M    M   1 18     22 230

 8  68  40  54  25  11   0 0.00    M    M 15.2 25 250   M    M   0        30 260

 9  50  29  40  11  25   0 0.00    M    M 11.1 23 320   M    M   0        29 330

10.  32  20  26  -3  39   0 0.00    M    M 11.7 22 320   M    M   2        28 290
    
11.  30  14  22  -7  43   0 0.00    M    M  6.6 14 330   M    M   0        16 340
    
    

Which is not very pretty in the font used here but its pretty readable on the NOAA site.  If you copy the data above and paste it into a word processor with a monospaced font it cleans it up nicely.

This is the raw data for the first eleven days of January 2003 for the airport in Vichy Missouri which is close to my site.

This gets pasted into a spreadsheet.  Most spreadsheets will automatically take the data in a one vertical column of text with the days of the month extending down in rows.  That's what you want if you're given formatting choices by the spreadsheet.

What I did is get the data for every month of one year and pasted them below each other in Excel until I had one year of data running vertically down my spreadsheet..  I know it sounds tedious but this is to develop baseline data for a location and you only have to do this once.

Now, how do get useful data from all this text?

The answer is string functions.

Remember the data you've pasted into your spreadsheet is text, not numbers.  It might as well be newspaper headlines as far as the spreadsheet is concerned.

Functions are little directives you put in a cell of a spreadsheet instead of data to tell it what you want in the cell.  The simplest function is a math function like =B1+C1.  In this case the cell that has this formula typed into it will hold the sum of whatever numbers are in cells B1 and C1.

In our case we use a string function.  Locate the cell that's holding all the text for the first day of the first month of your data.  In my case, from above its this one -

1  36  26  31   1  34   0 0.23    M    M  9.4 17  70   M    M   9 168    21  40

and the data above happened to be located at cell A10 in my spreadsheet

Now go to the right to an empty cell in the same row (like B10) and type =MID(A10,50,5) where A10 will need to be replaced with the location of your data cell.  What this function does is say that this cell is equal to the text located at the 50th character position and extending for 5 characters in the cell A10.  MID means text from somewhere in the middle of another string of characters.  Note this is still text, not a number.  B10 should now hold "70" which happens to be where the wind direction data is in the text from NOAA

Now go one more cell to the right of this one (C10) and type =VALUE(B10) assuming B10 is where you put the MID function.  Now the cell C10 actually holds a number rather than text.

Use the "fill down" feature to copy your formulas all the way to the bottom of your data.  Congratulations!  You now have a year long column of numerical data with the windspeed direction for your area.

Doing the same thing for wind speed, temperature, etc. is simply a matter of adding more columns with different values in the MID function.  For instance =MID(A10,5,4) grabs the text for the maximum temperature for that day.  You then use the Value function to convert this into a numeric value.

That's enough for tonight.  Next time: using trigonometry functions to make cool circular charts!

rp

[RP]

By now you should have a spreadsheet with columns containing wind-speed and direction data for the whole year.  This data should be numerical (using the =VALUE function).  The data below is for the first 6 days.

NOTE: NOAA monitors average and Max wind-speed so you can choose which one you want to use.  I use the average.

Dir    Avg. Speed

70    9.4

-40    0

-80    3.4

-50    10.3

-60    9.2

20    8.1

If you are using Microsoft Excel then you've already done most of the hard work to get the circular calendar chart.  Simply select the wind-speed data column (the direction data is not needed for this one) and create a "Radar" type chart.  Since the data is already in chronological order, the result should look like my example above.  Any charting program that can do a Radar chart should work.  

Set the scale for the gridlines however you'd like.  I chose 20 mph max with 5mph divisions because it provides an easy visual representation.

[ghurd]

Why not plot 5 years worth of data?  It would be a better average.

G-

[Aelric]

thank you very much, will have to plug all these into a spreadsheet :-)  

[RP]

I agree more data is better.  In my case the available data only goes back to 2002.  In other spots it goes back much further.

BTW:  I plan to add the trig part of the explanation on how to build the second chart but I probably won't get back to it for a few days.

rp

[ghurd]

Plain old regular averages work for me.

G-

[ghurd]

With 5 years worth of data, it would be much easier to read than linear charts.

G-

[RP]

Okay, on to the second chart.  Excel doesn't have a chart type to create the wind dorection type chart so we have to to use trigonometry function to make one.  Here's how to do it:

At this point you should already have set up two columns that have NUMERICAL values for wind direction and speed using the information above.  They may look like this:

What we have to do is use the Sin and Cosine functions to convert the direction angle into coordinates around a circle.  I won't go into a trig primer here but I'll be happy answer questions if it's not clear.  What we're going to do is treat our direction and speed data into X,Y coordinates giving us angle and magnitude for each data point.

The direction is the angle on the chart and the speed is the magnitude which is the radius from the center of our chart.  The basic formula is this:

X=COS(ANGLE)*speed

Y=SIN(ANGLE)*speed

In other words take the SIN or Cosine of the angle and multiply by the magnitude to convert angle, magnitude to X, Y coordinates.

Now, in reality there are a couple other things we need to do to make this work in a spreadsheet.  First, almost all spreadsheet functions expect angles to be in radians instead of degrees (kind of like centimeters instead of inches).  To get the NOAA data (in degrees) into radians we simply multiply by pi (3.14159) and divide by 180.  So now we have this:

X=COS(ANGLE*3.14159/180)*speed

Y=SIN(ANGLE*3.14159/180)*speed

The other thing is that trig function assume that zero degrees is a line pointing to the right and since we're dealing with compass directions we'd like zero degrees (north) to be pointing straight up so for our chart we need to rotate the data by 90 degrees CCW:

X=COS((ANGLE-90)*3.14159/180)*speed

Y=SIN((ANGLE-90)*3.14159/180)*speed

Note the extra parentheses.  These are important as they tell the spreadsheet to do the calculations in the paren's. first, then use the result to finish the rest of the math.

In all we take the NOAA wind direction, substract 90 from it to rotate the data by 90 degrees counter clockwise, covert it from degrees to radians and take either the Sin or Cosine of the resulting angle and multiply that by the windspeed.  This gives us a set of X, Y coordinates for each day of the year.  By plotting these we get a visual representation of where the wind comes usually comes from and how fast it's blowing.

To do this using functions we go to an empty cell to the right of our NOAA data and put in  

COS((ANGLE-90)*3.14159/180)*speed    (actually this needs an equals sign in front of it but its messing up the html when I put in the text here)

but substitute the address of the direction cell for ANGLE and the address of the speed cell for speed.  These might be F10 and G10 for example.  In that case it would be:  COS((F10-90)*3.14159/180)*G10

Use the fill down technique again to copy the formulas down to match the length of the NOAA data.  Mine looks like this:

Now make an X/Y scatter chart from the two columns of data.  Your spreadsheet or charting program may call it something else but it puts a point at each pair of x,y coordinates.  If you want to be fancy you can add some circles using a drawing tool like I did.

rp