text_plain_output_1.png
from fuzzywuzzy import fuzz

from tqdm.notebook import tqdm

import csv

import pandas as pd

import csv
import numpy as np
import re
import pandas as pd
from tqdm.notebook import tqdm
from fuzzywuzzy import fuzz
BANK_FILE = '../input/payment-id-ndsc-2020/bank_statement.csv'
CHECKOUT_FILE = '../input/payment-id-ndsc-2020/checkout.csv'

bank = pd.read_csv(BANK_FILE)
checkout = pd.read_csv(CHECKOUT_FILE)

bank = bank.sort_values(by=['stmt_amount'], ascending=True)
checkout = checkout.sort_values(by=['ckt_amount'], ascending=True)

grouped_bank = bank.groupby('stmt_amount')
grouped_checkout = checkout.groupby('ckt_amount')
amount_list = list(set(bank['stmt_amount'].tolist()))
len(amount_list)
amount_list.sort()

local_bank = bank
local_checkout = checkout
pair_list = []
paired_data = []
pbar = tqdm(total=len(bank))
skipped_amounts = []
matchless = []
matchless_count = 0
for amount in amount_list:
    bank_group = grouped_bank.get_group(amount)
    checkout_group = grouped_checkout.get_group(amount)
    skip_list = []
    matchless_count += len(checkout_group)
    matchless_min_score = 120
    while len(checkout_group) > 0 or len(bank_group) > 0:
        matchless_min_score -= 20
        for bank_index, bank_row in bank_group.iterrows():
            current = bank_index
            for checkout_index, checkout_row in checkout_group.iterrows():
                pbar.desc = 'Purge {} {} With {} data {} miss'.format(matchless_min_score, amount, len(bank_group), len(checkout_group))
                fuzz_result = fuzz.token_set_ratio(checkout_row['buyer_name'], bank_row['desc'])
                if fuzz_result >= matchless_min_score:
                    pbar.update(1)
                    checkout_group = checkout_group.drop(checkout_index)
                    bank_group = bank_group.drop(bank_index)
                    paired_data.append([bank_row['stmt_id'], checkout_row['ckt_id']])
                    matchless_count -= 1
                    break
with open('paired_data_long.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['stmt_id', 'ckt_id'])
    writer.writerows(paired_data)