46

Considering a pandas dataframe in python having a column named time of type integer, I can convert it to a datetime format with the following instruction.

df['time'] = pandas.to_datetime(df['time'], unit='s')

so now the column has entries like: 2019-01-15 13:25:43.

What is the command to revert the string to an integer timestamp value (representing the number of seconds elapsed from 1970-01-01 00:00:00)?

I checked pandas.Timestamp but could not find a conversion utility and I was not able to use pandas.to_timedelta for this.

Is there any utility for this conversion?

roschach
  • 8,390
  • 14
  • 74
  • 124

5 Answers5

41

You can typecast to int using astype(int) and divide it by 10**9 to get the number of seconds to the unix epoch start.

import pandas as pd
df = pd.DataFrame({'time': [pd.to_datetime('2019-01-15 13:25:43')]})
df_unix_sec = pd.to_datetime(df['time']).astype(int)/ 10**9
print(df_unix_sec)
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
  • 1
    This would be fantastic but it's not giving the expected result: I tried the following lines: `df = pd.DataFrame({'time': [pd.to_datetime('2019-01-15 13:25:43')]})` `df['time'] = pandas.to_datetime(df['time'], unit='s',origin='unix')` It is not returning any error but I cannot see any change in the column – roschach Jan 22 '19 at 17:09
  • Psst, casting to int is in my answer ;-) – cs95 Jan 22 '19 at 17:40
  • @FrancescoBoi actually initially I misunderstood the `to_datetime` parameters. Have a look I also asked a question on SO here https://stackoverflow.com/questions/54313463/pandas-datetime-to-unix-timestamp-seconds. So if you cast it to `int` then it'll work for you :) – A l w a y s S u n n y Jan 22 '19 at 17:41
  • @coldspeed yes sir, I've already mentioned that on my previous comment. So `df_unix_sec = pd.to_datetime(df['time']).astype(int)` will work here – A l w a y s S u n n y Jan 22 '19 at 17:42
  • Oh sorry, I meant my answer on this question as well. – cs95 Jan 22 '19 at 17:43
  • 1
    Well, if you can just add you need to divide by 10 ** 9 to get a nix timestamp, I'll just delete my answer then. – cs95 Jan 22 '19 at 17:44
  • Yes sir @coldspeed fixed that – A l w a y s S u n n y Jan 22 '19 at 17:45
  • 3
    Since I was getting a float type after dividing by `10**9` in my opinion is better to add another cast: `res = (pd.to_datetime(df['time'], unit='s').astype(int)/10**9).astype(int)` – roschach Jan 23 '19 at 10:05
  • @FrancescoBoi Yes Agreed. :) – A l w a y s S u n n y Jan 23 '19 at 11:15
36

The easiest way is to use .value

pd.to_datetime('1970-01-01').value

If you want to apply it to the whole column, just use .apply:

df['time'] = df['time'].apply(lambda x: x.value)
Ignacio Peletier
  • 2,056
  • 11
  • 24
9

Use .dt.total_seconds() on a timedelta64:

import pandas as pd
df = pd.DataFrame({'time': [pd.to_datetime('2019-01-15 13:25:43')]})

# pd.to_timedelta(df.time).dt.total_seconds() # Is deprecated
(df.time - pd.to_datetime('1970-01-01')).dt.total_seconds()

Output

0    1.547559e+09
Name: time, dtype: float64
ALollz
  • 57,915
  • 7
  • 66
  • 89
7

One can also use .view(...):

import pandas as pd
df = pd.DataFrame({'time': [pd.to_datetime('2019-01-15 13:25:43')]})
df_unix_sec = pd.to_datetime(df['time']).view(int) // 10 ** 9
print(df_unix_sec)

Casting with .astype(int), recommended above, is deprecated in pandas 1.3.0, and throws a warning:

FutureWarning: casting datetime64[ns] values to int64 with .astype(...) is deprecated and will raise in a future version. Use .view(...) instead.
Grigory Sizov
  • 79
  • 1
  • 3
3

As @Ignacio recommends, this is what I am using to cast to integer:

df['time'] = df['time'].apply(lambda x: x.value)

Then, to get it back:

df['time'] = df['time'].apply(pd.Timestamp)
Jared Marks
  • 948
  • 8
  • 15