Calculating a new MATLAB array column based on looking up values from two arrays -

i have matlab double array looks this:

year    quarter id  var 2000    1       1   50 2000    1       2   20 2000    1       3   67 2000    2       1   43 

it goes on many years , many quarters, , number of rows in each quarter , year varies unpredictably. variables constitute estimates individual people.

another double array looks this:

year    quarter outcome 2000    1       100 2000    2       0 

it goes on many years , many quarters. there 1 outcome in each quarter. want subtract person's estimate outcome , place result in initial array.

the result should this:

year    quarter id  var   result 2000    1       1   50    50 2000    1       2   20    80 2000    1       3   67    33 2000    2       1   43    43 

what's best way achieve this?

here 3 options, depending on desired speed / readability / assumptions.

%% load data estimate = [...   2000    1       1   50; ...   2000    1       2   20; ...   2000    1       3   67; ...   2000    2       1   43; ...   2000    4       1   50]; outcome = [...   2000    1       100; ...   2000    2       0; ...   2000    4       0; ...   2001    1       10]; n_estimate = size(estimate,1); n_outcome = size(outcome,1);  %% loop version (easier read, more flexible)  result = zeros(n_estimate,1); = 1:n_estimate   % find matching year & quarter estimate   j = all(bsxfun(@eq, outcome(:,1:2), estimate(i,1:2)),2);   % subtract estimate outcome (seems want absolute value)   result(i) = abs(outcome(j,3) - estimate(i,4)); end  % append result estimate matrix, , display estimated_result = [estimate result]; display(estimated_result);  %% vectorized version (more efficient, forced assumptions) % note: assumes have outcomes every quarter % (i.e. there none missing), can calculate offset % start year/quarter % second-last outcome violates assumption, % causing last estimate incorrect version  % build integer index combined year/quarter, offset % first year/quarter available in outcome list begin = outcome(1,1)*4 + outcome(1,2); j = estimate(:,1)*4 + estimate(:,2) - begin + 1;  % subtract estimate outcome (seems want absolute value) result = abs(outcome(j,3) - estimate(:,4));  % append result estimate matrix, , display estimated_result = [estimate result]; display(estimated_result);  %% vectorize version 2 (more efficient, hardest read) % note: not assume have data every quarter  % build inverted index map year*4+quarter-begin outcome index. begin = outcome(1,1)*4 + outcome(1,2); = outcome(:,1)*4+outcome(:,2)-begin+1; % outcome indices j_inv(i) = 1:n_outcome;  % build forward index estimate outcome j = j_inv(estimate(:,1)*4 + estimate(:,2) - begin + 1);  % subtract estimate outcome (seems want absolute value) result = abs(outcome(j,3) - estimate(:,4));  % append result estimate matrix, , display estimated_result = [estimate result]; display(estimated_result); 

output:

estimated_result =

    2000           1           1          50          50     2000           1           2          20          80     2000           1           3          67          33     2000           2           1          43          43     2000           4           1          50          50 

estimated_result =

    2000           1           1          50          50     2000           1           2          20          80     2000           1           3          67          33     2000           2           1          43          43     2000           4           1          50          40 

estimated_result =

    2000           1           1          50          50     2000           1           2          20          80     2000           1           3          67          33     2000           2           1          43          43     2000           4           1          50          50