Converting time zone pandas dataframe

Question

I have data:

                             Symbol      bid      ask
Timestamp                                            
2014-01-01 21:55:34.378000  EUR/USD  1.37622  1.37693
2014-01-01 21:55:40.410000  EUR/USD  1.37624  1.37698
2014-01-01 21:55:47.210000  EUR/USD  1.37619  1.37696
2014-01-01 21:55:57.963000  EUR/USD  1.37616  1.37696
2014-01-01 21:56:03.117000  EUR/USD  1.37616  1.37694

The timestamp is in GMT. Is there a way to convert that to Eastern?

Note when I do:

data.index

I get output:

<class 'pandas.tseries.index.DatetimeIndex'>
[2014-01-01 21:55:34.378000, ..., 2014-01-01 21:56:03.117000]
Length: 5, Freq: None, Timezone: None

Abed Merii · Accepted Answer · 2020-06-02 09:22:10Z

57

Localize the index (using tz_localize) to UTC (to make the Timestamps timezone-aware) and then convert to Eastern (using tz_convert):

import pytz
eastern = pytz.timezone('US/Eastern')
df.index = df.index.tz_localize(pytz.utc).tz_convert(eastern)

For example:

import pandas as pd
import pytz

index = pd.date_range('20140101 21:55', freq='15S', periods=5)
df = pd.DataFrame(1, index=index, columns=['X'])
print(df)
#                      X
# 2014-01-01 21:55:00  1
# 2014-01-01 21:55:15  1
# 2014-01-01 21:55:30  1
# 2014-01-01 21:55:45  1
# 2014-01-01 21:56:00  1

# [5 rows x 1 columns]
print(df.index)
# <class 'pandas.tseries.index.DatetimeIndex'>
# [2014-01-01 21:55:00, ..., 2014-01-01 21:56:00]
# Length: 5, Freq: 15S, Timezone: None

eastern = pytz.timezone('US/Eastern')
df.index = df.index.tz_localize(pytz.utc).tz_convert(eastern)
print(df)
#                            X
# 2014-01-01 16:55:00-05:00  1
# 2014-01-01 16:55:15-05:00  1
# 2014-01-01 16:55:30-05:00  1
# 2014-01-01 16:55:45-05:00  1
# 2014-01-01 16:56:00-05:00  1

# [5 rows x 1 columns]

print(df.index)
# <class 'pandas.tseries.index.DatetimeIndex'>
# [2014-01-01 16:55:00-05:00, ..., 2014-01-01 16:56:00-05:00]
# Length: 5, Freq: 15S, Timezone: US/Eastern

edited Jun 2, 2020 at 9:22

Abed Merii

59410 silver badges25 bronze badges

answered Apr 2, 2014 at 2:04

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

3 Comments

AstroFloyd Over a year ago

My datetimes are in the column called "date", so I'm doing df.date = df['date'].pytz_localize(pytz.utc).pytz_convert(pytz.timezone('Europe/Amsterdam')), but I get TypeError: index is not a valid DatetimeIndex or PeriodIndex. Why does the error mention "index" when I do not? Does this only work if the datetime is in the df index?

lb_so Over a year ago

@AstroFloyd --- see the thread below for your answer (I had same problem): This also works similarly for datetime columns, but you need dt after accessing the column: df['column'] = df['column'].dt.tz_convert('America/New_York')

FObersteiner Over a year ago

If pandas is to drop pytz at some point in the future, better omit that part in the code now. Just e.g. df.index.tz_localize("UTC").tz_convert("America/New_York")

wordsforthewise · Accepted Answer · 2021-01-02 23:24:45Z

52

The simplest way is to use to_datetime with utc=True:

df = pd.DataFrame({'Symbol': ['EUR/USD'] * 5,
                  'bid': [1.37622, 1.37624, 1.37619, 1.37616, 1.37616],
                  'ask': [1.37693, 1.37698, 1.37696, 1.37696, 1.37694]})

df.index = pd.to_datetime(['2014-01-01 21:55:34.378000',
                          '2014-01-01 21:55:40.410000',
                          '2014-01-01 21:55:47.210000',
                          '2014-01-01 21:55:57.963000',
                          '2014-01-01 21:56:03.117000'],
                           utc=True)

For more flexibility, you can convert timezones with tz_convert(). If your data column/index is not timezone-aware, you will get a warning, and should first make the data timezone-aware with tz_localize.

df = pd.DataFrame({'Symbol': ['EUR/USD'] * 5,
                  'bid': [1.37622, 1.37624, 1.37619, 1.37616, 1.37616],
                  'ask': [1.37693, 1.37698, 1.37696, 1.37696, 1.37694]})

df.index = pd.to_datetime(['2014-01-01 21:55:34.378000',
                          '2014-01-01 21:55:40.410000',
                          '2014-01-01 21:55:47.210000',
                          '2014-01-01 21:55:57.963000',
                          '2014-01-01 21:56:03.117000'])

df.index = df.index.tz_localize('GMT')
df.index = df.index.tz_convert('America/New_York')

This also works similarly for datetime columns, but you need dt after accessing the column:

df['column'] = df['column'].dt.tz_convert('America/New_York')

edited Jan 2, 2021 at 23:24

answered Aug 9, 2018 at 3:04

wordsforthewise

16.1k6 gold badges96 silver badges126 bronze badges

7 Comments

Ufos Over a year ago

and if your column is not an index, you can do: df['time_local'] = df['time'].dt.tz_localize('GMT').dt.tz_convert('America/New_York')

Saaru Lindestøkke Over a year ago

For me simply writing ...dt.tz_convert('Asia/Singapore') didn't work, I got strange times. I had to do ...dt.tz_convert(pytz.timezone('Asia/Singapore'))

FObersteiner Over a year ago

no need to localize to GMT; just parse the timestamps with kwarg utc=True (docs).

Psychotechnopath Over a year ago

Does this procedure (tc_localize & tc_convert) Also take into account the jungle that is DST?

BioData41 Over a year ago

In response to the comment-question by @Psychotechnopath, I just tested it out for 2020 and 2021 and it correctly captured the DST changes for EST.

|

Johannes Kuhn · Accepted Answer · 2019-09-20 16:39:08Z

6

To convert EST time into Asia tz

df.index = data.index.tz_localize('EST')
df.index = data.index.tz_convert('Asia/Kolkata')

Pandas has now inbuilt tz conversion ability.

edited Sep 20, 2019 at 16:39

Johannes Kuhn

15.3k6 gold badges53 silver badges80 bronze badges

answered Sep 20, 2019 at 16:33

Shivendra Rajawat

1412 silver badges4 bronze badges

1 Comment

FObersteiner Over a year ago

note this comment on EST - better use US/Eastern or America/New_York instead.

CPlus · Accepted Answer · 2024-03-08 06:20:31Z

0

This worked for me:

# Import pandas
import pandas as pd 

# Import pytz
import pytz

# If the column is not the index
# Assuming the column is in UTC or GMT times
# Convert to your desired time zone and remove the time zone information after conversion

df_1['time'] = pd.to_datetime(df_1['time'], unit='s', utc=True).dt.tz_convert('Europe/Paris').dt.tz_localize(None)

# To create a separate column with only the date values and without time
df_1['date'] = pd.to_datetime(df_1['time']).dt.normalize()

edited Mar 8, 2024 at 6:20

CPlus

5,11048 gold badges33 silver badges77 bronze badges

answered Mar 3, 2024 at 9:21

Raphael Affiah

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Converting time zone pandas dataframe

4 Answers 4

3 Comments

7 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

7 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related