Show values in Matlab depending on previous values in another column

Question

I'm struggling with the following problem in Matlab:

I’ve got a table containing a few column vectors: Day, Name, Result My goal is to create another column vector (New vector) that shows me in each row the result of the previous day for the corresponding name.

| Day | Name | Result | New Vector |
|-----|------|--------|------------|
| 1   | A    | 1.2    | 0          |
| 1   | C    | 0.9    | 0          |
| 1   | B    | 0.7    | 0          |
| 1   | D    | 1.1    | 0          |
| 2   | B    | 1      | 0.7        |
| 2   | A    | 1.5    | 1.2        |
| 2   | C    | 1.4    | 0.9        |
| 2   | D    | 0.9    | 1.1        |
| 3   | B    | 1.1    | 1          |
| 3   | C    | 1.3    | 1.4        |
| 3   | A    | 1      | 1.5        |
| 3   | D    | 0.3    | 0.9        |

For example row 5:

It is day 2 and name is "B". The vector "RESULT" shows 1.0 in the same row but what I want to show in my new vector, is the result value of "B" of the previous day (day 1 in this example). Since one can find "B" on the previous day in row 3, the result value is 0.7, which should be shown in row 5 of my New Vector.

When day is equal to 1, the logical consequence is that there are no values since there is no previous day. Consequently I want to show 0 for each row on Day 1.

I've already tried some combinations of unique to get the index and some if clauses but it did not work at all since I'm relatively new to Matlab and still very confused.

Is anybody able to help? Thank you so much!!

Answer 1

Your question is not well defined, but the code below solves your problem as it is stated.

This code works by internally sorting each Day's information in order of Name. This allows New Vector to be created easily by simply shifting and then inverting the sort operation.

close all; clear all; clc;

% A few column vectors
Day = [1;1;1;1;2;2;2;2;3;3;3;3];
Name = ['A';'C';'B';'D';'B';'A';'C';'D';'B';'C';'A';'D'];
Result = [1.2;0.9;0.7;1.1;1;1.5;1.4;0.9;1.1;1.3;1;0.3];

% Sort the table (so Name is in order for each Day)
[~,Index] = sort(max(Name)*Day + Name);
Day = Day(Index);
Name = Name(Index);
Result = Result(Index);

% Shift Result to get sorted NewVector
NewVector = circshift(Result, 4);
NewVector(1:4) = 0;

% Unsort NewVector, to get original table ordering
ReverseIndex(Index) = 1:length(Index);
NewVector = NewVector(ReverseIndex)

This prints the following result:

NewVector =

         0
         0
         0
         0
    0.7000
    1.2000
    0.9000
    1.1000
    1.0000
    1.4000
    1.5000
    0.9000