Filter löschen
Filter löschen

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

2 Ansichten (letzte 30 Tage)
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

Antworten (0)

Kategorien

Mehr zu Loops and Conditional Statements finden Sie in Help Center und File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by