Pandas Dataframe performance vs list performance
<p>I'm comparing two dataframes to determine if rows in df1 begin any row in df2. df1 is on the order of a thousand entries, df2 is in the millions.</p>

<p>This does the job but is rather slow. </p>

<pre><code>df1['name'].map(lambda x: any(df2['name'].str.startswith(x)))
</code></pre>

<p>When run on a subset of df1 (10 items), this is the result:</p>

<pre><code>35243     True
39980    False
40641    False
45974    False
53788    False
59895     True
61856    False
81083     True
83054     True
87717    False
Name: name, dtype: bool
Time: 57.8873581886 secs
</code></pre>

<p>When I converted df2 to a list, it runs much faster:</p>

<pre><code>df2_list = df2['name'].tolist()

df1['name'].map(lambda x: any(item.startswith(x + ' ') for item in df2_list))

35243     True
39980    False
40641    False
45974    False
53788    False
59895     True
61856    False
81083     True
83054     True
87717    False
Name: name, dtype: bool
Time: 33.0746209621 secs
</code></pre>

<p>Why is it quicker to iterate through a list than a Series?</p>

<p><code>any()</code> will early return when it get a <code>True</code> value, thus the <code>startswith()</code> calls is less then the <code>Dataframe</code> version. </p>

<p>Here is a method that use <code>searchsorted()</code>:</p>

<pre><code>import random, string
import pandas as pd
import numpy as np

def randomword(length):
    return ''.join(random.choice(string.ascii_lowercase) for i in range(length))


xs = pd.Series([randomword(3) for _ in range(1000)])
ys = pd.Series([randomword(10) for _ in range(10000)])

def is_any_prefix1(xs, ys):
    yo = ys.sort_values().reset_index(drop=True)
    y2 = yo[yo.searchsorted(xs)]
    return np.fromiter(map(str.startswith, y2, xs), dtype=bool)

def is_any_prefix2(xs, ys):
    x = xs.tolist()
    y = ys.tolist()
    return np.fromiter((any(yi.startswith(xi) for yi in y) for xi in x), dtype=bool)

res1 = is_any_prefix1(xs, ys)
res2 = is_any_prefix2(xs, ys)
print(np.all(res1 == res2))

%timeit is_any_prefix1(xs, ys)
%timeit is_any_prefix2(xs, ys)
</code></pre>

<p>output:</p>

<pre><code>True
100 loops, best of 3: 17.8 ms per loop
1 loop, best of 3: 2.35 s per loop
</code></pre>

<p>It's 100x faster.</p>

python pandas