Testy8 Testy8 - 1 year ago 130
Python Question

Pandas replace full word string

I have a dataframe:

df = pd.DataFrame({'id' : ['abarth 1.4 a','abarth 1 a','land rover 1.3 r','land rover 2',
'land rover 5 g','mazda 4.55 bl'],
'series': ['a','a','r','','g', 'bl'] })

I would like to remove the 'series' string from the corresponding id, so the end result should be:

'id': ['abarth 1.4','abarth 1','land rover 1.3','land rover 2','land rover 5', 'mazda 4.55']

Currently I am using df.apply:

df.id = df.apply(lambda x: x['id'].replace(x['series'], ''), axis =1)

But this removes all instances of the strings, even in other words, like so:
'id': ['brth 1.4','brth 1','land ove 1.3','land rover 2','land rover 5', 'mazda 4.55']

Should I somehow mix and match regex with the variable inside df.apply, like so?

df.id = df.apply(lambda x: x['id'].replace(r'\b' + x['series'], ''), axis =1)

Answer Source

Using re, in case you want to specify the series string:

df.apply(lambda x: re.sub('\s*{}$'.format(x['series']), '', x['id']), axis=1)

In case the the series string is always a predictable pattern (i.e. [a-z]) you can also try:

df['id'].apply(lambda x: re.sub('\s*[a-z]+$', '', x))

Either way the output is what you are looking for:

0        abarth 1.4
1          abarth 1
2    land rover 1.3
3      land rover 2
4      land rover 5
5        mazda 4.55
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download