BUG: Merge_asof fails for unknow reason

[igjayanth]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
from io import StringIO

alloc_txt = """User_Id  Date  Allocated_Points  alloc_flag_day
1      466 2025-02-08                560            True
1      469 2025-02-08                560            True"""

red_txt = """User_Id  Date  Deducted_Points  red_flag_day
1         466 2025-02-08              560          True
2         728 2025-02-02              750          True
3         728 2025-02-09              540          True
4         829 2025-04-03              800          True
5         869 2025-04-22             1400          True"""
# Read as if reading a CSV/TSV file
alloc_df = pd.read_csv(StringIO(alloc_txt), delim_whitespace=True)
red_df = pd.read_csv(StringIO(red_txt), delim_whitespace=True)



alloc_df['User_Id'] = alloc_df['User_Id'].astype(int)
red_df['User_Id'] = red_df['User_Id'].astype(int)

# Ensure date is datetime and remove any possible NaT
alloc_df = alloc_df[alloc_df['Date'].notnull()]
red_df = red_df[red_df['Date'].notnull()]
alloc_df['Date'] = pd.to_datetime(alloc_df['Date'])
red_df['Date'] = pd.to_datetime(red_df['Date'])
try:
    one_result = pd.merge_asof(
        alloc_df,
        red_df,
        by='User_Id',
        left_on='Date',
        right_on='Date',
        direction='forward',
        tolerance=pd.Timedelta(days=3),
        suffixes=('_alloc', '_red')
    )
    print("Success!", one_result)
except Exception as ex:
    print("Fails for first row alone:", ex)

Issue Description

I am trying to find the users who have redeemed some points within 3 days of allocation. This is a sample data.
Not sure why this fails.

Expected Behavior

Ideally it should return the users who have entries in red_df within 3 days of alloc_df

Installed Versions

INSTALLED VERSIONS

commit : 4665c10
python : 3.12.9
python-bits : 64
OS : Linux
OS-release : 5.15.167.4-microsoft-standard-WSL2
Version : #1 SMP Tue Nov 5 00:21:55 UTC 2024
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : C.UTF-8
LOCALE : C.UTF-8

pandas : 2.3.2
numpy : 2.2.5
pytz : 2025.2
dateutil : 2.9.0.post0
pip : 25.0
Cython : None
sphinx : None
IPython : 8.30.0
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : 3.1.6
lxml.etree : None
matplotlib : 3.10.3
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.5
pandas_gbq : None
psycopg2 : None
pymysql : None
pyarrow : None
pyreadstat : None
pytest : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : 2.0.40
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
xlsxwriter : None
zstandard : 0.23.0
tzdata : 2025.2
qtpy : 2.4.1
pyqt5 : None

[be991]

I’d like to work on this issue as my contribution. From my understanding, the problem happens because pd.merge_asof requires both keys (left_on and right_on) to be sorted by the "on" column (Date in this case). Since the sample data isn’t sorted by Date, the first row fails.

I plan to fix this by updating the docs/error message to make this requirement clearer (or by suggesting an internal check with a better error). Let me know if this approach sounds good!

[igjayanth]

@be991 but the data has to be sorted first using "by" column and then only "on" columns, right?

Btw, I did do the following first (didn't think it was relevant for the sample code)

alloc_df = alloc_df.sort_values(['User_Id', 'Date']).reset_index(drop=True)
red_df = red_df.sort_values(['User_Id', 'Date']).reset_index(drop=True)

I did try this on the actual data (which is pretty huge) and it failed.

[be991]

@igjayanth Thanks for clarifying that 🙏. You’re right sorting by both User_Id and Date should normally cover the merge_asof requirement. If it’s still failing on your larger dataset, that suggests there might be an edge case we’re not seeing in the small sample.

I’ll try to reproduce this with a bigger synthetic dataset to check if something else (like duplicates, gaps in dates, or how tolerance interacts with the by column) is causing the issue. If I can isolate the pattern that triggers the failure, I’ll share a minimal reproducible example here so we can confirm whether it’s a bug or just stricter input requirements than the docs explain.

[rhshadrach]

Thanks for the report.

but the data has to be sorted first using "by" column and then only "on" columns, right?

Where do you see this indicated? When just sorting by Date, I get a successful merge.

I do think this can be made clearer in the docs, reworking this as a docs issue for now.