Extract unique terms from a PANDAS series

Question

Background

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  

Problem

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,[]))

Question

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

Answer 1

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(';')]:
        present_colors.add(color.split('x')[-1])

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)))

Answer 2

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.