Realtime Fuzzy-Join in Pathway

Part 2: Fuzzy Join - reconciliation with audit: when the computer is not enough.

In this article, we are going to show you how Pathway interacts with incremental data flows with a feedback loop.

In the first part of this showcase we explained how smart_fuzzy_join may be helpful in bookkeeping. Previously, we had a simple pipeline that matched entries of two different tables, such as two logs of bank transfers, in two different formats. Many matchings can be inferred automatically, but some can be really tricky without help: while the fans of Harry Potter can instantaneously make the connection between 'You-Know-Who' and 'Voldemort', it is impossible for a computer to do so, at least without help.

Human audit is unavoidable in many areas such as accounting or banking. As such, we extend our pipeline with an auditor that supervises the process of reconciliation. The auditor may help the system by providing some hints, i.e. suggesting difficult matchings by hand.

Feedback loop in Pathway

Graph image

This figure represents an architecture with a feedback loop to understand how the pieces work together.

Reconciliation by SmartFuzzyJoin lies at the heart of the architecture:

  • it consumes inputs from 3 sources:
    • two tables with transactions in different formats;
    • a table with manual corrections provided by the auditor;
  • it outputs one table with matched records.

You might think of the auditor as a simple automaton. Either they are satisfied with presented results and simply save them in some storage, or they provide some hints for the algorithm to find a better matching.

Note: Although the architecture contains a feedback loop, all tables here are either inputs or outputs of the system.

The data

Human audit is certainly needed to handle the sample dataset below.

Recipient and sender in a 'standard' CSV format

idrecipientsender
1Bill H.Nancy R.
2Harry P.Hermione G.
3Julian S.Dick F.

Messages describing the transactions

idmessage
ADear William, thank you for your patience. Regards, Ann
BDear Colleague! I think they might have sent me your particle! Yours, Richard
CPaying back for two Chocolate Frogs, cheers Hermione!

Automatic reconciliation

Let's see how many records we can match without any human help. We reuse code from Part 1 of this showcase.

import pandas as pdimport pathway as pw

We need our csv files:

%%capture --no-display!wget https://public-pathway-releases.s3.eu-central-1.amazonaws.com/data/fuzzy_join_part_2_transactionsA.csv!wget https://public-pathway-releases.s3.eu-central-1.amazonaws.com/data/fuzzy_join_part_2_transactionsB.csv
index_left = pw.debug.table_from_markdown(    """        | index    0   | 1    1   | 2    2   | 3    """)index_right = pw.debug.table_from_markdown(    """        | index    0   | A    1   | B    2   | C    """)def match_transactions(transactionsA, transactionsB, by_hand_matching):    matching = pw.ml.smart_table_ops.fuzzy_match_tables(        transactionsA, transactionsB, by_hand_match=by_hand_matching    )    matching = matching.join(transactionsA, matching.left == transactionsA.id).select(        left_key=transactionsA.key,        right=matching.right,        weight=matching.weight,        left=matching.left,    )    matching = matching.join(transactionsB, matching.right == transactionsB.id).select(        left_key=matching.left_key,        right_key=transactionsB.key,        weight=matching.weight,        left=matching.left,    )    transactionsA_reconciled = (        pw.Table.empty(left_key=str, right_key=str, confidence=float)        .update_rows(            transactionsA.select(left_key=None, right_key=None, confidence=0.0)        )        .update_rows(            matching.select(                matching.left_key, matching.right_key, confidence=matching.weight            ).with_id(matching.left)        )    )    return transactionsA_reconcileddef reconcile_transactions(    formatA="fuzzy_join_part_2_transactionsA.csv",    formatB="fuzzy_join_part_2_transactionsB.csv",    audit=None,):    transactionsA = pw.csv.read(formatA)    transactionsB = pw.csv.read(formatB)    by_hand_matching = pw.Table.empty(left=pw.Pointer, right=pw.Pointer, weight=float)    if audit is not None:        by_hand_matching = pw.csv.read(audit)        by_hand_matching = by_hand_matching.select(            left=transactionsA.pointer_from(by_hand_matching.left),            right=transactionsB.pointer_from(by_hand_matching.right),            weight=by_hand_matching.weight,        )    transactionsA_reconciled = match_transactions(        transactionsA, transactionsB, by_hand_matching    )    by_hand_matching = by_hand_matching.join(        index_left, by_hand_matching.left == index_left.id    ).select(left_key=index_left.index, right=by_hand_matching.right)    by_hand_matching = by_hand_matching.join(        index_right, by_hand_matching.right == index_right.id    ).select(left_key=by_hand_matching.left_key, right_key=index_right.index)    return transactionsA_reconciled, by_hand_matchingmatching, _ = reconcile_transactions()pw.debug.compute_and_print(matching)
            | left_key | right_key | confidence
^YHZBTNY... |          |           | 0.0
^8JFNKVV... |          |           | 0.0
^2TMTFGY... | 2        | C         | 0.5

Not a perfect matching. It seems that the help of an auditor is needed.

Incremental reconciliation with an auditor

The correct matching is 1 - A, 2 - C and 3 - B. Why? Tip 1, Tip 2.

Previously, the algorithm identified matching 2 - C correctly but failed to find the connections between the other pairs. Now, we run it with a hint - feedback from an auditor.

To include the hint (nothing complicated), we just need to launch our function with the parameter audit:

%%capture --no-display!wget https://public-pathway-releases.s3.eu-central-1.amazonaws.com/data/fuzzy_join_part_2_audit1.csv
matching, suggested_matchings = reconcile_transactions(    audit="fuzzy_join_part_2_audit1.csv")

Here is the author's feedback, the pair 1 - A:

pw.debug.compute_and_print(suggested_matchings)
            | left_key | right_key
^71BYNRW... | 1        | A

Given this feedback, we check that the new matching took into account this pair:

pw.debug.compute_and_print(matching)
            | left_key | right_key | confidence
^YHZBTNY... |          |           | 0.0
^8JFNKVV... | 1        | A         | 1
^2TMTFGY... | 2        | C         | 0.5

Still not perfect but better. It seems that more help from the auditor is needed. Now, with one more extra hint the algorithm matches all the records correctly.

%%capture --no-display!wget https://public-pathway-releases.s3.eu-central-1.amazonaws.com/data/fuzzy_join_part_2_audit2.csv
matching, suggested_matchings = reconcile_transactions(    audit="fuzzy_join_part_2_audit2.csv")

This time we provide the last pair, 3 - B:

pw.debug.compute_and_print(suggested_matchings)
            | left_key | right_key
^71BYNRW... | 1        | A
^M7BS2AM... | 3        | B

Given those, we should obtain a full --and hopefully correct -- matching.

pw.debug.compute_and_print(matching)
            | left_key | right_key | confidence
^8JFNKVV... | 1        | A         | 1
^2TMTFGY... | 2        | C         | 0.5
^YHZBTNY... | 3        | B         | 1

Bingo!

It may sound long and tedious but in practice most of the matchings should have been done automatically. This process is only performed for the few remaining cases, where the linkages are hard to make.

Conclusion

In conclusion, writing pipelines with a feedback loop is as easy as can be. When writing such a data processing algorithm, a tip is to always clearly separate inputs from outputs. It is important because the Pathway engine observes inputs for any changes and recalculates parts of the computation when needed.

In the next chapter, we will show you how to make a Pathway installation which provides a full Fuzzy-Matching application, complete with frontend. (Coming soon!) Demo animation