title: Pandas - SQL case statement equivalent
body: <p><strong>NOTE: Looking for some help on an efficient way to do this besides a mega join and then calculating the difference between dates</strong></p>
  
  <p>I have <code>table1</code> with country ID and a date (no duplicates of these values) and I want to summarize <code>table2</code> information (which has country, date, cluster_x and a count variable, where cluster_x is cluster_1, cluster_2, cluster_3) so that <code>table1</code> has appended to it each value of the cluster ID and the summarized count from <code>table2</code> where date from <code>table2</code> occurred within 30 days prior to date in <code>table1</code>.</p>
  
  <p>I believe this is simple in SQL: How to do this in Pandas?</p>
  
  <pre><code>select a.date,a.country, 
  sum(case when a.date - b.date between  1 and 30 then b.cluster_1 else 0 end) as cluster1,
  sum(case when a.date - b.date between  1 and 30 then b.cluster_2 else 0 end) as cluster2,
  sum(case when a.date - b.date between  1 and 30 then b.cluster_3 else 0 end) as cluster3
  
  from  table1 a
  left outer join table2 b
  on a.country=b.country
  
  group by a.date,a.country
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Here is a somewhat altered example. Say this is table1, an aggregated data set with date, city, cluster and count. Below it is the "query" dataset (table2). in this case we want to sum the count field from table1 for cluster1,cluster2,cluster3 (there is actually 100 of them) corresponding to the country id as long as the date field in table1 is within 30 days prior.</p>
  
  <p>So for example, the first row of the query dataset has date 2/2/2015 and country 1. In table 1, there is only one row within 30 days prior and it is for cluster 2 with count 2.</p>
  
  <p><a href="https://i.stack.imgur.com/kZWzn.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kZWzn.jpg" alt="enter image description here"></a></p>
  
  <p>Here is a dump of the two tables in CSV:</p>
  
  <pre><code>date,country,cluster,count
  2014-01-30,1,1,1
  2015-02-03,1,1,3
  2015-01-30,1,2,2
  2015-04-15,1,2,5
  2015-03-01,2,1,6
  2015-07-01,2,2,4
  2015-01-31,2,3,8
  2015-01-21,2,1,2
  2015-01-21,2,1,3
  </code></pre>
  
  <p>and table2:</p>
  
  <pre><code>date,country
  2015-02-01,1
  2015-04-21,1
  2015-02-21,2
  </code></pre>