Convert string(object) column of dataframe to numeric by applying some string formatting

Question

Take pandas dataframe as

index           A    
    0       1qwe 3asd
    1       6qwe 35asd
    2       11qwe 13asd
    3       17qwe 8asd
    4       5qwe 9asd
    5       7qwe 2asd
    6       1qwe 20asd

A.dtype = object

Convert this dataframe as follows

index        A    
    0       1.03
    1       6.35
    2       11.13
    3       17.08
    4       5.09
    5       7.02
    6       1.20

A.dtype = float64

Is conversion possible in python? If yes, please give code in efficient way.

After period(.) padding of zero should take place if only one digit is present

cs95 · Accepted Answer · 2019-04-13 09:21:49Z

1

You can do this with one str.replace call,

df['A'].str.replace(r'(\d+).*?(\d+).*', lambda x: '{}.{:0>2}'.format(x[1], x[2]))

pd.to_numeric(df['A'].str.replace(
  r'(\d+).*?(\d+).*', lambda x: '{}.{:0>2}'.format(x[1], x[2])), errors='coerce')

edited Apr 13, 2019 at 9:21

answered Apr 13, 2019 at 8:28

cs95

406k106 gold badges744 silver badges797 bronze badges

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

2 Comments

Jayant Over a year ago

Thank you, please take a look at edited question. After period(.) padding of zero should take place if only one digit is present

cs95 Over a year ago

@Jayant there was a bug in which numbers with two decimal places weren't handled correctly. I've fixed it now!

Chris Adams · Accepted Answer · 2019-04-13 08:11:48Z

1

Try with Series.str.findall, apply.join and pandas.to_numeric:

df['A'] = pd.to_numeric(df.A.str.findall('(\d+)').apply('.'.join))

0     1.30
1     6.35
2    11.13
3    17.80
4     5.90
5     7.20
6     1.20
Name: A, dtype: float64

edited Apr 13, 2019 at 8:11

answered Apr 13, 2019 at 8:06

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

1 Comment

Jayant Over a year ago

Thank you, can you please take look at edited question and answer how it can be done?

Collectives™ on Stack Overflow

Convert string(object) column of dataframe to numeric by applying some string formatting

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related