How to compare two dataframes and assign a conditional value-CodePudding

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.

CodePudding user response：

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