Matching Regexes and adding corresponding labels in df

user_15671498 0
Viewed 32

I'm having some issues regarding regexes matching in my dataframe. I want to create an additional column in the dataframe that matches the right RegexLabel if the String of column y contains substrings of the RegexGroceries | RegexFurniture

Here is the code to reproduce:

# Dataframe
data = {'X': [1, 2, 3, 4], 'Y': ["supermarkt", "ikea", "albert heijn", "vloer"]}  

# Regexes
RegexGroceries = ["boni","supermarkt","nutri","company lukic","albert heijn"] 
RegexFurniture = ["ikea","meubel","tuin","tapijt","carpet","auping","bed"]

# Labels and categories
RegexCategories = [RegexGroceries, RegexFurniture]
RegexLabels = ["Groceries", "Furniture"]

# Apply labels for all regexes
counter = 0
for i in RegexCategories:
    for j in i:
        data['RegexCategory'] = np.where(data['y'].str.contains(j), RegexLabels[0 + counter], "None")
    counter = counter + 1

Somehow my df[RegexCategory] only returns None. Can anyone help me?

My output needs to look like this:

X Y RegexCategory
1 supermarkt Groceries
2 ikea Furniture
3 albert heijn Groceries
4 vloer None

|

pandas numpy python
1 Answers

What you call regexes are in fact lists of strings which means you can use isin() for matching, ie.:

# Dataframe
data = pd.DataFrame({'X': [1, 2, 3, 4], 'Y': ["supermarkt", "ikea", "albert heijn", "vloer"]})

# Categories
Groceries = ["boni","supermarkt","nutri","company lukic","albert heijn"]
Furniture = ["ikea","meubel","tuin","tapijt","carpet","auping","bed"]

data['Category'] = None
data.loc[data.Y.isin(Groceries), 'Category'] = 'Groceries'
data.loc[data.Y.isin(Furniture), 'Category'] = 'Furniture'

Result:

In [72]: data
Out[72]: 
   X             Y   Category
0  1    supermarkt  Groceries
1  2          ikea  Furniture
2  3  albert heijn  Groceries
3  4         vloer       None
user_34424090
Related