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

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

[Tommy]

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)');

[buhmatlab]

This works ALMOST perfectly, sorry for my late answer. The only issue that occurred was that MATLAB asked for a correction with the following hint:

Error using arrayfun
Non-scalar in Uniform output, at index 13, output 1.
Set 'UniformOutput' to false.

After accepting the automatic correction the final code looks like:

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)', 'UniformOutput', false);

Which is not a problem per se. BUT instead of showing a 0 in the first rows, it shows only []. What could cause this issue?

THANK YOU!

[Tommy]

When using

B = arrayfun(func,A)

with 'UniformOutput' set to true (the default), then "func must return scalars that arrayfun concatenates into arrays." If you got an error suggesting you set 'UniformOutput' to false, then our func:

@(r) T.Result(T.Day==T.Day(r)-1 & T.Name==T.Name(r))

must not be returning scalars that can be concatenated into an array. (And, when 'UniformOutput' is false, the output is instead a cell array, which is why you are left with empty cells instead of 0s.)

I think there could be a few reasons why. Are you able to provide your table and your result?

[buhmatlab]

I see, I tried some combinations but without any success. I was wondering if it might be caused by the fact that the column 'Name' actually contains categories instead of strings but unfortunately the same message appeared. I actually wanna understand the code but maybe it would be much easier to just fill up empty cells with zeros?

My table looks like this (only the first 36 rows of a 300x1 table but after row 31 no empty cells occur):

Matlab is showing empty cells as [].

+-----+------+--------+------------+
| Day | Name | Result | New Vector |
+-----+------+--------+------------+
|  1  |   B  |    1   |            |
+-----+------+--------+------------+
|  1  |   C  |    2   |            |
+-----+------+--------+------------+
|  1  |   E  |    3   |            |
+-----+------+--------+------------+
|  1  |   J  |    2   |            |
+-----+------+--------+------------+
|  1  |   K  |    1   |            |
+-----+------+--------+------------+
|  1  |   L  |    0   |            |
+-----+------+--------+------------+
|  1  |   P  |    0   |            |
+-----+------+--------+------------+
|  1  |   O  |    0   |            |
+-----+------+--------+------------+
|  1  |   A  |    3   |            |
+-----+------+--------+------------+
|  1  |   D  |    2   |            |
+-----+------+--------+------------+
|  1  |   F  |    2   |            |
+-----+------+--------+------------+
|  1  |   G  |    0   |            |
+-----+------+--------+------------+
|  1  |   H  |    0   |            |
+-----+------+--------+------------+
|  1  |   Q  |    3   |            |
+-----+------+--------+------------+
|  1  |   R  |    1   |            |
+-----+------+--------+------------+
|  1  |   I  |    3   |            |
+-----+------+--------+------------+
|  1  |   M  |    2   |            |
+-----+------+--------+------------+
|  2  |   B  |    1   |            |
+-----+------+--------+------------+
|  2  |   C  |    3   |      2     |
+-----+------+--------+------------+
|  2  |   E  |    2   |      3     |
+-----+------+--------+------------+
|  2  |   I  |   2.5  |      3     |
+-----+------+--------+------------+
|  2  |   J  |   1.5  |      2     |
+-----+------+--------+------------+
|  2  |   L  |   0.5  |      0     |
+-----+------+--------+------------+
|  2  |   P  |    0   |      0     |
+-----+------+--------+------------+
|  2  |   K  |   0.5  |      1     |
+-----+------+--------+------------+
|  2  |   O  |    0   |      0     |
+-----+------+--------+------------+
|  2  |   A  |   2.5  |      3     |
+-----+------+--------+------------+
|  2  |   F  |    2   |      2     |
+-----+------+--------+------------+
|  2  |   G  |    0   |      0     |
+-----+------+--------+------------+
|  2  |   M  |    2   |      2     |
+-----+------+--------+------------+
|  1  |   N  |    3   |            |
+-----+------+--------+------------+
|  2  |   Q  |    2   |      3     |
+-----+------+--------+------------+
|  2  |   R  |    1   |      1     |
+-----+------+--------+------------+
|  2  |   D  |    1   |      2     |
+-----+------+--------+------------+
|  2  |   H  |    1   |      0     |
+-----+------+--------+------------+
|  2  |   N  |    3   |      3     |
+-----+------+--------+------------+

[Tommy]

Ok let me know if this makes any sense! This line:

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

runs the function

@(r) T.Result(T.Day==T.Day(r)-1 & T.Name==T.Name(r))

for each r value in the vector

(n+1:N)'

where r is a row within T. So when r equals, say, 20, the function becomes

T.Result(T.Day==T.Day(20)-1 & T.Name==T.Name(20))

In your example, the 20th row of T is

|  2  |   E  |    2   |

so T.Day(20) is 2 and T.Name(20) is 'E'. The output for row 20 is then

T.Result(T.Day==1 & T.Name=='E')

This finds the row within T where the Day is 1 and the Name is 'E' (which is the 3rd row), and it takes the Result within that row (which is 3) and puts it at T.NewVector(20), so the 20th row of T is now

|  2  |   E  |    2   |      3     |

I assumed all of the Day 1 rows were at the top of the table, so that I could just skip the first n rows, hence the

(n+1:N)'

(where n is the number of rows taken up by Day 1, and N is the total number of rows). But if you have a Day 1 row hidden within Day 2 rows, at row 31 in your example:

|  1  |   N  |    3   |

then when r is 31, the function will be looking for

T.Result(T.Day==0 & T.Name=='N')

which doesn't exist. It then returns an empty array, which is not a scalar, and so you get an error and the recommendation to set 'UniformOutput' to false. (Additionally, this caused the 18th row in your example to be skipped).

Long story short, we need to make sure the values for r include only the rows within T where Day is not 1. I believe this

T.New(T.Day~=1) = arrayfun(@(r) T.Result(T.Day==T.Day(r)-1 & T.Name==T.Name(r)), find(T.Day~=1));

should work.

[buhmatlab]

I've understood it now AAAND it works perfectly...I cannot thank you enough!!