Python Pandas Dataframe: Find Last Occurrence Of Value Less-than-or-equal-to Current Row

I have 2 pandas dataframes: df1: ksat muacres SAND SILT CLAY 0 5326 0 0 0 0.1 4346 0 0 0 0.4 4146 0 0 0 0.8

Solution 1:

One way is to merge twice. First with just the percentile column so you can backwards fill:

In [11]: merged = df1[['ksat']].merge(df2[['ksat', 'PERCENTILE']], how='outer', sort=True)

In [12]: merged
Out[12]:
       ksat  PERCENTILE
00.000000         NaN
10.100000         NaN
20.370684130.400000         NaN
40.558053250.794836360.800000         NaN
70.962329481.200000         NaN
91.2023685

In [13]: merged.bfill()
Out[13]:
       ksat  PERCENTILE
00.000000110.100000120.370684130.400000240.558053250.794836360.800000470.962329481.200000591.2023685

and then merge with this result:

In [14]:df.merge(merged.bfill())Out[14]:ksatmuacresSANDSILTCLAYPERCENTILE00.05326     000110.14346     000120.44146     000230.83476     000441.22006     0005

Solution 2:

you can try numpy.searchsorted

df1['st'] = np.searchsorted(df2.ksat, df1.ksat, side='left') + 1

if the PERCENTILE values are not ordinal then there is an extra step:

idx = np.searchsorted(df2.ksat, df1.ksat, side='left')
df1['st'] = df2.PERCENTILE[idx].values