title: Matching columns in pandas df complex
body: <p>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.</p>
  <pre><code>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
  </code></pre>
  <p>Output:</p>
  <pre><code>
  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
  </code></pre>
  <p>The following 2 functions were implemented to match the rate/code on both CR/Db sides to the rate database.</p>
  <pre><code>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)
  
  </code></pre>
  <p>Outcome from executing both functions:</p>
  <pre><code>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
  </code></pre>
  <p><strong>Problem:</strong>
  If a USD account ONLY that the ends in &quot;5400&quot;, it can have a reserve or backup account (but not in every case) where the first 6 digits of the &quot;5400&quot; account are the same but the last 4 will be &quot;5405&quot;.</p>
  <p>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.</p>
  <p>Summary of what needs to happen:</p>
  <ol>
  <li>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</li>
  <li>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 &quot;IgnoreHasCorAcc&quot;</li>
  <li>How to match just the credit rate/code on the 5404 account and ignore its debit rate/code but also return &quot;IgnoreHasCorAcc&quot;</li>
  </ol>
  <p>Ideally i want to enhance the 2 functions above to try and solve this.</p>
  <p>Desired outcome:</p>
  <pre><code>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
  </code></pre>