How to count the number of times that the previous observation was repeated
1 visualizzazione (ultimi 30 giorni)
Mostra commenti meno recenti
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
Risposta accettata
Cris LaPierre
il 9 Gen 2021
Modificato: Cris LaPierre
il 9 Gen 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 Commenti
Cris LaPierre
il 9 Gen 2021
Modificato: Cris LaPierre
il 9 Gen 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
Più risposte (0)
Vedere anche
Categorie
Scopri di più su Logical in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!