title: python pandas countifs using multiple criteria AND multiple data frames
body: <p>Trying to create--in Python using multiple data frames--the equivalent of a countifs in Excel that would span multiple sheets. </p>
  
  <p>I need a new column count of records on <em>another data frame</em> based on criteria from the <strong>current data frame</strong>.</p>
  
  <p>See <a href="https://i.stack.imgur.com/BjgeM.png" rel="nofollow noreferrer">Excel impression</a> of what I want to do in python, also <a href="https://i.stack.imgur.com/fex5j.png" rel="nofollow noreferrer">here</a>.</p>
  
  <p>My goal?</p>
  
  <ul>
  <li>Count exams on <em>students</em> data frame</li>
  <li>by Student ID</li>
  <li>with exam date >= enroll date</li>
  <li>with exam date &lt;= detail date</li>
  <li>with exam grade >= 70</li>
  </ul>
  
  <p>Basically the Excel equivalent would be...</p>
  
  <blockquote>
    <p>=COUNTIFS(Summary!$B$1:$B$11, ">="&amp;Detail!B2, 
                               Summary!$B$1:$B$11, "&lt;="&amp;Detail!C2, 
                               Summary!$C$1:$C$11, ">="&amp;70, 
                               Summary!$A$1:$A$11, "="&amp;Detail!A2)</p>
  </blockquote>
  
  <p>...where Summary is the primary data frame and Detail is the secondary data frame where I want to count records.</p>
  
  <p>Found these answers in my research:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/11035767/sumifs-function-in-python">sumifs function in python</a></li>
  <li><a href="https://stackoverflow.com/questions/2643850/what-is-a-good-way-to-do-countif-in-python">What is a good way to do countif in Python</a></li>
  <li><a href="https://stackoverflow.com/questions/20995196/python-pandas-counting-and-summing-specific-conditions">Python Pandas counting and summing specific conditions</a></li>
  </ul>
  
  <p>Not quite what I'm looking for, because they don't span multiple data frames. I was able to create a basic countifs for a <strong>singular</strong> data frame:</p>
  
  <pre><code>sum(1 for x in students['Student ID'] if x == 1)
  sum(1 for x in exams['Exam Grade'] if x &gt;= 70)
  </code></pre>