两个 pandas 数据帧之间的最大匹配

Question

假设我们有两个数据范围。

原始_data

sequence_number	fixed_criteria	fuzzy_criteria
1	a	10.42
2	b	1.27
3	b	6.32
4	a	5.91

jumbled_data

sequence_number	field_criteria fuzzy_criteria	fuzzy_criteria
11	b	6.43
12	12 b	b 6.43
1.26 13	a	9.98
14 A 9.98 14	A	15 a 6.0.84
15	a	6.0.84 15

15.84 它们之间的对应关系为1-1。匹配最大化匹配的大小，并最大程度地减少fuzzy_criteria的差异。 将为

sequence_number_original	fuzzy_criteria_original	fixed_criteria	fuzzy_criteria_jumbled	sequence_number_jumbled	fuzz_diff
1	10.42	a	9.98	一个	IE
需要	匹配	​	​	​	​
​	​	​	​	​	​
​	​	​	​	​	​

​

​最大匹配考虑以下示例：

ointer_data

sequence_number	fixed_criteria	fuzzy_criteria
1	a	1
2 a 2	a	2 a 2

jumbled_data

sequence_number	fuzzy_criteria fuzzy_criteria	13
a	1.9 a	a
14	a	a 2.9

2.9 criteria_jumbled

2	fuzzy_criteria	fired_criteria	fuzzy_criteria	sequence_number_jumbled	fuzz_diff
2	1.9	a	1.9	13	0.1
1	1	a	1.9	13	0.9
2	2	a	2.9	14	0.9
1	1	a	2.9	14	1.9

then removing duplicates in sequence_number_original would provide the following

sequence_number_original	fuzzy_criteria_original	fixed_criteria	fuzzy_criteria_jumbled	sequence_number_jumbled	fuzz_diff
2	2	a	1.9	13	0.1
1	1	a	1.9	13	0.9

then in sequence_number_jumbled

sequence_number_original	fuzzy_criteria_original	fixed_criteria	fuzzy_criteria_jumbled	sequence_number_jumbled	fuzz_diff
2	2	a	1.9	13	0.1

同样，相反的方式也会做同样的方法。 sequence_number_jumbled

ia	fuzzy_criteria_jumbled	2	sequence_number_jumbled	第	fuzz_diff
2	但这	，	1.9	...	0.1
不是	最大	a	​	13	​

一个

的	​	​	​	​	​
​	​	​	​	​	​

​：

sequence_number_original	fuzzy_criteria_original	fixed_criteria	fuzzy_criteria_jumbled	sequence_number_jumbled	fuzz_diff
1 1	1	a	1.9	13	0.9
2 a 2.9 2	a 2.9 2	a	2.9	14	0.9

最大程度地匹配图表中的算法。实际上，我确实只是看到 a>与我的类似。

Answer 1

如果两个 fuzzy_criteria 列上都没有重复的值。您可以创建一个辅助数据框来确定两个 fuzzy_criteria 列之间最接近的值。

from itertools import product

df = pd.DataFrame(sorted(product(original_data['fuzzy_criteria'], jumbled_data['fuzzy_criteria']), key=lambda t: abs(t[0]-t[1])))
df = df.drop_duplicates(0, keep='first')
df = df.drop_duplicates(1, keep='first')

print(df)

       0     1
0   1.27  1.26
1   5.91  6.01
2   6.32  6.43
4  10.42  9.98

然后使用这个辅助数据框分别合并这两个数据框，最后根据辅助数据框列合并合并的数据框。

df_ = pd.merge(
    (pd.merge(original_data, df, left_on='fuzzy_criteria', right_on=0)),
    (pd.merge(df, jumbled_data, left_on=1, right_on='fuzzy_criteria')),
    on=[0,1],
    suffixes=('_original', '_jumbled')
).drop([0, 1], axis=1)
df_['fuzz_diff'] = (df_['fuzzy_criteria_original'] - df_['fuzzy_criteria_jumbled']).abs()

   sequence_number_original fixed_criteria_original  fuzzy_criteria_original  \
0                         1                       a                    10.42
1                         2                       b                     1.27
2                         3                       b                     6.32
3                         4                       a                     5.91

   sequence_number_jumbled fixed_criteria_jumbled  fuzzy_criteria_jumbled  \
0                       13                      a                    9.98
1                       12                      b                    1.26
2                       11                      b                    6.43
3                       15                      a                    6.01

   fuzz_diff
0       0.44
1       0.01
2       0.11
3       0.10

Answer 2

这很大程度上从@spghtcd的答案中复制到

想法是使用NetworkX执行最大匹配。

import pandas as pd
import networkx as nx

# Data input

original_data = pd.DataFrame({
    'sequence_number' : [1,2,3,4],
    'fixed_criteria' : ['a','b','b','a'],
    'fuzzy_criteria' : [10.42, 1.27, 6.32, 5.91]
})

jumbled_data = pd.DataFrame({
    'sequence_number' : [11,12,13,14,15],
    'fixed_criteria' : ['b','b','a','a','a'],
    'fuzzy_criteria' : [6.43, 1.26, 9.98, 15.84, 6.01]
})

# Merge along fixed criteria

joined_data = pd.merge(
    original_data,
    jumbled_data,
    how = 'inner',
    on = ['fixed_criteria'],
    suffixes=['_original','_jumbled']
)

# To use max weight, take the reciricol of the difference (if they are the non-
# unique values this will have to be changed)

joined_data['weight'] = (1/abs(
    joined_data['fuzzy_criteria_original'] -
    joined_data['fuzzy_criteria_jumbled']
))

# Form graph

matching_graph = nx.from_pandas_edgelist(
    joined_data,
    source = 'sequence_number_original',
    target = 'sequence_number_jumbled',
    edge_attr = 'weight'
)

# Find matching

mathing = nx.max_weight_matching(
    matching_graph,
    weight = 'weight'
)

# Convert results back into dataframe and format

results = pd.DataFrame(
    list(mathing),
    columns=['sequence_number_original', 'sequence_number_jumbled']
)

results = pd.merge(
    results,
    joined_data,
    how = 'inner',
    on = ['sequence_number_original', 'sequence_number_jumbled'],
)

results['fuzzy_difference'] = abs(
    results['fuzzy_criteria_original'] -
    results['fuzzy_criteria_jumbled']
)

print(results)