title: How to do/workaround a conditional join in python Pandas?
body: <p>I am trying to calculate time-based aggregations in Pandas based on date values stored in a separate tables.</p>
  <p>The top of the first table <code>table_a</code> looks like this:</p>
  <pre><code>    COMPANY_ID  DATE            MEASURE
      1   2010-01-01 00:00:00     10
      1   2010-01-02 00:00:00     10
      1   2010-01-03 00:00:00     10
      1   2010-01-04 00:00:00     10
      1   2010-01-05 00:00:00     10
  </code></pre>
  <p>Here is the code to create the table:</p>
  <pre><code>    table_a = pd.concat(\
      [pd.DataFrame({'DATE': pd.date_range(&quot;01/01/2010&quot;, &quot;12/31/2010&quot;, freq=&quot;D&quot;),\
      'COMPANY_ID': 1 , 'MEASURE': 10}),\
      pd.DataFrame({'DATE': pd.date_range(&quot;01/01/2010&quot;, &quot;12/31/2010&quot;, freq=&quot;D&quot;),\
      'COMPANY_ID': 2 , 'MEASURE': 10})])
  </code></pre>
  <p>The second table, <code>table_b</code>, looks like this:</p>
  <pre><code>        COMPANY     END_DATE
          1   2010-03-01 00:00:00
          1   2010-06-02 00:00:00
          2   2010-03-01 00:00:00
          2   2010-06-02 00:00:00
  </code></pre>
  <p>and the code to create it is:</p>
  <pre><code>    table_b = pd.DataFrame({'END_DATE':pd.to_datetime(['03/01/2010','06/02/2010','03/01/2010','06/02/2010']),\
                      'COMPANY':(1,1,2,2)})
  </code></pre>
  <p>I want to be able to get the sum of the 'measure' column for each 'COMPANY_ID' for each 30-day period prior to the 'END_DATE' in <code>table_b</code>.</p>
  <p>This is (I think) the SQL equivalent:</p>
  <pre><code> select
         b.COMPANY_ID,
         b.DATE
         sum(a.MEASURE) AS MEASURE_TO_END_DATE
   from table_a a, table_b b
   where a.COMPANY = b.COMPANY and
         a.DATE &lt; b.DATE and
         a.DATE &gt; b.DATE - 30  
   group by b.COMPANY;
  </code></pre>