Pandas 合并两个数据框，其中 df1.column 等于或在 df2.column1 和 df2.column2 之间。

Question

我似乎找不到这个例子。 我很难确定是否需要使用索引、布尔掩码或直接合并。 我已尝试了各种变体的.isin和.between，但都没有成功。

情景：

• Two dataFrames with no common index:

  df1 = pd.DataFrame({'printId': ['x','y', 'z', 'a'],'locCode': [0.9, 1.5, 4.0, 7.8]})
  df2 = pd.DataFrame({'assetId': ['1','1a', '2', '2a', '3', '4'], 'locStart': [0.9, 0.9, 1, 1, 4, 8], 'locEnd': [0.9, 0.9, 3, 3, 5, 13]})
  

df1:

df2:

需要这个：

df3 = pd.DataFrame({'printId': ['x','x', 'y', 'y', 'z', 'a', 'NaN'], 'locCode': ['0.9', '0.9', '1.5', '1.5', '4.0', '7.8', 'NaN'], 'assetID': ['1', '1a', '2', '2a', '3', 'NaN', '4'], 'locStart': ['0.9', '0.9', '1.0', '1.0', '4.0', 'NaN', '8.0'], 'locEnd':['0.9', '0.9', '3.0', '3.0', '4.0', 'NaN', '13.0']})

df3

职业技术人员如何解决这个问题？

编辑： 经过仔细检查，原来的答案不可行。

• 当df2中有重复的locStart/End记录，但具有唯一的assetID（第0行、1行和第2行、3行），df1将无法合并。

- ericOnline

---

我不太明白leftmiss和rightmiss的含义。左缺失是否意味着df1记录中的locCode未在df2的任何codestart和codeend之间？展示您期望的数据框将有助于我回答这个问题。 - Gilseung Ahn

@GilseungAhn，请参考帖子中显示的最后一张图片以获取预期结果。 "HitList" = 一个列表，其中df1的=或落在df2的列之间。例如：上面的第2行和第3行。 "LeftMiss" = df1中没有相应df2的列表。例如：上面的第1行和第4行。 "RightMiss" = df2中没有相应df1的列表。例如：上面的第0行和第5行。 - ericOnline

Answer 1

试试这个。

import pandas as pd
import numpy as np
df1 = pd.DataFrame({'printId': ['01','1A', '2B', '3C'],'locCode': [0.9, 1.5, 4.0, 7.8], 'a1': ['foo', 'foo', 'foo', 'foo']})
df2 = pd.DataFrame({'assetId': ['oo', 'aa', 'zz', 'xx'], 'locCodeStart': [0, 1, 4, 8], 'locCodeEnd': [0.4, 3, 5, 13]})

df1['assetId'] = np.nan
for ind, row in df1.iterrows():
    loc_code = row['locCode']
    temp = (df2['locCodeStart'] <= loc_code) & (loc_code < df2['locCodeEnd'])
    df2_index = temp[temp == True].index
    if len(df2_index) == 1:
        df1['assetId'].loc[ind] = df2['assetId'].loc[df2_index[0]]

pd.merge(df1, df2, how = 'outer')

- Gilseung Ahn

---

感谢您的专业知识。根据要求，我对temp = (loc_code >= df2['locCodeStart']) & (loc_code <= df2['locCodeEnd'])进行了一次更改。但是，我也发现了一个破坏性的变化：当存在重叠的locCodes时。例如：将以下df替换为：df1 = pd.DataFrame({'printId': ['01','1A', '2B', '3C'],'locCode': [0.4, 1.5, 4.0, 8], 'a1': ['foo', 'foo', 'foo', 'foo']}) df2 = pd.DataFrame({'assetId': ['22','oo', 'aa', 'zz', 'xx'], 'locCodeStart': [2, 0, 1, 4, 8], 'locCodeEnd': [4, 0.4, 3, 5, 13]})。在df2中添加一条记录，其中locEndCode与locStartCode匹配。 - SeaDude

@Gilseung Ahn：我该如何调整代码以适应locCodeStart和locCodeEnd具有相同值的情况？上述代码在这种情况下无法正常工作。 - ericOnline

