Find closest value for each row in timetable

I have a timetable with 3 columns.

I would like to use the first column (Var1) as reference to select the closest value in the 2 other columns, resulting in a timetable with just 1 column (i.e. the value closest to Var1).

If both values in the 2 colums are NaN, then the resulting value can be left out.

I tried using a for loop but I'm not sure this is the best way forward.

Does anyone has a suggestion how to do this?

Thanks a lot

Answer 1

[Star Strider]

I am defining ‘closest’ as the minimum absolute difference between ‘Var1’ and the last two variables.

(My attempt at a more direct approach for the creation of ‘Var4’ failed, so I went with the loop, that worked.)

Try something like this —

LD = open(websave('nHS_TS','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1257052/nHS_TS.mat'));
nHs_TS = LD.nHs_TS

nHs_TS = 2033×3 timetable
           Time             Var1     Hs_C1_TS    Hs_C2_TS
    ___________________    ______    ________    ________

    2021-10-08 15:00:00    1.8849        NaN         NaN 
    2021-10-08 15:15:00    1.8849        NaN      0.3493 
    2021-10-08 15:30:00    2.1875        NaN     0.31827 
    2021-10-08 15:45:00    2.2015        NaN         NaN 
    2021-10-08 16:00:00    2.3331        NaN     0.30235 
    2021-10-08 16:15:00    2.3331        NaN     0.32572 
    2021-10-08 16:30:00    2.4653        NaN         NaN 
    2021-10-08 16:45:00     2.377        NaN         NaN 
    2021-10-08 17:00:00    2.4221        NaN         NaN 
    2021-10-08 17:15:00    2.4221        NaN     0.37422 
    2021-10-08 17:30:00    2.4087        NaN     0.38577 
    2021-10-08 17:45:00    2.2971        NaN     0.47792 
    2021-10-08 18:00:00    2.0863        NaN     0.50083 
    2021-10-08 18:15:00    2.0863        NaN     0.46436 
    2021-10-08 18:30:00    1.8908        NaN     0.46218 
    2021-10-08 18:45:00    1.6217    0.37528     0.47233 

[~,idx] = min(abs(nHs_TS{:,1}-[nHs_TS{:,[2 3]}]),[],2,'omitnan');           % Minimum Absolute Difference
Extract = nHs_TS{:,[2 3]};
for k = 1:numel(idx)
    Var4(k,:) = Extract(k,idx(k));
end
nHs_TS.NewVar= Var4

nHs_TS = 2033×4 timetable
           Time             Var1     Hs_C1_TS    Hs_C2_TS    NewVar 
    ___________________    ______    ________    ________    _______

    2021-10-08 15:00:00    1.8849        NaN         NaN         NaN
    2021-10-08 15:15:00    1.8849        NaN      0.3493      0.3493
    2021-10-08 15:30:00    2.1875        NaN     0.31827     0.31827
    2021-10-08 15:45:00    2.2015        NaN         NaN         NaN
    2021-10-08 16:00:00    2.3331        NaN     0.30235     0.30235
    2021-10-08 16:15:00    2.3331        NaN     0.32572     0.32572
    2021-10-08 16:30:00    2.4653        NaN         NaN         NaN
    2021-10-08 16:45:00     2.377        NaN         NaN         NaN
    2021-10-08 17:00:00    2.4221        NaN         NaN         NaN
    2021-10-08 17:15:00    2.4221        NaN     0.37422     0.37422
    2021-10-08 17:30:00    2.4087        NaN     0.38577     0.38577
    2021-10-08 17:45:00    2.2971        NaN     0.47792     0.47792
    2021-10-08 18:00:00    2.0863        NaN     0.50083     0.50083
    2021-10-08 18:15:00    2.0863        NaN     0.46436     0.46436
    2021-10-08 18:30:00    1.8908        NaN     0.46218     0.46218
    2021-10-08 18:45:00    1.6217    0.37528     0.47233     0.47233

.

[Siegmund]

That worked perfectly! Thanks a lot

[Star Strider]

As always, my pleasure!