Roll join with no duplicates in R

Question

I have two tables coming from devices that gather data with different sampling frequencies. One device samples every 30 seconds, the other is roughly 30 and sometimes drops measurements (example sequence might be 31, 61, 95, 151, notice how it missed the sample around ~120). My original data.frame contains a datetime instead of the number of seconds but the toy data should work to illustrate.

q1 <-
read.table(text="
           A    0   1.1
           A    30  1.2
           A    90  1.3
           A    120 1.4
           B    15  -5
           B    45  -3
           B    75  -3.5
           C    10  0
           C    40  -1.4
           C    70  -1")

q2 <-
  read.table(text="
           A    10  10.1
           A    40  10.2
           A    110 10.4
           B    30  -50
           B    90  -30
           C    5   0
           C    35  -10.4
           C    76  -10")

names(q1) <- c("key","datetime","x")
names(q2) <- c("key","timepoint","y")

# create a joint_time to keep the originals in place
q1$joint_time <- q1$datetime
q2$joint_time <- q2$timepoint

If I try to join by nearest, I get

# set the keys
data.table::setkey(data.table::setDT(q1), key, joint_time)
data.table::setkey(data.table::setDT(q2), key, joint_time)

q2[q1, roll="nearest"]

Notice the duplicates on row 4 and 6.

    key timepoint     y joint_time datetime    x
 1:   A        10  10.1          0        0  1.1
 2:   A        40  10.2         30       30  1.2
 3:   A       110  10.4         90       90  1.3
 4:   A       110  10.4        120      120  1.4
 5:   B        30 -50.0         15       15 -5.0
 6:   B        30 -50.0         45       45 -3.0
 7:   B        90 -30.0         75       75 -3.5
 8:   C         5   0.0         10       10  0.0
 9:   C        35 -10.4         40       40 -1.4
10:   C        76 -10.0         70       70 -1.0

My ideal output would join by nearest but fill with NA instead of duplicate on y values.

    key timepoint     y joint_time datetime    x
 1:   A        10  10.1          0        0  1.1
 2:   A        40  10.2         30       30  1.2
 3:   A       110  10.4         90       90  1.3
 4:   A        NA    NA        120      120  1.4
 5:   B        30 -50.0         15       15 -5.0
 6:   B        NA    NA         45       45 -3.0
 7:   B        90 -30.0         75       75 -3.5
 8:   C         5   0.0         10       10  0.0
 9:   C        35 -10.4         40       40 -1.4
10:   C        76 -10.0         70       70 -1.0

I'm fine with doing the join first and then finding the duplicates and changing them to NA. I will later try to interpolate the y variable there. Not sure if there's a direct way to do the join and fill with NA or if it has to be done a posteriori.

user_3215940 · Answer

Here's what I ended up doing, I don't think it's awesome but as far as I can see, it is working as expected.

q1$joint_time <- q1$datetime
q2$joint_time <- q2$timepoint
# create a sample id using the key since the data is grouped
q2$sample_id <- paste0(q2$key, as.character(1:nrow(q2)))

# Join
res <- q2[q1, roll="nearest"]
# fill with NAs
res %>% mutate_at(vars(y,timepoint), ~ifelse(duplicated(sample_id), NA, .))

Which produces

    key timepoint     y joint_time sample_id datetime    x
 1:   A        10  10.1          0        A1        0  1.1
 2:   A        40  10.2         30        A2       30  1.2
 3:   A       110  10.4         90        A3       90  1.3
 4:   A        NA    NA        120        A3      120  1.4
 5:   B        30 -50.0         15        B4       15 -5.0
 6:   B        NA    NA         45        B4       45 -3.0
 7:   B        90 -30.0         75        B5       75 -3.5
 8:   C         5   0.0         10        C6       10  0.0
 9:   C        35 -10.4         40        C7       40 -1.4
10:   C        76 -10.0         70        C8       70 -1.0

Roll join with no duplicates in R

1 Answers