[FIXED] How to merge data to data within bands/bins?

Issue

I am trying to join/merge df1['binfig'] to the bin/band in df2

df1 = pd.DataFrame({"cust_id": ['ooo'],
                    "amount": [1000],
                    "group":[10],
                    "binfig":[600]})

df2 = pd.DataFrame({"ID": ['x1','x2'],
                     "bin":["600 to 650","651 to 670"]})

Solution

You can generate new low/high columns from df2's bin, then use them for a merge_asof:

df2[['low', 'high']] = df2['bin'].str.split(r'\s*to\s*', expand=True).astype(int)

out = (pd.merge_asof(df1, df2, left_on='binfig', right_on='low')
         .loc[lambda d: d['binfig'].le(d['high'])] # optional
         .drop(columns=['low', 'high'])
      )

output:

  cust_id  amount  group  binfig  ID         bin
0     ooo    1000     10     600  x1  600 to 650

Answered By - mozway