How to calculate the number of times the values of a column changes

1 view (last 30 days)
Mia Dier
Mia Dier on 13 Jan 2021
Edited: Mia Dier on 14 Jan 2021
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

Accepted Answer

Adam Danz
Adam Danz on 13 Jan 2021
Edited: Adam Danz on 14 Jan 2021
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)
id Year A numberOfChanges __ ____ ___ _______________ 1 1990 10 NaN 1 1991 10 NaN 1 1992 20 0 1 1993 11 1 1 1994 11 2 1 1995 11 2 1 1996 11 2 1 1997 10 1 2 2001 20 NaN 2 2002 25 NaN 2 2003 NaN 1 2 2004 20 2

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by