How can I match values using a 'translation array' without looping

Assume, you have a table with n observations for which something is measured, e.g. only the week in which they were observed. Then, there is some matrix, that I would describe as a translation or mapping array. In the first column it has week values and in the second it has average temperatures or something. The following code should illustrate that:

dataset = table([1:100]', randi([1,52],100,1), 'VariableNames', ["ID", "week"])
translation = [[1:52]', randi([-10,40],52,1)]

How can I make a third column in dataset that gives me correctly assigned temperature of the week of the observation without using any kind of loop?

It should be some very efficient function or way of indexing as I am having a very large dataset. Right now, I am using some containers.Map object in a for loop which is endlessly slow.

Answer 1

[JESUS DAVID ARIZA ROYETH]

solution:

dataset = table([1:100]', randi([1,52],100,1), 'VariableNames', ["ID", "week"]);
dataset=[dataset table(nan(size(dataset,1),1),'VariableNames',"mean")];
translation = [[1:52]', randi([-10,40],52,1)];
[d,x]=ismember(dataset{:,1}',translation(:,1)');
idx=x(x>0);
dataset{d,3}=translation(idx,2)

[Clemens Gersch]

There is a small mistake in the fourth row as you match via "ID" instead of the true matching criterion week.

But that helped quite a lot. I do it this way now as I know there is always a matching value:

dataset = table([1:100]', randi([1,52],100,1), 'VariableNames', ["ID", "week"]);
translation = [[1:52]', randi([-10,40],52,1)];
[~,idx]=ismember(dataset.week',translation(:,1)');
dataset.mean=translation(idx,2)

[JESUS DAVID ARIZA ROYETH]

great!

[Clemens Gersch]

and thanks btw.