Is .isin() faster than .query()

Question

Question:

Hi,

When searching for methods to make a selection of a dataframe (being relatively unexperienced with Pandas), I had the following question:

What is faster for large datasets - .isin() or .query()?

Query is somewhat more intuitive to read, so my preferred approach due to my line of work. However, testing it on a very small example dataset, query seems to be much slower.

Is there anyone who has tested this properly before? If so, what were the outcomes? I searched the web, but could not find another post on this.

See the sample code below, which works for Python 3.8.5.

Thanks a lot in advance for your help!

Code:

# Packages
import pandas as pd
import timeit
import numpy as np


# Create dataframe
df = pd.DataFrame({'name': ['Foo', 'Bar', 'Faz'],
               'owner': ['Canyon', 'Endurace', 'Bike']},
                index=['Frame', 'Type', 'Kind'])

# Show dataframe
df

# Create filter
selection = ['Canyon']

# Filter dataframe using 'isin' (type 1)
df_filtered = df[df['owner'].isin(selection)] 

%timeit df_filtered = df[df['owner'].isin(selection)]
213 µs ± 14 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)


# Filter dataframe using 'isin' (type 2)
df[np.isin(df['owner'].values, selection)]

%timeit df_filtered = df[np.isin(df['owner'].values, selection)]
128 µs ± 3.11 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)


# Filter dataframe using 'query'
df_filtered = df.query("owner in @selection")

%timeit df_filtered = df.query("owner in @selection")
1.15 ms ± 9.35 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Answer 1

The best test in real data, here fast comparison for 3k, 300k,3M rows with this sample data:

selection = ['Hedge']

df = pd.concat([df] * 1000, ignore_index=True)
In [139]: %timeit df[df['owner'].isin(selection)]
449 µs ± 58 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [140]: %timeit df.query("owner in @selection")
1.57 ms ± 33.4 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

df = pd.concat([df] * 100000, ignore_index=True)
In [142]: %timeit df[df['owner'].isin(selection)]
8.25 ms ± 66.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [143]: %timeit df.query("owner in @selection")
13 ms ± 1.05 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)

df = pd.concat([df] * 1000000, ignore_index=True)
In [145]: %timeit df[df['owner'].isin(selection)]
94.5 ms ± 9.28 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [146]: %timeit df.query("owner in @selection")
112 ms ± 499 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
    

If check docs:

DataFrame.query() using numexpr is slightly faster than Python for large frames

Conclusion - The best test in real data, because depends of number of rows, number of matched values and also by length of list selection.

Answer 2

A perfplot over some generated data:

Assuming some hypothetical data, as well as a proportionally increasing selection size (10% of frame size).

Sample data for n=10:

df:

       name  owner
0  Constant  JoVMq
1  Constant  jiKNB
2  Constant  WEqhm
3  Constant  pXNqB
4  Constant  SnlbV
5  Constant  Euwsj
6  Constant  QPPbs
7  Constant  Nqofa
8  Constant  qeUKP
9  Constant  ZBFce

Selection:

['ZBFce']

Performance reflects the docs. At smaller frames the overhead of query is significant over isin However, at frames around 200k rows the performance is comparable to isin and at frames around 10m rows query starts to become more performant.

I agree with @jezrael that, this is, as with most pandas runtime problems, very data dependent, and the best test would be to test on real datasets for a given use case and make a decision based on that.

---

Edit: Included @AlexanderVolkovsky's suggestion to convert selection to a set and use apply + in:

---

Perfplot Code:

import string

import numpy as np
import pandas as pd
import perfplot

charset = list(string.ascii_letters)

np.random.seed(5)


def gen_data(n):
    df = pd.DataFrame({'name': 'Constant',
                       'owner': [''.join(np.random.choice(charset, 5))
                                 for _ in range(n)]})
    selection = df['owner'].sample(frac=.1).tolist()
    return df, selection, set(selection)


def test_isin(params):
    df, selection, _ = params
    return df[df['owner'].isin(selection)]


def test_query(params):
    df, selection, _ = params
    return df.query("owner in @selection")


def test_apply_over_set(params):
    df, _, set_selection = params
    return df[df['owner'].apply(lambda x: x in set_selection)]


if __name__ == '__main__':
    out = perfplot.bench(
        setup=gen_data,
        kernels=[
            test_isin,
            test_query,
            test_apply_over_set
        ],
        labels=[
            'test_isin',
            'test_query',
            'test_apply_over_set'
        ],
        n_range=[2 ** k for k in range(25)],
        equality_check=None
    )
    out.save('perfplot_results.png', transparent=False)