CODEWITHSUNDEEP
rdata.table
R007
R007

Reputation: 123

Generate sequence within sub group in data.table

I would like to generate sequence within subgroup columns e.g. I have two columns id1,val and would like to sort data by id1, val but then generate counter for id1.

Input 

input <- data.frame("id1"=c(1,1,1,1,2,2,2),val=c(2,3,4,1,4,3,5))

Expected Output 

id1,val,grp 
1,1,1
1,2,2
1,3,3
1,4,4
2,3,1
2,4,2
2,5,3

Previous Reference Posts :

Count for sub group using .grp in data.table

Numbering rows within groups in a data frame

Used below code (I am trying to use code on big data and looking for a solution so I don't need to add an extra step to sort data for "val" column before generating sequence)

input[, new1:=seq_len(.N), by=c('id1')]

Upvotes: 2

Views: 1089

Answers (2)

akrun
akrun

Reputation: 887118

We group by 'id1', sort the 'val' and then create 'grp' as row_number()

input %>%
  group_by(id1) %>%
  mutate(val = sort(val), grp= row_number())

Or another option is to arrange

input %>%
   arrange(id1, val) %>%
   group_by(id1) %>%
   mutate(grp = row_number())

Or using data.table

library(data.table)
setDT(input)[, c("grp", "val") := .(seq_len(.N), sort(val)), by = id1]
input
#   id1 val grp
#1:   1   1   1
#2:   1   2   2
#3:   1   3   3
#4:   1   4   4
#5:   2   3   1
#6:   2   4   2
#7:   2   5   3

If we need to sort as well, use setorder based on the 'id1' and 'val' to order in place, then create the 'grp' as the rowid of 'id1'

input <- data.frame("id1"=c(1,1,1,1,2,2,2),val=c(2,3,4,1,4,3,5), 
        achar=c('a','a','b','b','d','c','e'))
setorder(setDT(input), id1, val)[, grp := rowid(id1)][]
#   id1 val achar grp
#1:   1   1     b   1
#2:   1   2     a   2
#3:   1   3     a   3
#4:   1   4     b   4
#5:   2   3     c   1
#6:   2   4     d   2
#7:   2   5     e   3

Upvotes: 2

Dan
Dan

Reputation: 12074

Here's a little factor hack.

# Load library
library(data.table)

# Create data table
input <- data.table(id1=c(1,1,1,1,2,2,2),val=c(2,3,4,1,4,3,5))

input[, foo := as.integer(factor(val)), by = "id1"]

# Print result
input
#>    id1 val foo
#> 1:   1   2   2
#> 2:   1   3   3
#> 3:   1   4   4
#> 4:   1   1   1
#> 5:   2   4   2
#> 6:   2   3   1
#> 7:   2   5   3

# Reorder for comparison with question
input[order(id1, val)]
#>    id1 val foo
#> 1:   1   1   1
#> 2:   1   2   2
#> 3:   1   3   3
#> 4:   1   4   4
#> 5:   2   3   1
#> 6:   2   4   2
#> 7:   2   5   3

Created on 2019-11-29 by the reprex package (v0.3.0)

Upvotes: 0

Related Questions