How to calculate the number of times the values of a column changes
조회 수: 6 (최근 30일)
이전 댓글 표시
I want to calculate the number of times of column A changes over the last 5 years for each id number not counting the current year.
My table looks like this:
firm time A
1 1990 10
1 1991 10
1 1992 20
1 1993 11
1 1994 NaN
1 1995 11
1 1996 10
2 2001 20
2 2002 25
2 2003 20
2 2004 20
댓글 수: 2
채택된 답변
Adam Danz
2021년 1월 13일
편집: Adam Danz
2021년 1월 14일
This demo matches your 2nd example.
This temporarily breaks up the table into sub-tables based on the ID and loops through each sub-table (i-loop) and then loops through each row of the sub-table starting with row 3 (j-loop). NaN values in the 'A' column are replaced with the next non-nan value within the sub-table. The yearIdx chooses all rows of the sub-table that are within 5 years of the current row (the window is set by the window variable), then counts the number of changes to A within the window.
% Create input table
data = [
1 1990 10
1 1991 10
1 1992 20
1 1993 11
1 1994 11
1 1995 11
1 1996 11
1 1997 10
2 2001 20
2 2002 25
2 2003 NaN
2 2004 20];
T = array2table(data,'VariableNames',{'id','Year','A'});
window = 5; % number of years prior to current year
% Not assuming id's are consecutive
T.numberOfChanges = nan(height(T),1);
[unqIDs,~,idIdx] = unique(T.id,'stable');
for i = 1:size(unqIDs,1)
Tidx = idIdx==unqIDs(i);
Tid = T(Tidx,:); % subsection of table for current ID
Tid.A = fillmissing(Tid.A,'next');
counts = nan(height(Tid),1);
for j = 3:height(Tid)
yearDiff = Tid.Year - Tid.Year(j);
yearIdx = yearDiff>-window-1 & yearDiff<0 ;
counts(j) = sum(diff(Tid.A(yearIdx))~=0);
end
T.numberOfChanges(Tidx) = counts;
end
Result
disp(T)
댓글 수: 0
추가 답변 (0개)
참고 항목
카테고리
Help Center 및 File Exchange에서 Numeric Types에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!