Optimizing this pandas transformation

Question

First of all, I'm completely new to pandas, so I'm not sure if this is even called a "Transformation", but I've done this and it feels like there must be a much more efficient way (either in LOC or running time) to achieve the same. This is what I've written:

def findInCol(row, col, tags):
    try:
        for m in validTags:
            if m.lower() in row[col].lower():
                tags.add(m)
            else:
                m_fix = pat.sub('', m.lower())
                row_fix = pat.sub('', row[col].lower())
                if m_fix in row_fix:
                    tags.add(f"{m}")
    except KeyError:
        pass
def getTags(row):
    tags = set()
    findInCol(row, 'Ad_data', tags)
    findInCol(row, 'Name', tags)
    tags = clean(tags)
    return ','.join(tags) if tags else "NA"

def clean(tags):
    arr = list(tags)
    remove = set()
    for i in range(len(arr)):
        for j in range(i+1, len(arr)):
            i_l = pat.sub('', arr[i].lower())
            j_l = pat.sub('', arr[j].lower())
            if i_l in j_l:
                remove.add(i)
            elif j_l in i_l:
                remove.add(j)
    arr = [i for j, i in enumerate(arr) if j not in remove]
    return arr

df['Tag'] = df.fillna("NA").apply(getTags, axis=1)

If anything needs clarification please ask for it. This essentially tries to find some valid tags inside the fields 'Ad_data' y 'Name'. Many times it will find more than one matching tag, which is fine. However, it could be the case that both 'Horse' and 'RedHorse' are valid tags, so after I do the initial search, I need to clean up to keep just the more specific tag (ie. RedHorse) This is performed in the clean function.

EDIT:

Here is a sample dataset. So, going from a table with just the Name (ignore the Ad_data for now, as it may not exist in some cases), and a separate list of valid tags defined in python code (eg. just ["Horse", "RedHorse"] in this case), I need to get the following output table (with the tag added as a column):

Answer 1

I would start with validTags being a sequence (list, series, whatever) of words, but importantly that are sorted from least specific to most specific (at least within each set of tags that can be applied to a single row).

>>> validTags = pd.Series(['Horse', 'RedHorse'])
>>> validTags
0       Horse
1    RedHorse
dtype: object

Now you can to extract these tags from the columns Ad_data and Name:

>>> df = pd.DataFrame({'Name': ['this row is a horse'], 'Ad_data': ['buy a nice REDHORSE']}, index=['a'])
>>> regex = '(' + '|'.join(validTags) + ')'
>>> import re
>>> df['Name'].str.extractall(regex, flags=re.IGNORECASE)[0]
   match
a  0        horse
Name: 0, dtype: object
>>> df['Ad_data'].str.extractall(regex, flags=re.IGNORECASE)[0]
   match
a  0        REDHORSE
Name: 0, dtype: object

You can see extractall returns all matches with a first index level that corresponds to the row where the tag was found, and a second index level that is just the numbering of the match.

I see in your data there a some unexpected characters that are removed by what I suppose is a regular expression pat. You can do that by replacing df[col].str.extractall(...) by df[col].str.replace(pat, '').str.extractall(...).

Let’s put all these matches together, then use GroupBy.unique() to extract unique elements:

>>> allmatches = pd.concat([
...   df[col].str.replace(pat, '').str.extractall(regex, flags=re.IGNORECASE)[0]
... for col in ['Name', 'Ad_data']])
>>> tags = allmatches.str.lower().groupby(level=0).unique()
>>> tags
a    [horse, redhorse]
Name: 0, dtype: object

Now you have lists of items, you can join them together with .str.join:

>>> df['Tag'] = tags.str.join(', ')
>>> df
                  Name              Ad_data              Tag
a  this row is a horse  buy a nice REDHORSE  horse, redhorse

Now if we want to clean we can use the order in validTags. Note that tags is simply df['Tag'].str.split(', ')

>>> tags = tags.explode()
>>> tags
a       horse
a    redhorse
Name: 0, dtype: object
>>> tag_order = validTags.str.lower().reset_index(name='tag').rename(columns={'index': 'order'})
>>> tag_order
   order       tag
0      0     horse
1      1  redhorse
>>> ordered_tags = pd.merge(tags.reset_index(name='tag'), tag_order, on='tag')
>>> ordered_tags
  index       tag  order
0     a     horse      0
1     a  redhorse      1
>>> clean = ordered_tags.loc[ordered_tags.groupby('index')['order'].idxmax()].set_index('index')['tag']
>>> clean
index
a    redhorse
Name: tag, dtype: object

As you can see the order column now defines which tags are the most specific, and GroupBy + idxmax allows to get the most specific thanks to the ordering.

>>> df['clean tag'] = clean
>>> df
                  Name              Ad_data              Tag clean tag
a  this row is a horse  buy a nice REDHORSE  horse, redhorse  redhorse