I'm trying to create a new column from existing column in Python dataframe.
Table show below is the sample data and want to add new col by extracting particular text from column Col2.
Below is the regex I'm using and looping through rows to calculate new column values. I'm looking for regex that can even handle Empty cells in Col2.
"[A-Z]*[A-Z].{9}"
Can anyone please let me know any better way to do it?
You may use
import pandas as pd
cols = {'Col1':['Abc', 'def'],
'Col2': ['abc.02.06.05 (Control NA', 'Test acc.02.06.05 Unknown'],
}
df = pd.DataFrame(cols)
df['NewCol'] = df['Col2'].str.extract(r'(\w+(?:\.\d+)+)', expand=False)
Output:
Col1 Col2 NewCol
0 Abc abc.02.06.05 (Control NA abc.02.06.05
1 def Test acc.02.06.05 Unknown acc.02.06.05
The (\w+(?:\.\d+)+) regex matches 1+ word chars, and then one or more sequences of a . and one or more digits.
The (...) create a capturing group that is necessary when you need to extract a substring (the first occurrence) with str.extract.
If you need a more precise regex, you need to come up with the actual requirements. For example, you may want to only extract the substrings in between whitespace boundaries and only when there are three groups of dot-separated two-digit numbers after an alphanumeric string:
r'(?<!\S)(\w+(?:\.\d{2}){3})(?!\S)'
See this regex demo.
df['NewCol'] = df['Col2'].str.extract(r'(\w+(?:\.\d+)+)', expand=False)?