CODEWITHSUNDEEP
r
Bob
Bob

Reputation: 37

Is there an elegent way to merge two data frame by timestamp in R?

Suppose I have two data frame, df1 and df2.

df1 <- data.frame(value = 1:5, timestamp = as.POSIXct( c( "2020-03-02 12:20:00", "2020-03-02 12:20:01", "2020-03-02 12:20:03" , "2020-03-02 12:20:05", "2020-03-02 12:20:08")))

df2 <- data.frame(value = 6:10, timestamp = as.POSIXct( c( "2020-03-02 12:20:01", "2020-03-02 12:20:02", "2020-03-02 12:20:03" , "2020-03-02 12:20:04", "2020-03-02 12:20:05")))

df1

value timestamp
1 2020-03-02 12:20:00
2 2020-03-02 12:20:01
3 2020-03-02 12:20:03
4 2020-03-02 12:20:05
5 2020-03-02 12:20:08

df2

value timestamp
6 2020-03-02 12:20:01
7 2020-03-02 12:20:02
8 2020-03-02 12:20:03
9 2020-03-02 12:20:04
10 2020-03-02 12:20:05

Now, I want to keep df1, and left join with df2 by timestamp, since the timestamp is not exactly the same, what I want to do is:

  1. If there is an exact match, then just left join the value from df2
  2. If there is not an exact match, then try to match with the latest timestamp, and left join that value
  3. If there is not a match (no latest timestamp), then return NA

Therefore, my expect output would be like this

data.frame(df1, value.df2 = c(NA, 6, 8, 10, 10))
value timestamp value.df2
1 2020-03-02 12:20:00 NA
2 2020-03-02 12:20:01 6
3 2020-03-02 12:20:03 8
4 2020-03-02 12:20:05 10
5 2020-03-02 12:20:08 10

I hope I could do this by tidyverse or data.table.

Upvotes: 1

Views: 1805

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269596

Here are several alternatives. I find the SQL solution the most descriptive. The base solution is pretty short and has no dependencies. The data.table approach is likely fast and the code is compact but you need to read the documentation carefully to determine whether or not it is doing what you want since it is not obvious from the code unlike the prior two solutions. The dplyr/fuzzyjoin solution may be of interest if you are using the tidyverse.

1) sqldf Perform a left self join such that we join to each a row all b rows having a timestamp less than or equal to it and then take only the b row having the maximum timestamp of the ones joined to each a row. Note that SQLite guarantees that when max is used on a particular field that any other column references in the same table will be to that same row.

For large data add the argument dbname = tempfile() to the sqldf call and it will perform the join out of memory so that R memory limitations don't apply. It would also be possible to add an index to the data to speed it up.

library(sqldf)

sqldf("select max(b.timestamp), a.*, b.value as 'value.df2'
  from df1 a
  left join df2 b on b.timestamp <= a.timestamp
  group by a.timestamp
  order by a.timestamp"
)[-1]

giving:

  value           timestamp value.df2
1     1 2020-03-02 12:20:00        NA
2     2 2020-03-02 12:20:01         6
3     3 2020-03-02 12:20:03         8
4     4 2020-03-02 12:20:05        10
5     5 2020-03-02 12:20:08        10

Note that it can be used within a magrittr pipeline by placing the sqldf statement within brace brackets and referring to the left hand side as [.] within the sql statement:

library(magrittr)
library(sqldf)

df1 %>%
  { sqldf("select max(b.timestamp), a.*, b.value as 'value.df2'
      from [.] a
      left join df2 b on b.timestamp <= a.timestamp
      group by a.timestamp
      order by a.timestamp")[-1]
  }

2) base For each timestamp find the ones that are less than or equal to it and take the last one or NA if none.

Match <- function(tt) with(df2, tail(c(NA, value[timestamp <= tt]), 1))
transform(df1, value.df2 = sapply(timestamp, Match))

3) data.table This package supports rolling joins:

as.data.table(df2)[df1, on = .(timestamp), roll = TRUE]

4) dplyr/fuzzyjoin the fuzzy_left_join joins all rows of df2 to df1 whose timestamp is less than or equal to it. Then for each joined row we take the last one and fix up the names.

library(dplyr)
library(fuzzyjoin)

df1 %>%
  fuzzy_left_join(df2, by = "timestamp", match_fun = `>=`) %>%
  group_by(timestamp.x) %>%
  slice(n = n()) %>%
  ungroup %>%
  select(timestamp = timestamp.x, value = value.x, value.df2 = value.y)

Upvotes: 4

Daniel James
Daniel James

Reputation: 1433

Use tidyverse package this simple way

df1 <- data.frame(value = 1:5, timestamp = as.POSIXct( c( "2020-03-02 12:20:00", "2020-03-02 12:20:01", "2020-03-02 12:20:03" , "2020-03-02 12:20:05", "2020-03-02 12:20:08")))
df2 <- data.frame(value = 6:10, timestamp = as.POSIXct( c( "2020-03-02 12:20:01", "2020-03-02 12:20:02", "2020-03-02 12:20:03" , "2020-03-02 12:20:04", "2020-03-02 12:20:05")))

library(tidyverse)
left_join(df1, df2, by = 'timestamp')

 #value.x           timestamp value.y
 #1       1 2020-03-02 12:20:00      NA
 #2       2 2020-03-02 12:20:01       6
 #3       3 2020-03-02 12:20:03       8
 #4       4 2020-03-02 12:20:05      10
 #5       5 2020-03-02 12:20:08      NA

Upvotes: 1

Related Questions