How to replace a column string value if it is a substring of another column

Question

How to replace a part string value of a column using another column.

My DataSet here is :

ID          Product Name                            Size ID    Size Name
1   24 Mantra Ancient Grains Foxtail Millet 500 gm      1       500 gm
2   24 Mantra Ancient Grains Little Millet 500 gm       2       500 gm
3   24 Mantra Naturals Almonds 100 gm                   3       100 gm
4   24 Mantra Naturals Kismis 100 gm                    4       100 gm
5   24 Mantra Organic Ajwain 100 gm                     5       100 gm
6   24 Mantra Organic Apple Blast Drink 250 ml          6       250 ml
7   24 Mantra Organic Apple Juice 1 Ltr Tetra Pack      7       1000 ml
8   24 Mantra Organic Apple Juice 200 ml                8       200 ml
9   24 Mantra Organic Assam Tea 100 gm                  9       100 gm

Requirement here is the Product Name column value is 24 Mantra Ancient Grains Foxtail Millet 500 gm and the Size Name column has 500 Gm. In this case my output will be 24 Mantra Ancient Grains Foxtail Millet. If the Size Name contains in the Product Name string remove the size name word ignoring the case else no need to take any action.

Perhaps you could post an example of any code you have tried and an example of what your ideal output should look like so people can help you better. SO is not a code writing website. — d_kennetz
– d_kennetz, Commented Oct 10, 2018 at 14:18
Possible duplicate of Python Pandas removing substring using another column — Georgy
– Georgy, Commented Oct 10, 2018 at 14:19
Possible duplicate of Python Pandas: Check if string in one column is contained in string of another column in the same row — Ankur Sinha
– Ankur Sinha, Commented Oct 10, 2018 at 14:20

rahlf23 · Accepted Answer · 2018-10-10 15:09:08Z

IIUC, you can use apply() and replace():

df['Product Name'] = df.apply(lambda x: x['Product Name'].replace(x['Size Name'], '').strip(), axis=1)

Yields:

   ID                                    Product Name  Size ID Size Name
0   1         24 Mantra Ancient Grains Foxtail Millet        1    500 gm
1   2          24 Mantra Ancient Grains Little Millet        2    500 gm
2   3                      24 Mantra Naturals Almonds        3    100 gm
3   4                       24 Mantra Naturals Kismis        4    100 gm
4   5                        24 Mantra Organic Ajwain        5    100 gm
5   6             24 Mantra Organic Apple Blast Drink        6    250 ml
6   7  24 Mantra Organic Apple Juice 1 Ltr Tetra Pack        7   1000 ml
7   8                   24 Mantra Organic Apple Juice        8    200 ml
8   9                     24 Mantra Organic Assam Tea        9    100 gm

Mohit Motwani · Accepted Answer · 2018-10-10 14:48:48Z

Assuming you want to replace the Size Name values with None if they are substrings of Product Name

df = pd.DataFrame({
            'Product Name' : ['24 Mantra Ancient Grains Foxtail Millet 500 gm', '24 Mantra Ancient Grains Little Millet 500 gm ', '24 Mantra Naturals Kismis 100 gm'], 
            'Size ID' : [1, 2, 3],
            'Size Name': ['500 gm', '500 gm', '200 gm']
        })

df['same']= df.apply(lambda x: x['Size Name'] in x['Product Name'], axis = 1)
df['Size Name'] = np.where(df['same'], None, df['Size Name'])
df.drop(columns=['same'], inplace = True)
df

  Product Name                                Size ID      Size Name
0   24 Mantra Ancient Grains Foxtail Millet 500 gm  1              None
1   24 Mantra Ancient Grains Little Millet 500 gm   2              None
2   24 Mantra Naturals Kismis 100 gm                3              200 gm

silverhash · Accepted Answer · 2018-10-10 15:00:58Z

0

Assuming,you the size name will always be the last column, here is what i think you need:

import re

data = '''ID          Product Name                            Size ID    Size Name
1   24 Mantra Ancient Grains Foxtail Millet 500 gm      1       500 gm
2   24 Mantra Ancient Grains Little Millet 500 gm       2       500 gm
3   24 Mantra Naturals Almonds 100 gm                   3       100 gm
4   24 Mantra Naturals Kismis 100 gm                    4       100 gm
5   24 Mantra Organic Ajwain 100 gm                     5       100 gm
6   24 Mantra Organic Apple Blast Drink 250 ml          6       250 ml
7   24 Mantra Organic Apple Juice 1 Ltr Tetra Pack      7       1000 ml
8   24 Mantra Organic Apple Juice 200 ml                8       200 ml
9   24 Mantra Organic Assam Tea 100 gm                  9       100 gm
'''
def cleaner(txt):
    data = txt
    temp = data.split('\n')
    products = temp[1:-1]
    fixed_products = [temp[0]]

    for p in products:
    res = re.search('(\d+\s\w*)$', p)
    try:
        match = res.group(0)
        ignore_from = len(match)
        found_at = p[:-ignore_from].find(match)
        if found_at > -1:#we found a duplicate
            fixed_product = p.replace(match,'',1)
            fixed_products.append(fixed_product)
    except:
        pass
    products = '\n'.join(fixed_products)
    return products

#Example
#cleaner(data)

answered Oct 10, 2018 at 15:00

silverhash

9299 silver badges22 bronze badges

2 Comments

rahlf23 Over a year ago

This does not leverage pandas or dataframes at all.

silverhash Over a year ago

@rahlf23, nope it doesn't..I'm simply trying to show him how to DIY, it's more understandable but if pd's dfs is what he is looking for then your solution and Mohit's solutions above should suffice

Collectives™ on Stack Overflow

How to replace a column string value if it is a substring of another column

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related