CODEWITHSUNDEEP
rdataframedatetime
Maya
Maya

Reputation: 579

Join two data frames in R based on closest timestamp within groups

I have the following dataframes

structure(list(id = c(1, 2, 3, 4, 5), time = structure(c(1484092800, 
1485907200, 1490227200, 1490918400, 1491955200), class = c("POSIXct", 
"POSIXt"), tzone = "UTC")), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

     id time               
  <dbl> <dttm>             
1     1 2017-01-11 00:00:00
2     2 2017-02-01 00:00:00
3     3 2017-03-23 00:00:00
4     4 2017-03-31 00:00:00
5     5 2017-04-12 00:00:00

structure(list(id = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 
3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5), time = structure(c(1466553600, 
1465948800, 1453420800, 1485302400, 1433030400, 1421712000, 1453852800, 
1485302400, 1485993600, 1517529600, 1400544000, 1434067200, 1466985600, 
1497484800, 1390003200, 1516060800, 1464825600, 1497916800, 1527638400, 
1454025600, 1390608000, 1421712000, 1466467200, 1453852800, 1485820800
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), score = c(3, 
2, 5, 4, 5, 24.2, 24.8, 25.4, 26, 26.6, 36.2, 36.8, 37.4, 38, 
38.6, 44, 44.6, 45.2, 45.8, 46.4, 59, 59.6, 60.2, 60.8, 61.4)), row.names = c(NA, 
-25L), class = c("tbl_df", "tbl", "data.frame"))

      id time                score
   <dbl> <dttm>              <dbl>
 1     1 2016-06-22 00:00:00   3  
 2     1 2016-06-15 00:00:00   2  
 3     1 2016-01-22 00:00:00   5  
 4     1 2017-01-25 00:00:00   4  
 5     1 2015-05-31 00:00:00   5  
 6     2 2015-01-20 00:00:00  24.2
 7     2 2016-01-27 00:00:00  24.8
 8     2 2017-01-25 00:00:00  25.4
 9     2 2017-02-02 00:00:00  26  
10     2 2018-02-02 00:00:00  26.6
# … with 15 more rows

I would like to have the score of sdf where the time is closest to that of in df. But I would also have to look at the id's! I already tried this from Join two data frames in R based on closest timestamp:

d   <- function(x,y) abs(x-y) # define the distance function
idx <- sapply( df$time, function(x) which.min( d(x,sdf$time) )) 

cbind(df,sdf[idx,-1,drop=FALSE])

  id       time       time score
1  1 2017-01-11 2017-01-25     4
2  2 2017-02-01 2017-02-02    26
3  3 2017-03-23 2017-02-02    26
4  4 2017-03-31 2017-02-02    26
5  5 2017-04-12 2017-06-15    38

But you don't look at the id, I tried to incorporate the id, however did not work. Any ideas? Thank you in advance :)

Upvotes: 0

Views: 90

Answers (1)

Ahorn
Ahorn

Reputation: 3876

We can join the data frames by id and then calculate the time difference and keep the observation with the minimal time difference for each individual:

library(tidyverse)
df2 %>% 
  left_join(df1, by = "id") %>% 
  mutate(time_dif = abs(time.x - time.y)) %>% 
  group_by(id) %>% 
  filter(time_dif == min(time_dif))

# A tibble: 5 x 5
# Groups:   id [5]
     id time.x              score time.y              time_dif
  <dbl> <dttm>              <dbl> <dttm>              <drtn>  
1     1 2017-01-25 00:00:00   4   2017-01-11 00:00:00 14 days 
2     2 2017-02-02 00:00:00  26   2017-02-01 00:00:00  1 days 
3     3 2017-06-15 00:00:00  38   2017-03-23 00:00:00 84 days 
4     4 2017-06-20 00:00:00  45.2 2017-03-31 00:00:00 81 days 
5     5 2017-01-31 00:00:00  61.4 2017-04-12 00:00:00 71 days

Data

df1 <- structure(list(id = c(1, 2, 3, 4, 5), time = structure(c(1484092800, 
                                                         1485907200, 1490227200, 1490918400, 1491955200), class = c("POSIXct", 
                                                                                                                    "POSIXt"), tzone = "UTC")), row.names = c(NA, -5L), class = c("tbl_df", 
                                                                                                                                                                                  "tbl", "data.frame"))



df2 <- structure(list(id = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 
                      3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5), time = structure(c(1466553600, 
                                                                              1465948800, 1453420800, 1485302400, 1433030400, 1421712000, 1453852800, 
                                                                              1485302400, 1485993600, 1517529600, 1400544000, 1434067200, 1466985600, 
                                                                              1497484800, 1390003200, 1516060800, 1464825600, 1497916800, 1527638400, 
                                                                              1454025600, 1390608000, 1421712000, 1466467200, 1453852800, 1485820800
                      ), class = c("POSIXct", "POSIXt"), tzone = "UTC"), score = c(3, 
                                                                                   2, 5, 4, 5, 24.2, 24.8, 25.4, 26, 26.6, 36.2, 36.8, 37.4, 38, 
                                                                                   38.6, 44, 44.6, 45.2, 45.8, 46.4, 59, 59.6, 60.2, 60.8, 61.4)), row.names = c(NA, 
                                                                                                                                                                 -25L), class = c("tbl_df", "tbl", "data.frame"))

Upvotes: 1

Related Questions