title: How to lookup/find the value in a two-columns range from another Dataframe? - Python Pandas Dataframe
body: <p>I have a question about Pandas Dataframe. There are two tables, 1 table is a mapping table, and 2nd table is a transactional date.</p>
  
  <p>In the mapping table, there are two columns with a range of From and To.</p>
  
  <p>Below are the two dataframes:</p>
  
  <p>1). The df1 is the mapping table with a range of account numbers to map to a specific tax type.</p>
  
  <pre><code>import pandas as pd
  import numpy as np
  df1 = pd.DataFrame({'Category':['FBT Tax','CIT','GST','Stamp Duty','Sales Tax'],
            'GL From':['10000000','20000000','30000000','40000000','50000000'],
            'GL To':['10009999','20009999','30009999','40009999','50009999']})
  
  
       Category   GL From     GL To
  0     FBT Tax  10000000  10009999
  1         CIT  20000000  20009999
  2         GST  30000000  30009999
  3  Stamp Duty  40000000  40009999
  4   Sales Tax  50000000  50009999
  </code></pre>
  
  <p>2). The df2 is the transactional table (there should be more columns I skipped for this demo), with the account number that I want to use to search/lookup in the range in df1.</p>
  
  <pre><code>df2 = pd.DataFrame({'Date':['1/10/19','2/10/19','3/10/19','10/11/19','12/12/19','30/08/19','01/07/19'],
            'GL Account':['20000456','30000199','20004689','40008900','50000876','10000325','70000199'],
            'Product LOB':['Computer','Mobile Phone','TV','Fridge','Dishwasher','Tablet','Table']})
  
         Date GL Account   Product LOB
  0   1/10/19   20000456      Computer
  1   2/10/19   30000199  Mobile Phone
  2   3/10/19   20004689            TV
  3  10/11/19   40008900        Fridge
  4  12/12/19   50000876    Dishwasher
  5  30/08/19   10000325        Tablet
  6  01/07/19   70000199        Table
  </code></pre>
  
  <p>In the df1 and df2, the account numbers are in String dtype. Hence, I created a simple function to convert into Integer.</p>
  
  <pre><code>def to_integer(col):
      return pd.to_numeric(col,downcast='integer')
  </code></pre>
  
  <p>I have tried both np.dot and .loc to map the Category column, but I encountered this error:
  <code>ValueError: Can only compare identically-labeled Series objects</code></p>
  
  <pre><code>result = np.dot((to_integer(df2['GL Account']) &gt;= to_integer(df1['GL From'])) &amp;
                   (to_integer(df2['GL Account']) &lt;= to_integer(df1['GL To'])),df1['Category'])
  
  
  result = df1.loc[(to_integer(df2['GL Account']) &gt;= to_integer(df1['GL From'])) &amp;
                   (to_integer(df2['GL Account']) &lt;= to_integer(df1['GL To'])),"Category"]
  </code></pre>
  
  <p>What I want to achieve  is like below:</p>
  
  <pre><code>       Date GL Account   Product LOB   Category
  0   1/10/19   20000456      Computer   CIT
  1   2/10/19   30000199  Mobile Phone   GST
  2   3/10/19   20004689            TV   CIT
  3  10/11/19   40008900        Fridge   Stamp Duty
  4  12/12/19   50000876    Dishwasher   Sales Tax
  5  30/08/19   10000325        Tablet   FBT Tax
  6  01/07/19   70000199        Table    NaN
  </code></pre>
  
  <p>Is there anyway to map between two dataframes based on From-To range?</p>