Insert rows based on column entry and determine the entry of the inserted row based on where it inserts

Question

I have this data, which I named A:

A <- read.table(text = "ID  TIME    EVID    AMT DOSE
1   10      1       100 20
1   12      1       100 20
1   14      1       100 20
1   16      1       100 20
1   17      0       100 20
1   18      1       100 20
1   20      1       100 20
1   22      1       100 20
2   5       1       100 40
2   10      1       100 40
2   15      1       100 40
2   17      0       100 40
2   20      1       100 40
3   4       1       100 25
3   7       1       100 25
3   10      1       100 25
3   11      0       100 25
3   13      1       100 25
3   16      1       100 25
3   19      1       100 25", header = TRUE)

And my goal is insert new rows with EVID=2, ID the same as the preceding row ID, and TIME = preceding row's TIME entry plus AMT/DOSE, and I want the new rows to be follow after the first EVID=1 after the 0s, as below:

ID  TIME    EVID    AMT DOSE
1   10      1       100 20
1   12      1       100 20
1   14      1       100 20
1   16      1       100 20
1   17      0       100 20
1   18      1       100 20
1   23      2       100 20
1   20      1       100 20
1   22      1       100 20
2   5       1       100 40
2   10      1       100 40
2   15      1       100 40
2   17      0       100 40
2   20      1       100 40
2   22.5    2       100 40
3   4       1       100 25
3   7       1       100 25
3   10      1       100 25
3   11      0       100 25
3   13      1       100 25
3   17      2       100 25
3   16      1       100 25
3   19      1       100 25

I get as far as to indexing my EVID's

rle(as.character(EVID))$lengths
A$Index<-unlist(sapply(rle(as.character(EVID))$lengths, seq_len), use.names = FALSE)

In this circumstance this code works better than ave(EVID, EVID, FUN=seq_along) which would index all the 1s and all the 0s regardless of whether they are continuous. I want to insert my new rows between Index=1 and Index=2 rows (I will just manually delete the first new row).

   ID TIME EVID AMT DOSE Index
1   1   10    1 100   20     1
2   1   12    1 100   20     2
3   1   14    1 100   20     3
4   1   16    1 100   20     4
5   1   17    0 100   20     1
6   1   18    1 100   20     1
7   1   20    1 100   20     2
8   1   22    1 100   20     3
9   2    5    1 100   40     4
10  2   10    1 100   40     5
11  2   15    1 100   40     6
12  2   17    0 100   40     1
13  2   20    1 100   40     1
14  3    4    1 100   25     2
15  3    7    1 100   25     3
16  3   10    1 100   25     4
17  3   11    0 100   25     1
18  3   13    1 100   25     1
19  3   16    1 100   25     2
20  3   19    1 100   25     3

The resulting A has a new Index column; I want the new rows to be between Index 1 and 2, i.e. after row number 1, 6, 13, and 19 in this example.

I've come across solutions in which we can make a column vector, then insert the column as row into the data by defined row number. How do I add the rows based on column entry and determined some entries dynamically?

Thanks for your help!

Answer 1

here is a solution with data.table It's really just two lines of code (with a bit of comments)

library(data.table)
ADT <- data.table(row=1:nrow(A), A, key="ID")

# just to give an idea of how we can Find the first 0 after the first 1, look at the output from this
ADT[, list(row, EVID,c(NA,diff(EVID)), c(NA,diff(EVID))==1)]

# identify afer which row to insert
# the values you want to change, assign using the `=`
# the values to keep, just call the variable name, no `=` sign
newRows <- ADT[c(NA,diff(EVID))==1, list(row=row+1, ID, TIME=TIME+AMT/DOSE, EVID=2, AMT, DOSE)]

# rbind the new rows with the original DT
# then reverse order by EVID, and order by row.  
# After ordering, remove the first column (`row`) since it is not needed
newA <- rbind(ADT, newRows)[order(EVID, decreasing=TRUE)][order(row)][, -1, with=FALSE]


### Results: 

 > newA
    ID TIME EVID AMT DOSE
 1:  1   10    1 100   20
 2:  1   12    1 100   20
 3:  1   14    1 100   20
 4:  1   16    1 100   20
 5:  1   17    0 100   20
 6:  1   18    1 100   20
 7:  1   23    2 100   20
 8:  1   20    1 100   20
 9:  1   22    1 100   20
10:  2    5    1 100   40
11:  2   10    1 100   40
12:  2   15    1 100   40
13:  2   17    0 100   40
14:  2   20    1 100   40
15:  2   22    2 100   40
16:  3    4    1 100   25
17:  3    7    1 100   25
18:  3   10    1 100   25
19:  3   11    0 100   25
20:  3   13    1 100   25
21:  3   17    2 100   25
22:  3   16    1 100   25
23:  3   19    1 100   25
    ID TIME EVID AMT DOSE