CODEWITHSUNDEEP
pythonpandastimezone
Amelio Vazquez-Reina
Amelio Vazquez-Reina

Reputation: 96274

Converting timezones from pandas Timestamps

I have the following in a dataframe:

> df['timestamps'].loc[0]
Timestamp('2014-09-02 20:24:00')

I know the timezone (I think it is GMT) it uses and would like to convert the entire column to EST. How can I do that in Pandas?

For reference, I found these other threads:

but they work with datetime timestamps. E.g.:

> datetime.datetime.fromtimestamp(df['timestamps'].loc[0], tz=None)
returns:

TypeError                                 Traceback (most recent call last)
----> 2 datetime.datetime.fromtimestamp(ts, tz=None)

TypeError: an integer is required (got type Timestamp)

Upvotes: 24

Views: 59835

Answers (3)

Contango
Contango

Reputation: 80272

Example of how to convert UTC times (originally from Unix time) to US/Eastern.

This runs in vectorised mode, so it is blazingly fast: runs in seconds for millions of rows.

Tested on Python 3.9.

df = pd.DataFrame({"timestamp": [Timestamp("2017-01-03 14:30:00.049"), Timestamp("2017-01-03 14:30:00.049"), Timestamp("2017-01-03 14:30:00.049")],
                   "x": [1,2,3]})
timestamp = df["timestamp"].values
timestamp = pd.to_datetime(timestamp)
timestamp = timestamp.tz_localize("UTC").tz_convert("US/Eastern") # Convert UTC to US/Eastern
timestamp = timestamp.tz_localize(None)  # Strip timezone information off.
df["timestamp"] = timestamp.values
df

In:

              timestamp  x
2017-01-03 14:30:00.049  1
2017-01-03 14:30:00.049  2
2017-01-03 14:30:00.049  3

Out:

              timestamp  x
2017-01-03 09:30:00.049  1
2017-01-03 09:30:00.049  2
2017-01-03 09:30:00.049  3

Bonus

If the column was originally in Unix time (milliseconds) use this to cast it into an array of datetime64[ns]:

timestamp = pd.to_datetime(timestamp, unit="ms")

Upvotes: 0

rhlobo
rhlobo

Reputation: 1336

Just use tz_convert method.

Lets say you have a Timestamp object:

   stamp = Timestamp('1/1/2014 16:20', tz='America/Sao_Paulo')
   new_stamp = stamp.tz_convert('US/Eastern')

If you are interested in converting date ranges:

   range = date_range('1/1/2014', '1/1/2015', freq='S', tz='America/Sao_Paulo')
   new_range = range.tz_convert('US/Eastern')

For large time series:

   import numpy as np
   ts = Series(np.random.randn(len(range)), range)
   new_ts = ts.tz_convert('US/Eastern')

As stated in another answer, if your data does not have a timezone set, you'll need to tz_localize it:

   data.tz_localize('utc')

Upvotes: 42

Andy Hayden
Andy Hayden

Reputation: 375485

datetime's fromtimestamp is actually from a POSIX timestamp i.e. ms from 1970-1-1 GMT

In [11]: datetime.datetime.fromtimestamp?
Type:        builtin_function_or_method
String form: <built-in method fromtimestamp of type object at 0x101d90500>
Docstring:   timestamp[, tz] -> tz's local time from POSIX timestamp.

In [12]: datetime.datetime.fromtimestamp(0)
Out[12]: datetime.datetime(1969, 12, 31, 16, 0)

In [13]: datetime.datetime.fromtimestamp(1)
Out[13]: datetime.datetime(1969, 12, 31, 16, 0, 1)

I think maybe is an issue as I'm in PST timezone.

This is different from pandas Timestamp (although under the hood that is ns from 1970-1-1).

In [21]: pd.Timestamp(0)
Out[21]: Timestamp('1970-01-01 00:00:00')

To convert a Timestamp/datetime64 column use tz_convert (if the are tz naive, i.e. don't have a timezone yet, you'll need to tz_localize first):

In [31]: pd.Timestamp(0).tz_localize('UTC')
Out[31]: Timestamp('1970-01-01 00:00:00+0000', tz='UTC')

In [32]: t = pd.Timestamp(0).tz_localize('UTC')

In [33]: t.tz_convert('US/Eastern')
Out[33]: Timestamp('1969-12-31 19:00:00-0500', tz='US/Eastern')

See the time-zone-handling section of the docs.

Upvotes: 7

Related Questions