Are for-loops in pandas really bad? When should I care?
TLDR; No, for
loops are not blanket "bad", at least, not always. It is probably more accurate to say that some vectorized operations are slower than iterating, versus saying that iteration is faster than some vectorized operations. Knowing when and why is key to getting the most performance out of your code. In a nutshell, these are the situations where it is worth considering an alternative to vectorized pandas functions:
- When your data is small (...depending on what you're doing),
- When dealing with
object
/mixed dtypes - When using the
str
/regex accessor functions
Let's examine these situations individually.
Iteration v/s Vectorization on Small Data
Pandas follows a "Convention Over Configuration" approach in its API design. This means that the same API has been fitted to cater to a broad range of data and use cases.
When a pandas function is called, the following things (among others) must internally be handled by the function, to ensure working
- Index/axis alignment
- Handling mixed datatypes
- Handling missing data
Almost every function will have to deal with these to varying extents, and this presents an overhead. The overhead is less for numeric functions (for example, Series.add
), while it is more pronounced for string functions (for example, Series.str.replace
).
for
loops, on the other hand, are faster then you think. What's even better is list comprehensions (which create lists through for
loops) are even faster as they are optimized iterative mechanisms for list creation.
List comprehensions follow the pattern
[f(x) for x in seq]
Where seq
is a pandas series or DataFrame column. Or, when operating over multiple columns,
[f(x, y) for x, y in zip(seq1, seq2)]
Where seq1
and seq2
are columns.
Numeric Comparison
Consider a simple boolean indexing operation. The list comprehension method has been timed against Series.ne
(!=
) and query
. Here are the functions:
# Boolean indexing with Numeric value comparison.
df[df.A != df.B] # vectorized !=
df.query('A != B') # query (numexpr)
df[[x != y for x, y in zip(df.A, df.B)]] # list comp
For simplicity, I have used the perfplot
package to run all the timeit tests in this post. The timings for the operations above are below:
The list comprehension outperforms query
for moderately sized N, and even outperforms the vectorized not equals comparison for tiny N. Unfortunately, the list comprehension scales linearly, so it does not offer much performance gain for larger N.
Note
It is worth mentioning that much of the benefit of list comprehension come from not having to worry about the index alignment,
but this means that if your code is dependent on indexing alignment,
this will break. In some cases, vectorised operations over the
underlying NumPy arrays can be considered as bringing in the "best of
both worlds", allowing for vectorisation without all the unneeded overhead of the pandas functions. This means that you can rewrite the operation above asdf[df.A.values != df.B.values]
Which outperforms both the pandas and list comprehension equivalents:
NumPy vectorization is out of the scope of this post, but it is definitely worth considering, if performance matters.
Value Counts
Taking another example - this time, with another vanilla python construct that is faster than a for loop - collections.Counter
. A common requirement is to compute the value counts and return the result as a dictionary. This is done with value_counts
, np.unique
, and Counter
:
# Value Counts comparison.
ser.value_counts(sort=False).to_dict() # value_counts
dict(zip(*np.unique(ser, return_counts=True))) # np.unique
Counter(ser) # Counter
The results are more pronounced, Counter
wins out over both vectorized methods for a larger range of small N (~3500).
Note
More trivia (courtesy @user2357112). TheCounter
is implemented with a C
accelerator,
so while it still has to work with python objects instead of the
underlying C datatypes, it is still faster than afor
loop. Python
power!
Of course, the take away from here is that the performance depends on your data and use case. The point of these examples is to convince you not to rule out these solutions as legitimate options. If these still don't give you the performance you need, there is always cython and numba. Let's add this test into the mix.
from numba import njit, prange
@njit(parallel=True)
def get_mask(x, y):
result = [False] * len(x)
for i in prange(len(x)):
result[i] = x[i] != y[i]
return np.array(result)
df[get_mask(df.A.values, df.B.values)] # numba
Numba offers JIT compilation of loopy python code to very powerful vectorized code. Understanding how to make numba work involves a learning curve.
Operations with Mixed/object
dtypes
String-based Comparison
Revisiting the filtering example from the first section, what if the columns being compared are strings? Consider the same 3 functions above, but with the input DataFrame cast to string.
# Boolean indexing with string value comparison.
df[df.A != df.B] # vectorized !=
df.query('A != B') # query (numexpr)
df[[x != y for x, y in zip(df.A, df.B)]] # list comp
So, what changed? The thing to note here is that string operations are inherently difficult to vectorize. Pandas treats strings as objects, and all operations on objects fall back to a slow, loopy implementation.
Now, because this loopy implementation is surrounded by all the overhead mentioned above, there is a constant magnitude difference between these solutions, even though they scale the same.
When it comes to operations on mutable/complex objects, there is no comparison. List comprehension outperforms all operations involving dicts and lists.
Accessing Dictionary Value(s) by Key
Here are timings for two operations that extract a value from a column of dictionaries: map
and the list comprehension. The setup is in the Appendix, under the heading "Code Snippets".
# Dictionary value extraction.
ser.map(operator.itemgetter('value')) # map
pd.Series([x.get('value') for x in ser]) # list comprehension
Positional List Indexing
Timings for 3 operations that extract the 0th element from a list of columns (handling exceptions), map
, str.get
accessor method, and the list comprehension:
# List positional indexing.
def get_0th(lst):
try:
return lst[0]
# Handle empty lists and NaNs gracefully.
except (IndexError, TypeError):
return np.nan
ser.map(get_0th) # map
ser.str[0] # str accessor
pd.Series([x[0] if len(x) > 0 else np.nan for x in ser]) # list comp
pd.Series([get_0th(x) for x in ser]) # list comp safe
Note
If the index matters, you would want to do:pd.Series([...], index=ser.index)
When reconstructing the series.
List Flattening
A final example is flattening lists. This is another common problem, and demonstrates just how powerful pure python is here.
# Nested list flattening.
pd.DataFrame(ser.tolist()).stack().reset_index(drop=True) # stack
pd.Series(list(chain.from_iterable(ser.tolist()))) # itertools.chain
pd.Series([y for x in ser for y in x]) # nested list comp
Both itertools.chain.from_iterable
and the nested list comprehension are pure python constructs, and scale much better than the stack
solution.
These timings are a strong indication of the fact that pandas is not equipped to work with mixed dtypes, and that you should probably refrain from using it to do so. Wherever possible, data should be present as scalar values (ints/floats/strings) in separate columns.
Lastly, the applicability of these solutions depend widely on your data. So, the best thing to do would be to test these operations on your data before deciding what to go with. Notice how I have not timed apply
on these solutions, because it would skew the graph (yes, it's that slow).
Regex Operations, and .str
Accessor Methods
Pandas can apply regex operations such as str.contains
, str.extract
, and str.extractall
, as well as other "vectorized" string operations (such as str.split
, str.find
, str.translate
, and so on) on string columns. These functions are slower than list comprehensions, and are meant to be more convenience functions than anything else.
It is usually much faster to pre-compile a regex pattern and iterate over your data with re.compile
(also see Is it worth using Python's re.compile?). The list comp equivalent to str.contains
looks something like this:
p = re.compile(...)
ser2 = pd.Series([x for x in ser if p.search(x)])
Or,
ser2 = ser[[bool(p.search(x)) for x in ser]]
If you need to handle NaNs, you can do something like
ser[[bool(p.search(x)) if pd.notnull(x) else False for x in ser]]
The list comp equivalent to str.extract
(without groups) will look something like:
df['col2'] = [p.search(x).group(0) for x in df['col']]
If you need to handle no-matches and NaNs, you can use a custom function (still faster!):
def matcher(x):
m = p.search(str(x))
if m:
return m.group(0)
return np.nan
df['col2'] = [matcher(x) for x in df['col']]
The matcher
function is very extensible. It can be fitted to return a list for each capture group, as needed. Just extract query the group
or groups
attribute of the matcher object.
For str.extractall
, change p.search
to p.findall
.
String Extraction
Consider a simple filtering operation. The idea is to extract 4 digits if it is preceded by an upper case letter.
# Extracting strings.
p = re.compile(r'(?<=[A-Z])(\d{4})')
def matcher(x):
m = p.search(x)
if m:
return m.group(0)
return np.nan
ser.str.extract(r'(?<=[A-Z])(\d{4})', expand=False) # str.extract
pd.Series([matcher(x) for x in ser]) # list comprehension
More Examples
Full disclosure - I am the author (in part or whole) of these posts listed below.
Fast punctuation removal with pandas
String concatenation of two pandas columns
Remove unwanted parts from strings in a column
Replace all but the last occurrence of a character in a dataframe
Conclusion
As shown from the examples above, iteration shines when working with small rows of DataFrames, mixed datatypes, and regular expressions.
The speedup you get depends on your data and your problem, so your mileage may vary. The best thing to do is to carefully run tests and see if the payout is worth the effort.
The "vectorized" functions shine in their simplicity and readability, so if performance is not critical, you should definitely prefer those.
Another side note, certain string operations deal with constraints that favour the use of NumPy. Here are two examples where careful NumPy vectorization outperforms python:
Create new column with incremental values in a faster and efficient way - Answer by Divakar
Fast punctuation removal with pandas - Answer by Paul Panzer
Additionally, sometimes just operating on the underlying arrays via .values
as opposed to on the Series or DataFrames can offer a healthy enough speedup for most usual scenarios (see the Note in the Numeric Comparison section above). So, for example df[df.A.values != df.B.values]
would show instant performance boosts over df[df.A != df.B]
. Using .values
may not be appropriate in every situation, but it is a useful hack to know.
As mentioned above, it's up to you to decide whether these solutions are worth the trouble of implementing.
Appendix: Code Snippets
import perfplot
import operator
import pandas as pd
import numpy as np
import re
from collections import Counter
from itertools import chain
<!- ->
# Boolean indexing with Numeric value comparison.
perfplot.show(
setup=lambda n: pd.DataFrame(np.random.choice(1000, (n, 2)), columns=['A','B']),
kernels=[
lambda df: df[df.A != df.B],
lambda df: df.query('A != B'),
lambda df: df[[x != y for x, y in zip(df.A, df.B)]],
lambda df: df[get_mask(df.A.values, df.B.values)]
],
labels=['vectorized !=', 'query (numexpr)', 'list comp', 'numba'],
n_range=[2**k for k in range(0, 15)],
xlabel='N'
)
<!- ->
# Value Counts comparison.
perfplot.show(
setup=lambda n: pd.Series(np.random.choice(1000, n)),
kernels=[
lambda ser: ser.value_counts(sort=False).to_dict(),
lambda ser: dict(zip(*np.unique(ser, return_counts=True))),
lambda ser: Counter(ser),
],
labels=['value_counts', 'np.unique', 'Counter'],
n_range=[2**k for k in range(0, 15)],
xlabel='N',
equality_check=lambda x, y: dict(x) == dict(y)
)
<!- ->
# Boolean indexing with string value comparison.
perfplot.show(
setup=lambda n: pd.DataFrame(np.random.choice(1000, (n, 2)), columns=['A','B'], dtype=str),
kernels=[
lambda df: df[df.A != df.B],
lambda df: df.query('A != B'),
lambda df: df[[x != y for x, y in zip(df.A, df.B)]],
],
labels=['vectorized !=', 'query (numexpr)', 'list comp'],
n_range=[2**k for k in range(0, 15)],
xlabel='N',
equality_check=None
)
<!- ->
# Dictionary value extraction.
ser1 = pd.Series([{'key': 'abc', 'value': 123}, {'key': 'xyz', 'value': 456}])
perfplot.show(
setup=lambda n: pd.concat([ser1] * n, ignore_index=True),
kernels=[
lambda ser: ser.map(operator.itemgetter('value')),
lambda ser: pd.Series([x.get('value') for x in ser]),
],
labels=['map', 'list comprehension'],
n_range=[2**k for k in range(0, 15)],
xlabel='N',
equality_check=None
)
<!- ->
# List positional indexing.
ser2 = pd.Series([['a', 'b', 'c'], [1, 2], []])
perfplot.show(
setup=lambda n: pd.concat([ser2] * n, ignore_index=True),
kernels=[
lambda ser: ser.map(get_0th),
lambda ser: ser.str[0],
lambda ser: pd.Series([x[0] if len(x) > 0 else np.nan for x in ser]),
lambda ser: pd.Series([get_0th(x) for x in ser]),
],
labels=['map', 'str accessor', 'list comprehension', 'list comp safe'],
n_range=[2**k for k in range(0, 15)],
xlabel='N',
equality_check=None
)
<!- ->
# Nested list flattening.
ser3 = pd.Series([['a', 'b', 'c'], ['d', 'e'], ['f', 'g']])
perfplot.show(
setup=lambda n: pd.concat([ser2] * n, ignore_index=True),
kernels=[
lambda ser: pd.DataFrame(ser.tolist()).stack().reset_index(drop=True),
lambda ser: pd.Series(list(chain.from_iterable(ser.tolist()))),
lambda ser: pd.Series([y for x in ser for y in x]),
],
labels=['stack', 'itertools.chain', 'nested list comp'],
n_range=[2**k for k in range(0, 15)],
xlabel='N',
equality_check=None
)
<!- _>
# Extracting strings.
ser4 = pd.Series(['foo xyz', 'test A1234', 'D3345 xtz'])
perfplot.show(
setup=lambda n: pd.concat([ser4] * n, ignore_index=True),
kernels=[
lambda ser: ser.str.extract(r'(?<=[A-Z])(\d{4})', expand=False),
lambda ser: pd.Series([matcher(x) for x in ser])
],
labels=['str.extract', 'list comprehension'],
n_range=[2**k for k in range(0, 15)],
xlabel='N',
equality_check=None
)
Alternative for FOR loops where FOR loop is really slow
Try .groupby
with custom function:
def fn(x):
rv, current = [], x["inventory"].iat[0]
for requested_quantity, inv in zip(x["quantity"], x["inventory"]):
if requested_quantity <= inv:
current = inv - requested_quantity
rv.append(current)
x["deliverable"] = rv
df.groupby("product", as_index=False).apply(fn)
print(df)
Prints:
customer product quantity inventory availability deliverable
0 1 A 100 800 700 700.0
1 2 A 1000 700 700 700.0
2 3 A 700 700 0 0.0
3 4 A 50 0 0 0.0
4 5 B 20 100 80 80.0
5 6 B 50 80 30 30.0
6 7 B 40 30 30 30.0
7 8 B 30 30 0 0.0
Why Pandas apply can be faster than vectorized built-ins
To put it short, your question is whether
s.astype(np.str).str[0].astype(np.int)
fuses your operations together, then iterates over the series, or creates a temporary series for each operation, and how to verify this?
My hypothesis (and I guess yours) is that it is the latter. You have the right explanation there but how to test?
My suggestion is:
s1=s.astype(np.str)
s2=s1.str[0]
s3=s2.astype(np.int)
See how long each operation takes and how long the 3 operations take together. Most likely each operation will take about the same amount of time (the complexity of each operation is about the same) which would strongly indicate that our hypothesis is right. If the first two operations take no time, but last, pretty much all of the time, probably our hypothesis is wrong.
How to build and fill pandas dataframe from for loop?
Try this using list comprehension:
import pandas as pd
df = pd.DataFrame(
[p, p.team, p.passing_att, p.passer_rating()] for p in game.players.passing()
)
Should the self reference be used for iteration variables in for loops inside classes?
The answer is generally "no". The for loop is assigning the variable to point to each element in the list. When it's done, it persists outside the for
loop. The better way would be to keep element
a local variable, and it will go away when the method ends. But if you assign it to self.element
, that's equivalent to self.element = my_list[-1]
when the loop ends. Then it will persist when the method exists, and will be accessible from other methods and holders of the class instance. That's usually not what you want.
How to iterate over rows in a DataFrame in Pandas
DataFrame.iterrows
is a generator which yields both the index and row (as a Series):
import pandas as pd
df = pd.DataFrame({'c1': [10, 11, 12], 'c2': [100, 110, 120]})
df = df.reset_index() # make sure indexes pair with number of rows
for index, row in df.iterrows():
print(row['c1'], row['c2'])
10 100
11 110
12 120
Is it possible to fix len(df) when this is being executed in a while loop?
x=48
initial_length = len(df.columns)
while x < initial_length:
x += 1
...
Related Topics
How to Test If a String Contains One of the Substrings in a List, in Pandas
How to Create a List of Random Numbers Without Duplicates
Is There a Zip-Like Function That Pads to Longest Length
"Unicode Error "Unicodeescape" Codec Can't Decode Bytes... Cannot Open Text Files in Python 3
What Is the Eafp Principle in Python
"Large Data" Workflows Using Pandas
How to Call a Function from Another .Py File
How to List All Functions in a Module
Sorting Arrays in Numpy by Column
Iterate a List as Pair (Current, Next) in Python
How to Pass a Variable Between Flask Pages
How to Translate an Iso 8601 Datetime String into a Python Datetime Object
Speed Up Millions of Regex Replacements in Python 3
Timeout Function If It Takes Too Long to Finish
Parsing Xml With Namespace in Python Via 'Elementtree'
How to Modify List Entries During For Loop
Pip Install Failing With: Oserror: [Errno 13] Permission Denied on Directory