Filter data frame from values in different columns Pandas
<p>I am very new in Pandas and hope that somebody at least can point me in the right direction. </p>

<p>Here comes the actual question: </p>

<p>df: </p>

<pre><code>           time               Area      lon        lat      mode         ID
    1993-08-01 00:34:28          A  45.627800  34.733400     false       3183
    1993-08-01 00:34:28          A  45.699600  34.639300     false       3183
    1993-08-01 00:34:28          A  45.603800  34.730600     false       3183
    1992-03-21 01:13:18          A  45.686400  34.548100      false      3184
    1992-03-21 01:13:18          A  45.702400  34.554300     false       3184
    1992-03-21 01:13:18          B  45.304784  34.626540      NaN        3184
    1992-03-21 16:13:20          A  45.633800  34.709700     false       3185
    1992-03-21 16:13:20          A  45.643400  34.709000     true        3185
    1992-03-21 16:13:20          A  45.634600  34.959500     true        3185
</code></pre>

<p>I want to filter out all instances of <strong>âIDâ</strong> that just has data from one <strong>âAreaâ</strong> (<em>either A or B</em>). The <strong>âIDâ</strong> s I want must therefore have at least one instance of <strong>âAâ</strong> <em>AND</em> <strong>âBâ</strong> to be stored in a new data frame. </p>

<p>From df presented above only the entires presented below fits the constrain: </p>

<pre><code>    1992-03-21 01:13:18          A  45.686400  34.548100      false      3184
    1992-03-21 01:13:18          A  45.702400  34.554300     false       3184
    1992-03-21 01:13:18          B  45.304784  34.626540      NaN        3184
</code></pre>

<p>Right now Iâm about to try to do a regular for loop with if statements and a list to temporary store <strong>âAreaâ</strong> attributes for each <strong>âIDâ</strong>. This feels like a very bad approach and there must be some idiomatic pandas way of doing this. </p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow"><code>pivot_table</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html" rel="nofollow"><code>dropna</code></a> for removing all values which are not in all groups:</p>

<pre><code>print (df.pivot_table(index='Area', columns='ID', values='lat').dropna(axis=1))
ID        3184
Area          
A     34.55120
B     34.62654

vals = df.pivot_table(index='Area', columns='ID', values='lat').dropna(axis=1).columns
print (vals)
Int64Index([3184], dtype='int64', name='ID')
</code></pre>

<p>Last use <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.isin.html" rel="nofollow"><code>isin</code></a>:</p>

<pre><code>print (df[df.ID.isin(vals)])
                  time Area        lon       lat   mode    ID
3  1992-03-21 01:13:18    A  45.686400  34.54810  False  3184
4  1992-03-21 01:13:18    A  45.702400  34.55430  False  3184
5  1992-03-21 01:13:18    B  45.304784  34.62654    NaN  3184
</code></pre>

python pandas