MATLAB: Show values depending on values in another column above the actual row

Question

Hi,

I'm struggling with the following problem:

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

A few possibilities:

If you don't care about the order of the rows changing:

T = sortrows(T, {'Day', 'Name'});
N = sum(T.Day==1);
T.New = [zeros(N,1); T.Result(1:end-N)];

If you do:

n = sum(T.Day==1);
N = numel(T.Result);
T.New(n+1:N) = arrayfun(@(r) T.Result(T.Day==T.Day(r)-1 & T.Name==T.Name(r)), (n+1:N)');