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

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

[Adam Danz]

Are the year values double or datatime?

[Mia Dier]

They are double

Answer 1

[Adam Danz]

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