Creating a new df column based on conditional substring of another column

Question

I wanted to create a new column categorizing records according to a substring in a tracking code. For example, it tracking code contains 'KNC-' the new column Channel should be 'Paid Search'

From this post Pandas: Check if a substring exists in another column then create a new column with a specific value I was able to find a solution.

desc = {"KNC-":"Paid Search","SL-": "Display",'SNP-':'Social','EMC-':'Email'}
def check_desc(x):
    for key in desc:
        if key.lower() in x.lower():
            return desc[key]
    return ''
df['Marketing Channel'] = df["Tracking Code"].map(lambda x: check_desc(x))

However, the first thing I tried was using numpy select:

conditions = [
    ('KNC-' in df['Tracking Code']),
    ('SL-' in df['Tracking Code']),
    ('SNP-' in df['Tracking Code']),
    ('EMC-' in df['Tracking Code'])
    ]
values = ['Paid Search', 'Display', 'Social', 'Email']
df['Marketing Channel'] = np.select(conditions, values)

This latter code created the column but all values were zero. Why?

tdy · Accepted Answer · 2021-09-24 19:58:17Z

1

'KNC-' in df['Tracking Code'] checks if the value KNC- exists in the column. It doesn't check each value for the substring KNC-.

Change your conditions to use str.contains:

conditions = [
    df['Tracking Code'].str.contains('KNC-'),
    df['Tracking Code'].str.contains('SL-'),
    df['Tracking Code'].str.contains('SNP-'),
    df['Tracking Code'].str.contains('EMC-'),
]
values = ['Paid Search', 'Display', 'Social', 'Email']
df['Marketing Channel'] = np.select(conditions, values)

answered Sep 24, 2021 at 19:58

tdy

42k42 gold badges124 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a new df column based on conditional substring of another column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related