How to check if three column values of a dataframe are less than simultaneous values of another standard dataframe?

user_11484001 0
Viewed 82

I have a standard dataframe template df1:

a = [['100', '20', '20'], ['150', '60', '80'], ['200', '100', '100'],['500', '50', '100']]
df1 = pd.DataFrame(a, columns=['A', 'B', 'C'])

df1
Out[50]: 
     A    B    C
0  100   20   20
1  150   60   80
2  200  100  100
3  500   50  100

and my dataset df2

b = [['180.5', '60.3', '20.2'], ['40.2', '2.2', '33.9'], ['205', '100', '100'],['455', '20', '88'],['100', '60', '130'],['10', '10', '10'],['300.64', '66.4', '29.4']]
df2 = pd.DataFrame(b, columns=['A1', 'B1', 'C1'])

df2 
       A1    B1    C1
0   180.5  60.3  20.2
1    40.2   2.2  33.9
2     205   100   100
3     455    20    88
4     100    60   130
5      10    10    10
6  300.64  66.4  29.4

I want to iterate value of A1,B1,C1 of df2 against all simultaneous values of df1 and append only that set of values to my df2 where all 3 conditions are satisfied (A1<=A, B1<=B, C1<=C). If the conditions are satisfied for multiple rows in df1, then I will just need the first set of standard value.

This may be considered as checking if all 3 dimensions are within any available standard dimension template.

Expected Output for my dataset df2:

       A1    B1    C1      A      B      C
0   180.5  60.3  20.2  200.0  100.0  100.0
1    40.2   2.2  33.9  150.0   60.0   80.0
2     205   100   100    NaN    NaN    NaN
3     455    20    88  500.0   50.0  100.0
4     100    60   130    NaN    NaN    NaN
5      10    10    10  100.0   20.0   20.0
6  300.64  66.4  29.4    NaN    NaN    NaN

Could not find any suitable solution. I tried with lists but could not come any closer.

pandas numpy python
2 Answers

Numpy broadcasting

df1 = df1.astype(float)
df2 = df2.astype(float)

m = (df2.values[:, None] <= df1.values).all(-1)
df2 = df2.join(df1.iloc[m.argmax(1)].set_axis(df2.index)[m.any(1)])

Explanations

We can use broadcasting to create a boolean mask by comparing each row in df2 to every other row in df1, then reduce this boolean mask with all along axis=-1

>>> m

array([[False, False,  True, False],
       [False,  True,  True,  True],
       [False, False, False, False],
       [False, False, False,  True],
       [False, False, False, False],
       [ True,  True,  True,  True],
       [False, False, False, False]])

Now, use argmax along axis=1 on mask m to find the indices of first max value in each row where all the conditions A1<=A, B1<=B, C1<=C are satisfied

>>> m.argmax(1)

array([2, 1, 0, 3, 0, 0, 0])

Select the rows from df1 corresponding to the above indices with the help of iloc and conform the index of these selected rows with the index of df2

>>> df1.iloc[m.argmax(1)].set_axis(df2.index)

       A      B      C
0  200.0  100.0  100.0
1  150.0   60.0   80.0
2  100.0   20.0   20.0
3  500.0   50.0  100.0
4  100.0   20.0   20.0
5  100.0   20.0   20.0
6  100.0   20.0   20.0

Now filter the rows from the above selected rows such that they satisfies the given condition A1<=A, B1<=B, C1<=C and join the filtered rows to to df2

>>> df2

       A1     B1     C1      A      B      C
0  180.50   60.3   20.2  200.0  100.0  100.0
1   40.20    2.2   33.9  150.0   60.0   80.0
2  205.00  100.0  100.0    NaN    NaN    NaN
3  455.00   20.0   88.0  500.0   50.0  100.0
4  100.00   60.0  130.0    NaN    NaN    NaN
5   10.00   10.0   10.0  100.0   20.0   20.0
6  300.64   66.4   29.4    NaN    NaN    NaN
user_128331660

First use cross join by all values, then filter by conditions, remove duplicates by index value and add to df2 by DataFrame.join:

#input data ar strings, so convert to numbers
df1 = df1.astype(float)
df2 = df2.astype(float)

df = pd.merge(df2.reset_index(), df1, how='cross')

df = (df[(df.A1<=df.A) & (df.B1<=df.B) & (df.C1<=df.C)]
      .drop_duplicates('index')
      .set_index('index')[df1.columns])
print (df)
           A      B      C
index                     
0      200.0  100.0  100.0
1      150.0   60.0   80.0
3      500.0   50.0  100.0
5      100.0   20.0   20.0

df = df2.join(df)
print (df)
       A1     B1     C1      A      B      C
0  180.50   60.3   20.2  200.0  100.0  100.0
1   40.20    2.2   33.9  150.0   60.0   80.0
2  205.00  100.0  100.0    NaN    NaN    NaN
3  455.00   20.0   88.0  500.0   50.0  100.0
4  100.00   60.0  130.0    NaN    NaN    NaN
5   10.00   10.0   10.0  100.0   20.0   20.0
6  300.64   66.4   29.4    NaN    NaN    NaN
user_29010020
Related