CODEWITHSUNDEEP
r
Werner Hertzog
Werner Hertzog

Reputation: 2022

Counting the number of of incidences of a certain number, adding results to a new column

I have the following dataset:

structure(list(SERIAL = c(118694001L, 118694001L, 118694001L, 
118695001L, 118696001L, 118696001L, 118696001L, 118697001L, 118698001L, 
118698001L, 118699001L, 118699001L, 118699001L, 118700001L, 118700001L, 
118701001L, 118701001L), RELATED = c(9999L, 9999L, 9999L, 3100L, 
3100L, 3100L, 3100L, 3100L, 3100L, 3100L, 9999L, 9999L, 9999L, 
3100L, 3100L, 3100L, 3100L)), class = "data.frame", row.names = c(NA, 
-17L))

I want to create a new column, 'count,' that counts the incidence of the number 3100 in the column RELATED, but it has to be grouped by SERIAL.

I have tried

df <- within(data, DILs2 <- ave(SERIAL, list(SERIAL, RELATED == 3100), FUN=length))

The result should look like this:

SERIAL  RELATED Count
118694001   9999    0
118694001   9999    0
118694001   9999    0
118695001   3100    1
118696001   3100    3
118696001   3100    3
118696001   3100    3
118697001   3100    1
118698001   3100    2
118698001   3100    2
118699001   9999    0
118699001   9999    0
118699001   9999    0
118700001   3100    2
118700001   3100    2
118701001   3100    2
118701001   3100    2

Upvotes: 0

Views: 41

Answers (3)

Rushabh Patel
Rushabh Patel

Reputation: 2764

You can also do this-

library(data.table)
setDT(dt)
dt[,count:=.N,by=c("SERIAL")]
dt[,count:=ifelse(RELATED!=3100,0,count)]

> dt
       SERIAL RELATED count
 1: 118694001    9999     0
 2: 118694001    9999     0
 3: 118694001    9999     0
 4: 118695001    3100     1
 5: 118696001    3100     3
 6: 118696001    3100     3
 7: 118696001    3100     3
 8: 118697001    3100     1
 9: 118698001    3100     2
10: 118698001    3100     2
11: 118699001    9999     0
12: 118699001    9999     0
13: 118699001    9999     0
14: 118700001    3100     2
15: 118700001    3100     2
16: 118701001    3100     2
17: 118701001    3100     2

Upvotes: 1

Sonny
Sonny

Reputation: 3183

If you are looking for multiple values in RELATED, then using group_by(SERIAL, RELATED) and then mutate(count = n()) will be better. Below codes might help you going ahead

You could do that using dplyr as below:

library(dplyr)
df %>%
  group_by(SERIAL) %>%
  summarise(count  = sum(RELATED == 3100))
# A tibble: 8 x 2
     SERIAL count
      <int> <int>
1 118694001     0
2 118695001     1
3 118696001     3
4 118697001     1
5 118698001     2
6 118699001     0
7 118700001     2
8 118701001     2

Or in data.table as:

library(data.table)
setDT(df)[, .(count = sum(RELATED == 3100)), SERIAL]
      SERIAL count
1: 118694001     0
2: 118695001     1
3: 118696001     3
4: 118697001     1
5: 118698001     2
6: 118699001     0
7: 118700001     2
8: 118701001     2

Or in base R using aggregate as:

aggregate(RELATED ~ SERIAL, data=df, function(x) {sum(x == 3100)})
     SERIAL RELATED
1 118694001       0
2 118695001       1
3 118696001       3
4 118697001       1
5 118698001       2
6 118699001       0
7 118700001       2
8 118701001       2

Upvotes: 2

Bensstats
Bensstats

Reputation: 1056

Using your data frame. This can be done with one line of code. 

 > data.frame(table(df$SERIAL,df$RELATED))
        Var1 Var2 Freq
1  118694001 3100    0
2  118695001 3100    1
3  118696001 3100    3
4  118697001 3100    1
5  118698001 3100    2
6  118699001 3100    0
7  118700001 3100    2
8  118701001 3100    2
9  118694001 9999    3
10 118695001 9999    0
11 118696001 9999    0
12 118697001 9999    0
13 118698001 9999    0
14 118699001 9999    3
15 118700001 9999    0
16 118701001 9999    0

The rest is aesthetics.

Hope this is helpful.

Upvotes: 2

Related Questions