apply condition on column1 where string contains only given set of keywords rather contains give set of keywords

user_13770523 0
Viewed 72

how to apply condition on column1 where string contains only given set of keywords rather contains give set of keywords. Emphasizing on word "only"

import pandas as pd
import numpy as np
import re

d = {'_id': [1, 2, 3, 4, 5, 6, 7],
     'column1': ['FullName', 'custfullnm', 'nm123', 'sitenm', 'full12', 'suplnm', 'countryfulln'],
     'column2': ['', '', '', '', '', '', '']}

df = pd.DataFrame(data=d)


key_words = ["full", "nm", "name", "txt", "[0-9]"]

check = f"{'|'.join(key_words)}"

mand_key = "full"

df["column2"] = np.where(
    df.column1.str.contains(mand_key, case=False)
    & (df.column1.str.contains(check, case=False, regex=True)),
    "Full Name",
    "",
)

desired output:

_id,column1,column2
1,FullName,Full Name
2,custfullnm,
3,nm123,
4,sitenm,
5,full12,Full Name
6,suplnm,
7,countryfullnm,

only FullName, full12 matches the criteria with reason mentioned below:

FullName is only made of words from given set of keywords 'full' & 'name'
full12 is only made of words from given set of keywords 'full' & a number '12'

And rest doesn't match the criteria because as follows:

custfullnm      contains 'cust' not in given list of keywords though contains 'nm' & 'full'
nm123           dones't contain madate keyword 'full' though contains a number & 'nm'
sitename        contains 'site' not in given list of keywords though contains 'name'
suplnm          contains 'supl' not in given list of keywords though contains 'nm'
countryfullnm       contains 'country' not in given list of keywords though contains 'nm' & 'full'
pandas numpy python regex
2 Answers

You may create a pattern that will start matching any amount of your allowed subpatterns from the start of the string, then will try to match your mandatory key, and then will again allow any amount of the specified subpatterns till the end of the string:

import pandas as pd
import numpy as np
import re

d = {'_id': [1, 2, 3, 4, 5, 6, 7, 8],
     'column1': ['FullName', 'custfullnm', 'nm123', 'sitenm', 'full12', 'suplnm', 'countryfulln', np.NaN],
     'column2': ['1', '2', '3', '4', '5', '6', '7', '8']}

df = pd.DataFrame(data=d)

key_words = ["full", "nm", "name", "txt", "[0-9]"]
mand_key = "full"
check = rf'^(?:{"|".join(key_words)})*{mand_key}(?:{"|".join(key_words)})*$'
df["column2"] = np.where(df["column1"].str.contains(check, case=False, na=True),"Full Name","")

Output:

>>> df
   _id       column1    column2
0    1      FullName  Full Name
1    2    custfullnm           
2    3         nm123           
3    4        sitenm           
4    5        full12  Full Name
5    6        suplnm           
6    7  countryfulln           
7    8           NaN  Full Name

The pattern will look like

^(?:full|nm|name|txt|[0-9])*full(?:full|nm|name|txt|[0-9])*$

See the regex demo

Details

  • ^ - start of string
  • (?:full|nm|name|txt|[0-9])* - 0 or more repetitions of the specified subpatterns
  • full - mandatory key
  • (?:full|nm|name|txt|[0-9])* - 0 or more repetitions of the specified subpatterns
  • $ - end of string.
user_38329700

I got an answer myself:

key_words = ["full", "nm", "name", "txt", "[0-9]"]
check = f"{'|'.join(key_words)}"
mand_key = "full"
df["column2"] = np.where(
    df.column1.str.contains(mand_key, flags=re.IGNORECASE)
    & df.column1.str.match(check, flags=re.IGNORECASE),"Full Name","",)
df
user_137705230
Related