I have a table in which I want to
group every four rows into successive groups
compare each row to the 4 rows in the previous group
In effect, I will be using four rows at a time as a reference set in which each row of the following group of four will be compared to.
Specifically, given a row in group x, I want to count how many rows in the preceding group (i.e., group x-1) have a value that is less than or equal to the value in the row of interest.
I want to do this for each row.
Hence, I want to count, for each row in the second group of four rows (say 5 to 8), the number of rows that have a value which is less than or equal to it in the first (say rows 1 to 4). Then row 5 to 8 become the next reference group for the next four rows (9 to 12). Etc...
Row Values
1 1.35
2 0.71
3 1.00
4 0.07
5 0.53
6 0.12
7 0.36
8 2.03
9 3.83
10 1.30
11 2.17
12 1.71
13 1.52
14 1.27
15 0.29
16 0.05
17 0.14
The result would look like:
Row Values Count
1 1.35
2 0.71
3 1.00
4 0.07
5 0.53 1
6 0.12 1
7 0.36 1
8 2.03 4
9 3.83 4
10 1.30 3
11 2.17 4
12 1.71 3
13 1.52 1
14 1.27 0
15 0.29 0
16 0.05 0
17 0.14 1
You can try (if df is your data.frame):
sdf<-split(df$Values,(df$Row-1)%/%4)
c(rep(NA,4),unlist(Map(f=function(x,y)
findInterval(x,sort(y)),sdf[-1],sdf[-length(sdf)]),use.names=F))
#[1] NA NA NA NA 1 1 1 4 4 3 4 3 1 0 0 0 1