Python Pandas - Merge between condition

Question

I have 2 dataframes in python pandas

Dataframe 1

User_id  zipcode

1        12345

2        23456

3        34567

Dataframe 2

ZipCodeLowerBound ZipCodeUpperBound Region

10000             19999             1

20000             29999             2

30000             39999             3

How can I map in the Region to dataframe 1 with the condition if(df1.zipcode>=df2.ZipCodeLowerBound and df1.zipcode<=df2.ZipCodeUpperBound) using pandas merge

Answer 1

This gives a column per region and a mask of each zipcode belonging to that region or not:

df2 = df2.set_index('Region')
mask = df2.apply(lambda r: df1.zipcode.between(r['ZipCodeLowerBound'],
                                               r['ZipCodeUpperBound']),
                 axis=1).T
mask
Out[103]: 
Region      1      2      3
0        True  False  False
1       False   True  False
2       False  False   True

Then you can use that matrix against its own column names to apply it as a mask and find back the region:

mask.dot(mask.columns)
Out[110]: 
0    1
1    2
2    3
dtype: int64