Converting wind/wave data to hourly data by interpolation

Dear All,

I have wind and wave data that as can be seen attached. I want to convert it to hourly data acording to duration (time) values in column A. The value of duration here represents the duration of the wind blowing and its unit is hour. I want to interpolate all the other parameters while converting to hourly data. For example, I have formulated this in Excel for the line as follows,

D2-(($D$2-$D$3)/$A$2)

D3-(($D$2-$D$3)/$A$2)

D4-(($D$2-$D$3)/$A$2)

......

D8-(($D$2-$D$3)/$A$2)

The problem is, this is a very large set of data and doing this manually in Excel will take an enourmous amount of time. Is there a command in MATLAB where I can add (A-1) number of columns to each line and interpolate the rest of columns as described above?

Thank you in advance!

[Adam Danz]

Are the durations consecutive? In other words, the first 3 durations are 8,1,2, does that account for a consecutive 11 hour period?

[Seda Cora Özdemir]

Hello Adam,

Thank you very much for your prompt response!

The durations are consecutive. But the yellow ones were added by me to better explain the situation. So the durations go as 8, 9, 14, 7, ...., etc. I have to insert 7 lines and get 8 lines in total for the first data point, insert 8 lines and get 9 lines in total for the second, insert 13 lines and get 14 lines in total for the third, etc. The other values have to be interpolated as was done in the code you shared.

I hope I could clarify the situation a bit. Do you think this can be done with MATLAB?

[Adam Danz]

I've updated my answer with the following changes.

1. I replaced your demo file with a clean version that removes the added highlighted rows.
2. Critically, the timestamps are computed so that they start with 0.

I've spot-checked a few rows of the results and they seem to match your expected results from the original data file.

[Seda Cora Özdemir]

Thank you very much Adam!!! You saved my life :)

[Adam Danz]

Look at the last row of data; it may not be included in the final interpolated timetable. I don't know how you would interpolate that last row but it's just something to keep in mind.

Answer 1

[Adam Danz]

If the durations are consecutive, then I suggest creating an time stamps based on the durations which allows you to use retime to interpolate the timetable to hourly samples.

I've created a "clean" version of your file by removing the rows that were highlighted.

T = readtable('sample_data_clean.xlsx')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T = 109×6 table
    Duration    WindDirection_degree_    WindSpeed_m_s_       Hs         Tp         Tm   
    ________    _____________________    ______________    ________    _______    _______

        8               206.2                8.9288         0.11265     1.0981    0.91139
        9              228.66                8.1667         0.10047     1.0481    0.86994
       14              224.28                6.6657        0.082006    0.97952      0.813
        7              226.63                  9.29         0.11429     1.0941    0.90813
        3              241.58                  3.99        0.052364    0.86184    0.71533
        2              331.51                 2.045         0.11948     1.8665     1.5492
        5              19.992                 4.712         0.15356     1.6705     1.3865
        3                9.01                7.4567          0.3187     2.3324     1.9359
       19              21.945                5.3311         0.17374     1.7407     1.4448
        6              19.252                2.6983        0.087938     1.3872     1.1514
        2               140.3                 1.365        0.076353     1.5845     1.3152
        2              181.79                 2.535        0.036398    0.78666    0.65293
        3              224.42                3.4667        0.042649    0.78771     0.6538
        2              248.24                 3.555        0.046655    0.82931    0.68832
        3              204.89                   2.7        0.034065    0.73703    0.61173
        7              179.76                4.1971        0.060263    0.93064    0.77243

% Create time stamps with arbitrary starting date/time
% Note that the time stamps start at hour 0. 
arbitraryDateTime = datetime(2000,1,1,0,0,0) + [0;cumsum(hours(T.Duration(1:end-1)))];;
TT = table2timetable(T,'RowTimes',arbitraryDateTime)

