Python Pandas Convert String to int/float

Question

I have a pandas dataframe that I am trying to manipulate so that the 'amount' column converts from a string ("hh.hh 'hours'") to an int or float.

I am wondering what the best practice is in this situation. I have tried pd.to_numeric(), but without success. I think my problem is with the 'hours' tacked on the end of each string.

Is there another function that will identify numeric characters and simply ignore the 'hours' portion of the string, or do I first need to trim the last 5 characters before working with the built in dtype conversion function (pd.to_numeric)? Thanks!

           day  amount
2018-08-23  3   24.00 hours
2018-08-24  4   8.00 hours
2018-08-25  5   32.00 hours
2018-08-26  6   24.00 hours
2018-08-27  0   24.00 hours

Trimming 'hours' would be easiest i think. should be pretty quick to do — SuperStew
– SuperStew, Commented Sep 10, 2018 at 14:57
Thanks. I had encountered a parser function imported from dateutils in the past when working with time series data (used for converting something to the tune of 'Jul 4th, 2018 10:30 AM' into a datetime() object). I was curious to know if there was something similar built into pandas that I haven't stumbled upon. — Andy
– Andy, Commented Sep 10, 2018 at 15:03

ALollz · Accepted Answer · 2018-09-10 15:14:41Z

2

Just use the string methods to get only the numbers that matter. There are a lot of options available depending upon how messy or formatted your column is:

import pandas as pd

df['amount'] = pd.to_numeric(df.amount.str.replace('hours', ''), downcast='integer')
# or 
df['amount'] = pd.to_numeric(df.amount.str[:-5], downcast='integer')
# or
df['amount'] = pd.to_numeric(df.amount.str.extract('(\d+\.?\d*)')[0], downcast='integer')

All output:

            day  amount
2018-08-23    3      24
2018-08-24    4       8
2018-08-25    5      32
2018-08-26    6      24
2018-08-27    0      24

answered Sep 10, 2018 at 15:14

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

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

Comments

TheMaster · Accepted Answer · 2018-09-10 15:40:21Z

2

Depending on the integrity of your data, You can use pd.to_timedelta to convert it to duration(timeDelta):

>>>df.amount=pd.to_timedelta(df.amount)
>>>df

    date      day   amount
0   2018-08-23  3   1 days 00:00:00
1   2018-08-24  4   0 days 08:00:00
2   2018-08-25  5   1 days 08:00:00
3   2018-08-26  6   1 days 00:00:00
4   2018-08-27  0   1 days 00:00:00

answered Sep 10, 2018 at 15:40

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Collectives™ on Stack Overflow

Python Pandas Convert String to int/float

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related