Delete rows from and interpolate

Hi,

Is there a way to implement in MATLAB to check first row and end row and then select 3 hr intervals rows from column 4 from table A (e.g. 0,3,6,9,12,15,18,21) and if not available include the 3 hr time steps and linear interpolate column 5 and 6. The new table should look like as shown in B. The "value" in col 5 and 6 should be obtained by linear interpolation. (See the test.xls attached)

A =

    2000  1  24  4  -11.5  99.8
    2000  1  24  6  -11.49  100.19
    2000  1  24  10  -11.5  101
    2000  1  24  12  -11.67  102.02
    2000  1  24  16  -11.7  102.3
    2000  1  24  18  -11.92  102.99
    2000  1  24  22  -12.1  103.5
    2000  1  25  0  -12.29  103.97
    2000  1  25  4  -12.6  104.8
    2000  1  25  6  -12.65  105.3
    2000  1  25  10  -13  106.2
    2000  1  25  12  -12.88  106.24
    2000  1  25  16  -13.1  107.1
    2000  1  25  18  -13.06  107.09
    2000  1  25  22  -13.4  108.3
    2000  1  26  0  -13.51  108.51

B =

      2000  1  24  6  -11.49  100.19
      2000  1  24  9   value  value
      2000  1  24  12  -11.67  102.02
      2000  1  24  15   value  value
      2000  1  24  18  -11.92  102.99
      2000  1  24  21   value  value
      2000  1  25  0  -12.29  103.97
      2000  1  25  3   value  value
      2000  1  25  6  -12.65  105.3
      2000  1  25  9   value  value
      2000  1  25  12  -12.88  106.24
      2000  1  25  15   value  value
      2000  1  25  18  -13.06  107.09
      2000  1  25  21   value  value
      2000  1  26  0  -13.51  108.51

Any help is highly appreciated.

Thanks in advance.

Answer 1

[Star Strider]

This works:

[d,s,r] = xlsread('Damith test.xls');
DN = datenum([d(:,1:4) repmat([0 0], size(d,1), 1)]);   % Convert All To ‘datenum’
D0 = floor(DN(1));                                      % Start Date & Time
DE = ceil(DN(end));                                     % End Date & Time
DT = 1/24;                                              % Days/Hour
DV = D0 + cumsum([0; ones(round(24*(DE-D0)),1)*DT]);    % All Hours
DV = DV(1:3:end-1);                                     % q3 Hours
DC = unique([DN; DV]);                                  % Combine Date Vectors
DataIntrp  = interp1(DN, d(:,5:6), DC);                 % Interpolate
DateMtx    = datevec(DC);                               % Date Vectors
DataResult = [DateMtx(:,1:4), DataIntrp];               
DataResult = DataResult(~isnan(DataResult(:,5)),:);     % Output Eliminating Extrapolations
CheckResult = DataResult([1:10 end-9:end],:)            % Check Result (Temporary)

The ‘CheckResult’ line is there to look at the first and last 10 entries. It doesn’t match your ‘B’ matrix, but it does match your description (and your question in my not yet Accepted Answer to your previous Question). It could likely be made more efficient, but if you only need to run it once for each data set, this is likely sufficient.

[Damith]

Thanks a lot again Star. I highly appreciate your quick reply.

[Star Strider]

My pleasure!

Answer 2

[Andrei Bobrov]

x = xlsread('testbyDamith.xls');
n = find(rem(x(:,4),3)==0);
x1 = x(n(1):n(end),:);
t0 = num2cell(x1(:,1:4),1);
t = datenum(t0{:},0,0);
t2 = (t(1):3/24:t(end))';
[y,m,d,h] = datevec(t2);
out = [y,m,d,h,interp1(t,x1(:,end-1:end),t2)];

[Damith]

Thanks Andrei. Your code produce the exact output I want. I appreciate it.