基于条件合并两个panda数据帧

Question

如果满足预先确定的条件，目标是按行组合两个df。具体地，如果列之间的差小于或等于threshold，则加入df的行。

给定两个df:df1和df2，下面的代码部分实现了目标。

import pandas as pd
df1 = pd.DataFrame ( {'time': [2, 3, 4, 24, 31]} )
df2 = pd.DataFrame (  {'time': [4.1, 24.7, 31.4, 5]} )
th = 0.9
all_comb=[]
for index, row in df1.iterrows ():
for index2, row2 in df2.iterrows ():
diff = abs ( row ['time'] - row2 ['time'] )
if diff <= th:
all_comb.append({'idx_1':index,'time_1':row ['time'], 'idx_2':index2,'time_2':row2 ['time']})
df_all = pd.DataFrame(all_comb)

输出

idx_1  time_1  idx_2  time_2
0      2       4      0     4.1
1      3      24      1    24.7
2      4      31      2    31.4

然而，上述方法忽略了某些信息，即来自df1的2和3的值，以及来自df2的5的值。

预期输出应该类似

idx_1  time_1  idx_2  time_2
0      2       NA    NA
1      3       NA    NA    
2       4      0     4.1
3      24      1    24.7
4      31      2    31.4
NA     NA      3     5

感谢任何比上述建议更紧凑、更高效的提示或方式。

Answer 1

您可以执行交叉合并，然后根据您的条件一次对所有行进行子集设置。然后我们concat，从两个DataFrames中添加回任何不满足条件的行。

import pandas as pd
df1 = df1.reset_index().add_suffix('_1')
df2 = df2.reset_index().add_suffix('_2')
m = df1.merge(df2, how='cross')
# Subset to all matches: |time_diff| <= thresh
th = 0.9
m = m[(m['time_1'] - m['time_2']).abs().le(th)]
# Add back rows with no matches
res = pd.concat([df1[~df1.index_1.isin(m.index_1)],
m,
df2[~df2.index_2.isin(m.index_2)]], ignore_index=True)

---

print(res)
index_1  time_1  index_2  time_2
0      0.0     2.0      NaN     NaN
1      1.0     3.0      NaN     NaN
2      2.0     4.0      0.0     4.1
3      3.0    24.0      1.0    24.7
4      4.0    31.0      2.0    31.4
5      NaN     NaN      3.0     5.0