TT = 109×6 timetable
            Time            Duration    WindDirection_degree_    WindSpeed_m_s_       Hs         Tp         Tm   
    ____________________    ________    _____________________    ______________    ________    _______    _______

    01-Jan-2000 00:00:00        8               206.2                8.9288         0.11265     1.0981    0.91139
    01-Jan-2000 08:00:00        9              228.66                8.1667         0.10047     1.0481    0.86994
    01-Jan-2000 17:00:00       14              224.28                6.6657        0.082006    0.97952      0.813
    02-Jan-2000 07:00:00        7              226.63                  9.29         0.11429     1.0941    0.90813
    02-Jan-2000 14:00:00        3              241.58                  3.99        0.052364    0.86184    0.71533
    02-Jan-2000 17:00:00        2              331.51                 2.045         0.11948     1.8665     1.5492
    02-Jan-2000 19:00:00        5              19.992                 4.712         0.15356     1.6705     1.3865
    03-Jan-2000 00:00:00        3                9.01                7.4567          0.3187     2.3324     1.9359
    03-Jan-2000 03:00:00       19              21.945                5.3311         0.17374     1.7407     1.4448
    03-Jan-2000 22:00:00        6              19.252                2.6983        0.087938     1.3872     1.1514
    04-Jan-2000 04:00:00        2               140.3                 1.365        0.076353     1.5845     1.3152
    04-Jan-2000 06:00:00        2              181.79                 2.535        0.036398    0.78666    0.65293
    04-Jan-2000 08:00:00        3              224.42                3.4667        0.042649    0.78771     0.6538
    04-Jan-2000 11:00:00        2              248.24                 3.555        0.046655    0.82931    0.68832
    04-Jan-2000 13:00:00        3              204.89                   2.7        0.034065    0.73703    0.61173
    04-Jan-2000 16:00:00        7              179.76                4.1971        0.060263    0.93064    0.77243

% Interpolate to hourly samples
TThourly = retime(TT,'hourly','linear')

TThourly = 844×6 timetable
            Time            Duration    WindDirection_degree_    WindSpeed_m_s_       Hs         Tp         Tm   
    ____________________    ________    _____________________    ______________    ________    _______    _______

    01-Jan-2000 00:00:00          8             206.2                8.9288         0.11265     1.0981    0.91139
    01-Jan-2000 01:00:00      8.125            209.01                8.8335         0.11113     1.0918    0.90621
    01-Jan-2000 02:00:00       8.25            211.81                8.7382         0.10961     1.0856    0.90103
    01-Jan-2000 03:00:00      8.375            214.62                 8.643         0.10808     1.0793    0.89585
    01-Jan-2000 04:00:00        8.5            217.43                8.5477         0.10656     1.0731    0.89067
    01-Jan-2000 05:00:00      8.625            220.24                8.4524         0.10504     1.0668    0.88548
    01-Jan-2000 06:00:00       8.75            223.04                8.3572         0.10352     1.0606     0.8803
    01-Jan-2000 07:00:00      8.875            225.85                8.2619         0.10199     1.0544    0.87512
    01-Jan-2000 08:00:00          9            228.66                8.1667         0.10047     1.0481    0.86994
    01-Jan-2000 09:00:00     9.5556            228.17                7.9999         0.09842     1.0405    0.86361
    01-Jan-2000 10:00:00     10.111            227.69                7.8331        0.096368     1.0329    0.85729
    01-Jan-2000 11:00:00     10.667             227.2                7.6663        0.094317     1.0253    0.85096
    01-Jan-2000 12:00:00     11.222            226.71                7.4996        0.092265     1.0176    0.84463
    01-Jan-2000 13:00:00     11.778            226.22                7.3328        0.090213       1.01    0.83831
    01-Jan-2000 14:00:00     12.333            225.74                 7.166        0.088161     1.0024    0.83198
    01-Jan-2000 15:00:00     12.889            225.25                6.9993         0.08611    0.99476    0.82565

Convince yourself that the results are reasonable by plotting the original and interp'd data

figure
plot(TT.Time, TT.WindDirection_degree_,'-b','linewidth',2,'displayname','original'); 
hold on
plot(TThourly.Time, TThourly.WindDirection_degree_,'-r','displayname','interp''d'); 
legend