Show values depending on values in another column above the actual row
조회 수: 1 (최근 30일)
이전 댓글 표시
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
채택된 답변
Tommy
2020년 4월 8일
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
Tommy
2020년 4월 10일
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.
추가 답변 (0개)
참고 항목
카테고리
Help Center 및 File Exchange에서 Structures에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!