[Python] Why pandas.merge_asof working with error in my case?

I'm trying to merge 2 tables with pandas.merge_asof.

First table administrators_system_with_schemes_sort:

salon_id​

staff_id​

date​

872646​

2715596​

2024-10-02 00:00:00​

872646​

2715596​

2024-10-03 00:00:00​

872646​

2715596​

2024-10-06 00:00:00​

872646​

2715596​

2024-10-07 00:00:00​

872646​

2715596​

2024-10-10 00:00:00​

872646​

2715596​

2024-10-11 00:00:00​

872646​

2715596​

2024-10-14 00:00:00​

872646​

2715596​

2024-10-15 00:00:00​

Second table, bonus_and_penalty_for_staff_id_administrators_sort:

salon_id​

staff_id​

date​

bonus​

penalty
872646​

2715596​

2024-10-12 00:00:00​

4070​

0​

My code:

astype_dict = {
'salon_id': 'int64', 'staff_id': 'int64'
, 'date': 'datetime64[ns]'
}

administrators_system_with_schemes['date'] = [pd.to_datetime(date).date() for date in administrators_system_with_schemes['date']]
bonus_and_penalty_for_staff_id_administrators['date'] = [pd.to_datetime(date).date() for date in bonus_and_penalty_for_staff_id_administrators['date']]

administrators_system_with_schemes_sort = (
administrators_system_with_schemes.copy()
.astype(astype_dict)
.sort_values(by='date')
)

bonus_and_penalty_for_staff_id_administrators_sort = (
bonus_and_penalty_for_staff_id_administrators.copy()
.astype(astype_dict)
.sort_values(by='date')
)

administrators_system_with_schemes_with_additional_bonus_penalty = (
pd.merge_asof(
left = administrators_system_with_schemes_sort
, right = bonus_and_penalty_for_staff_id_administrators_sort
, on = ['date']
, by = ['salon_id', 'staff_id']
, suffixes=['', '_y']
, direction='nearest'
))


Result: | salon_id | staff_id | date | bonus | penalty | |-----------:|-----------:|:--------------------|--------:|----------:| | 872646 | 2715596 | 2024-10-02 00:00:00 | 0 | 0 | | 872646 | 2715596 | 2024-10-03 00:00:00 | 0 | 0 | | 872646 | 2715596 | 2024-10-06 00:00:00 | 0 | 0 | | 872646 | 2715596 | 2024-10-07 00:00:00 | 0 | 0 | | 872646 | 2715596 | 2024-10-10 00:00:00 | 0 | 0 | | 872646 | 2715596 | 2024-10-11 00:00:00 | 0 | 0 | | 872646 | 2715596 | 2024-10-14 00:00:00 | 0 | 0 | | 872646 | 2715596 | 2024-10-15 00:00:00 | 0 | 0 |

Result is wrong, cause I got suitable value in tables. I've already tries many way to change data types, but still have this mistake.

Any ideas, how to fix this problem?

Thanks.

Pandas ver. 2.1.4 (same error on ver.2.2.3). Python ver. 3.11.7

Continue reading...