CODEWITHSUNDEEP
pythonpandasdatetime
Rock
Rock

Reputation: 2977

Convert datetime column with multiple timezones to epoch timestamp

I have a datetime column with multiple timezones that I want to convert to epoch timestamp:

pd.DataFrame(['2020-09-29 09:30:00-04:00', '2020-09-29 10:30:00-04:00', '2020-09-29 11:00:00-05:00'], columns = ['datetime'])

The desired results look like:

                    datetime   timestamp
0  2020-09-29 09:30:00-04:00  1601386200
1  2020-09-29 10:30:00-04:00  1601389800
2  2020-09-29 11:00:00-05:00  1601395200

(note 2020-09-29 11:00:00 has a -05:00 tz)

I tried:

df['timestamp'] = pd.to_datetime(df['datetime']).astype(int) // 10 ** 9

It works well when datetime has only one timezone info but throws a int() argument must be a string, a bytes-like object or a number, not 'datetime.datetime' error when the timezone info is different and I'm not sure why Pandas cannot recognize this.

I assume people may ask similar questions but couldn't find the exact solution. Any ideas? Thanks!

Upvotes: 1

Views: 113

Answers (2)

drec4s
drec4s

Reputation: 8077

Using pd.Timestamp:

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

datetime    timestamp
0   2020-09-29 09:30:00-04:00   1601386200
1   2020-09-29 10:30:00-04:00   1601389800
2   2020-09-29 11:00:00-05:00   1601395200

Upvotes: 1

Quang Hoang
Quang Hoang

Reputation: 150745

You have mixed time zone, so you want to pass utc=True to to_datetime:

pd.to_datetime(df['datetime'], utc=True).astype(int) // 10 ** 9

Output:

0    1601386200
1    1601389800
2    1601395200
Name: datetime, dtype: int64

Upvotes: 2

Related Questions