Python Pandas-在条件之间合并 [英] Python Pandas - Merge between condition

问题描述

我在python熊猫中有2个数据框

I have 2 dataframes in python pandas

数据框1

User_id  zipcode

1        12345

2        23456

3        34567

数据框2

ZipCodeLowerBound ZipCodeUpperBound Region

10000             19999             1

20000             29999             2

30000             39999             3

如何使用熊猫合并在条件 if(df1.zipcode> = df2.ZipCodeLowerBound和df1.zipcode< = df2.ZipCodeUpperBound)的情况下将区域映射到数据框1

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

推荐答案

这将给出每个区域的列以及是否属于该区域的每个邮政编码的掩码:

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

这篇关于Python Pandas-在条件之间合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！