How to count the number of times that the previous observation was repeated

1 view (last 30 days)
Mia Dier
Mia Dier on 15 Jan 2021
Commented: Mia Dier on 16 Jan 2021
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 times 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 (first value for each id=NaN)
1 2001 10 1 (last year's M=10 repeated once in the past 5 years)
1 2002 NaN 2 (last year's M=10 and is repeated twice in the past 5years)
1 2003 15 NaN (last year's M=NaN therefore it is also 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'm using the following code but it takes forever to run it with a large data set and I couldn't come up with any other solution without using the for loop.
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

Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by