title: Merge 2 dataframes on notnull values
body: <p>I have to merge two df. One is my main df, the other has many NaN</p>
  <p>df1 sample:</p>
  <pre><code>code        hotel_region   hotel_country        chain_name   brand_name
  9737              EUROPE       ESTONIA        Bridgestreet        NaN
  5397       LATIN AMERICA    COSTA RICA         Independent   No Brand
  2392       LATIN AMERICA         ARUBA        DIVI RESORTS        NaN
  9776       LATIN AMERICA        BRAZIL         Independent   W Hotels
  4720       LATIN AMERICA     ARGENTINA         Independent   No Brand
  </code></pre>
  <p>df2 sample:</p>
  <pre><code>r_id  hotel_region    hotel_country                   chain_name     brand_name
  78   LATIN AMERICA         HONDURAS     Barcelo Hotels and Resorts        NaN
  92   LATIN AMERICA     SANDWICH ISL     Barcelo Hotels and Resorts        NaN
  151            NaN              NaN                   Bridgestreet        NaN
  117  NORTH AMERICA           CANADA                Magnuson Hotels        NaN
  47   LATIN AMERICA           BRAZIL                            NaN   W Hotels 
  </code></pre>
  <p>The result that I would like to have is roughly this:</p>
  <pre><code>code   hotel_region   hotel_country     chain_name   brand_name  r_id
  9737         EUROPE       ESTONIA     Bridgestreet        NaN     151
  9776  LATIN AMERICA        BRAZIL      Independent   W Hotels      47
  </code></pre>
  <p>The merge should just &quot;ignore&quot; NaN values and merge only where column value is not NaN. I tried different things, however data in df2 has tens of possibilities where NaN values could appear. df1 has 168k lines and df2 has roughly 170, and <code>r_id</code> should be associated to any <code>code</code> that matches all non-NaN values. Does anyone have any idea about how to do this efficiently?</p>
  <p>After extensive research about different approaches it seems that a &quot;magical&quot; way to ignore NaNs probably does not exist. I thought about applying a mask on df2 and split in groups, loop through them, merge each group with df1 and remove duplicates afterwards. I.e. here I would have</p>
  <pre><code>(True, True, True,  True, False),
  (True, False, False, True, False),
  (True, True, True, False, True)
  </code></pre>
  <p>However I am not sure if this is the best approach and frankly I am baffled at how I should implement it.</p>
  <h2>Edit - how I ended up solving this</h2>
  <p>I ended up exploring the approach above - applying a mask on <code>df2</code>, splitting it according to the mask, merging it with <code>df1</code>.</p>
  <p>Step 1: create mask</p>
  <pre><code>masked = df2[['hotel_region', 'hotel_country', 'chain_name', 'brand_name']]
  
  mask = pd.notnull(masked)
  </code></pre>
  <p>Step 2: group df, according to <code>NaN</code> (<code>= False</code>) values</p>
  <pre><code>    group_mask = mask.groupby(['hotel_region','hotel_country', 'chain_name','brand_name']).count().reset_index()
  </code></pre>
  <p>Step 3: append groups of columns in <code>df2</code> to an array <code>split_groups</code> according to true/false values in <code>group_mask</code></p>
  <pre><code>split_groups = []
  
  for index, row in group_mask.iterrows():
      bool_groups = []
      # If the whole group is False, then cannot be taken in consideration, 
      # as it would result in a merge on the whole df1
      if not any(row.to_dict().values()):
          pass
      else:
          bool_groups.append(
                  [key for key in row.to_dict().keys() if row.to_dict()[key] == False])
          bool_groups.append(
                  [key for key in row.to_dict().keys() if row.to_dict()[key] == True])
          split_groups.append(bool_groups)
  </code></pre>
  <p>Step 4: create array of dfs split according to columns in <code>df2</code> where all values are not False</p>
  <pre><code>mps = []
  &quot;&quot;&quot;
  First, we extract rows where i[0] is null. In the resulting df, we extract rows
  where i[1] is not null. Then, we drop all columns with na values. In this way
  we retain only columns good for the merge. 
  &quot;&quot;&quot;
  for i in split_groups:
      df = df2[(df2[i[0]].isnull()).all(1)]
      df = df[(df[i[1]].notnull()).all(1)]
      df = df.dropna(axis='columns', how='all')
      mps.append(df)
  </code></pre>
  <p>Step 5: Loop through the array and merge the 2 dfs according to existing columns</p>
  <pre><code>merged_dfs = []
  
  for i in range(len(mps)):
      merged_dfs.append(df1.merge(mps[i], on=(split_groups[i][1]), how='left'))
  </code></pre>
  <p>Step 6: concat dfs in <code>merged_dfs</code></p>
  <pre><code>merged_df = pd.concat(merged_dfs, sort=False)
  </code></pre>
  <p>Step 7: drop duplicates</p>
  <pre><code>merged_df = merged_df.drop_duplicates()
  </code></pre>
  <p>Step 8 is calling <code>merged_df.columns.tolist()</code> and retaining only columns which are useful to the final result.</p>
  <p>I think this approach is not optimal - if anyone has any ideas about how to make this more efficient I will really appreciate. Thanks <a href="https://stackoverflow.com/users/1296779/qingshan">@qingshan</a> for the suggestion on looping, it gave me the hint to eventually loop through different lists of dfs.</p>