×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Manipulating Weather Data

## Manipulating Weather Data

(OP)
I've got 8760 lines of weather data that I'm trying to manipulate into three different tables.  Currently the data is in the following format:

Date            Hour    Air Temp (°F)
08/01/01    100    60.6
08/01/01    200    58.4
08/01/01    300    57.3
...
07/31/02    2200    64.3
07/31/02    2300    63.3
07/31/02    2400    62.9

I need three different tables  in the following format:
01 to 08    Jan    Feb    Mar...
102            0    0    0
97             0    0    0
92             0    0    0
87             0    0    0
82             0    0    0
...
7              0    0    0
2              0    0    0

09 to 16    Jan    Feb    Mar...
102            0    0    0
97             0    0    0
92             0    0    0
87             0    0    0
82             0    0    0
...
7              0    0    0
2              0    0    0

16 to 24    Jan    Feb    Mar...
102            0    0    0
97             0    0    0
92             0    0    0
87             0    0    0
82             0    0    0
...
7              0    0    0
2              0    0    0

I can do it manually, but it's very tedious.  The first column of the three tables represent 5°F temperature bins such that 102 => 100 < temperature <= 105 and so forth.  The numbers underneath the months represent number of hours within that month that the temperature falls within the 'bin'.  The three tables represent the first 8 hours, second 8 hours and final 8 hours of the day.  Thanks in advance.

### RE: Manipulating Weather Data

mkay... let's see... one way to do this is to assign a "bin code" to each data point, then use countif() within your summary table to count the cells that fit in each bin.

you have:
12 months
3 time bins
a whole bunch of temperature bins, but certainly less than 60

So if you use two digits for the temperature bin, two digits for a month bin, and one digit for a time bin, you should be covered.

set up a function, or a group of functions, to assemble "bin codes" from the supplied data.  Try the format MMHTT (month/hour/temp) for your bin codes, as an example.

If you start your bins at -125F, then your bin digits would be int((YourTemp-(-125))/5)

your hour digit is int((YourHour)/800)  (note that this lumps 00:00 to 07:59 in a single bin, and 08:00 to 15:59 in the next bin.  You can add an offset if you want to move the bin boundaries.

So your bin code, in my proposed format, would be
=month(YourDate)*1000 + int((YourHour)/800)*100 + int((YourTemp-(-125))/5)

For 08/01/01 100 60.6 this gives 8000 + 0 + 37 = 8037
for 09/01/01 2200 2 this gives 9225

use that formula alongside your table of values to generate bin codes for each value

*** note that I'm changing my temp offset from -125 to -122 for the following examples, so that 97.1 and 102.0 are in the same bin ***

Then set up your summary tables:
next to your temp bins, use the above function to generate the bin digits for temp
above your month bins, type in the bin digit for the month
for each table, type in the bin digit for the hours considered

your table above would then look something like this:
1    (blank)    1    2    3
(blank)    00-08    Jan    Feb    Mar
126    102    0    0    0
121    97    0    0    0
116    92    0    0    0
111    87    0    0    0
106    82    0    0    0

then write out the countif() formula to compare your list values to the value that goes in whichever bin you're trying to fill.

example for the cell corresponding to 00-08 january 102, assuming the 1 at the top left of the table is in cell F1, so 00-0759 january 97-102 value goes in cell H3
(00-08 january 102 means a bin code of 1000+0+44 =1044):

=countif($A$1:$A$8760,"=1044")

blah.

### RE: Manipulating Weather Data

(OP)
Thanks ivymike for pointing me in the right direction.  The manual method involves a lot of manual highlighting and the frequency function.

I've tried out your method, but am a bit confused.  I understand the month and the hour portion of the bin code, but don't understand the temperature portion.  How's that -125 fit in the picture?

Also, I manually verified the highest temperature bin, and I'm quite off when compared to this method.  I'm pretty sure it has something to do with the offset.

### RE: Manipulating Weather Data

the -125 ensures that you don't have any negative-numbered bins.  If you omit the -125, then bin 0 starts at 0degF, and -1degF falls in bin -1.  If you include the -125, then -125degF falls in bin 0.  Using -122 instead of -125 gives you a 97-102 bin.

### RE: Manipulating Weather Data

heheh... corrections section...

I messed up when I typed in my countif example.  I meant to use a formula instead of "=1044".  The formula should have been
=COUNTIF($D$1:$D$3,H$1*1000+$F$1*100+$F3)
assuming that F1 is the top left cell, and contains the value 0.  D1:D3 are the cells with the sample data converted to a bin code.  H1 is the month code (1 for jan) and F3 is the temp code.

Oh, I noticed that I put some incorrect info in the earlier sample table - I must have left out a set of parentheses in my temperature bin number calculation.  97 to 102 should be bin 44, using the -122 offset.  If I use -125 and leave out the parentheses, I get int(g4-(-125)/5)= 127.

more corrections to come, I'm sure.

### RE: Manipulating Weather Data

I would rather use Dynamic Tables, which is the underestimated and almost unknown tool, that Excel Provides to create cross references tables from raw data as abramos mentioned.
Of course, some previous preparation, as described by ivymike, is required:
- Add three new columns yo your sheet (Month, Hour Range & temp Range)
- Extract Month Number from Date , function Month
- Convert Hour to Range with a Nested IF (or an INT division)
- Convert Temp Value to the Temp range. I used a separate sheet for ranges and a search function
- Then build the dynamic table. This can be refreshed any time you get new raw data, so you need to solve the problem once!!

Regards

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!