Home > Blockchain >  Create a new column with data.table that add unique values
Create a new column with data.table that add unique values

Time:11-15

ID
1
1
2
3
3
3
3

I want to create an additional column with data table that count the unique 1s, 2s, 3s, etc and sums them up. The final dat.table would be

ID CountID
1 2
1 2
2 1
3 4
3 4
3 4
3 4

I'm trying this but does not work:

df[, CountID := uniqueN(df, by = ID)]

CodePudding user response:

Using dplyr package

df1 = group_by(df, id) %>% count()
merge(df, df1)
   id n
1   1 3
2   1 3
3   1 3
4   2 1
5   3 4
6   3 4
7   3 4
8   3 4
9   4 2
10  4 2

Data

df = data.frame('id' = c( 1  , 1  , 1, 2, 3, 3, 3, 3, 4, 4))

CodePudding user response:

data.table

You can use .N for this:

library(data.table)
DT[, CountID := .N, by = ID]
DT
#       ID CountID
#    <int>   <int>
# 1:     1       2
# 2:     1       2
# 3:     2       1
# 4:     3       4
# 5:     3       4
# 6:     3       4
# 7:     3       4

base R

DT$CountID2 <- ave(rep(1L, nrow(DT)), DT$ID, FUN = length)

Data

DT <- setDT(structure(list(ID = c(1L, 1L, 2L, 3L, 3L, 3L, 3L), CountID = c(2L, 2L, 1L, 4L, 4L, 4L, 4L)), class = c("data.table", "data.frame"), row.names = c(NA, -7L)))
  • Related