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

Question

buhmatlab 2020-4-8

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/516483-show-values-depending-on-values-in-another-column-above-the-actual-row

编辑： buhmatlab 2020-4-11

采纳的回答： Tommy

在 MATLAB Online 中打开

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!!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Tommy 2020-4-8

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/516483-show-values-depending-on-values-in-another-column-above-the-actual-row#answer_424883

在 MATLAB Online 中打开

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

5 个评论
显示 3更早的评论隐藏 3更早的评论

buhmatlab 2020-4-10

编辑：buhmatlab 2020-4-10

在 MATLAB Online 中打开

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 2020-4-10

在 MATLAB Online 中打开

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 2020-4-11

编辑：buhmatlab 2020-4-11

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

请先登录，再进行评论。

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

5 个评论
显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

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

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

5 个评论 显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

5 个评论
显示 3更早的评论隐藏 3更早的评论