Take the difference based on ID

Question

I have a df with 212 rows in the form of:

ID visit treatment value1 value2 value3
1    V0      A        2.6     3.4   .1
1    V1      A        2.3     4.6   .5
2    V0      B        1.3     5.4   .6
3    V0      A        1.6     5.4   .7
2    V1      B        1.8     4.5   .3
3    V1      A        1.3     7.3   1.2

So o have a column with ID, one with visit week and treatment and a bunch of columns with values. I want to take the difference for each ID, the treatment is the same for each ID, it never change from week 0 and 1. The ID don't necessarily comes in order. Is this possible?

It would be something like:

ID visit treatment value1 value2 value3
 1  v0-v1    A       0.3   -1.2     -.4

and so on.

Answer 1

Here's a data.table solution:

dt[by=.(ID,treatment),j={
    z <- nrow(.SD);
    c(
        .(visit=paste0(visit[1L],'-',visit[z])),
        lapply(mget(grep(value=T,'^value',names(.SD))),function(x) x[1L]-x[z])
    );
}];
##    ID treatment visit value1 value2 value3
## 1:  1         A V0-V1    0.3   -1.2   -0.4
## 2:  2         B V0-V1   -0.5    0.9    0.3
## 3:  3         A V0-V1    0.3   -1.9   -0.5

Data

library(data.table);
dt <- data.table(ID=c(1L,1L,2L,3L,2L,3L),visit=c('V0','V1','V0','V0','V1','V1'),treatment=c(
'A','A','B','A','B','A'),value1=c(2.6,2.3,1.3,1.6,1.8,1.3),value2=c(3.4,4.6,5.4,5.4,4.5,7.3),
value3=c(0.1,0.5,0.6,0.7,0.3,1.2));