Delete rows from and interpolate

8 views (last 30 days)
Damith
Damith on 25 Feb 2015
Commented: Star Strider on 25 Feb 2015
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.

Accepted Answer

Star Strider
Star Strider on 25 Feb 2015
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.
  2 Comments
Damith
Damith on 25 Feb 2015
Thanks a lot again Star. I highly appreciate your quick reply.

Sign in to comment.

More Answers (1)

Andrei Bobrov
Andrei Bobrov on 25 Feb 2015
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)];
  1 Comment
Damith
Damith on 25 Feb 2015
Thanks Andrei. Your code produce the exact output I want. I appreciate it.

Sign in to comment.

Categories

Find more on Graphics Performance in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!