title: Merge left with mixed number of identifiers
body: <p>I have a maptable and a <code>df</code> where I want to apply a left merge to map an additional column on a set of one or more columns. However, in my case the available identifiers differ per row. </p>
  
  <p>Here is an example:</p>
  
  <pre><code>maptable =
  
    asset_class currency target
  0      Equity      EUR     t1
  1          FX      EUR     t2
  2       Rates      USD     t3
  3       Rates              t3o
  4       Bonds              t4o
  5       Bonds      AAA     t4
  </code></pre>
  
  <p>Suppose we have the following df:</p>
  
  <pre><code>df =
  
    asset_class currency
  0      Equity      EUR
  1      Equity      USD
  2      Equity      GBP
  3       Rates      EUR
  4       Rates      USD
  5       Rates      GBP
  6       Bonds      AAA
  7       Bonds      BBB
  8       Bonds      CCC
  </code></pre>
  
  <p>In that case, the desired result should be:</p>
  
  <pre><code>  asset_class currency target
  0      Equity      EUR     t1   (we have Equity+EUR)
  1      Equity      USD    NaN   (we don't have Equity+USD and also not Equity)
  2      Equity      GBP    NaN   (we don't have Equity+GBP and also not Equity)
  3       Rates      EUR    t3o   (we don't have Rates+EUR, but we do have Rates)
  4       Rates      USD     t3   (we have Rates+USD)
  5       Rates      GBP    t30   (we don't have Rates+GBP, but we do have Rates)
  6       Bonds      AAA     t4   (we have Bonds+AA)
  7       Bonds      BBB    t4o   (we don't have Bonds+BBB, but we do have Bonds)
  8       Bonds      CCC    t4o   (we don't have Bonds+CCC, but we do have Bonds)
  </code></pre>
  
  <p>Simply applying a merge left on asset_class and currency will not work as the cases where one identifier column out of two has values, it will be ignored:</p>
  
  <pre><code>df_m = df.merge(maptable, how='left', on=['asset_class','currency'])
  </code></pre>
  
  <p>Also, very important, is that we need to overwrite in the case when a target col is already mapped if we are using more identifier columns. For instance, using 'asset_class' and 'currency' has more priority than just mapping on 'asset_class'. For this reason <code>fillna</code> will not work as we actually need an <code>update</code>.</p>
  
  <p>How can this be achieved in an efficient way?</p>
  
  <hr>
  
  <p><strong>Example data</strong></p>
  
  <p>You can recreate the example above as follows:</p>
  
  <pre><code>import pandas as pd
  
  maptable = pd.DataFrame({
      'asset_class': ['Equity', 'FX',   'Rates', 'Rates', 'Bonds', 'Bonds'],
      'currency':    ['EUR',    'EUR',  'USD',   '',      '',      'AAA'],
      'target':      ['t1',     't2',   't3',    't3o',    't4o',    't4']
  })
  
  df = pd.DataFrame({
      'asset_class': ['Equity', 'Equity', 'Equity', 'Rates', 'Rates', 'Rates', 'Bonds', 'Bonds', 'Bonds'],
      'currency':    ['EUR', 'USD', 'GBP', 'EUR', 'USD', 'GBP', 'AAA', 'BBB', 'CCC'],
  })
  </code></pre>
  
  <hr>
  
  <p><strong>What I tried so far</strong></p>
  
  <p>Here is what I tried so far (but this is really rudimentary):</p>
  
  <pre><code>def merge_mix(dl, dr, target_cols, id_cols):
      """Apply a merge left with a mixed number of identifiers
  
      :param dl:  target DataFrame on which we want to map the target_cols, contains id_cols but might also
      contain target_cols. If non-NA matching target values are found in dr, it will overwrite the values for the
      index/col combinations
      :param dr:  mapping DataFrame that contains both target_cols and id_cols
      :param target_cols: list of column names that we want to map from the dr
      :param id_cols: list of columns that we want to use as identifier, can be empty
      """
      def is_empty(x):
          """Check if empty"""
          if x is not None:
              if isinstance(x, str) and x != '':
                  return False
              else:
                  if not pd.np.isnan(value):
                      return False
          return True
  
      # Append target col
      for target_col in target_cols:
          if target_col not in dl:
              dl.insert(loc=len(dl.columns), column=target_col, value=None)
  
      # Clean dr
      dr = dr[id_cols + target_cols]
      dr = dr.drop_duplicates(keep='last')
  
      # Loop over all the indices and check which combinations exists
      for index in dr.index:
          combo_cols = []
          for col in id_cols:
              value = dr.loc[index, col]
  
              # Add combination if value is not empty
              if not is_empty(value):
                  combo_cols.append(col)
  
          # The combination for this index
          dr.loc[index, 'combo_cols'] = "+".join(combo_cols)
          dr.loc[index, 'combo_count'] = len(combo_cols)
  
      # Get the unique combo cols combinations. Take first the least granular and then work towards more granular
      # as we are working with .update and not with .merge
      combos_count = list(dr['combo_count'].unique())  # Unique list
      combos_count = [x for x in combos_count if x &gt; 0]  # Take out zero count combo cols
      combos_count.sort(reverse=False)  # Sort to move the least granular first
  
      for count in combos_count:
  
          # For a given count, check all combo combinations with this count
          dr_cc = dr[dr['combo_count'] == count]
          unique_combo_cols_cc = list(dr_cc['combo_cols'].unique())
  
          for combo_col in unique_combo_cols_cc:
  
              # Maptable for given combo col
              dr_uc_cc = dr_cc[dr_cc['combo_cols'] == combo_col]
              dr_uc_cc = dr_uc_cc.drop_duplicates(keep='last')
  
              # Set index on the id cols for this combo combination
              id_cols_uc_cc = combo_col.split('+')
              dl = dl.set_index(id_cols_uc_cc)
              dr_uc_cc = dr_uc_cc.set_index(id_cols_uc_cc)
  
              # Update matching row, cols
              dl.update(dr_uc_cc[target_cols])
              dl = dl.reset_index()
  
      return dl
  </code></pre>