title: multiconditional mapping in python pandas
body: <p>I am looking for a way to do some conditional mapping using multiple comparisons. </p>
  
  <p>I have millions and millions of rows that I am investigating using sample SQL extracts in pandas. Along with SQL extracts read into pandas DataFrames I also have some rules tables, each with a few columns (these are also loaded into dateframes). </p>
  
  <p>This is what I want to do: where a row in my SQL extract matches the conditions expressed in any one row in my rules table, I would like to generate a 1, else: 0. In the end I would like to add a column to my SQL extract called Rule Result with either 1's and 0's. </p>
  
  <p>I have got a system that works using df.merge, but it produces many many extra duplicate rows in the process that must then be removed afterwards. I am looking for a better, faster, more elegant solution and would be grateful for any suggestions. </p>
  
  <p>Here is a working example of the problem and the current solution code:</p>
  
  <pre><code>import pandas as pd
  import numpy as np 
  
  #Create a set of test data
  test_df = pd.DataFrame()
  test_df['A']   = [1,120,982,1568,29,455,None, None, None]
  test_df['B']   = ['EU','US',None, 'GB','DE','EU','US', 'GB',None]
  test_df['C']   = [1111,1121,1111,1821,1131,1111,1121,1821,1723]
  test_df['C_C'] = test_df['C']
  
  test_df
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/EO9he.png" rel="nofollow noreferrer">test_df</a></p>
  
  <pre><code>#Create a rules_table
  rules_df = pd.DataFrame()
  rules_df['A_MIN'] = [0,500,None,600,200]
  rules_df['A_MAX'] = [10,1000,500,1200,800]
  rules_df['B_B']   = ['GB','GB','US','EU','EU']
  rules_df['C_C']   = [1111,1821,1111,1111,None]
  
  rules_df
  
  def map_rules_to_df(df,rules):
  
      #create  column that mimics the index to keep track of later duplication
      df['IND'] = df.index
  
      #merge the rules with the data on C values
      df = df.merge(rules,left_on='C_C',right_on='C_C',how='left')
  
      #create a rule_result_column with a default value of zero
      df['RULE_RESULT']=0
  
      #create a mask indentifying those test_df_rows that fit with a    
      # rule_df_row
      mask = df[
          ((df['A'] &gt; df['A_MIN']) | (df['A_MIN'].isnull())) &amp;
          ((df['A'] &lt; df['A_MAX']) | (df['A_MAX'].isnull())) &amp;
          ((df['B'] == df['B_B'])  | (df['B_B'].isnull())) &amp;
          ((df['C'] == df['C_C'])  | (df['C_C'].isnull()))
          ]
  
      #use mask.index to replace 0's in the result column with a 1
      df.loc[mask.index.tolist(),'RULE_RESULT']=1
  
      #drop the redundant rule_df columns
      df = df.drop(['B_B','C_C','A_MIN','A_MAX'],axis=1)
  
      #drop duplicate rows
      df = df.drop_duplicates(keep='first')
  
      #drop rows where the original index is duplicated and the rule result
      #is zero
      df = df[(df['IND'].duplicated(keep=False)) &amp; (df['RULE_RESULT']==0) == False]
  
      #reset the df index with the original index
      df.index = df['IND'].values
  
      #drop the now redundant second index column (IND)
      df = df.drop('IND', axis=1)
  
      print('df shape',df.shape)
  
      return df
  
  #map the rules
  result_df = map_rules_to_df(test_df,rules_df)
  result_df
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/nvuGe.png" rel="nofollow noreferrer">result_df</a></p>
  
  <p>I hope I have made what I would like to do clear and thank you for your help.</p>
  
  <p>PS, my rep is non-existent, so i was not allowed to post more than two supporting images.</p>