How to convert pandas data frame datetime column to int?

Question

I am facing an issue while converting one of my datetime columns in pandas dataframe to int. My code is:

df['datetime_column'].astype(np.int64)

The error which I am getting is:

invalid literal for int() with base 10: '2018-02-25 09:31:15'

I am quite clueless about what is happening as the conversion for some of my other datetime columns are working fine. Is there some issue with the range of the date which can be converted to int?

What integer do you expect that '2018-02-25 09:31:15' be converted to? — L.B.
– L.B., Commented Dec 22, 2018 at 5:40
It seemed to me as well that the values are out of range. But when I tried to convert them to 'float' , I got the following error: could not convert string to float: '2018-02-15 14:28:08' I guess I forgot to convert the column to 'datetime' before converting it to 'int'. I updated the code as: 'pd.to_datetime(df['datetime_column']).astype(np.int64)' and the conversion is working fine. — ironhide012
– ironhide012, Commented Dec 22, 2018 at 5:50
Why would you convert datetime to int by casting? Don’t you want df['date_time'].map(dt.datetime.toordinal) instead? — Prayson W. Daniel
– Prayson W. Daniel, Commented Dec 22, 2018 at 6:02
Thanks a lot for the suggestion. I am building a decision tree model and need to extract some features from the datetime field. I have extracted month, year, day, day of the week, hour of the day, weekend indicator, offset from current date etc. I am just experimenting by adding new features and hence doing a conversion simply by casting. By the way, datetime.toordinal and offset from the current date should be correlated, I guess? — ironhide012
– ironhide012, Commented Dec 22, 2018 at 6:13

Pedro Lobito · Accepted Answer · 2019-04-10 23:44:07Z

5

You would use

df['datetime_colum'].apply(lambda x:x.toordinal())

If it fails, the cause could be that your column is an object and not datetime. So you need:

df['datetime_colum'] = pd.to_datetime(df['datetime_colum'])

before sending it to ordinal.

If you are working on features engineering, you can try creating days between date1 and date2, get boolean for if it is winter, summer, autumn or spring by looking at months, and if you have time, boolean of if it is morning, noontime, or night, but all depending on your machines learning problem.

edited Apr 10, 2019 at 23:44

Pedro Lobito

99.8k36 gold badges274 silver badges278 bronze badges

answered Dec 22, 2018 at 6:18

Prayson W. Daniel

15.8k6 gold badges57 silver badges62 bronze badges

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

Comments

Addem · Accepted Answer · 2018-12-22 05:54:35Z

0

it seems you solved the problem yourself judging from your comment. My guess is that you created the data frame without specifying that the column should be read as anything other than a string, so it's a string. If I'm right, and you check the column type, it should show as object. If you check an individual entry in the column, it should show as a string.

If the issue is something else, please follow up.

answered Dec 22, 2018 at 5:54

Addem

4,0136 gold badges41 silver badges66 bronze badges

1 Comment

ironhide012 Over a year ago

Thanks a lot. It was exactly the same issue.

Collectives™ on Stack Overflow

How to convert pandas data frame datetime column to int?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related