Removing multiple substrings in a pandas dataframe column

Question

I have a column of ingredients in a pandas dataframe. I need to remove everything except the name of the ingredient (ex: 1/3 cup cashews > cashews).

Input

    recipe_name                                ingredient
0   Truvani Chocolate Turmeric Caramel Cups    ⅓ cup cashews
1   Truvani Chocolate Turmeric Caramel Cups    4 dates
2   Truvani Chocolate Turmeric Caramel Cups    1 tablespoon almond butter
3   Truvani Chocolate Turmeric Caramel Cups    3 tablespoons coconut milk
4   Truvani Chocolate Turmeric Caramel Cups    ½ teaspoon vanilla extract

Expected Output

    recipe_name                                ingredient
0   Truvani Chocolate Turmeric Caramel Cups    cashews
1   Truvani Chocolate Turmeric Caramel Cups    dates
2   Truvani Chocolate Turmeric Caramel Cups    almond butter
3   Truvani Chocolate Turmeric Caramel Cups    coconut milk
4   Truvani Chocolate Turmeric Caramel Cups    vanilla extract

I've tried using a dictionary, with common words mapped to empty strings like so:

remove_list ={'\d+': '', 'ounces': '', 'ounce': '', 'tablespoons': '', 'tablespoon': '', 'teaspoons': '', 'teaspoon': '', 'cup': '', 'cups': ''}
column = df['ingredient']
column.apply(lambda column: [remove_list[y] if y in remove_list else y for y in column])

This didn't change the data at all.

I've also tried using regex:

df['ingredients'] = re.sub(r'|'.join(map(re.escape, remove_list)), '', df['ingredients'])

But that just gives an error saying "TypeError: expected string or buffer."

I'm very new to Python so I think it's possible with regex, I'm just not sure how to do it.

ALollz · Accepted Answer · 2018-10-09 18:45:34Z

8

Since you want to replace everything with the same character, just put them into a list.

l = ['\d+', '[^\x00-\x80]+', 'ounces', 'ounce', 'tablespoons', 
     'tablespoon', 'teaspoons', 'teaspoon', 'cup', 'cups']

Then use one replace, joining everything.

df.ingredient.str.replace('|'.join(l), '', regex=True).str.strip()
# Safer to only replace stand-alone words. strip not needed
#df.ingredient.str.replace('|'.join([x + '\s' for x in l]), '', regex=True)

Output:

0            cashews
1              dates
2      almond butter
3       coconut milk
4    vanilla extract
Name: ingredient, dtype: object

I added '[^\x00-\x80]+' to the list to remove those fractional characters, and the .str.strip removes any excess or leading whitespace after the replacements.

edited Oct 9, 2018 at 18:45

answered Oct 9, 2018 at 18:26

ALollz

59.7k7 gold badges74 silver badges97 bronze badges

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

3 Comments

ALollz Over a year ago

@Conor Just be careful, because this will accidentally replace something like 5 cupcakes with just cakes. You can get around that by making your replacements '\cup\s' that way it only replaces the word when followed by a space as in 'cup ' and not 'cupc'

Conor Over a year ago

Ah thank you, I just checked back because I was having that problem.

sudden_clarity_clarence Over a year ago

@ALollz how can I amend it so that it will only replace the word when followed by nothing, i.e. end of string?

CJR · Accepted Answer · 2018-10-09 18:19:45Z

0

There are a set of string functions built into pandas dataframes for this.

Something like this should work:

df['ingredient'] = df['ingredient'].str.replace('\d+', '', regex=True)

I don't know if you can use a dict with it, you may have to iterate through your dictionary to get all of the replacements that you want.

for ptn, rpl in remove_list.items():
    df['ingredient'] = df['ingredient'].str.replace(ptn, rpl, regex=True)

edited Oct 9, 2018 at 18:19

answered Oct 9, 2018 at 18:13

CJR

3,9872 gold badges13 silver badges24 bronze badges

2 Comments

Conor Over a year ago

I was trying to do this without using an individual replace statement for every word but that may be the only way

CJR Over a year ago

You can use the dict that you've got to iterate through the patterns with just one extra line of code (as above).

schaefferda · Accepted Answer · 2018-10-09 18:27:39Z

You can use a loop and the .split() method:

i = 0
for row in df['ingredient']:
    item = row.split(sep=' ', maxsplit=1)
    df['ingredient'].loc[i] = item[1]
    i += 1

The output will be:

    recipe_name                                ingredient
0   Truvani Chocolate Turmeric Caramel Cups    cup cashews
1   Truvani Chocolate Turmeric Caramel Cups    dates
2   Truvani Chocolate Turmeric Caramel Cups    tablespoon almond butter
3   Truvani Chocolate Turmeric Caramel Cups    tablespoons coconut milk
4   Truvani Chocolate Turmeric Caramel Cups    teaspoon vanilla extract

If you want to keep the measurements you can create a duplicate column and in one column keep the values and in the other keep the ingredient.

Collectives™ on Stack Overflow

Removing multiple substrings in a pandas dataframe column

3 Answers 3

Output:

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Output:

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related