I have a column in pandas data frame like below. Column name is ABC
ABC Fuel FUEL Fuel_12_ab Fuel_1 Lube Lube_1 Lube_12_a cat_Lube Now I want to replace the values in this column using regex like below
ABC Fuel FUEL Fuel Fuel Lube Lube Lube cat_Lube How can we do this type of string matching in pandas data frame.
3 Answers
In [63]: df.ABC.str.replace(r'_\d+.*', r'') Out[63]: 0 Fuel 1 FUEL 2 Fuel 3 Fuel 4 Lube 5 Lube 6 Lube 7 cat_Lube Name: ABC, dtype: object Use positive lookbehind for lube or fuel while ignoring case.
import re import pandas as pd pat = re.compile('(?<=lube|fuel)_', re.IGNORECASE) df.assign(ABC=[re.split(pat, x, 1)[0] for x in df.ABC]) ABC 0 Fuel 1 FUEL 2 Fuel 3 Fuel 4 Lube 5 Lube 6 Lube 7 cat_Lube Alt with str.extract:
df.ABC.str.extract('^(.*?)(?=_\d|$)', expand=False) 0 Fuel 1 FUEL 2 Fuel 3 Fuel 4 Lube 5 Lube 6 Lube 7 cat_Lube Name: ABC, dtype: object Extension courtesy piRSquared:
df.ABC.str.extract('(.*(?<=lube|fuel)).*', re.IGNORECASE, expand=False) 0 Fuel 1 FUEL 2 Fuel 3 Fuel 4 Lube 5 Lube 6 Lube 7 cat_Lube Name: ABC, dtype: object 
