基于data.table实现重复组索引重启并生成trip与seq字段

Got it, let's solve this problem where we need to create two new fields (trip and seq) from a site column, where:

• trip gets a new number every time the site value changes from the previous row (each consecutive same-site block is a unique trip)
• seq restarts at 1 for each new trip and counts up through the rows in that trip.

Step-by-Step Solution #

First, we'll use data.table since it's efficient for these kinds of row-wise and group-wise operations.

1. Load the data.table package (if you haven't already):

library(data.table)

2. Create your sample data (or use your existing dataset):

dt <- data.table(
  site = c("A", "A", "B", "B", "B", "A", "A", "C", "B")
)

3. Generate the trip field with rleid():
   This function creates a unique ID for each consecutive run of identical values in the site column—exactly what we need for trips.

dt[, trip := rleid(site)]

4. Generate the resetting seq field:
   Group by the trip we just created, then assign a sequence from 1 to the number of rows in each group (using .N, which gives the size of the current group).

dt[, seq := seq_len(.N), by = trip]

Final Result #

If you print the table now, you'll get exactly the output you provided:

print(dt)
#    site trip seq
# 1:    A    1   1
# 2:    A    1   2
# 3:    B    2   1
# 4:    B    2   2
# 5:    B    2   3
# 6:    A    3   1
# 7:    A    3   2
# 8:    C    4   1
# 9:    B    5   1

Why This Works #

• rleid(site) is perfect here because it's optimized to detect consecutive value groups—no need for messy lag comparisons or cumulative sums.
• Using seq_len(.N) grouped by trip ensures the sequence resets to 1 at the start of every new trip, which is exactly the "index restart" functionality you need.

内容的提问来源于stack exchange，提问作者Tse Lert