I have two dataframes
The first one:
MONTH NAME VALUE
01/01/2021 A 10
01/01/2021 B 20
01/01/2021 C 35
01/01/2021 D 40
01/02/2021 E 50
01/02/2021 F 60
01/02/2021 G 75
01/02/2021 H 80
The second:
MONTH ATTRIBUTE FEATURE
01/01/2021 x 0
01/01/2021 y 15
01/01/2021 z 30
01/02/2021 x 50
01/02/2021 y 70
01/02/2021 z 80
I need to compare month, value and feature.
If the feature from attribute is less than the value in month, assign the attribute. The expected output would be:
MONTH NAME VALUE ATTRIBUTE
01/01/2021 A 10 x
01/01/2021 B 20 y
01/01/2021 C 35 z
01/01/2021 D 40 z
01/02/2021 E 50 x
01/02/2021 F 60 x
01/02/2021 G 75 y
01/02/2021 H 80 z
I think this is a very difficult problem. Could you help me??
Thank you very much.
>Solution :
You need a merge_asof:
pd.merge_asof(df1.sort_values('VALUE'), df2.sort_values('FEATURE'),
by='MONTH', left_on='VALUE', right_on='FEATURE',
#allow_exact_matches=False
)
NB if you want a strict inferior values, add the allow_exact_matches=False parameter.
output:
MONTH NAME VALUE ATTRIBUTE FEATURE
0 01/01/2021 A 10 x 0
1 01/01/2021 B 20 y 15
2 01/01/2021 C 35 z 30
3 01/01/2021 D 40 z 30
4 01/02/2021 E 50 x 50
5 01/02/2021 F 60 x 50
6 01/02/2021 G 75 y 70
7 01/02/2021 H 80 z 80