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

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

[Rik]

The mere fact that you have a follow-up question doesn't make this one a duplicate.

Answer 1

[Cris LaPierre]

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

[Mia Dier]

This unfortunately doesn't give the result that I want.

Here is the outcome I get from your code:

prev_count

0

1

0

0

2

3

0

1

0

1

2

[Cris LaPierre]

Perhaps you could explain your algorithm in more detail?

For example, why is [1 2002 NaN] supposed to be 2 when there are no previous instances of NaN. Yet [1 2004 10] supposed to be 1 when there are 2 instances of 10 in the previous 5 years in that country?

[Mia Dier]

I want to calculate the number of times the previous year's M was observed in the past 5 years. (If the previous year's M value is NaN then I want to get NaN as the prev_count as well. [1 2002 NaN] is supposed to be 2 because the previous year's M value (10) is observed twice in the past 5 years. I hope it is more clear now!

[Cris LaPierre]

And [1 2004 10]? It is 1 despite 2000 and 2001 also being 10.

[Mia Dier]

It is 1 because previous year's M value (15) appears only once.

[Cris LaPierre]

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

[Mia Dier]

thank you Cris!