PANDAS create column by iterating row by row checking values in 2nd dataframe until all values are true

user_14654691 0
Viewed 49
df1 = pd.DataFrame({
    'ItemNo' : ['001', '002', '003', '004', '005'], 
    'L'      : ['5', '65.0',  '445.0', '3200', '65000'], 
    'H'      : ['2', '15.5', '150.5', '1500', '54000'],
    'W'      : ['5', '85.0', '640.0', '1650', '45000'] 
})

df2 = pd.DataFrame({ 
    'Rank'  : ['1','2','3','4','5'],
    'Length': ['10', '100', '1000', '10000'],
    'Width' : ['10', '100', '1000', '10000'],
    'Height': ['10', '100', '1000', '10000'], 
    'Code'  : [ 'aa', 'bb', 'cc', 'dd', 'ee'] 
})

So here I have two example dataframes. The first dataframe shows unique item numbers with given dimensions. df2 shows maximum allowable dimensions for given rank and code. Meaning all elements (length, width, height) must not exceed maximum given dimensions. I would like to check the dimensions in df1 against df2 until all dimension criteria are True in order to retrieve it's 'rank' and 'code'. So, in essence, iterate down row by row of df2 until all the criteria is True.

Make a new df3 as follows:

ItemNo Rank Code
001    1     aa
002    2     bb
003    3     cc
004    4     dd
005    5     ee
pandas numpy python
2 Answers

Using a numpy

  • changed sample data so that it's not just incrementing results
  • get index of row in df2 that matches required logic
  • build df3 using index in step 2
df1 = pd.DataFrame({
    'ItemNo' : ['001', '002', '003', '004', '005'], 
    'L'      : ['5', '65.0',  '445.0', '5', '65000'], 
    'H'      : ['2', '15.5', '150.5', '5', '54000'],
    'W'      : ['5', '85.0', '640.0', '5', '45000'] 
})

df2 = pd.DataFrame({ 
    'Rank'  : ['1','2','3','4','5'],
    'Length': ['10', '100', '1000', '10000',100000],
    'Width' : ['10', '100', '1000', '10000',100000],
    'Height': ['10', '100', '1000', '10000',100000], 
    'Code'  : [ 'aa', 'bb', 'cc', 'dd', 'ee'] 
})

# fix up datatypes for comparisons
df1.loc[:,["L","H","W"]] = df1.loc[:,["L","H","W"]].astype(float)
df2.loc[:,["Length","Height","Width"]] = df2.loc[:,["Length","Height","Width"]].astype(float)

# row by row comparison, argmax to get first True
idx = [np.argmax((df1.loc[r,["L","H","W"]].values 
                  < df2.loc[:,["Length","Height","Width"]].values).all(axis=1)) 
       for r in df1.index]

# finally the result
pd.concat([df1.ItemNo, df2.loc[idx,["Rank","Code"]].reset_index(drop=True)],axis=1)
ItemNo Rank Code
0 001 1 aa
1 002 2 bb
2 003 3 cc
3 004 1 aa
4 005 5 ee
user_94414040

I think you can try:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({
    'ItemNo' : ['001', '002', '003', '004', '005', '006'], 
    'L'      : ['5', '65.0',  '445.0', '3200', '65000', '10'], 
    'H'      : ['2', '15.5', '150.5', '1500', '54000','1000'],
    'W'      : ['5', '85.0', '640.0', '1650', '45000', '10'] 
})

df2 = pd.DataFrame({ 
    'Rank'  : ['1','2','3','4','5'],
    'Length': ['10', '100', '1000', '10000', '100000'],
    'Width' : ['10', '100', '1000', '10000', '100000'],
    'Height': ['10', '100', '1000', '10000', '100000'], 
    'Code'  : [ 'aa', 'bb', 'cc', 'dd', 'ee'] 
})

df_sort = pd.DataFrame({'W': np.searchsorted(df2['Width'].astype(float), df1['W'].astype(float)),
                   'H': np.searchsorted(df2['Height'].astype(float), df1['H'].astype(float)),
                   'L': np.searchsorted(df2['Length'].astype(float), df1['L'].astype(float))})

df1['Rank'] = df_sort.max(axis=1).map(df2['Rank'])
df1['Code'] = df1['Rank'].map(df2.set_index('Rank')['Code'])

print(df1)

Output:

  ItemNo      L      H      W Rank Code
0    001      5      2      5    1   aa
1    002   65.0   15.5   85.0    2   bb
2    003  445.0  150.5  640.0    3   cc
3    004   3200   1500   1650    4   dd
4    005  65000  54000  45000    5   ee
5    006     10   1000     10    3   cc

The core to the code is the use of the np.searchsorted function. Which is used to find the index of the value of L in Length for example per the conditions listed in the documentations. So, I use np.searchsorted for each of the three dimension then, I take the largest value using max(axis=1) and assign the rank and code based on that largest value using map.

user_63615310
Related