Pandas: Change column of integers to datetime and add a timestamp

Question

I have a dataframe with an id column, and a date column made up of an integer.

d = {'id': [1, 2], 'date': [20161031, 20170930]}
df = pd.DataFrame(data=d)

   id      date
0   1  20161031
1   2  20170930

I can convert the date column to an actual date like so.

df['date'] = df['date'].apply(lambda x: pd.to_datetime(str(x), format='%Y%m%d'))

   id       date
0   1 2016-10-31
1   2 2017-09-30

But I need to have this field as a timestamp with with hours, minutes, and seconds so that it is compatible with my database table. I don't care what the the values are, we can keep it easy by setting it to zeros.

2016-10-31 00:00:00
2017-09-30 00:00:00

What is the best way to change this field to a timestamp? I tried

df['date'] = df['date'].apply(lambda x: pd.to_datetime(str(x), format='%Y%m%d%H%M%S'))

but pandas didn't like that.

I think I could append six 0's to the end of every value in that field and then use the above statement, but I was wondering if there is a better way.

use pandas.to_datetime(df['date']) that is enough, already discussed at stackoverflow.com/questions/32204631/… — Serge
– Serge, Commented Aug 19, 2020 at 18:43
format : string, default None strftime to parse time, eg “%d/%m/%Y”, note that “%f” will parse all the way up to nanoseconds. — gannu
– gannu, Commented Aug 19, 2020 at 18:47
@Serge are you sure it works in this case though? E.g. pd.to_datetime(20161031) == pd.Timestamp('1970-01-01 00:00:00.020161031') — Bill
– Bill, Commented Aug 19, 2020 at 19:33
You are right, it should be converted to string with df['date'].apply(str) — Serge
– Serge, Commented Aug 19, 2020 at 20:03

Serge · Accepted Answer · 2020-08-20 13:01:46Z

3

With pandas it is simpler and faster to convert entire columns. First you convert to string and then to time stamp

pandas.to_datatime(df['date'].apply(str))

PS there are few other conversion methods of varying performance https://datatofish.com/fastest-way-to-convert-integers-to-strings-in-pandas-dataframe/

edited Aug 20, 2020 at 13:01

answered Aug 19, 2020 at 20:00

Serge

3,8453 gold badges20 silver badges39 bronze badges

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

Comments

Bill · Accepted Answer · 2020-08-19 20:29:40Z

2

The problem seems to be that pd.to_datetime doesn't accept dates in this integer format:

pd.to_datetime(20161031) gives Timestamp('1970-01-01 00:00:00.020161031')

It assumes the integers are nanoseconds since 1970-01-01.

You have to convert to a string first:

df['date'] = pd.to_datetime(df["date"].astype(str))

Output:

   id       date
0   1 2016-10-31
1   2 2017-09-30

Note that these are datetimes so they include a time component (which are all zero in this case) even though they are not shown in the data frame representation above.

print(df.loc[0,'date'])

Out:

Timestamp('2016-10-31 00:00:00')

edited Aug 19, 2020 at 20:29

answered Aug 19, 2020 at 19:45

Bill

11.8k13 gold badges68 silver badges100 bronze badges

Comments

Charles · Accepted Answer · 2020-08-19 18:43:52Z

1

You can use

df['date'] = pd.to_datetime(df["date"].dt.strftime('%Y%m%d%H%M%S'))

answered Aug 19, 2020 at 18:43

Charles

2552 silver badges5 bronze badges

Collectives™ on Stack Overflow

Pandas: Change column of integers to datetime and add a timestamp

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related