def test_overlap_join_using_tokenizer_with_return_set_false(self):
A = pd.DataFrame([{'id':1, 'attr':'hello'}])
B = pd.DataFrame([{'id':1, 'attr':'he ll'}])
qg2_tok = QgramTokenizer(2)
assert_equal(qg2_tok.get_return_set(), False)
c = overlap_join(A, B, 'id', 'id', 'attr', 'attr', qg2_tok, 1)
assert_equal(len(c), 1)
assert_equal(qg2_tok.get_return_set(), False)
def test_overlap_join_using_tokenizer_with_return_set_false(self):
A = pd.DataFrame([{'id': 1, 'attr': 'hello'}])
B = pd.DataFrame([{'id': 1, 'attr': 'he ll'}])
qg2_tok = QgramTokenizer(2)
assert_equal(qg2_tok.get_return_set(), False)
c = overlap_join(A, B, 'id', 'id', 'attr', 'attr', qg2_tok, 1)
assert_equal(len(c), 1)
assert_equal(qg2_tok.get_return_set(), False)
def test_apply_matcher_with_join_attr_of_type_int(self):
tok = QgramTokenizer(qval=2, return_set=True)
sim_func = get_sim_function('JACCARD')
threshold = 0.3
comp_op = '>='
l_join_attr = 'A.zipcode'
r_join_attr = 'B.zipcode'
# apply sim function to the entire cartesian product to obtain
# the expected set of pairs satisfying the threshold.
cartprod = self.cartprod
cartprod['sim_score'] = cartprod.apply(
lambda row: sim_func(tok.tokenize(str(row[l_join_attr])),
tok.tokenize(str(row[r_join_attr]))),
axis=1)
comp_fn = COMP_OP_MAP[comp_op]
# compute expected output pairs
expected_pairs = set()
for idx, row in cartprod.iterrows():
if comp_fn(float(row['sim_score']), threshold):
expected_pairs.add(','.join(
(str(row[self.l_key_attr]), str(row[self.r_key_attr]))))
# use overlap filter to obtain a candset.
overlap_filter = OverlapFilter(tok, 1, comp_op)
candset = overlap_filter.filter_tables(self.ltable, self.rtable,
self.l_key_attr,
self.r_key_attr, l_join_attr,
r_join_attr)
# apply a jaccard matcher to the candset
output_candset = apply_matcher(
candset, DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr, self.ltable, self.rtable,
self.l_key_attr, self.r_key_attr, l_join_attr, r_join_attr, tok,
sim_func, threshold)
expected_output_attrs = [
'_id', DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr, '_sim_score'
]
# verify whether the output table has the necessary attributes.
assert_list_equal(list(output_candset.columns.values),
expected_output_attrs)
actual_pairs = set()
for idx, row in output_candset.iterrows():
actual_pairs.add(','.join(
(str(row[DEFAULT_L_OUT_PREFIX + self.l_key_attr]),
str(row[DEFAULT_R_OUT_PREFIX + self.r_key_attr]))))
# verify whether the actual pairs and the expected pairs match.
assert_equal(len(expected_pairs), len(actual_pairs))
common_pairs = actual_pairs.intersection(expected_pairs)
assert_equal(len(common_pairs), len(expected_pairs))
def test_apply_matcher(self):
tok = QgramTokenizer(qval=2, return_set=True)
sim_func = get_sim_function('JACCARD')
threshold = 0.3
comp_op = '>='
# apply sim function to the entire cartesian product to obtain
# the expected set of pairs satisfying the threshold.
cartprod = self.cartprod
cartprod['sim_score'] = cartprod.apply(lambda row: sim_func(
tok.tokenize(str(row[self.l_join_attr])),
tok.tokenize(str(row[self.r_join_attr]))),
axis=1)
comp_fn = COMP_OP_MAP[comp_op]
# compute expected output pairs
expected_pairs = set()
for idx, row in cartprod.iterrows():
if comp_fn(float(row['sim_score']), threshold):
expected_pairs.add(','.join((str(row[self.l_key_attr]),
str(row[self.r_key_attr]))))
# use overlap filter to obtain a candset.
overlap_filter = OverlapFilter(tok, 1, comp_op)
candset = overlap_filter.filter_tables(self.ltable, self.rtable,
self.l_key_attr, self.r_key_attr,
self.l_join_attr, self.r_join_attr)
# apply a jaccard matcher to the candset
output_candset = apply_matcher(candset,
DEFAULT_L_OUT_PREFIX+self.l_key_attr, DEFAULT_R_OUT_PREFIX+self.r_key_attr,
self.ltable, self.rtable, self.l_key_attr, self.r_key_attr,
self.l_join_attr, self.r_join_attr, tok, sim_func, threshold,
comp_op, False,
[self.l_join_attr], [self.r_join_attr], out_sim_score=True)
expected_output_attrs=['_id',
DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr,
DEFAULT_L_OUT_PREFIX + self.l_join_attr,
DEFAULT_R_OUT_PREFIX + self.r_join_attr,
'_sim_score']
# verify whether the output table has the necessary attributes.
assert_list_equal(list(output_candset.columns.values),
expected_output_attrs)
actual_pairs = set()
for idx, row in output_candset.iterrows():
actual_pairs.add(','.join((str(row[DEFAULT_L_OUT_PREFIX + self.l_key_attr]),
str(row[DEFAULT_R_OUT_PREFIX + self.r_key_attr]))))
# verify whether the actual pairs and the expected pairs match.
assert_equal(len(expected_pairs), len(actual_pairs))
common_pairs = actual_pairs.intersection(expected_pairs)
assert_equal(len(common_pairs), len(expected_pairs))
def get_features(sim_measures=None, tokenizers=None):
features = []
ws_tok = WhitespaceTokenizer(return_set=True)
if sim_measures is None:
sim_measures = [
'JACCARD',
'COSINE',
'DICE',
# 'LEFT_LENGTH', 'RIGHT_LENGTH', 'LENGTH_SUM', 'LENGTH_DIFF']
'OVERLAP_COEFFICIENT',
'EDIT_DISTANCE',
'LEFT_LENGTH',
'RIGHT_LENGTH',
'LENGTH_SUM',
'LENGTH_DIFF'
]
if tokenizers is None:
tokenizers = {
'alph': AlphabeticTokenizer(return_set=True),
'alph_num': AlphanumericTokenizer(return_set=True),
'num': NumericTokenizer(return_set=True),
'ws': WhitespaceTokenizer(return_set=True),
'qg2': QgramTokenizer(qval=2, return_set=True),
'qg3': QgramTokenizer(qval=3, return_set=True)
}
for sim_measure_type in sim_measures:
if sim_measure_type in [
'EDIT_DISTANCE', 'LEFT_LENGTH', 'RIGHT_LENGTH', 'LENGTH_SUM',
'LENGTH_DIFF'
]:
features.append(
(sim_measure_type.lower(), 'none', sim_measure_type, None,
get_sim_function(sim_measure_type)))
continue
for tok_name in tokenizers.keys():
# if sim_measure_type == 'COSINE' and tok_name == 'qg3':
# continue
features.append((sim_measure_type.lower() + '_' + tok_name,
tok_name, sim_measure_type, tokenizers[tok_name],
get_sim_function(sim_measure_type)))
feature_table_header = [
'feature_name', 'tokenizer_type', 'sim_measure_type', 'tokenizer',
'sim_function'
]
feature_table = pd.DataFrame(features, columns=feature_table_header)
feature_table = feature_table.set_index('feature_name')
return feature_table
def edit_distance_join(ltable,
rtable,
l_key_attr,
r_key_attr,
l_join_attr,
r_join_attr,
threshold,
comp_op='<=',
allow_missing=False,
l_out_attrs=None,
r_out_attrs=None,
l_out_prefix='l_',
r_out_prefix='r_',
out_sim_score=True,
n_jobs=1,
show_progress=True,
tokenizer=QgramTokenizer(qval=2)):
from py_stringsimjoin import __use_cython__
if __use_cython__:
from py_stringsimjoin.join.edit_distance_join_cy import edit_distance_join_cy
return edit_distance_join_cy(ltable, rtable, l_key_attr, r_key_attr,
l_join_attr, r_join_attr, threshold,
comp_op, allow_missing, l_out_attrs,
r_out_attrs, l_out_prefix, r_out_prefix,
out_sim_score, n_jobs, show_progress,
tokenizer)
else:
from py_stringsimjoin.join.edit_distance_join_py import edit_distance_join_py
return edit_distance_join_py(ltable, rtable, l_key_attr, r_key_attr,
l_join_attr, r_join_attr, threshold,
comp_op, allow_missing, l_out_attrs,
r_out_attrs, l_out_prefix, r_out_prefix,
out_sim_score, n_jobs, show_progress,
tokenizer)
def test_candset_with_join_attr_of_type_int(self):
A = pd.DataFrame([{'l_id': 1, 'l_attr':1990},
{'l_id': 2, 'l_attr':2000},
{'l_id': 3, 'l_attr':0},
{'l_id': 4, 'l_attr':-1},
{'l_id': 5, 'l_attr':1986}])
B = pd.DataFrame([{'r_id': 1, 'r_attr':2001},
{'r_id': 2, 'r_attr':1992},
{'r_id': 3, 'r_attr':1886},
{'r_id': 4, 'r_attr':2007},
{'r_id': 5, 'r_attr':2012}])
dataframe_column_to_str(A, 'l_attr', inplace=True)
dataframe_column_to_str(B, 'r_attr', inplace=True)
A['tmp_join_key'] = 1
B['tmp_join_key'] = 1
C = pd.merge(A[['l_id', 'tmp_join_key']],
B[['r_id', 'tmp_join_key']],
on='tmp_join_key').drop('tmp_join_key', 1)
qg2_tok = QgramTokenizer(2, return_set=True)
expected_pairs = set(['1,2', '1,3', '2,1', '2,4', '2,5',
'4,1', '5,2', '5,3'])
self.test_filter_candset(qg2_tok, 1, '>=', False,
(C, 'l_id', 'r_id',
A, B, 'l_id', 'r_id',
'l_attr', 'r_attr'),
expected_pairs)
def apply_filterable_rule(self, rule_name, l_df, r_df, l_key, r_key,
l_output_attrs, r_output_attrs, l_output_prefix,
r_output_prefix, verbose, show_progress,
n_chunks):
candset = None
conjunct_list = self.rule_str[rule_name]
for conjunct in conjunct_list:
is_auto_gen, sim_fn, l_attr, r_attr, l_tok, r_tok, op, th = parse_conjunct(
conjunct, self.rule_ft[rule_name])
if l_tok == 'dlm_dc0':
tokenizer = WhitespaceTokenizer(return_set=True)
elif l_tok == 'qgm_3':
tokenizer = QgramTokenizer(qval=3, return_set=True)
if sim_fn == 'jaccard':
join_fn = ssj.jaccard_join
elif sim_fn == 'cosine':
join_fn = ssj.cosine_join
elif sim_fn == 'dice':
join_fn = ssj.dice_join
elif sim_fn == 'overlap_coeff':
join_fn = ssj.overlap_coefficient_join
elif sim_fn == 'lev_dist':
join_fn = ssj.edit_distance_join
if join_fn == ssj.edit_distance_join:
comp_op = '<='
if op == '>=':
comp_op = '<'
else:
comp_op = '>='
if op == '<=':
comp_op = '>'
ssj.dataframe_column_to_str(l_df, l_attr, inplace=True)
ssj.dataframe_column_to_str(r_df, r_attr, inplace=True)
if join_fn == ssj.edit_distance_join:
c_df = join_fn(l_df, r_df, l_key, r_key, l_attr, r_attr,
float(th), comp_op, True, l_output_attrs,
r_output_attrs, l_output_prefix,
r_output_prefix, False, n_chunks, show_progress)
else:
c_df = join_fn(l_df, r_df,
l_key, r_key, l_attr, r_attr, tokenizer,
float(th), comp_op, True, True, l_output_attrs,
r_output_attrs, l_output_prefix,
r_output_prefix, False, n_chunks, show_progress)
if candset is not None:
# union the candset of this conjunct with the existing candset
candset = pd.concat([candset, c_df]).drop_duplicates(
[l_output_prefix + l_key,
r_output_prefix + r_key]).reset_index(drop=True)
else:
# candset from the first conjunct of the rule
candset = c_df
return candset
def test_set_padding(self):
tok = QgramTokenizer()
self.assertEqual(tok.get_padding(), True)
self.assertEqual(
tok.tokenize('database'),
['#d', 'da', 'at', 'ta', 'ab', 'ba', 'as', 'se', 'e$'])
tok.set_padding(False)
self.assertEqual(tok.get_padding(), False)
self.assertEqual(tok.tokenize('database'),
['da', 'at', 'ta', 'ab', 'ba', 'as', 'se'])
def test_invalid_candset(self):
tok = QgramTokenizer(qval=2, return_set=True)
sim_func = get_sim_function('JACCARD')
threshold = 0.3
apply_matcher([], DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr, self.ltable,
self.rtable, self.l_key_attr, self.r_key_attr,
self.l_join_attr, self.r_join_attr, tok, sim_func,
threshold)
def test_invalid_rtable(self):
tok = QgramTokenizer(qval=2, return_set=True)
sim_func = get_sim_function('JACCARD')
threshold = 0.3
apply_matcher(pd.DataFrame([], columns=['_id', 'l_A.ID', 'r_B.ID']),
DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr, self.ltable, [],
self.l_key_attr, self.r_key_attr, self.l_join_attr,
self.r_join_attr, tok, sim_func, threshold)
def test_set_suffix_pad(self):
tok = QgramTokenizer()
self.assertEqual(tok.get_suffix_pad(), '$')
self.assertEqual(
tok.tokenize('database'),
['#d', 'da', 'at', 'ta', 'ab', 'ba', 'as', 'se', 'e$'])
tok.set_suffix_pad('!')
self.assertEqual(tok.get_suffix_pad(), '!')
self.assertEqual(
tok.tokenize('database'),
['#d', 'da', 'at', 'ta', 'ab', 'ba', 'as', 'se', 'e!'])
def __init__(self, vals):
self.vals = sorted(vals, key=lambda x: x.lower(), reverse=True)
self.val_map = vals if isinstance(vals, dict) else None
# whether to show debugging info or not
self.show = False
jarowinkler_sim = JaroWinkler()
levenshtein_sim = Levenshtein()
qgtok = QgramTokenizer(qval=3, padding=True)
jaccard_sim = Jaccard()
Jaccard3Gram = lambda x, y: jaccard_sim.get_sim_score(
qgtok.tokenize(x), qgtok.tokenize(y))
self.str_sims = {
'Jaro-Winkler': JaroWinkler().get_sim_score,
'Levenshtein': Levenshtein().get_sim_score,
'3gram Jaccard': Jaccard3Gram
}
self.linkages = ['single', 'average', 'complete']
def test_empty_candset(self):
tok = QgramTokenizer(qval=2, return_set=True)
sim_func = get_sim_function('JACCARD')
threshold = 0.3
empty_candset = pd.DataFrame(columns=[
DEFAULT_L_OUT_PREFIX + self.l_key_attr, DEFAULT_R_OUT_PREFIX +
self.r_key_attr
])
apply_matcher(empty_candset, DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr, self.ltable,
self.rtable, self.l_key_attr, self.r_key_attr,
self.l_join_attr, self.r_join_attr, tok, sim_func,
threshold)
def test_candset_with_numeric_r_filter_attr(self):
A = pd.DataFrame([{'l_id': 1, 'l_attr': '1990'}])
B = pd.DataFrame([{'r_id': 1, 'r_attr': 2001}])
A['tmp_join_key'] = 1
B['tmp_join_key'] = 1
C = pd.merge(A[['l_id', 'tmp_join_key']],
B[['r_id', 'tmp_join_key']],
on='tmp_join_key').drop('tmp_join_key', 1)
qg2_tok = QgramTokenizer(2, return_set=True)
overlap_filter = OverlapFilter(qg2_tok)
overlap_filter.filter_candset(C, 'l_id', 'r_id', A, B, 'l_id', 'r_id',
'l_attr', 'r_attr')
def test_jac_qg2_with_filter_attr_of_type_int(self):
A = pd.DataFrame([{
'l_id': 1,
'l_attr': 1990
}, {
'l_id': 2,
'l_attr': 2000
}, {
'l_id': 3,
'l_attr': 0
}, {
'l_id': 4,
'l_attr': -1
}, {
'l_id': 5,
'l_attr': 1986
}])
B = pd.DataFrame([{
'r_id': 1,
'r_attr': 2001
}, {
'r_id': 2,
'r_attr': 1992
}, {
'r_id': 3,
'r_attr': 1886
}, {
'r_id': 4,
'r_attr': 2007
}, {
'r_id': 5,
'r_attr': 2012
}])
dataframe_column_to_str(A, 'l_attr', inplace=True)
dataframe_column_to_str(B, 'r_attr', inplace=True)
qg2_tok = QgramTokenizer(2, return_set=True)
expected_pairs = set([
'1,1', '1,2', '1,3', '1,4', '1,5', '2,1', '2,2', '2,3', '2,4',
'2,5', '5,1', '5,2', '5,3', '5,4', '5,5'
])
self.test_filter_tables(qg2_tok, 'JACCARD', 0.8, False, False,
(A, B, 'l_id', 'r_id', 'l_attr', 'r_attr'),
expected_pairs)
def test_edit_distance_qg2_2(self):
A = pd.DataFrame([{
'l_id': 1,
'l_attr': '19990'
}, {
'l_id': 2,
'l_attr': '200'
}, {
'l_id': 3,
'l_attr': '0'
}, {
'l_id': 4,
'l_attr': ''
}, {
'l_id': 5,
'l_attr': np.NaN
}])
B = pd.DataFrame([{
'r_id': 1,
'r_attr': '200155'
}, {
'r_id': 2,
'r_attr': '190'
}, {
'r_id': 3,
'r_attr': '2010'
}, {
'r_id': 4,
'r_attr': ''
}, {
'r_id': 5,
'r_attr': np.NaN
}, {
'r_id': 6,
'r_attr': '18950'
}])
qg2_tok = QgramTokenizer(2)
expected_pairs = set(
['1,2', '1,6', '2,1', '2,2', '2,3', '3,2', '3,3', '4,4'])
self.test_filter_tables(qg2_tok, 'EDIT_DISTANCE', 2, False, False,
(A, B, 'l_id', 'r_id', 'l_attr', 'r_attr'),
expected_pairs)
def test_jac_qg2_with_filter_attr_of_type_int(self):
A = pd.DataFrame([{'l_id': 1, 'l_attr':1990},
{'l_id': 2, 'l_attr':2000},
{'l_id': 3, 'l_attr':0},
{'l_id': 4, 'l_attr':-1},
{'l_id': 5, 'l_attr':1986}])
B = pd.DataFrame([{'r_id': 1, 'r_attr':2001},
{'r_id': 2, 'r_attr':1992},
{'r_id': 3, 'r_attr':1886},
{'r_id': 4, 'r_attr':2007},
{'r_id': 5, 'r_attr':2012}])
dataframe_column_to_str(A, 'l_attr', inplace=True)
dataframe_column_to_str(B, 'r_attr', inplace=True)
qg2_tok = QgramTokenizer(2, return_set=True)
self.test_filter_tables(qg2_tok, 'JACCARD', 0.3, False, False,
(A, B,
'l_id', 'r_id', 'l_attr', 'r_attr'))
def block_tables(self, ltable, rtable, l_overlap_attr, r_overlap_attr,
rem_stop_words=False, q_val=None, word_level=True, overlap_size=1,
l_output_attrs=None, r_output_attrs=None,
l_output_prefix='ltable_', r_output_prefix='rtable_',
allow_missing=False, verbose=False, show_progress=True,
n_ltable_chunks=1, n_rtable_chunks=1):
"""
WARNING THIS COMMAND IS EXPERIMENTAL AND NOT TESTED. USE AT YOUR OWN RISK.
Blocks two tables based on the overlap of token sets of attribute
values. Finds tuple pairs from left and right tables such that the overlap
between (a) the set of tokens obtained by tokenizing the value of
attribute l_overlap_attr of a tuple from the left table, and (b) the
set of tokens obtained by tokenizing the value of attribute
r_overlap_attr of a tuple from the right table, is above a certain
threshold.
Args:
ltable (DataFrame): The left input table.
rtable (DataFrame): The right input table.
l_overlap_attr (string): The overlap attribute in left table.
r_overlap_attr (string): The overlap attribute in right table.
rem_stop_words (boolean): A flag to indicate whether stop words
(e.g., a, an, the) should be removed from the token sets of the
overlap attribute values (defaults to False).
q_val (int): The value of q to use if the overlap attributes
values are to be tokenized as qgrams (defaults to None).
word_level (boolean): A flag to indicate whether the overlap
attributes should be tokenized as words (i.e, using whitespace
as delimiter) (defaults to True).
overlap_size (int): The minimum number of tokens that must
overlap (defaults to 1).
l_output_attrs (list): A list of attribute names from the left
table to be included in the output candidate set (defaults
to None).
r_output_attrs (list): A list of attribute names from the right
table to be included in the output candidate set (defaults
to None).
l_output_prefix (string): The prefix to be used for the attribute names
coming from the left table in the output
candidate set (defaults to 'ltable\_').
r_output_prefix (string): The prefix to be used for the attribute names
coming from the right table in the output
candidate set (defaults to 'rtable\_').
allow_missing (boolean): A flag to indicate whether tuple pairs
with missing value in at least one of the
blocking attributes should be included in
the output candidate set (defaults to
False). If this flag is set to True, a
tuple in ltable with missing value in the
blocking attribute will be matched with
every tuple in rtable and vice versa.
verbose (boolean): A flag to indicate whether the debug
information should be logged (defaults to False).
show_progress (boolean): A flag to indicate whether progress should
be displayed to the user (defaults to True).
n_ltable_chunks (int): The number of partitions to split the left table (
defaults to 1). If it is set to -1, then the number of
partitions is set to the number of cores in the
machine.
n_rtable_chunks (int): The number of partitions to split the right table (
defaults to 1). If it is set to -1, then the number of
partitions is set to the number of cores in the
machine.
Returns:
A candidate set of tuple pairs that survived blocking (DataFrame).
Raises:
AssertionError: If `ltable` is not of type pandas
DataFrame.
AssertionError: If `rtable` is not of type pandas
DataFrame.
AssertionError: If `l_overlap_attr` is not of type string.
AssertionError: If `r_overlap_attr` is not of type string.
AssertionError: If `l_output_attrs` is not of type of
list.
AssertionError: If `r_output_attrs` is not of type of
list.
AssertionError: If the values in `l_output_attrs` is not of type
string.
AssertionError: If the values in `r_output_attrs` is not of type
string.
AssertionError: If `l_output_prefix` is not of type
string.
AssertionError: If `r_output_prefix` is not of type
string.
AssertionError: If `q_val` is not of type int.
AssertionError: If `word_level` is not of type boolean.
AssertionError: If `overlap_size` is not of type int.
AssertionError: If `verbose` is not of type
boolean.
AssertionError: If `allow_missing` is not of type boolean.
AssertionError: If `show_progress` is not of type
boolean.
AssertionError: If `n_ltable_chunks` is not of type
int.
AssertionError: If `n_rtable_chunks` is not of type
int.
AssertionError: If `l_overlap_attr` is not in the ltable
columns.
AssertionError: If `r_block_attr` is not in the rtable columns.
AssertionError: If `l_output_attrs` are not in the ltable.
AssertionError: If `r_output_attrs` are not in the rtable.
SyntaxError: If `q_val` is set to a valid value and
`word_level` is set to True.
SyntaxError: If `q_val` is set to None and
`word_level` is set to False.
Examples:
>>> from py_entitymatching.dask.dask_overlap_blocker import DaskOverlapBlocker
>>> A = em.read_csv_metadata('path_to_csv_dir/table_A.csv', key='ID')
>>> B = em.read_csv_metadata('path_to_csv_dir/table_B.csv', key='ID')
>>> ob = DaskOverlapBlocker()
# Use all cores
# # Use word-level tokenizer
>>> C1 = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'], word_level=True, overlap_size=1, n_ltable_chunks=-1, n_rtable_chunks=-1)
# # Use q-gram tokenizer
>>> C2 = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'], word_level=False, q_val=2, n_ltable_chunks=-1, n_rtable_chunks=-1)
# # Include all possible missing values
>>> C3 = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'], allow_missing=True, n_ltable_chunks=-1, n_rtable_chunks=-1)
"""
logger.warning(
"WARNING THIS COMMAND IS EXPERIMENTAL AND NOT TESTED. USE AT YOUR OWN "
"RISK.")
# Input validations
self.validate_types_params_tables(ltable, rtable, l_output_attrs,
r_output_attrs, l_output_prefix,
r_output_prefix, verbose, n_ltable_chunks, n_rtable_chunks)
self.validate_types_other_params(l_overlap_attr, r_overlap_attr,
rem_stop_words, q_val, word_level, overlap_size)
self.validate_allow_missing(allow_missing)
self.validate_show_progress(show_progress)
self.validate_overlap_attrs(ltable, rtable, l_overlap_attr, r_overlap_attr)
self.validate_output_attrs(ltable, rtable, l_output_attrs, r_output_attrs)
self.validate_word_level_qval(word_level, q_val)
log_info(logger, 'Required metadata: ltable key, rtable key', verbose)
l_key, r_key = cm.get_keys_for_ltable_rtable(ltable, rtable, logger, verbose)
# validate metadata
cm._validate_metadata_for_table(ltable, l_key, 'ltable', logger, verbose)
cm._validate_metadata_for_table(rtable, r_key, 'rtable', logger, verbose)
# validate input table chunks
validate_object_type(n_ltable_chunks, int, 'Parameter n_ltable_chunks')
validate_object_type(n_rtable_chunks, int,
'Parameter n_rtable_chunks')
validate_chunks(n_ltable_chunks)
validate_chunks(n_rtable_chunks)
if n_ltable_chunks == -1:
n_ltable_chunks = multiprocessing.cpu_count()
ltable_chunks = pd.np.array_split(ltable, n_ltable_chunks)
# preprocess/tokenize ltable
if word_level == True:
tokenizer = WhitespaceTokenizer(return_set=True)
else:
tokenizer = QgramTokenizer(qval=q_val, return_set=True)
preprocessed_tokenized_ltbl = []
# Construct DAG for preprocessing/tokenizing ltable chunks
start_row_id = 0
for i in range(len(ltable_chunks)):
result = delayed(self.process_tokenize_block_attr)(ltable_chunks[i][
l_overlap_attr],
start_row_id,
rem_stop_words, tokenizer)
preprocessed_tokenized_ltbl.append(result)
start_row_id += len(ltable_chunks[i])
preprocessed_tokenized_ltbl = delayed(wrap)(preprocessed_tokenized_ltbl)
# Execute the DAG
if show_progress:
with ProgressBar():
logger.info('Preprocessing/tokenizing ltable')
preprocessed_tokenized_ltbl_vals = preprocessed_tokenized_ltbl.compute(
scheduler="processes", num_workers=multiprocessing.cpu_count())
else:
preprocessed_tokenized_ltbl_vals = preprocessed_tokenized_ltbl.compute(
scheduler="processes", num_workers=multiprocessing.cpu_count())
ltable_processed_dict = {}
for i in range(len(preprocessed_tokenized_ltbl_vals)):
ltable_processed_dict.update(preprocessed_tokenized_ltbl_vals[i])
# build inverted index
inverted_index = self.build_inverted_index(ltable_processed_dict)
if n_rtable_chunks == -1:
n_rtable_chunks = multiprocessing.cpu_count()
rtable_chunks = pd.np.array_split(rtable, n_rtable_chunks)
# Construct the DAG for probing
probe_result = []
start_row_id = 0
for i in range(len(rtable_chunks)):
result = delayed(self.probe)(rtable_chunks[i][r_overlap_attr],
inverted_index, start_row_id, rem_stop_words,
tokenizer, overlap_size)
probe_result.append(result)
start_row_id += len(rtable_chunks[i])
probe_result = delayed(wrap)(probe_result)
# Execute the DAG for probing
if show_progress:
with ProgressBar():
logger.info('Probing using rtable')
probe_result = probe_result.compute(scheduler="processes",
num_workers=multiprocessing.cpu_count())
else:
probe_result = probe_result.compute(scheduler="processes",
num_workers=multiprocessing.cpu_count())
# construct a minimal dataframe that can be used to add more attributes
flat_list = [item for sublist in probe_result for item in sublist]
tmp = pd.DataFrame(flat_list, columns=['fk_ltable_rid', 'fk_rtable_rid'])
fk_ltable = ltable.iloc[tmp.fk_ltable_rid][l_key].values
fk_rtable = rtable.iloc[tmp.fk_rtable_rid][r_key].values
id_vals = list(range(len(flat_list)))
candset = pd.DataFrame.from_dict(
{'_id': id_vals, l_output_prefix+l_key: fk_ltable, r_output_prefix+r_key: fk_rtable})
# set the properties for the candidate set
cm.set_key(candset, '_id')
cm.set_fk_ltable(candset, 'ltable_'+l_key)
cm.set_fk_rtable(candset, 'rtable_'+r_key)
cm.set_ltable(candset, ltable)
cm.set_rtable(candset, rtable)
ret_candset = gh.add_output_attributes(candset, l_output_attrs=l_output_attrs,
r_output_attrs=r_output_attrs,
l_output_prefix=l_output_prefix,
r_output_prefix=r_output_prefix,
validate=False)
# handle missing values
if allow_missing:
missing_value_pairs = get_pairs_with_missing_value(ltable, rtable, l_key,
r_key, l_overlap_attr,
r_overlap_attr,
l_output_attrs,
r_output_attrs,
l_output_prefix,
r_output_prefix, False, False)
missing_value_pairs.insert(0, '_id', range(len(ret_candset),
len(ret_candset)+len(missing_value_pairs)))
if len(missing_value_pairs) > 0:
ret_candset = pd.concat([ret_candset, missing_value_pairs], ignore_index=True, sort=False)
cm.set_key(ret_candset, '_id')
cm.set_fk_ltable(ret_candset, 'ltable_' + l_key)
cm.set_fk_rtable(ret_candset, 'rtable_' + r_key)
cm.set_ltable(ret_candset, ltable)
cm.set_rtable(ret_candset, rtable)
# Return the final candidate set to user.
return ret_candset
def test_edit_dist_qg2_no_padding_empty(self):
self.test_filter_pair('', '', QgramTokenizer(2, padding=False),
'EDIT_DISTANCE', 1, False, False, False)
def setUp(self):
self.dlm = DelimiterTokenizer(delim_set=[' '], return_set=True)
self.qg2 = QgramTokenizer(2)
def edit_distance_join(ltable,
rtable,
l_key_attr,
r_key_attr,
l_join_attr,
r_join_attr,
threshold,
comp_op='<=',
allow_missing=False,
l_out_attrs=None,
r_out_attrs=None,
l_out_prefix='l_',
r_out_prefix='r_',
out_sim_score=True,
n_jobs=1,
show_progress=True,
tokenizer=QgramTokenizer(qval=2)):
"""Join two tables using edit distance measure.
Finds tuple pairs from left table and right table such that the edit
distance between the join attributes satisfies the condition on input
threshold. For example, if the comparison operator is '<=', finds tuple
pairs whose edit distance between the strings that are the values of
the join attributes is less than or equal to the input threshold, as
specified in "threshold".
Note:
Currently, this method only computes an approximate join result. This is
because, to perform the join we transform an edit distance measure
between strings into an overlap measure between qgrams of the strings.
Hence, we need at least one qgram to be in common between two input
strings, to appear in the join output. For smaller strings, where all
qgrams of the strings differ, we cannot process them.
This method implements a simplified version of the algorithm proposed in
`Ed-Join: An Efficient Algorithm for Similarity Joins With Edit Distance
Constraints (Chuan Xiao, Wei Wang and Xuemin Lin), VLDB 08
<http://www.vldb.org/pvldb/1/1453957.pdf>`_.
Args:
ltable (DataFrame): left input table.
rtable (DataFrame): right input table.
l_key_attr (string): key attribute in left table.
r_key_attr (string): key attribute in right table.
l_join_attr (string): join attribute in left table.
r_join_attr (string): join attribute in right table.
threshold (float): edit distance threshold to be satisfied.
comp_op (string): comparison operator. Supported values are '<=', '<'
and '=' (defaults to '<=').
allow_missing (boolean): flag to indicate whether tuple pairs with
missing value in at least one of the join attributes should be
included in the output (defaults to False). If this flag is set to
True, a tuple in ltable with missing value in the join attribute
will be matched with every tuple in rtable and vice versa.
l_out_attrs (list): list of attribute names from the left table to be
included in the output table (defaults to None).
r_out_attrs (list): list of attribute names from the right table to be
included in the output table (defaults to None).
l_out_prefix (string): prefix to be used for the attribute names coming
from the left table, in the output table (defaults to 'l\_').
r_out_prefix (string): prefix to be used for the attribute names coming
from the right table, in the output table (defaults to 'r\_').
out_sim_score (boolean): flag to indicate whether the edit distance
score should be included in the output table (defaults to True).
Setting this flag to True will add a column named '_sim_score' in
the output table. This column will contain the edit distance scores
for the tuple pairs in the output.
n_jobs (int): number of parallel jobs to use for the computation
(defaults to 1). If -1 is given, all CPUs are used. If 1 is given,
no parallel computing code is used at all, which is useful for
debugging. For n_jobs below -1, (n_cpus + 1 + n_jobs) are used
(where n_cpus is the total number of CPUs in the machine). Thus for
n_jobs = -2, all CPUs but one are used. If (n_cpus + 1 + n_jobs)
becomes less than 1, then no parallel computing code will be used
(i.e., equivalent to the default).
show_progress (boolean): flag to indicate whether task progress should
be displayed to the user (defaults to True).
tokenizer (Tokenizer): tokenizer to be used to tokenize the join
attributes during filtering, when edit distance measure is
transformed into an overlap measure. This must be a q-gram tokenizer
(defaults to 2-gram tokenizer).
Returns:
An output table containing tuple pairs that satisfy the join
condition (DataFrame).
"""
# check if the input tables are dataframes
validate_input_table(ltable, 'left table')
validate_input_table(rtable, 'right table')
# check if the key attributes and join attributes exist
validate_attr(l_key_attr, ltable.columns, 'key attribute', 'left table')
validate_attr(r_key_attr, rtable.columns, 'key attribute', 'right table')
validate_attr(l_join_attr, ltable.columns, 'join attribute', 'left table')
validate_attr(r_join_attr, rtable.columns, 'join attribute', 'right table')
# check if the join attributes are not of numeric type
validate_attr_type(l_join_attr, ltable[l_join_attr].dtype,
'join attribute', 'left table')
validate_attr_type(r_join_attr, rtable[r_join_attr].dtype,
'join attribute', 'right table')
# check if the input tokenizer is valid for edit distance measure. Only
# qgram tokenizer can be used for edit distance.
validate_tokenizer_for_sim_measure(tokenizer, 'EDIT_DISTANCE')
# check if the input threshold is valid
validate_threshold(threshold, 'EDIT_DISTANCE')
# check if the comparison operator is valid
validate_comp_op_for_sim_measure(comp_op, 'EDIT_DISTANCE')
# check if the output attributes exist
validate_output_attrs(l_out_attrs, ltable.columns, r_out_attrs,
rtable.columns)
# check if the key attributes are unique and do not contain missing values
validate_key_attr(l_key_attr, ltable, 'left table')
validate_key_attr(r_key_attr, rtable, 'right table')
# convert threshold to integer (incase if it is float)
threshold = int(floor(threshold))
# set return_set flag of tokenizer to be False, in case it is set to True
revert_tokenizer_return_set_flag = False
if tokenizer.get_return_set():
tokenizer.set_return_set(False)
revert_tokenizer_return_set_flag = True
# remove redundant attrs from output attrs.
l_out_attrs = remove_redundant_attrs(l_out_attrs, l_key_attr)
r_out_attrs = remove_redundant_attrs(r_out_attrs, r_key_attr)
# get attributes to project.
l_proj_attrs = get_attrs_to_project(l_out_attrs, l_key_attr, l_join_attr)
r_proj_attrs = get_attrs_to_project(r_out_attrs, r_key_attr, r_join_attr)
# Do a projection on the input dataframes to keep only the required
# attributes. Then, remove rows with missing value in join attribute from
# the input dataframes. Then, convert the resulting dataframes into ndarray.
ltable_array = convert_dataframe_to_array(ltable, l_proj_attrs,
l_join_attr)
rtable_array = convert_dataframe_to_array(rtable, r_proj_attrs,
r_join_attr)
# computes the actual number of jobs to launch.
n_jobs = min(get_num_processes_to_launch(n_jobs), len(rtable_array))
if n_jobs <= 1:
# if n_jobs is 1, do not use any parallel code.
output_table = _edit_distance_join_split(
ltable_array, rtable_array, l_proj_attrs, r_proj_attrs, l_key_attr,
r_key_attr, l_join_attr, r_join_attr, tokenizer, threshold,
comp_op, l_out_attrs, r_out_attrs, l_out_prefix, r_out_prefix,
out_sim_score, show_progress)
else:
# if n_jobs is above 1, split the right table into n_jobs splits and
# join each right table split with the whole of left table in a separate
# process.
r_splits = split_table(rtable_array, n_jobs)
results = Parallel(n_jobs=n_jobs)(delayed(_edit_distance_join_split)(
ltable_array, r_splits[job_index], l_proj_attrs, r_proj_attrs,
l_key_attr, r_key_attr, l_join_attr, r_join_attr, tokenizer,
threshold, comp_op, l_out_attrs, r_out_attrs, l_out_prefix,
r_out_prefix, out_sim_score, (
show_progress and (job_index == n_jobs - 1)))
for job_index in range(n_jobs))
output_table = pd.concat(results)
# If allow_missing flag is set, then compute all pairs with missing value in
# at least one of the join attributes and then add it to the output
# obtained from the join.
if allow_missing:
missing_pairs = get_pairs_with_missing_value(
ltable, rtable, l_key_attr, r_key_attr, l_join_attr, r_join_attr,
l_out_attrs, r_out_attrs, l_out_prefix, r_out_prefix,
out_sim_score, show_progress)
output_table = pd.concat([output_table, missing_pairs])
# add an id column named '_id' to the output table.
output_table.insert(0, '_id', range(0, len(output_table)))
# revert the return_set flag of tokenizer, in case it was modified.
if revert_tokenizer_return_set_flag:
tokenizer.set_return_set(True)
return output_table
def disk_edit_distance_join(ltable, rtable,
l_key_attr, r_key_attr,
l_join_attr, r_join_attr,
threshold, data_limit=1000000,
comp_op='<=', allow_missing=False,
l_out_attrs=None, r_out_attrs=None,
l_out_prefix='l_', r_out_prefix='r_',
out_sim_score=True, n_jobs=-1,
show_progress=True, tokenizer=QgramTokenizer(qval=2),
temp_dir=os.getcwd(), output_file_path=default_output_file_path):
"""
WARNING: THIS IS AN EXPERIMENTAL COMMAND. THIS COMMAND IS NOT TESTED.
USE AT YOUR OWN RISK.
Join two tables using edit distance measure.
This is the disk version of the previous edit_distance_join api.
There can be a scenario that while performing join on large datasets,
the intermediate in-memory data structures grow very large and thus lead
to termination of the program due to insufficient memory. Keeping this problem
in mind, disk_edit_distance_join is the updated version of the older
edit_distance_join function that solves the above mentioned problem.
So if the analysis is being done on the machine with small memory limits or
if the input tables are too large, then this new disk_edit_distance_join can be
used to avoid memory exceeding problem while processing.
It Finds tuple pairs from left table and right table such that the edit
distance between the join attributes satisfies the condition on input
threshold. For example, if the comparison operator is '<=', finds tuple
pairs whose edit distance between the strings that are the values of
the join attributes is less than or equal to the input threshold, as
specified in "threshold".
Note:
Currently, this method only computes an approximate join result. This is
because, to perform the join we transform an edit distance measure
between strings into an overlap measure between qgrams of the strings.
Hence, we need at least one qgram to be in common between two input
strings, to appear in the join output. For smaller strings, where all
qgrams of the strings differ, we cannot process them.
This method implements a simplified version of the algorithm proposed in
`Ed-Join: An Efficient Algorithm for Similarity Joins With Edit Distance
Constraints (Chuan Xiao, Wei Wang and Xuemin Lin), VLDB 08
<http://www.vldb.org/pvldb/1/1453957.pdf>`_.
Args:
ltable (DataFrame): left input table.
rtable (DataFrame): right input table.
l_key_attr (string): key attribute in left table.
r_key_attr (string): key attribute in right table.
l_join_attr (string): join attribute in left table.
r_join_attr (string): join attribute in right table.
threshold (float): edit distance threshold to be satisfied.
data_limit (int): threshold value for number of rows that would be kept
in memory before writing the output on the disk. This is the maximum sum
total of all rows that can be present in memory across all processes at
a time. This is a new argument compared to edit distance join.
(defaults to 1M)
comp_op (string): comparison operator. Supported values are '<=', '<'
and '=' (defaults to '<=').
allow_missing (boolean): flag to indicate whether tuple pairs with
missing value in at least one of the join attributes should be
included in the output (defaults to False). If this flag is set to
True, a tuple in ltable with missing value in the join attribute
will be matched with every tuple in rtable and vice versa.
l_out_attrs (list): list of attribute names from the left table to be
included in the output table (defaults to None).
r_out_attrs (list): list of attribute names from the right table to be
included in the output table (defaults to None).
l_out_prefix (string): prefix to be used for the attribute names coming
from the left table, in the output table (defaults to 'l\_').
r_out_prefix (string): prefix to be used for the attribute names coming
from the right table, in the output table (defaults to 'r\_').
out_sim_score (boolean): flag to indicate whether the edit distance
score should be included in the output table (defaults to True).
Setting this flag to True will add a column named '_sim_score' in
the output table. This column will contain the edit distance scores
for the tuple pairs in the output.
n_jobs (int): number of parallel jobs to use for the computation
(defaults to 1). If -1 is given, all CPUs are used. If 1 is given,
no parallel computing code is used at all, which is useful for
debugging. For n_jobs below -1, (n_cpus + 1 + n_jobs) are used
(where n_cpus is the total number of CPUs in the machine). Thus for
n_jobs = -2, all CPUs but one are used. If (n_cpus + 1 + n_jobs)
becomes less than 1, then no parallel computing code will be used
(i.e., equivalent to the default).
show_progress (boolean): flag to indicate whether task progress should
be displayed to the user (defaults to True).
tokenizer (Tokenizer): tokenizer to be used to tokenize the join
attributes during filtering, when edit distance measure is
transformed into an overlap measure. This must be a q-gram tokenizer
(defaults to 2-gram tokenizer).
temp_dir (string): absolute path where all the intermediate files will be generated.
This is a new argument compared to edit distance join. (defaults to the current
working directory).
output_file_path (string): absolute path where the output file will be generated.
Older file with same path and name will be removed. This is a new argument compared
to edit distance join. (defaults to the current working directory/$default_output_file_name).
Returns:
Returns the status of the computation. True if successfully completed else False (boolean).
"""
from py_stringsimjoin import __use_cython__
if __use_cython__:
from py_stringsimjoin.join.disk_edit_distance_join_cy import disk_edit_distance_join_cy
return disk_edit_distance_join_cy(ltable, rtable,
l_key_attr, r_key_attr,
l_join_attr, r_join_attr,
threshold, data_limit,
comp_op,
allow_missing,
l_out_attrs, r_out_attrs,
l_out_prefix, r_out_prefix,
out_sim_score, n_jobs,
show_progress,tokenizer,
temp_dir, output_file_path)
else:
raise AssertionError('Cython not installed.')
def test_apply_matcher_with_allow_missing(self):
tok = QgramTokenizer(qval=2, return_set=True)
sim_func = get_sim_function('JACCARD')
threshold = 0.3
comp_op = '>='
# apply sim function to the entire cartesian product to obtain
# the expected set of pairs satisfying the threshold.
cartprod = self.cartprod
cartprod['sim_score'] = cartprod.apply(
lambda row: sim_func(tok.tokenize(str(row[self.l_join_attr])),
tok.tokenize(str(row[self.r_join_attr]))),
axis=1)
# compute expected output pairs
comp_fn = COMP_OP_MAP[comp_op]
expected_pairs = set()
for idx, row in cartprod.iterrows():
if comp_fn(float(row['sim_score']), threshold):
expected_pairs.add(','.join(
(str(row[self.l_key_attr]), str(row[self.r_key_attr]))))
# find pairs that need to be included in output due to
# the presence of missing value in one of the join attributes.
missing_pairs = set()
for l_idx, l_row in self.orig_ltable.iterrows():
for r_idx, r_row in self.orig_rtable.iterrows():
if (pd.isnull(l_row[self.l_join_attr])
or pd.isnull(r_row[self.r_join_attr])):
missing_pairs.add(','.join((str(l_row[self.l_key_attr]),
str(r_row[self.r_key_attr]))))
# add the pairs containing missing value to the set of expected pairs.
expected_pairs = expected_pairs.union(missing_pairs)
# use overlap filter to obtain a candset with allow_missing set to True.
overlap_filter = OverlapFilter(tok, 1, comp_op, allow_missing=True)
candset = overlap_filter.filter_tables(
self.orig_ltable, self.orig_rtable, self.l_key_attr,
self.r_key_attr, self.l_join_attr, self.r_join_attr)
# apply a jaccard matcher to the candset with allow_missing set to True.
output_candset = apply_matcher(candset,
DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr,
self.orig_ltable,
self.orig_rtable,
self.l_key_attr,
self.r_key_attr,
self.l_join_attr,
self.r_join_attr,
tok,
sim_func,
threshold,
comp_op,
True,
out_sim_score=True)
expected_output_attrs = [
'_id', DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr, '_sim_score'
]
# verify whether the output table has the necessary attributes.
assert_list_equal(list(output_candset.columns.values),
expected_output_attrs)
actual_pairs = set()
for idx, row in output_candset.iterrows():
actual_pairs.add(','.join(
(str(row[DEFAULT_L_OUT_PREFIX + self.l_key_attr]),
str(row[DEFAULT_R_OUT_PREFIX + self.r_key_attr]))))
# verify whether the actual pairs and the expected pairs match.
assert_equal(len(expected_pairs), len(actual_pairs))
common_pairs = actual_pairs.intersection(expected_pairs)
assert_equal(len(common_pairs), len(expected_pairs))
def test_edit_distance_join():
# data to be tested.
test_scenario_1 = [('data/table_A.csv', 'A.ID', 'A.name'),
('data/table_B.csv', 'B.ID', 'B.name')]
data = {'TEST_SCENARIO_1': test_scenario_1}
# edit distance thresholds to be tested.
thresholds = [1, 2, 3, 4, 8, 9]
# tokenizers to be tested.
tokenizers = {
'2_GRAM': QgramTokenizer(qval=2),
'3_GRAM': QgramTokenizer(qval=3)
}
# comparison operators to be tested.
comp_ops = ['<=', '<', '=']
sim_measure_type = 'EDIT_DISTANCE'
# Test each combination of threshold and tokenizer
# for different test scenarios.
for label, scenario in iteritems(data):
for threshold in thresholds:
for tok_type, tok in iteritems(tokenizers):
for comp_op in comp_ops:
test_function = partial(test_valid_join, scenario, tok,
threshold, comp_op)
test_function.description = 'Test ' + sim_measure_type + \
' with ' + str(threshold) + ' threshold and ' + \
tok_type + ' tokenizer for ' + label + '.'
yield test_function,
# Test with allow_missing flag set to True.
test_function = partial(
test_valid_join, test_scenario_1, tokenizers['2_GRAM'], 9, '<=',
(True, ['A.birth_year', 'A.zipcode'], ['B.name', 'B.zipcode']))
test_function.description = 'Test ' + sim_measure_type + \
' with allow_missing set to True.'
yield test_function,
# Test with output attributes added.
test_function = partial(test_valid_join, test_scenario_1,
tokenizers['2_GRAM'], 9, '<=',
(False, ['A.ID', 'A.birth_year', 'A.zipcode'
], ['B.ID', 'B.name', 'B.zipcode']))
test_function.description = 'Test ' + sim_measure_type + \
' with output attributes.'
yield test_function,
# Test with a different output prefix.
test_function = partial(test_valid_join, test_scenario_1,
tokenizers['2_GRAM'], 9, '<=',
(False, ['A.birth_year', 'A.zipcode'],
['B.name', 'B.zipcode'], 'ltable.', 'rtable.'))
test_function.description = 'Test ' + sim_measure_type + \
' with output attributes and prefix.'
yield test_function,
# Test with output_sim_score disabled.
test_function = partial(
test_valid_join, test_scenario_1, tokenizers['2_GRAM'], 9, '<=',
(False, ['A.birth_year', 'A.zipcode'
], ['B.name', 'B.zipcode'], 'ltable.', 'rtable.', False))
test_function.description = 'Test ' + sim_measure_type + \
' with sim_score disabled.'
yield test_function,
# Test with n_jobs above 1.
test_function = partial(
test_valid_join, test_scenario_1, tokenizers['2_GRAM'], 9, '<=',
(False, ['A.birth_year', 'A.zipcode'
], ['B.name', 'B.zipcode'], 'ltable.', 'rtable.', False, 2))
test_function.description = 'Test ' + sim_measure_type + \
' with n_jobs above 1.'
yield test_function,
# scenario where join attributes are of type int
test_scenario_2 = [(os.sep.join(['data',
'table_A.csv']), 'A.ID', 'A.zipcode'),
(os.sep.join(['data',
'table_B.csv']), 'B.ID', 'B.zipcode')]
# Test with join attribute of type int.
test_function = partial(test_valid_join, test_scenario_2,
tokenizers['2_GRAM'], 3, '<=', (), True)
test_function.description = 'Test ' + sim_measure_type + \
' with join attribute of type int.'
yield test_function,
# scenario where join attributes are of type float
test_scenario_3 = [(os.sep.join(['data',
'table_A.csv']), 'A.ID', 'A.hourly_wage'),
(os.sep.join(['data',
'table_B.csv']), 'B.ID', 'B.hourly_wage')]
# Test with join attribute of type float.
test_function = partial(test_valid_join, test_scenario_3,
tokenizers['2_GRAM'], 3, '<=', (), True)
test_function.description = 'Test ' + sim_measure_type + \
' with join attribute of type float.'
yield test_function,
# Test with a tokenizer where return_set flag is set to True.
tok = QgramTokenizer(2, return_set=True)
test_function = partial(test_valid_join, test_scenario_1, tok, 9)
test_function.description = 'Test ' + sim_measure_type + \
' with a tokenizer where return_set flag is set to True'
yield test_function,
def block_candset(self, candset, l_overlap_attr, r_overlap_attr,
rem_stop_words=False, q_val=None, word_level=True,
overlap_size=1, allow_missing=False,
verbose=False, show_progress=True, n_chunks=-1):
"""
WARNING THIS COMMAND IS EXPERIMENTAL AND NOT TESTED. USE AT YOUR OWN RISK.
Blocks an input candidate set of tuple pairs based on the overlap
of token sets of attribute values. Finds tuple pairs from an input
candidate set of tuple pairs such that
the overlap between (a) the set of tokens obtained by tokenizing the
value of attribute l_overlap_attr of the left tuple in a tuple pair,
and (b) the set of tokens obtained by tokenizing the value of
attribute r_overlap_attr of the right tuple in the tuple pair,
is above a certain threshold.
Args:
candset (DataFrame): The input candidate set of tuple pairs.
l_overlap_attr (string): The overlap attribute in left table.
r_overlap_attr (string): The overlap attribute in right table.
rem_stop_words (boolean): A flag to indicate whether stop words
(e.g., a, an, the) should be removed
from the token sets of the overlap
attribute values (defaults to False).
q_val (int): The value of q to use if the overlap attributes values
are to be tokenized as qgrams (defaults to None).
word_level (boolean): A flag to indicate whether the overlap
attributes should be tokenized as words
(i.e, using whitespace as delimiter)
(defaults to True).
overlap_size (int): The minimum number of tokens that must overlap
(defaults to 1).
allow_missing (boolean): A flag to indicate whether tuple pairs
with missing value in at least one of the
blocking attributes should be included in
the output candidate set (defaults to
False). If this flag is set to True, a
tuple pair with missing value in either
blocking attribute will be retained in the
output candidate set.
verbose (boolean): A flag to indicate whether the debug information
should be logged (defaults to False).
show_progress (boolean): A flag to indicate whether progress should
be displayed to the user (defaults to True).
n_chunks (int): The number of partitions to split the candidate set. If it
is set to -1, the number of partitions will be set to the
number of cores in the machine.
Returns:
A candidate set of tuple pairs that survived blocking (DataFrame).
Raises:
AssertionError: If `candset` is not of type pandas
DataFrame.
AssertionError: If `l_overlap_attr` is not of type string.
AssertionError: If `r_overlap_attr` is not of type string.
AssertionError: If `q_val` is not of type int.
AssertionError: If `word_level` is not of type boolean.
AssertionError: If `overlap_size` is not of type int.
AssertionError: If `verbose` is not of type
boolean.
AssertionError: If `allow_missing` is not of type boolean.
AssertionError: If `show_progress` is not of type
boolean.
AssertionError: If `n_chunks` is not of type
int.
AssertionError: If `l_overlap_attr` is not in the ltable
columns.
AssertionError: If `r_block_attr` is not in the rtable columns.
SyntaxError: If `q_val` is set to a valid value and
`word_level` is set to True.
SyntaxError: If `q_val` is set to None and
`word_level` is set to False.
Examples:
>>> import py_entitymatching as em
>>> from py_entitymatching.dask.dask_overlap_blocker import DaskOverlapBlocker
>>> A = em.read_csv_metadata('path_to_csv_dir/table_A.csv', key='ID')
>>> B = em.read_csv_metadata('path_to_csv_dir/table_B.csv', key='ID')
>>> ob = DaskOverlapBlocker()
>>> C = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'])
>>> D1 = ob.block_candset(C, 'name', 'name', allow_missing=True)
# Include all possible tuple pairs with missing values
>>> D2 = ob.block_candset(C, 'name', 'name', allow_missing=True)
# Execute blocking using multiple cores
>>> D3 = ob.block_candset(C, 'name', 'name', n_chunks=-1)
# Use q-gram tokenizer
>>> D2 = ob.block_candset(C, 'name', 'name', word_level=False, q_val=2)
"""
logger.warning(
"WARNING THIS BLOCKER IS EXPERIMENTAL AND NOT TESTED. USE AT YOUR OWN "
"RISK.")
# Validate input parameters
self.validate_types_params_candset(candset, verbose, show_progress, n_chunks)
self.validate_types_other_params(l_overlap_attr, r_overlap_attr,
rem_stop_words, q_val, word_level, overlap_size)
# get and validate metadata
log_info(logger,
'Required metadata: cand.set key, fk ltable, fk rtable, '
'ltable, rtable, ltable key, rtable key', verbose)
# # get metadata
key, fk_ltable, fk_rtable, ltable, rtable, l_key, r_key = cm.get_metadata_for_candset(
candset, logger, verbose)
# # validate metadata
cm._validate_metadata_for_candset(candset, key, fk_ltable, fk_rtable,
ltable, rtable, l_key, r_key,
logger, verbose)
# validate overlap attrs
self.validate_overlap_attrs(ltable, rtable, l_overlap_attr,
r_overlap_attr)
# validate word_level and q_val
self.validate_word_level_qval(word_level, q_val)
# validate number of chunks
validate_object_type(n_chunks, int, 'Parameter n_chunks')
validate_chunks(n_chunks)
# # do projection before merge
l_df = ltable[[l_key, l_overlap_attr]]
r_df = rtable[[r_key, r_overlap_attr]]
# # set index for convenience
l_df = l_df.set_index(l_key, drop=False)
r_df = r_df.set_index(r_key, drop=False)
# # case the overlap attribute to string if required.
l_df.is_copy, r_df.is_copy = False, False # to avoid setwithcopy warning
ssj.dataframe_column_to_str(l_df, l_overlap_attr, inplace=True)
ssj.dataframe_column_to_str(r_df, r_overlap_attr, inplace=True)
if word_level == True:
tokenizer = WhitespaceTokenizer(return_set=True)
else:
tokenizer = QgramTokenizer(return_set=True)
n_chunks = get_num_partitions(n_chunks, len(candset))
c_splits = pd.np.array_split(candset, n_chunks)
valid_splits = []
# Create DAG
for i in range(n_chunks):
result = delayed(self._block_candset_split)(c_splits[i], l_df, r_df, l_key,
r_key, l_overlap_attr,
r_overlap_attr, fk_ltable,
fk_rtable, allow_missing,
rem_stop_words, tokenizer, overlap_size)
valid_splits.append(result)
valid_splits = delayed(wrap)(valid_splits)
# Execute the DAG
if show_progress:
with ProgressBar():
valid_splits = valid_splits.compute(scheduler="processes",
num_workers=get_num_cores())
else:
valid_splits = valid_splits.compute(scheduler="processes",
num_workers=get_num_cores())
valid = sum(valid_splits, [])
# construct output table
if len(candset) > 0:
out_table = candset[valid]
else:
out_table = pd.DataFrame(columns=candset.columns)
# update the catalog
cm.set_candset_properties(out_table, key, fk_ltable, fk_rtable,
ltable, rtable)
# return the output table
return out_table
def tuner_overlap_blocker(ltable,
rtable,
l_key,
r_key,
l_overlap_attr,
r_overlap_attr,
rem_stop_words,
q_val,
word_level,
overlap_size,
ob_obj,
n_bins=50,
sample_proportion=0.1,
seed=0,
repeat=1):
"""
WARNING THIS COMMAND IS EXPERIMENTAL AND NOT TESTED. USE AT YOUR OWN RISK.
Tunes the parameters for blocking two tables command implemented using Dask.
Given the input tables and the parameters for Dask-based overlap blocker command,
this command returns the configuration including whether the input tables need to
be swapped, the number of left table chunks, and the number of right table chunks.
It uses "Staged Tuning" approach to select the configuration setting. The key idea
of this approach select the configuration for one parameter at a time.
Conceptually, this command performs the following steps. First, it samples the
left table and rtable using stratified sampling. Next, it uses the
sampled tables to decide if they need to be swapped or not (by running the down
sample command and comparing the runtimes). Next, it finds the number of rtable
partitions using the sampled tables (by trying the a fixed set of partitions and
comparing the runtimes). The number of partitions is selected to be the number
before which the runtime starts increasing. Then it finds the number of right table
partitions similar to selecting the number of left table partitions. while doing
this, set the number of right table partitions is set to the value found in the
previous step. Finally, it returns the configuration setting back to the user as a
triplet (x, y, z) where x indicates if the tables need to be swapped or not,
y indicates the number of left table partitions (if the tables need to be swapped,
then this indicates the number of left table partitions after swapping),
and z indicates the number of right table partitions.
Args:
ltable (DataFrame): The left input table.
rtable (DataFrame): The right input table.
l_overlap_attr (string): The overlap attribute in left table.
r_overlap_attr (string): The overlap attribute in right table.
rem_stop_words (boolean): A flag to indicate whether stop words
(e.g., a, an, the) should be removed from the token sets of the
overlap attribute values (defaults to False).
q_val (int): The value of q to use if the overlap attributes
values are to be tokenized as qgrams (defaults to None).
word_level (boolean): A flag to indicate whether the overlap
attributes should be tokenized as words (i.e, using whitespace
as delimiter) (defaults to True).
overlap_size (int): The minimum number of tokens that must overlap.
ob_obj (OverlapBlocker): The object used to call commands to block two tables
and a candidate set
n_bins (int): The number of bins to be used for stratified sampling.
sample_proportion (float): The proportion used to sample the tables. This value
is expected to be greater than 0 and less thank 1.
repeat (int): The number of times to execute the down sample command while
selecting the values for the parameters.
Returns:
A tuple containing 3 values. For example if the tuple is represented as (x, y,
z) then x indicates if the tables need to be swapped or not, y indicates the number of
left table partitions (if the tables need to be swapped, then this indicates the
number of left table partitions after swapping), and z indicates the number of
right table partitions.
Examples:
>>> from py_entitymatching.tuner.tuner_overlap_blocker import tuner_overlap_blocker
>>> from py_entitymatching.dask.dask_overlap_blocker import DaskOverlapBlocker
>>> obj = DaskOverlapBlocker()
>>> (swap_or_not, n_ltable_chunks, n_sample_rtable_chunks) = tuner_overlap_blocker(ltable, rtable, 'id', 'id', "title", "title", rem_stop_words=True, q_val=None, word_level=True, overlap_size=1, ob_obj=obj)
"""
logger.warning(
"WARNING THIS COMMAND IS EXPERIMENTAL AND NOT TESTED. USE AT YOUR OWN "
"RISK.")
# Select the tokenizer
if word_level:
tokenizer = WhitespaceTokenizer()
else:
tokenizer = QgramTokenizer()
# Same the input tables, given in the original order
sampled_tables_orig_order = get_sampled_tables(
ltable, rtable, l_key, r_key, l_overlap_attr, r_overlap_attr,
rem_stop_words, tokenizer, ob_obj, n_bins, sample_proportion, seed)
# Same the input tables, given in the swapped order
sampled_tables_swap_order = get_sampled_tables(
rtable, ltable, r_key, l_key, r_overlap_attr, l_overlap_attr,
rem_stop_words, tokenizer, ob_obj, n_bins, sample_proportion, seed)
# Select if the tables need to be swapped
swap_config = should_swap(ob_obj, sampled_tables_orig_order,
sampled_tables_swap_order, l_overlap_attr,
r_overlap_attr, rem_stop_words, q_val,
word_level, overlap_size, repeat)
# Use the sampled tables
s_ltable, s_rtable = sampled_tables_orig_order
if swap_config == True:
s_ltable, s_rtable = sampled_tables_swap_order
# Find the number of right table partitions
n_rtable_chunks = find_rtable_chunks(ob_obj, s_ltable, s_rtable,
l_overlap_attr, r_overlap_attr,
rem_stop_words, q_val, word_level,
overlap_size)
# Find the number of left table partitions
n_ltable_chunks = find_ltable_chunks(ob_obj, s_ltable, s_rtable,
l_overlap_attr, r_overlap_attr,
rem_stop_words, q_val, word_level,
overlap_size, n_rtable_chunks)
# Return the configuration
return (swap_config, n_ltable_chunks, n_rtable_chunks)
def _apply_filterable_rule(self, rule_name, ltable, rtable, l_key, r_key):
candset = None
conjunct_list = self.rule_str[rule_name]
for conjunct in conjunct_list:
is_auto_gen, sim_fn, l_attr, r_attr, l_tok, r_tok, op, \
th = self._parse_conjunct(
conjunct, rule_name)
if l_tok == 'dlm_dc0':
tokenizer = WhitespaceTokenizer(return_set=True)
elif l_tok == 'qgm_3':
tokenizer = QgramTokenizer(qval=3, return_set=True)
if sim_fn == 'jaccard':
join_fn = ssj.jaccard_join
elif sim_fn == 'cosine':
join_fn = ssj.cosine_join
elif sim_fn == 'dice':
join_fn = ssj.dice_join
elif sim_fn == 'overlap_coeff':
join_fn = ssj.overlap_coefficient_join
elif sim_fn == 'lev_dist':
join_fn = ssj.edit_distance_join
if join_fn == ssj.edit_distance_join:
comp_op = '<='
if op == '>=':
comp_op = '<'
else:
comp_op = '>='
if op == '<=':
comp_op = '>'
ssj.dataframe_column_to_str(ltable, l_attr, inplace=True)
ssj.dataframe_column_to_str(rtable, r_attr, inplace=True)
if join_fn == ssj.edit_distance_join:
tokenizer = QgramTokenizer(qval=2, return_set=False)
c_df = join_fn(
ltable,
rtable,
l_key,
r_key,
l_attr,
r_attr,
float(th),
comp_op,
allow_missing=True,
# need to revisit allow_missing
out_sim_score=False,
l_out_prefix='l_',
r_out_prefix='r_',
show_progress=False,
tokenizer=tokenizer)
else:
c_df = join_fn(ltable,
rtable,
l_key,
r_key,
l_attr,
r_attr,
tokenizer,
float(th),
comp_op,
allow_empty=True,
allow_missing=True,
l_out_prefix='l_',
r_out_prefix='r_',
out_sim_score=False)
#c_df.drop('_id', axis=1)
if candset is not None:
# union the candset of this conjunct with the existing candset
candset = pd.concat([candset, c_df]).drop_duplicates(
[l_output_prefix + l_key,
r_output_prefix + r_key]).reset_index(drop=True)
else:
# candset from the first conjunct of the rule
candset = c_df
return candset
def block_tables(self,
ltable,
rtable,
l_overlap_attr,
r_overlap_attr,
rem_stop_words=False,
q_val=None,
word_level=True,
overlap_size=1,
l_output_attrs=None,
r_output_attrs=None,
l_output_prefix='ltable_',
r_output_prefix='rtable_',
allow_missing=False,
verbose=False,
show_progress=True,
n_jobs=1):
"""
Blocks two tables based on the overlap of token sets of attribute
values.
Finds tuple pairs from left and right tables such that the overlap
between (a) the set of tokens obtained by tokenizing the value of
attribute l_overlap_attr of a tuple from the left table, and (b) the
set of tokens obtained by tokenizing the value of attribute
r_overlap_attr of a tuple from the right table, is above a certain
threshold.
Args:
ltable (DataFrame): The left input table.
rtable (DataFrame): The right input table.
l_overlap_attr (string): The overlap attribute in left table.
r_overlap_attr (string): The overlap attribute in right table.
rem_stop_words (boolean): A flag to indicate whether stop words
(e.g., a, an, the) should be removed from the token sets of the
overlap attribute values (defaults to False).
q_val (int): The value of q to use if the overlap attributes
values are to be tokenized as qgrams (defaults to None).
word_level (boolean): A flag to indicate whether the overlap
attributes should be tokenized as words (i.e, using whitespace
as delimiter) (defaults to True).
overlap_size (int): The minimum number of tokens that must
overlap (defaults to 1).
l_output_attrs (list): A list of attribute names from the left
table to be included in the output candidate set (defaults
to None).
r_output_attrs (list): A list of attribute names from the right
table to be included in the output candidate set (defaults
to None).
l_output_prefix (string): The prefix to be used for the attribute names
coming from the left table in the output
candidate set (defaults to 'ltable\_').
r_output_prefix (string): The prefix to be used for the attribute names
coming from the right table in the output
candidate set (defaults to 'rtable\_').
allow_missing (boolean): A flag to indicate whether tuple pairs
with missing value in at least one of the
blocking attributes should be included in
the output candidate set (defaults to
False). If this flag is set to True, a
tuple in ltable with missing value in the
blocking attribute will be matched with
every tuple in rtable and vice versa.
verbose (boolean): A flag to indicate whether the debug
information should be logged (defaults to False).
show_progress (boolean): A flag to indicate whether progress should
be displayed to the user (defaults to True).
n_jobs (int): The number of parallel jobs to be used for computation
(defaults to 1). If -1 all CPUs are used. If 0 or 1,
no parallel computation is used at all, which is useful for
debugging. For n_jobs below -1, (n_cpus + 1 + n_jobs) are
used (where n_cpus is the total number of CPUs in the
machine). Thus, for n_jobs = -2, all CPUs but one are used.
If (n_cpus + 1 + n_jobs) is less than 1, then no parallel
computation is used (i.e., equivalent to the default).
Returns:
A candidate set of tuple pairs that survived blocking (DataFrame).
Raises:
AssertionError: If `ltable` is not of type pandas
DataFrame.
AssertionError: If `rtable` is not of type pandas
DataFrame.
AssertionError: If `l_overlap_attr` is not of type string.
AssertionError: If `r_overlap_attr` is not of type string.
AssertionError: If `l_output_attrs` is not of type of
list.
AssertionError: If `r_output_attrs` is not of type of
list.
AssertionError: If the values in `l_output_attrs` is not of type
string.
AssertionError: If the values in `r_output_attrs` is not of type
string.
AssertionError: If `l_output_prefix` is not of type
string.
AssertionError: If `r_output_prefix` is not of type
string.
AssertionError: If `q_val` is not of type int.
AssertionError: If `word_level` is not of type boolean.
AssertionError: If `overlap_size` is not of type int.
AssertionError: If `verbose` is not of type
boolean.
AssertionError: If `allow_missing` is not of type boolean.
AssertionError: If `show_progress` is not of type
boolean.
AssertionError: If `n_jobs` is not of type
int.
AssertionError: If `l_overlap_attr` is not in the ltable
columns.
AssertionError: If `r_block_attr` is not in the rtable columns.
AssertionError: If `l_output_attrs` are not in the ltable.
AssertionError: If `r_output_attrs` are not in the rtable.
SyntaxError: If `q_val` is set to a valid value and
`word_level` is set to True.
SyntaxError: If `q_val` is set to None and
`word_level` is set to False.
Examples:
>>> import py_entitymatching as em
>>> A = em.read_csv_metadata('path_to_csv_dir/table_A.csv', key='ID')
>>> B = em.read_csv_metadata('path_to_csv_dir/table_B.csv', key='ID')
>>> ob = em.OverlapBlocker()
# Use word-level tokenizer
>>> C1 = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'], word_level=True, overlap_size=1)
# Use q-gram tokenizer
>>> C2 = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'], word_level=False, q_val=2)
# Include all possible missing values
>>> C3 = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'], allow_missing=True)
# Use all the cores in the machine
>>> C3 = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'], n_jobs=-1)
"""
# validate data types of standard input parameters
self.validate_types_params_tables(ltable, rtable, l_output_attrs,
r_output_attrs, l_output_prefix,
r_output_prefix, verbose, n_jobs)
# validate data types of input parameters specific to overlap blocker
self.validate_types_other_params(l_overlap_attr, r_overlap_attr,
rem_stop_words, q_val, word_level,
overlap_size)
# validate data type of allow_missing
self.validate_allow_missing(allow_missing)
# validate data type of show_progress
self.validate_show_progress(show_progress)
# validate overlap attributes
self.validate_overlap_attrs(ltable, rtable, l_overlap_attr,
r_overlap_attr)
# validate output attributes
self.validate_output_attrs(ltable, rtable, l_output_attrs,
r_output_attrs)
# get and validate required metadata
log_info(logger, 'Required metadata: ltable key, rtable key', verbose)
# # get metadata
l_key, r_key = cm.get_keys_for_ltable_rtable(ltable, rtable, logger,
verbose)
# # validate metadata
cm._validate_metadata_for_table(ltable, l_key, 'ltable', logger,
verbose)
cm._validate_metadata_for_table(rtable, r_key, 'rtable', logger,
verbose)
# validate word_level and q_val
self.validate_word_level_qval(word_level, q_val)
# do blocking
# # do projection before merge
l_proj_attrs = self.get_attrs_to_project(l_key, l_overlap_attr,
l_output_attrs)
l_df = ltable[l_proj_attrs]
r_proj_attrs = self.get_attrs_to_project(r_key, r_overlap_attr,
r_output_attrs)
r_df = rtable[r_proj_attrs]
# # case the column to string if required.
l_df.is_copy, r_df.is_copy = False, False # to avoid setwithcopy warning
ssj.dataframe_column_to_str(l_df, l_overlap_attr, inplace=True)
ssj.dataframe_column_to_str(r_df, r_overlap_attr, inplace=True)
# # cleanup the tables from non-ascii characters, punctuations, and stop words
l_dummy_overlap_attr = '@#__xx__overlap_ltable__#@'
r_dummy_overlap_attr = '@#__xx__overlap_rtable__#@'
l_df[l_dummy_overlap_attr] = l_df[l_overlap_attr]
r_df[r_dummy_overlap_attr] = r_df[r_overlap_attr]
if not l_df.empty:
self.cleanup_table(l_df, l_dummy_overlap_attr, rem_stop_words)
if not r_df.empty:
self.cleanup_table(r_df, r_dummy_overlap_attr, rem_stop_words)
# # determine which tokenizer to use
if word_level == True:
# # # create a whitespace tokenizer
tokenizer = WhitespaceTokenizer(return_set=True)
else:
# # # create a qgram tokenizer
tokenizer = QgramTokenizer(qval=q_val, return_set=True)
# # perform overlap similarity join
candset = overlap_join(l_df, r_df, l_key, r_key, l_dummy_overlap_attr,
r_dummy_overlap_attr, tokenizer, overlap_size,
'>=', allow_missing, l_output_attrs,
r_output_attrs, l_output_prefix,
r_output_prefix, False, n_jobs, show_progress)
# # retain only the required attributes in the output candidate set
retain_cols = self.get_attrs_to_retain(l_key, r_key, l_output_attrs,
r_output_attrs, l_output_prefix,
r_output_prefix)
candset = candset[retain_cols]
# update metadata in the catalog
key = get_name_for_key(candset.columns)
candset = add_key_column(candset, key)
cm.set_candset_properties(candset, key, l_output_prefix + l_key,
r_output_prefix + r_key, ltable, rtable)
# return the candidate set
return candset
def block_candset(self,
candset,
l_overlap_attr,
r_overlap_attr,
rem_stop_words=False,
q_val=None,
word_level=True,
overlap_size=1,
allow_missing=False,
verbose=False,
show_progress=True,
n_jobs=1):
"""Blocks an input candidate set of tuple pairs based on the overlap
of token sets of attribute values.
Finds tuple pairs from an input candidate set of tuple pairs such that
the overlap between (a) the set of tokens obtained by tokenizing the
value of attribute l_overlap_attr of the left tuple in a tuple pair,
and (b) the set of tokens obtained by tokenizing the value of
attribute r_overlap_attr of the right tuple in the tuple pair,
is above a certain threshold.
Args:
candset (DataFrame): The input candidate set of tuple pairs.
l_overlap_attr (string): The overlap attribute in left table.
r_overlap_attr (string): The overlap attribute in right table.
rem_stop_words (boolean): A flag to indicate whether stop words
(e.g., a, an, the) should be removed
from the token sets of the overlap
attribute values (defaults to False).
q_val (int): The value of q to use if the overlap attributes values
are to be tokenized as qgrams (defaults to None).
word_level (boolean): A flag to indicate whether the overlap
attributes should be tokenized as words
(i.e, using whitespace as delimiter)
(defaults to True).
overlap_size (int): The minimum number of tokens that must overlap
(defaults to 1).
allow_missing (boolean): A flag to indicate whether tuple pairs
with missing value in at least one of the
blocking attributes should be included in
the output candidate set (defaults to
False). If this flag is set to True, a
tuple pair with missing value in either
blocking attribute will be retained in the
output candidate set.
verbose (boolean): A flag to indicate whether the debug information
should be logged (defaults to False).
show_progress (boolean): A flag to indicate whether progress should
be displayed to the user (defaults to True).
n_jobs (int): The number of parallel jobs to be used for computation
(defaults to 1). If -1 all CPUs are used. If 0 or 1,
no parallel computation is used at all, which is useful for
debugging. For n_jobs below -1, (n_cpus + 1 + n_jobs) are
used (where n_cpus are the total number of CPUs in the
machine).Thus, for n_jobs = -2, all CPUs but one are used.
If (n_cpus + 1 + n_jobs) is less than 1, then no parallel
computation is used (i.e., equivalent to the default).
Returns:
A candidate set of tuple pairs that survived blocking (DataFrame).
Raises:
AssertionError: If `candset` is not of type pandas
DataFrame.
AssertionError: If `l_overlap_attr` is not of type string.
AssertionError: If `r_overlap_attr` is not of type string.
AssertionError: If `q_val` is not of type int.
AssertionError: If `word_level` is not of type boolean.
AssertionError: If `overlap_size` is not of type int.
AssertionError: If `verbose` is not of type
boolean.
AssertionError: If `allow_missing` is not of type boolean.
AssertionError: If `show_progress` is not of type
boolean.
AssertionError: If `n_jobs` is not of type
int.
AssertionError: If `l_overlap_attr` is not in the ltable
columns.
AssertionError: If `r_block_attr` is not in the rtable columns.
SyntaxError: If `q_val` is set to a valid value and
`word_level` is set to True.
SyntaxError: If `q_val` is set to None and
`word_level` is set to False.
Examples:
>>> import py_entitymatching as em
>>> A = em.read_csv_metadata('path_to_csv_dir/table_A.csv', key='ID')
>>> B = em.read_csv_metadata('path_to_csv_dir/table_B.csv', key='ID')
>>> ob = em.OverlapBlocker()
>>> C = ob.block_tables(A, B, 'address', 'address', l_output_attrs=['name'], r_output_attrs=['name'])
>>> D1 = ob.block_candset(C, 'name', 'name', allow_missing=True)
# Include all possible tuple pairs with missing values
>>> D2 = ob.block_candset(C, 'name', 'name', allow_missing=True)
# Execute blocking using multiple cores
>>> D3 = ob.block_candset(C, 'name', 'name', n_jobs=-1)
# Use q-gram tokenizer
>>> D2 = ob.block_candset(C, 'name', 'name', word_level=False, q_val=2)
"""
# validate data types of standard input parameters
self.validate_types_params_candset(candset, verbose, show_progress,
n_jobs)
# validate data types of input parameters specific to overlap blocker
self.validate_types_other_params(l_overlap_attr, r_overlap_attr,
rem_stop_words, q_val, word_level,
overlap_size)
# get and validate metadata
log_info(
logger, 'Required metadata: cand.set key, fk ltable, fk rtable, '
'ltable, rtable, ltable key, rtable key', verbose)
# # get metadata
key, fk_ltable, fk_rtable, ltable, rtable, l_key, r_key = cm.get_metadata_for_candset(
candset, logger, verbose)
# # validate metadata
cm._validate_metadata_for_candset(candset, key, fk_ltable, fk_rtable,
ltable, rtable, l_key, r_key, logger,
verbose)
# validate overlap attrs
self.validate_overlap_attrs(ltable, rtable, l_overlap_attr,
r_overlap_attr)
# validate word_level and q_val
self.validate_word_level_qval(word_level, q_val)
# do blocking
# # do projection before merge
l_df = ltable[[l_key, l_overlap_attr]]
r_df = rtable[[r_key, r_overlap_attr]]
# # case the overlap attribute to string if required.
l_df.is_copy, r_df.is_copy = False, False # to avoid setwithcopy warning
ssj.dataframe_column_to_str(l_df, l_overlap_attr, inplace=True)
ssj.dataframe_column_to_str(r_df, r_overlap_attr, inplace=True)
# # cleanup the tables from non-ascii characters, punctuations, and stop words
self.cleanup_table(l_df, l_overlap_attr, rem_stop_words)
self.cleanup_table(r_df, r_overlap_attr, rem_stop_words)
# # determine which tokenizer to use
if word_level == True:
# # # create a whitespace tokenizer
tokenizer = WhitespaceTokenizer(return_set=True)
else:
# # # create a qgram tokenizer
tokenizer = QgramTokenizer(qval=q_val, return_set=True)
# # create a filter for overlap similarity join
overlap_filter = OverlapFilter(tokenizer,
overlap_size,
allow_missing=allow_missing)
# # perform overlap similarity filtering of the candset
out_table = overlap_filter.filter_candset(candset,
fk_ltable,
fk_rtable,
l_df,
r_df,
l_key,
r_key,
l_overlap_attr,
r_overlap_attr,
n_jobs,
show_progress=show_progress)
# update catalog
cm.set_candset_properties(out_table, key, fk_ltable, fk_rtable, ltable,
rtable)
# return candidate set
return out_table
def test_set_sim_join():
# data to be tested.
test_scenario_1 = [(os.sep.join(['data',
'table_A.csv']), 'A.ID', 'A.name'),
(os.sep.join(['data',
'table_B.csv']), 'B.ID', 'B.name')]
data = {'TEST_SCENARIO_1': test_scenario_1}
# similarity measures to be tested.
sim_measure_types = ['COSINE', 'DICE', 'JACCARD', 'OVERLAP_COEFFICIENT']
# similarity thresholds to be tested.
thresholds = {
'JACCARD': [0.3, 0.5, 0.7, 0.85, 1],
'COSINE': [0.3, 0.5, 0.7, 0.85, 1],
'DICE': [0.3, 0.5, 0.7, 0.85, 1],
'OVERLAP_COEFFICIENT': [0.3, 0.5, 0.7, 0.85, 1]
}
# tokenizers to be tested.
tokenizers = {
'SPACE_DELIMITER': DelimiterTokenizer(delim_set=[' '],
return_set=True),
'2_GRAM': QgramTokenizer(qval=2, return_set=True),
'3_GRAM': QgramTokenizer(qval=3, return_set=True)
}
# Test each combination of similarity measure, threshold and tokenizer
# for different test scenarios.
for label, scenario in iteritems(data):
for sim_measure_type in sim_measure_types:
for threshold in thresholds.get(sim_measure_type):
for tok_type, tok in iteritems(tokenizers):
test_function = partial(test_valid_join, scenario,
sim_measure_type, (tok, threshold))
test_function.description = 'Test ' + sim_measure_type + \
' with ' + str(threshold) + ' threshold and ' + \
tok_type + ' tokenizer for ' + label + '.'
yield test_function,
# Test each similarity measure with different comparison operators.
for sim_measure_type in sim_measure_types:
for comp_op in ['>', '=']:
test_function = partial(
test_valid_join, test_scenario_1, sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.3, comp_op, False))
test_function.description = 'Test ' + sim_measure_type + \
' with comp_op ' + comp_op + '.'
yield test_function,
# Test each similarity measure with allow_missing set to True.
for sim_measure_type in sim_measure_types:
test_function = partial(
test_valid_join, test_scenario_1, sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.7, '>=', False, True))
test_function.description = 'Test ' + sim_measure_type + \
' with allow_missing set to True.'
yield test_function,
# Test each similarity measure with output attributes added.
for sim_measure_type in sim_measure_types:
test_function = partial(
test_valid_join, test_scenario_1, sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.3, '>=', False, False, [
'A.ID', 'A.birth_year', 'A.zipcode'
], ['B.ID', 'B.name', 'B.zipcode']))
test_function.description = 'Test ' + sim_measure_type + \
' with output attributes.'
yield test_function,
# Test each similarity measure with a different output prefix.
for sim_measure_type in sim_measure_types:
test_function = partial(
test_valid_join, test_scenario_1, sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.7, '>=', False, False, [
'A.birth_year', 'A.zipcode'
], ['B.name', 'B.zipcode'], 'ltable.', 'rtable.'))
test_function.description = 'Test ' + sim_measure_type + \
' with output attributes and prefix.'
yield test_function,
# Test each similarity measure with output_sim_score disabled.
for sim_measure_type in sim_measure_types:
test_function = partial(
test_valid_join, test_scenario_1, sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.7, '>=', False, False, [
'A.birth_year', 'A.zipcode'
], ['B.name', 'B.zipcode'], 'ltable.', 'rtable.', False))
test_function.description = 'Test ' + sim_measure_type + \
' with sim_score disabled.'
yield test_function,
# Test each similarity measure with n_jobs above 1.
for sim_measure_type in sim_measure_types:
test_function = partial(
test_valid_join, test_scenario_1, sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.3, '>=', False, False, [
'A.birth_year', 'A.zipcode'
], ['B.name', 'B.zipcode'], 'ltable.', 'rtable.', False, 2))
test_function.description = 'Test ' + sim_measure_type + \
' with n_jobs above 1.'
yield test_function,
# scenario where join attributes are of type int
test_scenario_2 = [(os.sep.join(['data',
'table_A.csv']), 'A.ID', 'A.zipcode'),
(os.sep.join(['data',
'table_B.csv']), 'B.ID', 'B.zipcode')]
# Test each similarity measure with join attribute of type int.
for sim_measure_type in sim_measure_types:
test_function = partial(test_valid_join, test_scenario_2,
sim_measure_type, (tokenizers['2_GRAM'], 0.3),
True)
test_function.description = 'Test ' + sim_measure_type + \
' with join attribute of type int.'
yield test_function,
# scenario where join attributes are of type float
test_scenario_3 = [(os.sep.join(['data',
'table_A.csv']), 'A.ID', 'A.hourly_wage'),
(os.sep.join(['data',
'table_B.csv']), 'B.ID', 'B.hourly_wage')]
# Test each similarity measure with join attribute of type float.
for sim_measure_type in sim_measure_types:
test_function = partial(test_valid_join, test_scenario_3,
sim_measure_type, (tokenizers['2_GRAM'], 0.3),
True)
test_function.description = 'Test ' + sim_measure_type + \
' with join attribute of type float.'
yield test_function,
# Test each similarity measure with a tokenizer with return_set flag set to False.
for sim_measure_type in sim_measure_types:
tok = QgramTokenizer(2)
test_function = partial(test_valid_join, test_scenario_1,
sim_measure_type, (tok, 0.3))
test_function.description = 'Test ' + sim_measure_type + \
' with a tokenizer with return_set flag set to False .'
yield test_function,
# Test each similarity measure with allow_empty set to True.
for sim_measure_type in sim_measure_types:
test_function = partial(
test_valid_join, test_scenario_1, sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.7, '>=', True))
test_function.description = 'Test ' + sim_measure_type + \
' with allow_empty set to True.'
yield test_function,
# Test each similarity measure with allow_empty set to True and with output attributes.
for sim_measure_type in sim_measure_types:
test_function = partial(test_valid_join, test_scenario_1,
sim_measure_type,
(tokenizers['SPACE_DELIMITER'], 0.7, '>=',
True, False, ['A.name'], ['B.name']))
test_function.description = 'Test ' + sim_measure_type + \
' with allow_empty set to True and with output attributes.'
yield test_function,
def block_tuples(self,
ltuple,
rtuple,
l_overlap_attr,
r_overlap_attr,
rem_stop_words=False,
q_val=None,
word_level=True,
overlap_size=1,
allow_missing=False):
"""Blocks a tuple pair based on the overlap of token sets of attribute
values.
Args:
ltuple (Series): The input left tuple.
rtuple (Series): The input right tuple.
l_overlap_attr (string): The overlap attribute in left tuple.
r_overlap_attr (string): The overlap attribute in right tuple.
rem_stop_words (boolean): A flag to indicate whether stop words
(e.g., a, an, the) should be removed
from the token sets of the overlap
attribute values (defaults to False).
q_val (int): A value of q to use if the overlap attributes values
are to be tokenized as qgrams (defaults to None).
word_level (boolean): A flag to indicate whether the overlap
attributes should be tokenized as words
(i.e, using whitespace as delimiter)
(defaults to True).
overlap_size (int): The minimum number of tokens that must overlap
(defaults to 1).
allow_missing (boolean): A flag to indicate whether a tuple pair
with missing value in at least one of the
blocking attributes should be blocked
(defaults to False). If this flag is set
to True, the pair will be kept if either
ltuple has missing value in l_block_attr
or rtuple has missing value in r_block_attr
or both.
Returns:
A status indicating if the tuple pair is blocked (boolean).
Examples:
>>> import py_entitymatching as em
>>> A = em.read_csv_metadata('path_to_csv_dir/table_A.csv', key='ID')
>>> B = em.read_csv_metadata('path_to_csv_dir/table_B.csv', key='ID')
>>> ob = em.OverlapBlocker()
>>> status = ob.block_tuples(A.ix[0], B.ix[0], 'address', 'address')
"""
# validate data types of input parameters specific to overlap blocker
self.validate_types_other_params(l_overlap_attr, r_overlap_attr,
rem_stop_words, q_val, word_level,
overlap_size)
# validate word_level and q_val
self.validate_word_level_qval(word_level, q_val)
# determine which tokenizer to use
if word_level == True:
# # create a whitespace tokenizer
tokenizer = WhitespaceTokenizer(return_set=True)
else:
# # create a qgram tokenizer
tokenizer = QgramTokenizer(qval=q_val, return_set=True)
# # cleanup the tuples from non-ascii characters, punctuations, and stop words
l_val = self.cleanup_tuple_val(ltuple[l_overlap_attr], rem_stop_words)
r_val = self.cleanup_tuple_val(rtuple[r_overlap_attr], rem_stop_words)
# create a filter for overlap similarity
overlap_filter = OverlapFilter(tokenizer,
overlap_size,
allow_missing=allow_missing)
return overlap_filter.filter_pair(l_val, r_val)
def test_apply_matcher_with_allow_missing(self):
tok = QgramTokenizer(qval=2, return_set=True)
sim_func = get_sim_function('JACCARD')
threshold = 0.3
comp_op = '>='
# apply sim function to the entire cartesian product to obtain
# the expected set of pairs satisfying the threshold.
cartprod = self.cartprod
cartprod['sim_score'] = cartprod.apply(lambda row: sim_func(
tok.tokenize(str(row[self.l_join_attr])),
tok.tokenize(str(row[self.r_join_attr]))),
axis=1)
# compute expected output pairs
comp_fn = COMP_OP_MAP[comp_op]
expected_pairs = set()
for idx, row in cartprod.iterrows():
if comp_fn(float(row['sim_score']), threshold):
expected_pairs.add(','.join((str(row[self.l_key_attr]),
str(row[self.r_key_attr]))))
# find pairs that need to be included in output due to
# the presence of missing value in one of the join attributes.
missing_pairs = set()
for l_idx, l_row in self.orig_ltable.iterrows():
for r_idx, r_row in self.orig_rtable.iterrows():
if (pd.isnull(l_row[self.l_join_attr]) or
pd.isnull(r_row[self.r_join_attr])):
missing_pairs.add(','.join((str(l_row[self.l_key_attr]),
str(r_row[self.r_key_attr]))))
# add the pairs containing missing value to the set of expected pairs.
expected_pairs = expected_pairs.union(missing_pairs)
# use overlap filter to obtain a candset with allow_missing set to True.
overlap_filter = OverlapFilter(tok, 1, comp_op, allow_missing=True)
candset = overlap_filter.filter_tables(self.orig_ltable, self.orig_rtable,
self.l_key_attr, self.r_key_attr,
self.l_join_attr, self.r_join_attr)
# apply a jaccard matcher to the candset with allow_missing set to True.
output_candset = apply_matcher(candset,
DEFAULT_L_OUT_PREFIX+self.l_key_attr, DEFAULT_R_OUT_PREFIX+self.r_key_attr,
self.orig_ltable, self.orig_rtable, self.l_key_attr, self.r_key_attr,
self.l_join_attr, self.r_join_attr, tok, sim_func, threshold,
comp_op, True, out_sim_score=True)
expected_output_attrs=['_id',
DEFAULT_L_OUT_PREFIX + self.l_key_attr,
DEFAULT_R_OUT_PREFIX + self.r_key_attr,
'_sim_score']
# verify whether the output table has the necessary attributes.
assert_list_equal(list(output_candset.columns.values),
expected_output_attrs)
actual_pairs = set()
for idx, row in output_candset.iterrows():
actual_pairs.add(','.join((str(row[DEFAULT_L_OUT_PREFIX + self.l_key_attr]),
str(row[DEFAULT_R_OUT_PREFIX + self.r_key_attr]))))
# verify whether the actual pairs and the expected pairs match.
assert_equal(len(expected_pairs), len(actual_pairs))
common_pairs = actual_pairs.intersection(expected_pairs)
assert_equal(len(common_pairs), len(expected_pairs))
