Clear Filters
Clear Filters

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

1 view (last 30 days)
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!!

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
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
Edited: 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)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!