TT Camara TT Camara - 3 years ago 97
R Question

Counting frequency when comparing to alternating groups of rows in a data.frame

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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download