How to calculate the number of times the values of a column changes
2 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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 commentaires
Réponse acceptée
Adam Danz
le 13 Jan 2021
Modifié(e) : Adam Danz
le 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)
0 commentaires
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Numeric Types dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!