CODEWITHSUNDEEP
arraysmatlab
user1205901 - Слава Україні
user1205901 - Слава Україні

Reputation: 939

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

I have a MATLAB double array that looks like this:

YEAR    QUARTER ID  VAR
2000    1       1   50
2000    1       2   20
2000    1       3   67
2000    2       1   43

It goes on for many years and many quarters, and the number of rows in each quarter and year varies unpredictably. The variables constitute estimates by individual people.

Another double array that looks like this:

YEAR    QUARTER OUTCOME
2000    1       100
2000    2       0

It goes on for many years and many quarters. There is only one outcome in each quarter. I want to subtract the person's estimate from the outcome and place the result in the initial array.

The result should look like this 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 the best way to achieve this?

Upvotes: 0

Views: 47

Answers (1)

kmac
kmac

Reputation: 698

Here are three 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 to read, more flexible)

result = zeros(n_estimate,1);
for i = 1:n_estimate
  % Find matching year & quarter for this estimate
  j = all(bsxfun(@eq, outcome(:,1:2), estimate(i,1:2)),2);
  % Subtract estimate from outcome (seems like you want the absolute value)
  result(i) = abs(outcome(j,3) - estimate(i,4));
end

% Append the result to the estimate matrix, and display
estimated_result = [estimate result];
display(estimated_result);

%% Vectorized version (more efficient, forced assumptions)
% Note: this assumes that you have outcomes for every quarter
% (i.e. there are none missing), so we can just calculate an offset from
% the start year/quarter
% The second-last outcome violates this assumption,
% causing the last estimate to be incorrect for this version

% Build an integer index from the combined year/quarter, offset from
% the first year/quarter that is available in the outcome list
begin = outcome(1,1)*4 + outcome(1,2);
j = estimate(:,1)*4 + estimate(:,2) - begin + 1;

% Subtract estimate from outcome (seems like you want the absolute value)
result = abs(outcome(j,3) - estimate(:,4));

% Append the result to the estimate matrix, and display
estimated_result = [estimate result];
display(estimated_result);

%% Vectorize version 2 (more efficient, hardest to read)
% Note: this does not assume that you have data for every quarter

% Build an inverted index to map year*4+quarter-begin to an outcome index.
begin = outcome(1,1)*4 + outcome(1,2);
i = outcome(:,1)*4+outcome(:,2)-begin+1; % outcome indices
j_inv(i) = 1:n_outcome;

% Build the forward index from estimate into outcome
j = j_inv(estimate(:,1)*4 + estimate(:,2) - begin + 1);

% Subtract estimate from outcome (seems like you want the absolute value)
result = abs(outcome(j,3) - estimate(:,4));

% Append the result to the estimate matrix, and 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

Upvotes: 1

Related Questions