I have process tons of DataFrames with shapes of ~230 columns x ~2000-50000+ rows. Here is an extremely simplified example;

 numbers                colors
0    0.03620894806802     1xYellow ; 2xRed 
1  0.7641262315308163  2xYellow ; 1xOrange 
2  0.5607449770945651   3xYellow ; 2xGreen 
3  0.6714547913365702     1xYellow ; 1xRed 
4  0.8646309438322237     2xYellow ; 1xRed  


I need to break the colors column down to a set that looks like this; {'Green', 'Orange', 'Red', 'Yellow'}. The example code below can do this but it is painfully slow on huge DataFrames.

import re
import pandas as pd
import numpy as np
# Generating example data
color = ["1xYellow ; 2xRed ",
         "2xYellow ; 1xOrange ",
         "3xYellow ; 2xGreen ",
         "1xYellow ; 1xRed ",
         "2xYellow ; 1xRed "]
numbers = np.random.rand(len(color))
ex_df = pd.DataFrame(np.array([numbers,color]).T,
                     columns = ["numbers","colors"])
# Compile the regex to apply with findall
rx = re.compile("x(\w+)\s")
just_colors = ex_df.colors.apply(rx.findall)
# Below is the painfully slow operation that needs optimization.  
present_colors = set(sum(just_colors,[]))


Is there a better method out there for pulling unique terms out of a pandas series?


2 Answers 2


It doesn't look like you really need regular expressions. This construct just using basic string operations is about 10x faster than the construct with the regular expressions:

present_colors = set()
for value in ex_df['colors'].values:
    for color in [x.strip() for x in value.split(';')]:

And a bit faster yet, go with the same code as a generator using itertools:

import itertools as it
present_colors = set(it.chain.from_iterable(
    ([color.split('x')[-1].strip() for color in value.split(';')]
     for value in ex_df['colors'].values)))
  • \$\begingroup\$ So using %timeit in jupyter notebook doing it the old way I get; 1 loop, best of 3: 20.3 s per loop, and the itertools way I get; 10 loops, best of 3: 37.2 ms per loop! Hell yeah. Thanks again! \$\endgroup\$ Feb 24, 2017 at 14:08
  • \$\begingroup\$ *On a df with shape of (44919, 123) with Intel Core i5-4210M CPU @ 2.60GHz. \$\endgroup\$ Feb 24, 2017 at 14:19

To add to @Stephen Rauch's answer, here's a bit of background regarding regular expressions and why they aren't suitable in this case. (Note that my answer is about regexes in general, not about how python handles them.)

Behind the scenes regular expressions tend to work by building a state machine that operates on the individual characters of a string, and does a fair bit of work regarding captures and matching multiple characters. Thus regular expressions are optimal for patterns that have a degree of complexity, and in cases such as yours where the pattern is a simple matter of 'sections separated by single characters' they generally perform much worse than a simple character split.

For example, let's pull apart the regex you use in your code: "x(\w+)\s". (Note that my emphasis is not on what the regex means, but what must be done behind the scenes to implement the regex.)

  • x is simple enough, that's just a simple per-character match which is just a matter of iterating through the source to find a matching x.
  • ( begins a match, which may be as simple as pushing the character index on the stack, or may involve object creation depending on the approach used.
  • \w is a bit more complicated. This time instead of comparing each character to another character, each character is being compared to a set of characters. It's still (mostly) a constant time operation if the implementation is using a hash set, but it's more complicated than pushing an index or comparing two characters.
  • + is again more complicated. It's effectively a 'while' loop, which repeats the previous operation until it no longer matches. Firstly, this is implicitly a branching operation, which adds a bit of complexity in itself. Secondly, the previous operation was not completely trivial and that operation is now being repeated several times, which mounts up.
  • ) removes the earlier index from the stack and adds a capture to the capture list that starts at the removed index and continues to the index of the last matched character.
  • \s is yet again a set-matching operation.

Now compare this to @Stephen Rauch's suggested solution of splitting the input. (I'll only tackle the first solution

  • First the input is split on ;, which is a matter of advancing until a ; is found, returning everything before the ;, making a note of where the ; is and continuing either to the next ; or to the end. Importantly, this can be done lazily, with each split being generated only as required.
  • Next strip() goes through the start of the string up until the first non-whitespace character and notes that as the start index, then it goes backwards from the end until it finds a non-whitespace character and marks that as the end index, then it's a simple matter of returning a substring.
  • Finally the last operation is a matter of another split and an array index.

The split-based solution thus uses much simpler operations, which are both simpler and lazy, which leads to it being significantly faster. In this case the KISS principle definately prevails.

  • 1
    \$\begingroup\$ Thanks @Pharap for your clarification. I agree with you about using builtin string methods over regex when possible but believe it or not that was not the most expensive part of my original code it was set(sum(just_colors,[])) - at least when it comes to these large dfs. For some reason using itertools.chain.from_iterable is ~537x faster for specs noted in my comments for @Stephen Rauch's answer even though I'm still using the regex (b/c the data sets I'm working with are much more complicated than my simplified example). \$\endgroup\$ Feb 24, 2017 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.