<p dir="auto">see SO <a href="https://stackoverflow.com/questions/46074372/fastest-method-of-filtering-a-pandas-data-frame-by-category/46085920#46085920" rel="nofollow">here</a></p>
<p dir="auto">These are naturally duplicated indexes. These are handled in the underyling code using a pretty inefficient method of indexing.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import pandas as pd
import numpy as np
import string

np.random.seed(1234)
df = pd.DataFrame({'categories': np.tile(np.random.choice(list(string.ascii_letters), 100000), 100),
                     'values': np.tile(np.random.choice([0,1], 100000), 100)})
df['categories'] = df['categories'].astype('category')

# using the group indexer 
In [63]: %timeit -n 3 -r 1 df.take(np.sort(np.concatenate([v for k, v in df.groupby('categories').indices.items() if k in string.ascii_lowercase])))
361 ms +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# direct map
In [51]: %timeit -n 3 -r 1 df[df.categories.map(lambda x: x in string.ascii_lowercase)]
625 ms +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# isin
In [52]: %timeit -n 3 -r 1 df[df['categories'].isin(list(string.ascii_lowercase))]
456 ms +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# groupby with filter
In [53]: %timeit -n 3 -r 1 df.groupby('categories', as_index=False).filter(lambda x: x.name in string.ascii_lowercase)
1.1 s +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# loc (fixme!)
In [54]: %timeit -n 3 -r 1 df.set_index('categories').reindex(list(string.ascii_lowercase)).reset_index()
15.4 s +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)"><pre class="notranslate"><code class="notranslate">import pandas as pd
import numpy as np
import string

np.random.seed(1234)
df = pd.DataFrame({'categories': np.tile(np.random.choice(list(string.ascii_letters), 100000), 100),
                     'values': np.tile(np.random.choice([0,1], 100000), 100)})
df['categories'] = df['categories'].astype('category')

# using the group indexer 
In [63]: %timeit -n 3 -r 1 df.take(np.sort(np.concatenate([v for k, v in df.groupby('categories').indices.items() if k in string.ascii_lowercase])))
361 ms +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# direct map
In [51]: %timeit -n 3 -r 1 df[df.categories.map(lambda x: x in string.ascii_lowercase)]
625 ms +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# isin
In [52]: %timeit -n 3 -r 1 df[df['categories'].isin(list(string.ascii_lowercase))]
456 ms +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# groupby with filter
In [53]: %timeit -n 3 -r 1 df.groupby('categories', as_index=False).filter(lambda x: x.name in string.ascii_lowercase)
1.1 s +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)

# loc (fixme!)
In [54]: %timeit -n 3 -r 1 df.set_index('categories').reindex(list(string.ascii_lowercase)).reset_index()
15.4 s +- 0 ns per loop (mean +- std. dev. of 1 run, 3 loops each)
</code></pre></div>
<p dir="auto">there’s no way to drop rows with duplicated index using <code class="notranslate">drop_duplicates</code>.</p>
<p dir="auto">we’d have to add a copy of the index as column, or do this:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df[np.logical_not(df.index.duplicated(take_last=True).values)]"><pre class="notranslate"><span class="pl-s1">df</span>[<span class="pl-s1">np</span>.<span class="pl-en">logical_not</span>(<span class="pl-s1">df</span>.<span class="pl-s1">index</span>.<span class="pl-en">duplicated</span>(<span class="pl-s1">take_last</span><span class="pl-c1">=</span><span class="pl-c1">True</span>).<span class="pl-s1">values</span>)]</pre></div>