I have a data table like this.
ID1 ID2 member
1 a x parent
2 a y child
3 a z parent
4 a p child
5 a q child
6 b x parent
7 b z parent
8 b q child
And I want to assign a sequence like below.
ID1 ID2 member sequence
1 a x parent 1
2 a y child 2
3 a z parent 1
4 a p child 2
5 a q child 3
6 b x parent 1
7 b z parent 1
8 b q child 2
i.e.
> dt$sequence = 1, wherever dt$member == "parent"
> dt$sequence = previous_row_value + 1, wherever dt$member=="child"
As of now I have been doing it using loops, like below.
dt_sequence <- dt[ ,sequencing(.SD), by="ID1"]
sequencing <- function(dt){
for(i in 1:nrow(dt)){
if(i == 1){
dt$sequence[i] = 1
next
}
if(dt[i,member] %in% "child"){
dt$sequence[i] = as.numeric(dt$sequence[i-1]) + 1
}
else
dt$sequence[i] = 1
}
return(dt)
}
I ran this code on a data table of 400 000 rows and it took a lot of time to complete (around 15 mins). Can anyone suggest a faster way to do it.
Here's one way with seq
:
dt[ , sequence := seq(.N), by = cumsum(member == "parent")]
# ID1 ID2 member sequence
# 1: a x parent 1
# 2: a y child 2
# 3: a z parent 1
# 4: a p child 2
# 5: a q child 3
# 6: b x parent 1
# 7: b z parent 1
# 8: b q child 2
How it works?
The command member == "parent"
creates a logical vector. The function cumsum
is used to calculate the cumulative sum. In this case, it creates vector in which a parent and the following childs have the same number. This vector is used for grouping. Finally, seq(.N)
creates a sequence from 1 up to the number of elements in the group.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With