How to count the number of times that the previous observation was repeated
2 views (last 30 days)
Show older comments
I have a table that looks like this:
country_id year M
1 2000 10
1 2001 10
1 2002 NaN
1 2003 15
1 2004 10
1 2005 10
2 2002 5
2 2003 5
2 2004 20
2 2005 10
2 2006 5
And I want to calculate the number of years that the previous year's M value was repeated in the last 5 years. That is,
country_id year M prev_count
1 2000 10 NaN
1 2001 10 1
1 2002 NaN 2
1 2003 15 NaN
1 2004 10 1
1 2005 10 2
2 2002 5 NaN
2 2003 5 1
2 2004 20 2
2 2005 20 1
2 2006 5 2
I can't run the code below because the count function works only for strings. Is there a function that can be used alternatively?
prev_count=NaN(N,1);
for i=1:5
for k=i+1:N
if myTable{k,'country_id'} == myTable{k-i,'country_id'}
prev_count(k,1)=count(M(k-i:k-1,1), country_id(i,1));
end
end
end
Accepted Answer
Cris LaPierre
on 9 Jan 2021
Edited: Cris LaPierre
on 9 Jan 2021
Use logical indexing and sum the true cases.
for r = 1:height(myTable)
T = myTable.country_id == myTable.country_id(r) & ...
myTable.year >= myTable.year(r)-5 & ...
myTable.year < myTable.year(r) & ...
myTable.M == myTable.M(r);
myTable.prev_count(r) = sum(T);
end
7 Comments
Cris LaPierre
on 9 Jan 2021
Edited: Cris LaPierre
on 9 Jan 2021
Ok, so just update the code I shared to meet your criteria.
for r = 2:height(myTable)
T = myTable.country_id == myTable.country_id(r) & ...
myTable.year >= myTable.year(r)-4 & ...
myTable.year < myTable.year(r) & ...
myTable.M == myTable.M(r-1);
myTable.prev_count(r) = sum(T);
end
myTable.prev_count(myTable.prev_count==0) = NaN
More Answers (0)
See Also
Categories
Find more on Logical in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!