How to use two regex capture groups to make two pandas columns

user_13886745 0
Viewed 363

I have a dataframe column of strings and I want to extract numbers to another column:

    column
    1 abc123
    2 def456
    3 ghi789jkl012

I've used:

dataframe["newColumn"] = dataframe["column"].str.extract("(\d*\.?\d+)", expand=True)

It works, but only captures the first block of numbers to one column. My desired output is

column           newColumn  newColumn2
1 abc123         123        NaN
2 def456         456        NaN
3 ghi789jkl012   789        012

but can't figure out how to do it

pandas regex
2 Answers

Use Series.str.extractall with Series.unstack and DataFrame.add_prefix, last add to original DataFrame by DataFrame.join:

df = dataframe.join(dataframe["column"].str.extractall("(\d*\.?\d+)")[0]
                                       .unstack()
                                       .add_prefix('newColumn'))
print (df)
         column newColumn0 newColumn1
1        abc123        123        NaN
2        def456        456        NaN
3  ghi789jkl012        789        012

Or you can use (\d+), thank you @Manakin:

df = (dataframe.join(dataframe["column"].str.extractall("(\d+)")[0]
                                        .unstack()
                                        .add_prefix('newColumn'))
print (df)
user_29010020

Can also use split, expand=True and join back to df.

df.join(df.column.str.split('\D+', expand=True).replace({None: np.NaN}).rename({2:'newColumn2',1:'newColumn'},axis=1).iloc[:,-2::])


 

    column newColumn newColumn2
1        abc123       123        NaN
2        def456       456        NaN
3  ghi789jkl012       789        012
user_89869750
Related