CODEWITHSUNDEEP
rdata.table
VeraShao
VeraShao

Reputation: 63

Insert a row before each group of data using data.table

This maybe a silly question. But I want to insert a role before each group of data. All I can find online is how to insert a row after each group. Also, the inserted row will have a sequence number represent each ID. For example, i have a data table like

df 
ID     TIME   VAR VALUE
101     07/02   V1     9
101     07/03   V2     NA
101     07/03   V3     10
221     06/01   V1     2
221     07/03   V2     4

I want something like:

ID     TIME   VAR     VALUE
101       NA   sequence   1
101     07/02   V1        9
101     07/03   V2        NA
101     07/03   V3        10
221       NA   sequence   2
221     06/01   V1        2
221     07/03   V2        4

The real table have around 14 millions rows with 14,000 unique ID. Is there a quick way to update the table? Thank you!

Upvotes: 4

Views: 854

Answers (1)

Clayton Stanley
Clayton Stanley

Reputation: 7784

Might be faster as it does the rbind in bulk:

> tbl[, .SD
      ][, .N, ID
      ][, VALUE := .I
      ][, VAR := 'sequence'
      ][, N := NULL
      ][, rbind(.SD, tbl, fill=T)
      ][order(ID, VAR != 'sequence', TIME)
      ][, .(ID, TIME, VAR, VALUE)
      ]

    ID  TIME      VAR VALUE
1: 101    NA sequence     1
2: 101 07/02       V1     9
3: 101 07/03       V2    NA
4: 101 07/03       V3    10
5: 221    NA sequence     2
6: 221 06/01       V1     2
7: 221 07/03       V2     4
>

Upvotes: 2

Related Questions