How to prevent data from being recycled when using pd.merge_asof in Python

  dataframe, pandas, python

I am looking to join two data frames using the pd.merge_asof function. This function allows me to match data on a unique id and/or a nearest key. In this example, I am matching on the id as well as the nearest date that is less than or equal to the date in df1.

Is there a way to prevent the data from df2 being recycled when joining?

This is the code that I currently have that recycles the values in df2.

import pandas as pd
import datetime as dt

df1 = pd.DataFrame({'date': [dt.datetime(2020, 1, 2), dt.datetime(2020, 2, 2), dt.datetime(2020, 3, 2)],
                    'id': ['a', 'a', 'a']})

df2 = pd.DataFrame({'date': [dt.datetime(2020, 1, 1)],
                    'id': ['a'],
                    'value': ['1']})

pd.merge_asof(df1,
              df2,
              on='date',
              by='id',
              direction='backward',
              allow_exact_matches=True)

This is the output that I would like to see instead where only the first match is successful

Targeted Outcome

Source: Python Questions

LEAVE A COMMENT