如何基于另一DataFrame多条件为Pandas DataFrame新增gridid列

现有两个Pandas DataFrame：data1和data2，数据如下：

data1：

place   lat        long
pl-1   56.130432  106.346887
pl-2   56.182371  106.331682
pl-3   56.182370  106.331685

data2：

gridid    minlat   minlong     maxlat     maxlong
31      56.130430  106.346885  56.130434  106.346889
32      56.182369  106.331680  56.182379  106.331685

需要为data1新增gridid列，匹配条件为：
data1['lat'] >= data2['minlat'] and data1['long'] >= data2['minlong'] and data1['lat'] <= data2['maxlat'] and data1['long'] <= data2['maxlong']

最终data1需变为：

place   lat        long        grid
pl-1   56.130432  106.346887    31
pl-2   56.182371  106.331682    32
pl-3   56.182370  106.331685    32

方法1：交叉合并+布尔索引筛选 #

先做笛卡尔积合并两个DataFrame，再用条件筛选匹配行，最后整合回原数据结构：

import pandas as pd

# 构造示例数据
data1 = pd.DataFrame({
    'place': ['pl-1', 'pl-2', 'pl-3'],
    'lat': [56.130432, 56.182371, 56.182370],
    'long': [106.346887, 106.331682, 106.331685]
})

data2 = pd.DataFrame({
    'gridid': [31, 32],
    'minlat': [56.130430, 56.182369],
    'minlong': [106.346885, 106.331680],
    'maxlat': [56.130434, 56.182379],
    'maxlong': [106.346889, 106.331685]
})

# 交叉合并生成笛卡尔积
merged = pd.merge(data1.assign(key=1), data2.assign(key=1), on='key').drop('key', axis=1)

# 应用多条件筛选
mask = (merged['lat'] >= merged['minlat']) & \
       (merged['long'] >= merged['minlong']) & \
       (merged['lat'] <= merged['maxlat']) & \
       (merged['long'] <= merged['maxlong'])

# 提取目标列并重命名
result = merged[mask][['place', 'lat', 'long', 'gridid']].rename(columns={'gridid': 'grid'})
print(result)

方法2：逐行匹配（apply） #

对data1每行遍历data2，找到符合条件的gridid：

def get_gridid(row):
    # 筛选当前行匹配的grid
    match = data2[
        (data2['minlat'] <= row['lat']) &
        (data2['maxlat'] >= row['lat']) &
        (data2['minlong'] <= row['long']) &
        (data2['maxlong'] >= row['long'])
    ]
    # 返回匹配的gridid，无匹配则返回None
    return match['gridid'].values[0] if not match.empty else None

data1['grid'] = data1.apply(get_gridid, axis=1)
print(data1)

方法3：矢量化广播匹配（适合大数据量） #

利用NumPy广播机制批量计算匹配关系，效率更高：

import numpy as np

# 转换为NumPy数组，方便广播计算
lat1 = data1['lat'].values[:, np.newaxis]
long1 = data1['long'].values[:, np.newaxis]
minlat2 = data2['minlat'].values
minlong2 = data2['minlong'].values
maxlat2 = data2['maxlat'].values
maxlong2 = data2['maxlong'].values

# 生成匹配矩阵：每行对应data1的一条数据，每列对应data2的一个grid
matches = (lat1 >= minlat2) & (lat1 <= maxlat2) & (long1 >= minlong2) & (long1 <= maxlong2)

# 提取每个data1行对应的gridid
data1['grid'] = data2['gridid'].values[np.argmax(matches, axis=1)]
print(data1)

内容的提问来源于stack exchange，提问作者Amritesh