MATLAB Answers

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

4 views (last 30 days)
buhmatlab
buhmatlab on 8 Apr 2020
Edited: buhmatlab on 11 Apr 2020
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 Comments

Sign in to comment.

Accepted Answer

Tommy
Tommy on 8 Apr 2020
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 Comments

Show 2 older comments
buhmatlab
buhmatlab on 10 Apr 2020
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
Tommy on 10 Apr 2020
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
buhmatlab on 11 Apr 2020
I've understood it now AAAND it works perfectly...I cannot thank you enough!!

Sign in to comment.

More Answers (0)


Translated by