Get Subset of MultiIndex Pandas DataFrame with Single Index Boolean Indexer
<p>If I have this dataframe</p>

<pre><code>import pandas as pd
tuples_index = [(1,1990), (2,1999), (2,2002), (3,1992), (3,1994), (3,1996)]
index = pd.MultiIndex.from_tuples(tuples_index, names=['id', 'FirstYear'])
df = pd.DataFrame([2007, 2006, 2006, 2000, 2000, 2000], index=index, columns=['LastYear'] )

df
Out[2]: 
              LastYear
id FirstYear          
1  1990           2007
2  1999           2006
   2002           2006
3  1992           2000
   1994           2000
   1996           2000
</code></pre>

<p>And i'd like to get a subset of the dataframe where the groups based on id are longer than one, I could do this, but its slow:</p>

<pre><code>%timeit df.groupby(level=0).filter(lambda x: len(x) &gt; 1)
1000 loops, best of 3: 1.36 ms per loop
</code></pre>

<p>My df has tens of millions of rows and a huge number of groups as well (most groups are of len 1) so the time adds up.  I can get a boolean indexer much more quickly this way:</p>

<pre><code>%timeit df.groupby(level=0).size() &gt; 1
1000 loops, best of 3: 364 Âµs per loop
</code></pre>

<p>But the boolean indexer only has the id as its index:</p>

<pre><code>id
1    False
2     True
3     True
</code></pre>

<p>I guess maybe I gave more context than necessary, but how can I use a boolean indexer with a single index to get a subset from a dataframe with a MultiIndex?  Desired output would be:</p>

<pre><code>              LastYear
id FirstYear          
2  1999           2006
   2002           2006
3  1992           2000
   1994           2000
   1996           2000
</code></pre>

<p>Use <code>groupby</code> and <code>transform</code> to build a mask</p>

<pre><code>df[df.groupby(level=0).transform(np.size).gt(1).values]
</code></pre>

<p><a href="http://i.stack.imgur.com/Zmwup.png" rel="nofollow"><img src="http://i.stack.imgur.com/Zmwup.png" alt="enter image description here"></a></p>

python pandas