R - Adding a count to a dataframe based on values in current row and other rows

user_8911493 0
Viewed 152

I have a dataframe (might not be sorted like this) that looks like this:

  Group Value
  A     1
  A     5
  A     6
  A     11
  B     3
  B     4
  B     5
  B     10

And now I want a new column that counts how many rows per Group that have a value that falls within a fixed range of the value in each row (let's say for this example that it has to be between 2 less than the current row's value and the actual value, inclusive). So the result would be

  Group Value New Count
  A     1     1 (because there is only 1 row in Group A between -1 and 1, this row)
  A     5     1 (because there is only 1 row in Group A between 3 and 5, this row)
  A     6     2 (because there are 2 rows in Group A between 4 and 6)..and so on
  A     11    1
  B     3     1
  B     4     2
  B     5     3
  B     10    1

I have seen some answers with respect to running total counters within a group, etc, but I haven't come across this situation in my searching on SO...

dataframe r
5 Answers

Another approach is to use a non-equi join and group on the join conditions:

library(data.table)
setDT(DF)[, New.Count := .SD[.(Group = Group, V1 = Value, V2 = Value - delta), 
                             on = .(Group, Value <= V1, Value >= V2), .N, by = .EACHI]$N][]

   Group Value New.Count
1:     A     1         1
2:     A     5         1
3:     A     6         2
4:     A    11         1
5:     B     3         1
6:     B     4         2
7:     B     5         3
8:     B    10         1

Data

library(data.table)
DF <- fread(
  "  Group Value
  A     1
  A     5
  A     6
  A     11
  B     3
  B     4
  B     5
  B     10"
)
user_38170040

I found a way looping, not sure how to do otherwise :

Df <- data.frame(list(Value = c(1,5,8,11,3,4,5,10), Group = c("A","A","A","A","B","B","B","B")))

for (i in 1:dim(Df)[1])
{Df$newcount[i] <-  sum(as.numeric(Df$Value <=Df$Value[i] & Df$Value >= Df$Value[i]-2 & Df$Group == Df$Group[i] )) }

It loop on each row and count the conditions you were saying : value between the value and value - 2, and in the same group. I was looking for a data.table way but didn't managed it. the output :

  Value Group newcount
1     1     A        1
2     5     A        1
3     8     A        1
4    11     A        1
5     3     B        1
6     4     B        2
7     5     B        3
8    10     B        1
user_80538170

You can achieve this with purrr, but maybe there is a more succinct way. We first create a new variable with the range we will search. Next we find all unique values for the given group. For the result we sum the count of all values which fall into the search range. We can wrap this in a function and re-use in a convenient way.

library(tidyverse)

find_counts <- function(x, range = 2) {
  search_range <- map(x, ~seq(.x-range, .x, 1))
  unique_vals <-  list(x)
  map2_int(unique_vals, search_range, ~sum(.x %in% .y))
}

Df %>% 
  group_by(Group) %>% 
  mutate(result = find_counts(Value))
#> # A tibble: 8 x 3
#> # Groups:   Group [2]
#>    Group Value result
#>   <fctr> <int>  <dbl>
#> 1      A     1      1
#> 2      A     5      1
#> 3      A     8      1
#> 4      A    11      1
#> 5      B     3      1
#> 6      B     4      2
#> 7      B     5      3
#> 8      B    10      1

Results from microbenchmark::microbenchmark with the following data:

set.seed(928374)
DF <- data.frame(Group = sample(letters[1:15], 500, replace = T),
                 Value = sample(1:10, 500, replace = T))

Unit: milliseconds
    expr        min         lq        mean      median          uq        max neval cld
     ANG 1607.59370 1645.93364 1776.582546 1709.976584 1822.011283 2603.61574    30   c
 ThomasK   15.30110   16.11919   19.040010   17.238959   19.550713   54.30369    30 a  
   denis  155.92567  165.73500  182.563020  171.147209  204.508171  253.26394    30  b 
     uwe    2.15669    2.46198    3.207837    2.570449    3.114574   13.28832    30 a

Data

Df <- read.table(text = " Group Value
  A     1
                     A     5
                     A     8
                     A     11
                     B     3
                     B     4
                     B     5
                     B     10", header = T)
user_31493490

Based on what you started (as mentionned in your comment), here is loop to do it

df <- data.frame(Group = c(rep("A", 4), rep("B", 4)),
                 Value = c(1, 5, 6, 11, 3, 4, 5, 10))
require(dplyr)
for(i in seq_along(df$Value)){
        df$NewCount[i] <- nrow(df %>% filter(Group == Group[i] &
                                                     Value <= Value[i] &
                                                     Value >= Value[i]-2))
}
user_85800930

Only base R:

count_in_range = function(x){
    delta = 2
    vapply(x, 
           FUN = function(value) sum(x>=(value - delta) & x<=value, na.rm = TRUE), 
           FUN.VALUE = numeric(1)
           ) 
}

dfs$newcount = ave(dfs$Value, dfs$Group, FUN = count_in_range)  
dfs 

#     Group Value newcount
# 1     A     1        1
# 2     A     5        1
# 3     A     6        2
# 4     A    11        1
# 5     B     3        1
# 6     B     4        2
# 7     B     5        3
# 8     B    10        1

Benchmark with data.table:

set.seed(928374)
DF <- data.frame(Group = sample(letters[1:15], 500, replace = T),
                 Value = sample(1:10, 500, replace = T))

library(data.table)
library(microbenchmark)
DT = as.data.table(DF)

delta = 2
microbenchmark(
    datatable = {
        DT[, New.Count := .SD[.(Group = Group, V1 = Value, V2 = Value - delta), 
                              on = .(Group, Value <= V1, Value >= V2), .N, by = .EACHI]$N][]
    },

    ave = {
        DF$newcount = ave(DF$Value, DF$Group, FUN = count_in_range)    
    } 

)

# Unit: microseconds
# expr      min        lq      mean    median       uq      max neval
# datatable 1424.814 1438.3355 1492.9422 1459.2175 1512.100 1914.575   100
# ave        712.708  737.1955  849.0507  756.7265  789.327 3583.369   100

all.equal(DF$newcount, DT$New.Count) # TRUE
user_3874080
Related