Updating matlab table using the closest match in a different table.

I have two tables: table1 and table2.

In both tables I have a time field table1.time and table2.time which are not equal.

In addition I have in table1 field id (table1.id) and in table2 field x (table2.x).

For each row in table 1 I want to add variable x from table 2 in such way that the difference in time in both tables would be minimal, so the match between the two times would be as close as possible.

Something like:

if (table1.time(index1) - table2.time(index2) == (minimum time distance)

table1.x(index1) = table2.x(index2);

end

For example given the tables:

time1 = [1;2;3];
time2 = [2.1; 1.2; 3.4]
x = [3; 4; 5]
id = [1;2;3];
table1 = table(id,time1);
table2 = (time2 , x);

I want that the final result would be:

for row 1: id:1, time1:1, x:4

for row 2: id:2 time1 : 2, x:3

for row 3: id:3 time1: 3 , x:5

Of course the real data is much bigger than that.

Thanks for the help.

Answer 1

[Arif Hoq]

try this:

time1 = [1;2;3];
time2 = [2.1; 1.2; 3.4];
x = [3; 4; 5];
id = [1;2;3];
T1 = table(time1,id);
T2 = table(time2,x);
% newcol1=zeros(length(time1),1);
newcol=cell(length(time1),1);
for i=1:length(time1)
[A I]=min(abs(table2array(T1(i,1))-table2array(T2(:,1))));
newcol{i}=T2.x(I,:);
end
newcol_out=[newcol{:}]'

newcol_out = 3×1

4 3 5

[kuku hello]

Thanks so much. I changed it a bit but seems to be working fine.