Matching columns in pandas df complex

Question

Need some help in determining best way to approach the following problem. The following df has a set of accounts by currency displaying its interest rate and code for both credit and debit sides. The outer right 4 columns are the rates to compare to from a rate database.

df = pd.DataFrame([[5551455514,'GBP','CR',3.5,'WW',-1.0,'WW',3.5,'WW',-1.0],
                    [9631475400,'USD','CE',0,'FF',0,'CE',1.25,'FF',0],
                   [8529875405,'USD','CE',1.25,'FF',0,'CE',1.25,'FF',0],
                    [9631475400,'EUR','CE',1.25,'FF',0,'CE',1.25,'FF',0],
                    [3265415409,'USD','CE',1.25,'FF',0,'CE',1.25,'FF',0],
                    [9631475405,'USD','CE',1.25,'GG',0,'CE',1.25,'FF',0]],
columns = ['Account','Currency','Credit_Code','Credit_Rate','Debit_Code','Debit_Rate','DB_Credit_Code','DB_Credit_Rate'
           ,'DB_Debit_Code','DB_Debit_Rate'])
df

Output:


Account         Cur CR_Code CR_Rate Db_Code Db_Rate DB_CR_Code  DB_CR_Rate  DB_Db_Code  DB_Db_Rate
0   5551455514  GBP CR      3.50    WW     -1.0     WW          3.50        WW          -1.0
1   9631475400  USD CE      0.00    FF      0.0     CE          1.25        FF           0.0
2   8529875405  USD CE      1.25    FF      0.0     CE          1.25        FF           0.0
3   9631475400  EUR CE      1.25    FF      0.0     CE          1.25        FF           0.0
4   3265415409  USD CE      1.25    FF      0.0     CE          1.25        FF           0.0
5   9631475405  USD CE      1.25    GG      0.0     CE          1.25        FF           0.0

The following 2 functions were implemented to match the rate/code on both CR/Db sides to the rate database.

def matching_func_credit(row):
    if row['CR_Code'] == row['DB_CR_Code'] and row['CR_Rate'] == row['DB_CR_Rate']:
        return 'Match'
    else:
        return 'NoMatch'
df['CR_Match'] = df.apply(lambda x: matching_func_credit(x),axis=1)

def matching_func_debit(row):
    if row['Db_Code'] == row['DB_Db_Code'] and row['Db_Rate'] == row['DB_Db_Rate']:
        return 'Match'
    else:
        return 'NoMatch'
df['Db_Match'] = df.apply(lambda x: matching_func_debit(x),axis=1)

Outcome from executing both functions:

Account         Cur CR_Code CR_Rate Db_Code Db_Rate DB_CR_Code  DB_CR_Rate  DB_Db_Code  DB_Db_Rate CR_Match Db_Match
0   5551455514  GBP CR      3.50    WW     -1.0     WW          3.50        WW          -1.0 NoMatch Match
1   9631475400  USD CE      0.00    FF      0.0     CE          1.25        FF           0.0 NoMatch Match
2   8529875405  USD CE      1.25    FF      0.0     CE          1.25        FF           0.0 Match Match
3   9631475400  EUR CE      1.25    FF      0.0     CE          1.25        FF           0.0 Match Match
4   3265415409  USD CE      1.25    FF      0.0     CE          1.25        FF           0.0 Match Match 
5   9631475405  USD CE      1.25    GG      0.0     CE          1.25        FF           0.0 Match NoMatch

Problem: If a USD account ONLY that the ends in "5400", it can have a reserve or backup account (but not in every case) where the first 6 digits of the "5400" account are the same but the last 4 will be "5405".

If a reserve account is detected, the 5400's credit rate/code matching will occur on the 5405 reserve account only and not the 5400. The debit rate/code matching will occur on the 5400 account only, and not on the 5405 account if one is detected.

Summary of what needs to happen:

My first challenge is how to search if an USD account ending in 5400 has a corresponding 5405 account where they would share the same first 6 digits. Create some sort of flag, maybe a new column to return a Boolean value Y on the 5400 row that is has a backup account
How to just match the debit rate/code on the 5400 account and ignore the credits. Somehow doing an override on the credit side it to default to "IgnoreHasCorAcc"
How to match just the credit rate/code on the 5404 account and ignore its debit rate/code but also return "IgnoreHasCorAcc"

