title: Pandas merge on two columns using date and another column
body: <p>Hello  trying to merge two data frames and sum visit counts by date and upc. </p>
  
  <ol>
  <li>Transaction data (date,upc,sales) 200k rows x 3 columns</li>
  <li>Visits counts(date, upc, visit count) 2 million+ rows x 3 columns </li>
  </ol>
  
  <p>I’ve tried this</p>
  
  <pre><code>df3 = pandas.merge(df1,df2, on = ['upc','date'], how = 'left') 
  </code></pre>
  
  <p>Result: merge executes but it does not sum up by date or upc </p>
  
  <p>I also tried </p>
  
  <pre><code>df3 = pandas.merge(df1,df2, left_on = ['date'], right_on ['upc'] how = 'left')
  </code></pre>
  
  <p>and that didn’t work. </p>
  
  <pre><code>df3 = pandas.merge(df1,df2, left_on = ['date','upc'], right_on ['date','upc'] how = 'left')
  </code></pre>
  
  <p>and that didn’t work.</p>
  
  <p>I also tried </p>
  
  <pre><code>df3 = pandas.merge(df1,df2, on = ['date'], how = 'left')
  </code></pre>
  
  <p>and I kept returning an error message. Based on the error message it looked like I needed to convert one of the dates in the data frames to pandas dtype. </p>
  
  <p>I made that change and returned the same results as my first try. The merge worked but it did not sum up the results. I tried converting both dates in both data frames to <code>astype(str)</code> and that didn’t work. I learned if both dates have the same date format dtype or <code>astype(str)</code> I return a memory error message. Merge would fail. </p>
  
  <p>I was successful with merging using the upc only but this creates an issue in my data, I return duplicate visit numbers because a upc is repeated in the transaction due to the date column. </p>
  
  <p>End of the day what I need is something similar to a sumif function in excel. </p>
  
  <p>I need to combine the two data sets by summarizing the total visits by each upc for each day and keeping transaction data unchanged or left join in terms of sql</p>
  
  <p>Sample data</p>
  
  <pre><code>df1
    Date         upc       sales
  0 09/01/2016   A01234    1000
  1 09/02/2016   A01234    500
  2 09/10/2016   A56789    1200
  
  df2
    Date         upc         visits
  0 09/01/2016   A01234      10
  1 09/02/2016   A01234      25
  2 09/05/2016   A56789      26
  3 09/10/2016   A56789      32
  
  
  df3
    Date         upc       sales   visits
  0 09/01/2016   A01234    1000    10
  1 09/02/2016   A01234    500     25
  2 09/10/2016   A56789    1200    32
  </code></pre>
  
  <p>Wondering if pandasql package is what I need to use. Any help is appreciated</p>