pandas - apply a function on column, using a second dataframe as an input for the function?-CodePudding

Look for the fastest and most pandas centric way of doing the following:

support=

Values Confidence R/S
10      3          S
20      6          S
40      10         S
35      12         S

df = 

name    strike
xyz      12
dfg      6
ghf      40

Aim: Get the closest greater than 0 row from support to df.

Excpected output:

df = 

name    strike   support
xyz      12      [10, 3, S, 2]
dfg      6       [0, 0, S, 0]   # as there is no > 0 value when subtracting strike from support
ghf      40      [35, 12, S, 5]

Bonus: expand the columns into the relevant columns.

I can do this by looping through the strikes, wondering if there is a better/faster way to achieve what I am thinking of doing.

CodePudding user response：

Use merge_asof

pd.merge_asof(df.sort_values('strike'),       # must be sorted by key
              support.sort_values('Values'),  # must be sorted by key
              left_on='strike', 
              right_on='Values', 
              direction='backward',           # default, so `Values <= strike`
              allow_exact_matches=False       # so that `Values != strike`
             )

Output:

  name  strike  Values  Confidence  R/S
0  dfg       6     NaN         NaN  NaN
1  xyz      12    10.0         3.0    S
2  ghf      40    35.0        12.0    S