Delete rows from and interpolate

3 visualizzazioni (ultimi 30 giorni)
Damith
Damith il 25 Feb 2015
Commentato: Star Strider il 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.

Risposta accettata

Star Strider
Star Strider il 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 Commenti
Damith
Damith il 25 Feb 2015
Thanks a lot again Star. I highly appreciate your quick reply.
Star Strider
Star Strider il 25 Feb 2015
My pleasure!

Accedi per commentare.

Più risposte (1)

Andrei Bobrov
Andrei Bobrov il 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 Commento
Damith
Damith il 25 Feb 2015
Thanks Andrei. Your code produce the exact output I want. I appreciate it.

Accedi per commentare.

Community Treasure Hunt

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

Start Hunting!

Translated by