R - identify row if next x rows have equal or smaller values compared to each previous row

Question

I'm trying to work out whether the next x (6 is the current plan but this could be subject to change) balances remain the same or decrease each month.

I did this in Excel such that it would start with the current month's value and compare next month's against it to see if it stayed the same or decreased and so on.

=IF(AND(H3<=H2,H4<=H3,H5<=H4,H6<=H5,H7<=H6,H8<=H7),1,0)

This isn't the most flexible or elegant formula as it was part of an initial exploration. To make everything cleaner and more reproducible, I'd like to put my calculations into R instead.

Here is a basic dataset that is like my data for multiple IDs over many months.

rbind(data.frame(ID=1,Month=1:11,Bal=seq(from=500, to=300, by=-20)),
  data.frame(ID=2,Month=1:10,Bal=rep(200,10)),
  data.frame(ID=3,Month=1:11,Bal=seq(from=300, to=500, by=20)))

Having something that calculates against the raw data on a row level or will work inside a ddply are ideal solutions variants.

I'm still pretty new to R and I'm sure there's an elegant solution for this, but I really can't see it. Anyone have a neat solution or could point me in the direction of the sorts of keyterms I should be researching to try and reach a solution?

Answer 1

I am not sure if I understood correctly:

checkfun <- function(x,n) {
   rev(filter(rev(c(diff(x) <= 0,NA)),rep(1,n),sides=1)) == n
}

This function calculates the differences between consecutive values and checks if they are <= 0. The filter sums the number of following n differences that fulfill the condition. This number is finally compared with n, to see if all of them fulfill the condition. (rev is only used, so that a one-sided filter can be used.)

DF$Bal[6] <- 505 #to not only have equal differences
library(plyr)
#example with 3 next values
ddply(DF,.(ID),transform,check=checkfun(Bal,3))
#    ID Month Bal check
# 1   1     1 500  TRUE
# 2   1     2 480  TRUE
# 3   1     3 460 FALSE
# 4   1     4 440 FALSE
# 5   1     5 420 FALSE
# 6   1     6 505  TRUE
# 7   1     7 380  TRUE
# 8   1     8 360  TRUE
# 9   1     9 340    NA
# 10  1    10 320    NA
# 11  1    11 300    NA
# 12  2     1 200  TRUE
# 13  2     2 200  TRUE
# 14  2     3 200  TRUE
# 15  2     4 200  TRUE
# 16  2     5 200  TRUE
# 17  2     6 200  TRUE
# 18  2     7 200  TRUE
# 19  2     8 200    NA
# 20  2     9 200    NA
# 21  2    10 200    NA
# 22  3     1 300 FALSE
# 23  3     2 320 FALSE
# 24  3     3 340 FALSE
# 25  3     4 360 FALSE
# 26  3     5 380 FALSE
# 27  3     6 400 FALSE
# 28  3     7 420 FALSE
# 29  3     8 440 FALSE
# 30  3     9 460    NA
# 31  3    10 480    NA
# 32  3    11 500    NA

Answer 2

If df is your data.frame:

you can find consecutive differences using:

df$diff <- do.call("c",lapply(unique(df$ID), function(x) c(0,diff(df$Bal[df$ID==x]))))

This assumes that you want to keep those calculations separate for different ID's.

> head(df)
  ID Month Bal diff
1  1     1 500    0
2  1     2 480  -20
3  1     3 460  -20
4  1     4 440  -20
5  1     5 420  -20
6  1     6 400  -20

Now, for a give k=6 (say), check:

sapply(unique(df$ID), function(x) ifelse(sum(df$diff[df$ID==x][1:k] < 0)!=0,1,0)) 
[1] 1 0 0

It returns a value of 1 (all differences are negative) or 0 (all differences are positive) for each ID.