Pandas DataFrame slicing based on logical conditions?
<p>I have this dataframe called data:</p>

<pre><code>       Subjects  Professor  StudentID
8     Chemistry       Jane        999
1     Chemistry       Jane       3455
0     Chemistry     Joseph       1234
2       History       Jane       3455
6       History      Smith        323
7       History      Smith        999
3   Mathematics        Doe      56767
10  Mathematics   Einstein       3455
5       Physics   Einstein       2834
9       Physics      Smith        323
4       Physics      Smith        999
</code></pre>

<p>I want to run this query "Professors with at least 2 classes with 2 or more of the same students". Desired Output</p>

<pre><code>Smith: Physics, History, 323, 999
</code></pre>

<p>I am familiar with SQL and could have done this easily, but I am still beginner in Python. How to achieve this output in Python? Another line of thought is to convert this dataframe into SQL database and have a SQL interface through python to run queries. Is there a way to accomplish that?</p>

<p>Solution with <a href="http://pandas.pydata.org/pandas-docs/stable/groupby.html#filtration" rel="nofollow"><code>filter</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html" rel="nofollow"><code>value_counts</code></a>:</p>

<pre><code>df1 = df.groupby('Professor').filter(lambda x: (len(x.Subjects) &gt; 1) &amp; 
                                               ((x.StudentID.value_counts() &gt; 1).sum() &gt; 1))
print (df1)
  Subjects Professor  StudentID
6  History     Smith        323
7  History     Smith        999
9  Physics     Smith        323
4  Physics     Smith        999
</code></pre>

<p>and with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.duplicated.html" rel="nofollow"><code>duplicated</code></a>:</p>

<pre><code>df1 = df.groupby('Professor').filter(lambda x: (len(x.Subjects) &gt; 1) &amp; 
                                               (x.StudentID.duplicated().sum() &gt; 1))
print (df1)
  Subjects Professor  StudentID
6  History     Smith        323
7  History     Smith        999
9  Physics     Smith        323
4  Physics     Smith        999
</code></pre>

<p>EDIT by comment:</p>

<p>You can return custom output from custom function and then remove <code>NaN</code> rows by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.dropna.html" rel="nofollow"><code>Series.dropna</code></a>:</p>

<pre><code>df.StudentID = df.StudentID.astype(str)

def f(x):
    if (len(x.Subjects) &gt; 1) &amp; (x.StudentID.duplicated().sum() &gt; 1):
        return ', '.join((x.Subjects.unique().tolist() + x.StudentID.unique().tolist()))

df1 = df.groupby('Professor').apply(f).dropna()
df1 = df1.index.to_series() + ': ' + df1
print (df1)
Professor
Smith    Smith: History, Physics, 323, 999
dtype: object
</code></pre>

python mysql pandas