1

Input data.table:

            V1     V2  V3   V4   V5 V6 V7 V8                   V9
1: k141_100290 PROKKA CDS   62  364  .  -  . gene_id PROKKA_00256
2: k141_100292 PROKKA CDS  767 1198  .  -  . gene_id PROKKA_00257
3: k141_100292 PROKKA CDS 1201 1707  .  -  . gene_id PROKKA_00258
4: k141_100293 PROKKA CDS   79 1824  .  -  . gene_id PROKKA_00259
5: k141_100293 PROKKA CDS 1892 2152  .  -  . gene_id PROKKA_00260
6: k141_100293 PROKKA CDS 2155 2715  .  -  . gene_id PROKKA_00261
7: k141_100293 PROKKA CDS 2718 3641  .  -  . gene_id PROKKA_00262
8: k141_100293 PROKKA CDS 3647 4198  .  -  . gene_id PROKKA_00263
9: k141_100294 PROKKA CDS    1  222  .  +  . gene_id PROKKA_00264

For each factor level in V1, I need to make sequential labels in column seq_ID, like this:

            V1     V2  V3   V4   V5 V6 V7 V8                   V9        seq_ID
1: k141_100290 PROKKA CDS   62  364  .  -  . gene_id PROKKA_00256  k141_100290_1
2: k141_100292 PROKKA CDS  767 1198  .  -  . gene_id PROKKA_00257  k141_100292_1
3: k141_100292 PROKKA CDS 1201 1707  .  -  . gene_id PROKKA_00258  k141_100292_2
4: k141_100293 PROKKA CDS   79 1824  .  -  . gene_id PROKKA_00259  k141_100293_1
5: k141_100293 PROKKA CDS 1892 2152  .  -  . gene_id PROKKA_00260  k141_100293_2
6: k141_100293 PROKKA CDS 2155 2715  .  -  . gene_id PROKKA_00261  k141_100293_3
7: k141_100293 PROKKA CDS 2718 3641  .  -  . gene_id PROKKA_00262  k141_100293_4
8: k141_100293 PROKKA CDS 3647 4198  .  -  . gene_id PROKKA_00263  k141_100293_5
9: k141_100294 PROKKA CDS    1  222  .  +  . gene_id PROKKA_00264  k141_100294_1

It seems simple, but I am very stuck. The data.table is way too large to be handled by ddply, and I need a data.table or a dplyr solution.

David Arenburg
  • 91,361
  • 17
  • 137
  • 196
willnotburn
  • 99
  • 5

1 Answers1

2

This is very similar/possible duplicate of Numbering rows within groups in a data frame

All that's required to change mnel's answer is to paste V1 with the row_number

test <- data.frame(X = c("A","A","A","B","B","C","C","C","C"))
test %>% group_by(X) %>% mutate(seq_ID = paste(X, row_number(), sep = "_"))
Sarah
  • 3,022
  • 1
  • 19
  • 40
  • Thanks! It works. I am upvoting your answer, but will hold out for a `data.table` solution. While `dlpyr` is fast, my table has over 100 million rows. – willnotburn May 28 '18 at 23:09
  • whoops, I followed the link in your answer and figured out a `data.table` solution. Thanks again! Marked yours as the answer. – willnotburn May 29 '18 at 00:55