Python Pandas Convert Datetime To Timestamp Effectively Through Dt Accessor

I have a dataframe with some (hundreds of) million of rows. And I want to convert datetime to timestamp effectively. How can I do it? My sample df: df = pd.DataFrame(index=pd.Datet

Solution 1:

I think you need convert first to numpy array by values and cast to int64 - output is in ns, so need divide by 10 ** 9:

df['ts']=df.datetime.values.astype(np.int64)//10**9print(df)datetimets02016-01-01 00:00:01  145160640112016-01-01 01:00:01  145161000122016-01-01 02:00:01  145161360132016-01-01 03:00:01  145161720142016-01-01 04:00:01  145162080152016-01-01 05:00:01  145162440162016-01-01 06:00:01  145162800172016-01-01 07:00:01  145163160182016-01-01 08:00:01  145163520192016-01-01 09:00:01  1451638801102016-01-01 10:00:01  1451642401112016-01-01 11:00:01  1451646001122016-01-01 12:00:01  1451649601132016-01-01 13:00:01  1451653201142016-01-01 14:00:01  1451656801152016-01-01 15:00:01  1451660401162016-01-01 16:00:01  1451664001172016-01-01 17:00:01  1451667601182016-01-01 18:00:01  1451671201192016-01-01 19:00:01  1451674801202016-01-01 20:00:01  1451678401212016-01-01 21:00:01  1451682001222016-01-01 22:00:01  1451685601232016-01-01 23:00:01  1451689201242016-01-02 00:00:01  1451692801

to_timestamp is used for converting from period to datetime index.

Solution 2:

I think you should not use apply, simply astype would be fine:

df['ts'] = df.datetime.astype('int64') // 10**9

Solution 3:

There's also another method to do this using the "hidden" attribute of DatetimeIndex called asi8, which creates an integer timestamp.

pd.DatetimeIndex(df.datetime).asi8

Wes McKinney suggested it in this tangentially related stackoverflow question linked here

Solution 4:

If you don't want to use numpy you can use pure pandas conversions

df['ts'] = pd.to_timedelta(df['datetime'], unit='ns').dt.total_seconds().astype(int)

Solution 5:

One option would be to use a lambda expressions like such

df['datetime'] = df['datetime'].apply(lambda x: pd.Timestamp(x))