I have a number of data sets that were generated by data loggers on wind turbines that I need to merge with a meteorological data set that possesses directional data. The difficulty is the data that was generated by the data loggers occurred at 10 minute intervals on :03, :13, :23, :33, :43, :53, and the date and time appear in different columns with the following format i.e. 01/03/2008, 1:53:17 PM, respectively.
The met data occurred only once each hour, on the top of the hour on :00, and is formatted with the date and time in the same column i.e. 12/30/2008 11:00 AM
Is there someway to merge these records by creating a rule that inserts the met data into the data logger file where:
- the date & time are placed in separate columns (this is the data logger convention and the format of my hundreds of data files) - a new row is created for each :00 time stamp
I've faced similar situations before and found it helpful to "average" the 10 minute data on the database side using an avg(data), group by (YYYYMMDD HH24) SQL command.
If you've only got files from the data loggers and can't use a database, you could create a "key" field on each table and join them in JMP. The key on the wind turbine table could be something like year(date)||month(date)||day(date)||hour(time) to be joined with the weather data key year(dateTime)||month(dateTime)||day(dateTime)||hour(dateTime).
That should make two equivalent, unique character strings for each hour in each table. I'm positive there are other, more elegant ways of going about this, but this is a method I've used to handle JMP's treatment of time data.
By the way, where is your weather data coming from? I'm always looking for useful sources.