哇！@GilseungAhn: 我将 temp = (df2['locCodeStart'] <= loc_code) & (loc_code < df2['locCodeEnd']) 更改为 temp = ( loc_code >= df2['locCodeStart'] ) & (loc_code <= df2['locCodeEnd'])，现在代码正好符合我的需求。再次感谢。 - ericOnline

不幸的是，当df2中多个记录的起始/结束locCodes相同时，这种方法无法正常工作，如编辑后的问题所示。 - SeaDude

Answer 2

即使在df2中存在locStart和locEnd的重复项（具有唯一的assetId），此方法也适用。

它还处理了df1中printId或locCode重复的情况。

df1 = pd.DataFrame({'printId': ['x','y', 'z', 'a'],'locCode': [0.9, 1.5, 4.0, 7.8]})
df2 = pd.DataFrame({'assetId': ['1','1a', '2', '2a', '3', '4'], 'locStart': [0.9, 0.9, 1, 1, 4, 8], 'locEnd': [0.9, 0.9, 3, 3, 5, 13]})


merge_id = []

for i,code in df1.locCode.iteritems():
    filled = True
    partial = []
    for j,row in df2.iterrows():
        if code>=row.locStart and code<=row.locEnd:
            filled = False
            partial.append(j)
    if filled:
        partial.append(-1)
    merge_id.append(partial)

df1['merge_id'] = merge_id

df = df1.explode('merge_id').merge(df2, right_index=True, left_on='merge_id', how='outer')
df = df.reset_index(drop=True).drop('merge_id', axis=1)


print(df)
  printId  locCode assetId  locStart  locEnd
0       x      0.9       1       0.9     0.9
1       x      0.9      1a       0.9     0.9
2       z      4.0       3       4.0     5.0
3       y      1.5       2       1.0     3.0
4       y      1.5      2a       1.0     3.0
5       a      7.8     NaN       NaN     NaN
6     NaN      NaN       4       8.0    13.0

- Marco Cerliani

Answer 3

你可以处理两种不同的情况。第一种情况是简单的合并，其中df1.locCode存在于df2.locStart中，第二种情况是通过使用df1和df2中locStart和locEnd的所有值创建箱子，然后在第一次合并已经处理过的情况之后将它们合并起来。

## handle the case where locCode of df1 is equal to locStart of df2

# get the rows in this case
mask_merge = df1['locCode'].isin(df2['locStart'].unique())

# handle them with a direct
m1 = df1[mask_merge].merge(df2, right_on='locStart', left_on='locCode', how='inner')

## handle the other cases

# get unique values from df2 both columns loc
l_unique = pd.concat([df2['locStart'], df2['locEnd']]).sort_values().unique()

# add cat columns with pd.cut in both dataframe with all unique values
df2['cat'] = pd.cut(df2['locEnd'], bins=[-np.inf]+l_unique.tolist() + [+np.inf],
                    labels=range(len(l_unique)+1))
df1['cat'] = pd.cut(df1['locCode'], bins=[-np.inf]+l_unique.tolist() + [+np.inf],
                    labels=range(len(l_unique)+1))

# mask to get which asser does not have same start and end
mask_startEnd = df2['locStart'].ne(df2['locEnd'])
# mask assert already merged above
mask_df2merged = ~df2['locStart'].isin(df1['locCode'])

# merge the rows needed
m2 = df1[~mask_merge].merge(df2[mask_startEnd&mask_df2merged], on='cat', how='outer')

#concat both and drop column cat
res = pd.concat([m1, m2], axis=0, ignore_index=True).drop('cat', axis=1)

print (res)
  printId  locCode assetId  locStart  locEnd
0       x      0.9       1       0.9     0.9
1       x      0.9      1a       0.9     0.9
2       z      4.0       3       4.0     5.0
3       y      1.5       2       1.0     3.0
4       y      1.5      2a       1.0     3.0
5       a      7.8     NaN       NaN     NaN
6     NaN      NaN       4       8.0    13.0

- Ben.T

---

还没有测试这个，因为我测试的第一个解决方案（Marco）已经可行了。 - ericOnline