Python function similar to "Roll" function in R

January 13, 2022

I want to join two tables on rolling date as shown below.
Is it possible to do same thing in Python as done in R?
I did not find any examples online to do rolling join in Python.
Thanks in advance.

sales <- data.table(
  SaleId = c("S1", "S2", "S3", "S4", "S5"),
  SaleDate = as.Date(c("2014-2-20", "2014-5-1", "2014-6-15", "2014-7-1", "2014-12-31"))
)
sales



commercials <- data.table(
  CommercialId = c("C1", "C2", "C3", "C4"),
  CommercialDate = as.Date(c("2014-1-1", "2014-4-1", "2014-7-1", "2014-9-15"))
)
commercials


setkey(sales, "SaleDate")
setkey(commercials, "CommercialDate")

commercials[sales, roll = TRUE]


output:-
##    CommercialId CommercialDate   RollDate SaleId   SaleDate
## 1:           C1     2014-01-01 2014-02-20     S1 2014-02-20
## 2:           C2     2014-04-01 2014-05-01     S2 2014-05-01
## 3:           C2     2014-04-01 2014-06-15     S3 2014-06-15
## 4:           C3     2014-07-01 2014-07-01     S4 2014-07-01
## 5:           C4     2014-09-15 2014-12-31     S5 2014-12-31

>Solution :

pd.merge_asof can merge on nearest dates, with an optional parameter to control the direction if needed.

import pandas as pd
sales = pd.DataFrame({
  'SaleId':["S1", "S2", "S3", "S4", "S5"],
  'SaleDate': pd.to_datetime(["2014-2-20", "2014-5-1", "2014-6-15", "2014-7-1", "2014-12-31"])
})


commercials = pd.DataFrame({
      'CommercialId':["C1", "C2", "C3", "C4"],
      'CommercialDate':pd.to_datetime(["2014-1-1", "2014-4-1", "2014-7-1", "2014-9-15"])
})

pd.merge_asof(sales, commercials, left_on='SaleDate', right_on='CommercialDate')

Output

  SaleId   SaleDate CommercialId CommercialDate
0     S1 2014-02-20           C1     2014-01-01
1     S2 2014-05-01           C2     2014-04-01
2     S3 2014-06-15           C2     2014-04-01
3     S4 2014-07-01           C3     2014-07-01
4     S5 2014-12-31           C4     2014-09-15