Merge Dataframes on Matching A, B and *Closest* C

Matching two dataframes based on a common ID and the closest timestamp (less than 5 min) in R

Using the dfata frames shown reproducibly in the Note at the end, perform a left join using the indicated condition grouping by dat1 rows and taking the minimum seconds difference over the matched rows. Remove the seconds difference column (4) at the end. Note that there are 60 * 5 seconds in 5 minutes.

library(sqldf)

out <- sqldf("select a.ID, 
              a.timestamp, 
              b.timestamp [timestamp.y], 
              min(abs(a.timestamp - b.timestamp)) seconds, 
              b.x1, 
              b.x2
  from dat1 a
  left join dat2 b on a.ID = b.ID and
                      abs(a.timestamp - b.timestamp) < 60 * 5
  group by a.rowid")[-4]
out$timestamp.y <- as.POSIXct(out$timestamp.y, origin = "1970-01-01")

# check
all.equal(out, target)
## [1] TRUE

Note

Assume the inputs and target below shown reproducibly. Note that the timestamp columns have POSIXct class.

dat1 <-
structure(list(ID = 1:3, timestamp = structure(c(1603708643, 
1603712596, 1603719412), class = c("POSIXct", "POSIXt"), tzone = "")), 
row.names = c(NA, -3L), class = "data.frame")

dat2 <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 3L), 
timestamp = structure(c(1603713323, 1603708763, 1603715319, 
1603724172, 1603751992, 1603719472, 1603721777, 
1603799685, 1603801544), class = c("POSIXct", "POSIXt"), tzone = ""), 
    x1 = c("a", "b", "c", "d", "e", "a", "b", "c", "d"), x2 = c("c", 
    "b", "e", "a", "e", "a", "f", "d", "a")), row.names = c(NA, 
-9L), class = "data.frame")

target <-
structure(list(ID = 1:3, timestamp = structure(c(1603708643, 
1603712596, 1603719412), class = c("POSIXct", "POSIXt")), 
timestamp.y = structure(c(1603708763, 
NA, 1603719472), class = c("POSIXct", "POSIXt")), 
    x1 = c("b", NA, "a"), x2 = c("b", NA, "a")), row.names = c(NA, 
-3L), class = "data.frame")

Merge dataframes on nearest datetime / timestamp

You can use reindex with method='nearest' and then merge:

A['date'] = pd.to_datetime(A.date)
B['date'] = pd.to_datetime(B.date)
A.sort_values('date', inplace=True)
B.sort_values('date', inplace=True)

B1 = B.set_index('date').reindex(A.set_index('date').index, method='nearest').reset_index()
print (B1)

print (pd.merge(A,B1, on='date'))
  ID_x       date ID_y value
0    B 1991-01-01    B     7
1    B 1999-08-02    C     1
2    A 2014-06-22    B     8
3    A 2014-07-02    A     5
4    C 2015-01-01    A     3

You can also add parameter suffixes:

print (pd.merge(A,B1, on='date', suffixes=('_', '')))
  ID_       date ID value
0   B 1991-01-01  B     7
1   B 1999-08-02  C     1
2   A 2014-06-22  B     8
3   A 2014-07-02  A     5
4   C 2015-01-01  A     3

Matching nearest values in two dataframes of different lengths

Using KDTree, you can find the closest math in df1 in m O(log n) which n is the number of elements in df2 and m number of elements in df1.

import pandas as pd
import numpy as np
from scipy.spatial import cKDTree

df1 = pd.DataFrame({'a':np.array([1.2345,2.2345,3.2345]),'b':np.array([4.123,5.123,6.123])})
df2 = pd.DataFrame({'A':np.array([1.2346,2.2343]),'B':np.array([4.1232,5.1239])})

def spatial_merge_NN(df1, df2, xyz=['A', 'B']):
    ''' Add features from df2 to df1, taking closest point '''
    tree = cKDTree(df2[xyz].values)
    dists, indices = tree.query(df1[['a','b']].values, k=1)
    fts = [c for c in df2.columns]
    for c in fts:
        df1[c] = df2[c].values[indices]
    return df1

df_new = spatial_merge_NN(df1, df2, ['A', 'B'])
#         a      b       A       B
# 0  1.2345  4.123  1.2346  4.1232
# 1  2.2345  5.123  2.2343  5.1239
# 2  3.2345  6.123  2.2343  5.1239

It put one dataframe constant ( in this case df1) and iterate through df2 and find the closest pair from d2 and add that row.

Related Topics