CODEWITHSUNDEEP
rdata.table
JiaHuei Lin
JiaHuei Lin

Reputation: 33

How to count data with a certain condition after group

I have the following data.table.

dat <- read_csv(
    "name,cnt
     a,10
     a,22
     a,13
     b,4
     c,4
     c,5
     c,10"
  )

> dat
# A tibble: 7 x 2
  name    cnt
  <chr> <dbl>
1 a        10
2 a        22
3 a        13
4 b         4
5 c         4
6 c         5
7 c        10

I would like to calculate the # of rows that cnt is lower than the value of the current row after group by:

> dat
# A tibble: 7 x 3
  name    cnt  nrow
  <chr> <dbl> <dbl>
1 a        10     0
2 a        22     2
3 a        13     1
4 b         4     0
5 c         4     0
6 c         5     1
7 c        10     2

I tried to write a custom function to calculate the number of rows but I am stuck on the performance of a for loop. Is there any better way to do it?

Upvotes: 3

Views: 75

Answers (2)

thelatemail
thelatemail

Reputation: 93803

I think this is just a ranking by group essentially:

ave(dat$cnt, dat$name, FUN=function(x) rank(x, ties.method="min")) - 1
#[1] 0 2 1 0 0 1 2

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388797

We can group_by name and count number of values greater than the current value.

library(dplyr)

dat %>%
  group_by(name) %>%
  mutate(nrow = purrr::map_int(cnt, ~sum(. > cnt)))

#  name    cnt  nrow
#  <chr> <dbl> <int>
#1 a        10     0
#2 a        22     2
#3 a        13     1
#4 b         4     0
#5 c         4     0
#6 c         5     1
#7 c        10     2

In base R, we can create a custom function

get_count <- function(x) {
  sapply(x, function(i) sum(i > x))
}

and apply it using ave for each name

with(dat, ave(cnt, name, FUN = get_count))
#[1] 0 2 1 0 0 1 2

Upvotes: 1

Related Questions