Creating a derived field based on df value comparison in python pandas

user_13093674 0
Viewed 131

I have 2 dataframes - one is a data source dataframe and another is reference dataframe. I want to create an additional column in df1 based on the comparison of those 2 dataframes

df1 - data source

No | Name
213344 | Apple
242342 | Orange
234234 | Pineapple

df2 - reference table

RGE_FROM | RGE_TO | Value
2100 | 2190 | Sweet
2200 | 2322 | Bitter
2400 | 5000 | Neutral

final if first 4 character of df1.No fall between the range of df2.RGE_FROM to df2.RGE_TO, get df2.Value for the derived column df.DESC. else, blank

No | Name | DESC
213344 | Apple | Sweet
242342 | Orange | Natural
234234 | Pineapple |

Any help is appreciated! Thank you!

pandas calculated-columns dataframe python-3.x
1 Answers

We can create an IntervalIndex from the columns RGE_FROM and RGE_TO, then set this as an index of column Value to create a mapping series, then slice the first four characters in the column No and using Series.map substitute the values from the mapping series.

i =  pd.IntervalIndex.from_arrays(df2['RGE_FROM'], df2['RGE_TO'], closed='both')
df1['Value'] = df1['No'].astype(str).str[:4].astype(int).map(df2.set_index(i)['Value'])

       No       Name    Value
0  213344      Apple    Sweet
1  242342     Orange  Neutral
2  234234  Pineapple      NaN
user_128331660
Related