sort multiple data.tables and fill in correct values

Question

I have 4 data.tables, I need a final table with merged value1-value4 in it.

Table 1 has 4 cases and 5 different timepoints and value1.

table 1:

case	time	value1	value2	value3	value4
A	2019-11-10	1	NA	NA	NA
A	2019-11-09	2	NA	NA	NA
B	2018-11-10	3	NA	NA	NA
C	2017-11-10	4	NA	NA	NA
D	2016-11-10	5	NA	NA	NA

Table 2 has the same 4 cases and the same 5 timepoints (with different order) and value2/value3 and value4.

table 2:

case	time	value1	value2	value3	value4
A	2019-11-10	NA	8	NA	NA
C	2017-11-10	NA	6	NA	NA
D	2016-11-10	NA	9	NA	NA
B	2018-11-10	NA	7	NA	NA
A	2019-11-09	NA	10	NA	NA

table3 and table 4:

same as before, table3 has value3 and table4 has value4

The final table should contain all 4 cases with the correct timepoints and value1 of table 1 / value2 of table2 and so on... How is it possible to create the final table with filled value1-value4?

table result:

case	time	value1	value2	value3	value4
A	2019-11-10	1	8	3	4
A	2019-11-09	2	10	3	4
B	2018-11-10	3	7	3	4
C	2017-11-10	4	6	3	4
D	2016-11-10	5	9	3	4

score 0 · Answer 1 · answered Nov 14 '22 at 09:43

With data.table:

data1 <- read.table(text='case  time    value1  value2
A   2019-11-10  1   NA
A   2019-11-09  2   NA
B   2018-11-10  3   NA
C   2017-11-10  4   NA
D   2016-11-10  5   NA',header=T)

data2 <- read.table(text='case  time    value1  value2
A   2019-11-10  1   8
A   2019-11-09  2   10
B   2018-11-10  3   7
C   2017-11-10  4   6
D   2016-11-10  5   9',header=T)

library(data.table)
setDT(data1)
setDT(data2)

data1[data2,on=.(case,time),.(case,time,value1,value2=i.value2,value1=x.value1)]

     case       time value1 value2 value1
   <char>     <char>  <int>  <int>  <int>
1:      A 2019-11-10      1      8      1
2:      A 2019-11-09      2     10      2
3:      B 2018-11-10      3      7      3
4:      C 2017-11-10      4      6      4
5:      D 2016-11-10      5      9      5

sort multiple data.tables and fill in correct values

1 Answers1