Extracting the first instance of alphabetical string from an alphanumeric string in a dataframe cell

Question

I have this dataset:

NAME    SUBSET 
Apple   48 – ABC – HH
Bat     72 – ACD – HH, 72 – FI, 36 – FE – PP 
Cat     ABC, 72 – AA – LL

From this data, I want to extract only the first instance of alphabet occurrence and omit the remaining part i.e. I want the output of the form:

NAME      SUBSET
Apple     ABC
Bat       ACD, FI, FE
Cat       ABC, AA

How will I be able to do it in Python? Please guide.

user_16343464 · Answer

You can use a regex with extractall and aggregate:

df['SUBSET'] = (df['SUBSET'].str.extractall(r'[^a-zA-Z]*([a-zA-Z]+)[^,]*')
                .groupby(level=0).agg(', '.join))

output:

    NAME       SUBSET
0  Apple          ABC
1    Bat  ACD, FI, FE
2    Cat      ABC, AA

Alternatively, using split as a first step:

df['SUBSET'] = (df['SUBSET']
 .str.split(',')
 .explode()
 .str.extract(r'([a-zA-Z]+)', expand=False)
 .groupby(level=0).agg(', '.join)
)