extract number from string in pandas dataframe column

Question

I have a dataframe in the below format and and trying to use the extract function but I keep getting the following error:

ValueError: If using all scalar values, you must pass an index

column1    column2
1         abc2150/abc2152/abc2154/abc215601/U215602


df.column2.str
    .split('/',expand=True)
    .apply(lambda row: row.str.extract('(\d+)', expand=True))
    .apply(lambda x: '/'.join(x.dropna().astype(str)), axis=1)

I need the output in the below format.

column1    column2
1         2150/2152/2154/215601/215602

Please let me know how to fix it.

Thanks

yatu · Accepted Answer · 2020-04-29 11:14:11Z

2

You could instead use str.replace with a positive lookahead to remove all characters that precede the numerical part:

df.column2.str.replace(r'[a-zA-Z]+(?=\d+)','')

 0    2150/2152/2154/215601/215602
Name: column2, dtype: object

answered Apr 29, 2020 at 11:14

yatu

88.6k12 gold badges93 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wwnde · Accepted Answer · 2020-04-29 19:30:17Z

0

Why not?

df['column2']=df.column2.str.replace('abc','')

edited Apr 29, 2020 at 19:30

answered Apr 29, 2020 at 11:24

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Comments

quest · Accepted Answer · 2020-04-29 11:18:24Z

-1

Here is what I will do:

df.loc[:, "column2"] = df.column2.apply(lambda x: re.sub("[a-zA-Z]+", "", x))

answered Apr 29, 2020 at 11:18

quest

3,9662 gold badges18 silver badges27 bronze badges

2 Comments

quest Over a year ago

could the down -voter please explain what was wrong with the solution?

Leu_Grady Over a year ago

Maybe you mixed the slice methods? df["column2"] = df["column2"].apply(lambda x: re.sub("[a-zA-Z]+", "", x)) will not get downvotes?

Collectives™ on Stack Overflow

extract number from string in pandas dataframe column

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related