Ideally i want to enhance the 2 functions above to try and solve this.

Desired outcome:

ccount  Cur CR_Code CR_Rate Db_Code Db_Rate DB_CR_Code  DB_CR_Rate  DB_Db_Code  DB_Db_Rate  CR_Match    Db_Match    HasCorresAc
0   5551455514  GBP CR  3.50    WW  -1.0    WW  3.50    WW  -1.0    NoMatchOnCredit MatchOnDebit    N
1   9631475400  USD CE  0.00    FF  0.0 CE  1.25    FF  0.0 IgnoreHasCorAcc MatchOnDebit    Y
2   8529875405  USD CE  1.25    FF  0.0 CE  1.25    FF  0.0 MatchOnCredit   MatchOnDebit    N
3   9631475400  EUR CE  1.25    FF  0.0 CE  1.25    FF  0.0 MatchOnCredit   MatchOnDebit    N
4   3265415409  USD CE  1.25    FF  0.0 CE  1.25    FF  0.0 MatchOnCredit   MatchOnDebit    N
5   9631475405  USD CE  1.25    GG  0.0 CE  1.25    FF  0.0 MatchOnCredit   IgnoreHasCorAcc N

user_10659189 · Answer

First things first, it's better if we convert the Account column as a string, as it being an int doesn't really help us.

df['Account'] = df['Account'].apply(str)

We then get the list of possible reserves and flag them accordingly.

df['Is_Reserve'] = df['Account'].str.endswith('5405')
reserves = df[df['Is_Reserve']]['Account'].tolist()
accounts = df['Account'].tolist()

We define a custom function to check if a given account meets the following criteria:

Is in USD
Its reserve appears in the list
It ends with 5400

def has_reserve(row):
    account = row['Account']
    if account.endswith('5405'):
        return False
    reserve = account[:6] + '5405'
    if reserve in reserves and row['Currency'] == 'USD' and account.endswith('5400'):
        return True
    else:
        return False

df['Has_Reserve'] = df.apply(lambda x: has_reserve(x),axis=1)

To expand the functions as proposed in points 2 and 3:

def matching_func_credit(row):
    if row['Has_Reserve']:
        return 'IgnoreHasCorAcc'
    else:
        if row['Credit_Code'] == row['DB_Credit_Code'] and row['Credit_Rate'] == row['DB_Credit_Rate']:
            return 'MatchOnCredit'
        else:
            return 'NoMatchCredit'

df['CR_Match'] = df.apply(lambda x: matching_func_credit(x), axis=1)

def matching_func_debit(row):
    account = row['Account']
    corr_account = account[:6] + '5400'
    if row['Is_Reserve'] and corr_account in accounts:
        return 'IgnoreIsRes'
    if row['Debit_Code'] == row['DB_Debit_Code'] and row['Debit_Rate'] == row['DB_Debit_Rate']:
        return 'MatchOnDebit'
    else:
        return 'NoMatchInDebit'
    
df['Db_Match'] = df.apply(lambda x: matching_func_debit(x), axis=1)

Which gives us the desired output

      Account Currency Credit_Code  Credit_Rate Debit_Code  Debit_Rate DB_Credit_Code  DB_Credit_Rate DB_Debit_Code  DB_Debit_Rate  Is_Reserve  Has_Reserve         CR_Match         Db_Match
0  5551455514      GBP          CR         3.50         WW        -1.0             WW            3.50            WW           -1.0       False        False    NoMatchCredit     MatchOnDebit
1  9631475400      USD          CE         0.00         FF         0.0             CE            1.25            FF            0.0       False         True  IgnoreHasCorAcc     MatchOnDebit
2  8529875405      USD          CE         1.25         FF         0.0             CE            1.25            FF            0.0        True        False    MatchOnCredit     MatchOnDebit
3  9631475400      EUR          CE         1.25         FF         0.0             CE            1.25            FF            0.0       False        False    MatchOnCredit     MatchOnDebit
4  3265415409      USD          CE         1.25         FF         0.0             CE            1.25            FF            0.0       False        False    MatchOnCredit     MatchOnDebit
5  9631475405      USD          CE         1.25         GG         0.0             CE            1.25            FF            0.0        True        False    MatchOnCredit  IgnoreHasCorAcc

Matching columns in pandas df complex

1 Answers