R: Joining dataframes and keep latest samples without any duplicates

user_10017159 0
Viewed 35

I have two dataframes which I would like to join in a specific way. First of all, this is my base dataframe:

df1 <- tibble(ID = c(1, 1, 2, 2), time = c(as.POSIXct("2011-03-27 01:30:00"), as.POSIXct("2011-03-27 04:30:00"), as.POSIXct("2011-03-27 05:30:00"), as.POSIXct("2011-03-27 06:30:00")), weight = c(78, 21, 22, 38))

     ID time                weight
  <dbl> <dttm>               <dbl>
1     1 2011-03-27 01:30:00     78
2     1 2011-03-27 04:30:00     21
3     2 2011-03-27 05:30:00     22
4     2 2011-03-27 06:30:00     38

Then I have a second dataframe:

df2 <- tibble(ID = c(1,  2, 2), time = c(as.POSIXct("2011-03-27 00:30:00"), as.POSIXct("2011-03-27 04:30:00"), as.POSIXct("2011-03-27 06:00:00")), newValue = c(45, 5, 3))

     ID time           newValue
  <dbl> <dttm>                 <dbl>
1     1 2011-03-27 00:30:00       45
2     2 2011-03-27 04:30:00        5
3     2 2011-03-27 06:00:00        3

I now want to do a left join on ID and time. However, while == should be used to join rows for ID, I want to join rows considering time with >. In words, the resulting dataframe should contain the rows of df1 and the joined elements of df2 where df2$Time <=df1$Time. For that I'm using fuzzyjoin:

df_all <- fuzzyjoin::fuzzy_left_join(df1, df2, by = c("ID", "time"), match_fun = c(`==`, `>`))

This gives me:

   ID.x time.x              weight  ID.y time.y              newValue
  <dbl> <dttm>               <dbl> <dbl> <dttm>                 <dbl>
1     1 2011-03-27 01:30:00     78     1 2011-03-27 00:30:00       45
2     1 2011-03-27 04:30:00     21     1 2011-03-27 00:30:00       45
3     2 2011-03-27 05:30:00     22     2 2011-03-27 04:30:00        5
4     2 2011-03-27 06:30:00     38     2 2011-03-27 04:30:00        5
5     2 2011-03-27 06:30:00     38     2 2011-03-27 06:00:00        3

So, for ID == 1 everything is fine. I only have one entry in df2 for that ID and therefore the same newValue is used in both rows. Line 3 is also correct, because there is only one row in df2 where ID == 2 and df2$Time < "2011-03-27 05:30:00". However, for ID == 2 and df1$Time == 2011-03-27 06:30:00 both entries in df2 do fullfill the join requirement, because 2011-03-27 04:30:00 and 2011-03-27 06:00:00 are both smaller than 2011-03-27 06:30:00.

What I actually want is that in case of multiple matches only the entry in df2 with the latest timestamp should be used. So in the above case, only line 5 should be kept and there shouldn't be the line 4. As I have a huge database, joining all rows and then selecting afterwards is no option for me - so is there away to implement this logical on the joining level?

dataframe r
2 Answers

Joining is the process of building the cross product of all possible row pairs between two tables followed by filtering the rows matching the condition (e.g. df1$ID == df2$ID & df2$time <= df1$time). There is no other way to do this.

To minimize overhead, you can replace the fuzzy join with a full_join followed by a filter. These more primitive functions can be directly converted e.g. to SQL (see dbplyr package for details) to be executed in the db management system of your choice. We can not help further with the database due to the lack of naming the specific setup.

library(tidyverse)

df1 <- tibble(ID = c(1, 1, 2, 2), time = c(as.POSIXct("2011-03-27 01:30:00"), as.POSIXct("2011-03-27 04:30:00"), as.POSIXct("2011-03-27 05:30:00"), as.POSIXct("2011-03-27 06:30:00")), weight = c(78, 21, 22, 38))
df2 <- tibble(ID = c(1, 2, 2), time = c(as.POSIXct("2011-03-27 00:30:00"), as.POSIXct("2011-03-27 04:30:00"), as.POSIXct("2011-03-27 06:00:00")), newValue = c(45, 5, 3))


df1 %>%
  full_join(df2 %>% rename(time2 = time), by = "ID") %>%
  filter(time2 <= time) %>%
  group_by(ID, time) %>%
  arrange(desc(time2)) %>%
  slice(1)
#> # A tibble: 4 × 5
#> # Groups:   ID, time [4]
#>      ID time                weight time2               newValue
#>   <dbl> <dttm>               <dbl> <dttm>                 <dbl>
#> 1     1 2011-03-27 01:30:00     78 2011-03-27 00:30:00       45
#> 2     1 2011-03-27 04:30:00     21 2011-03-27 00:30:00       45
#> 3     2 2011-03-27 05:30:00     22 2011-03-27 04:30:00        5
#> 4     2 2011-03-27 06:30:00     38 2011-03-27 06:00:00        3

Created on 2022-05-24 by the reprex package (v2.0.0)

user_168531140

If you're not wedded to tidy here is a data.table solution that seems to work and will likely be > 10 times faster if you really do have a large dataset (> 1MM rows).

library(data.table)
setDT(df1)
setDT(df2)
df1[df2, newValue:=i.newValue, on=.(ID, time > time)]
df1
##    ID                time weight newValue
## 1:  1 2011-03-27 01:30:00     78       45
## 2:  1 2011-03-27 04:30:00     21       45
## 3:  2 2011-03-27 05:30:00     22        5
## 4:  2 2011-03-27 06:30:00     38        3
user_29850070
Related