How to change timezone of a pandas dataframe columnin Python

I have the following code creating a table of business days in NYSE during the past 5 years:

import pandas as pd
import datetime, pytz
import pandas_market_calendars as mcal

today_date = datetime.datetime.now(tz=pytz.timezone('US/Eastern'))
print(today_date)

start_date = datetime.date(today_date.year - 5, today_date.month, today_date.day)
print(start_date)

nyse_calender = mcal.get_calendar('NYSE')
print(nyse_calender.tz.zone)
nyse_business_days = nyse_calender.schedule(start_date=start_date, 
                                            end_date=today_date)

nyse_business_days

And here is the output:

            market_open                 market_close
2017-01-18  2017-01-18 14:30:00+00:00   2017-01-18 21:00:00+00:00
2017-01-19  2017-01-19 14:30:00+00:00   2017-01-19 21:00:00+00:00
2017-01-20  2017-01-20 14:30:00+00:00   2017-01-20 21:00:00+00:00
2017-01-23  2017-01-23 14:30:00+00:00   2017-01-23 21:00:00+00:00
2017-01-24  2017-01-24 14:30:00+00:00   2017-01-24 21:00:00+00:00
...     ...     ...
2022-01-11  2022-01-11 14:30:00+00:00   2022-01-11 21:00:00+00:00
2022-01-12  2022-01-12 14:30:00+00:00   2022-01-12 21:00:00+00:00
2022-01-13  2022-01-13 14:30:00+00:00   2022-01-13 21:00:00+00:00
2022-01-14  2022-01-14 14:30:00+00:00   2022-01-14 21:00:00+00:00
2022-01-18  2022-01-18 14:30:00+00:00   2022-01-18 21:00:00+00:00

1260 rows × 2 columns

I actually expect to see times in Eastern time zone in US but it is not the case.

For instance, market_open in the first row should be 2017-01-18 09:30:00+00:00 but what it shows is 2017-01-18 14:30:00+00:00.

What am I supposed to do to get timezones in both columns in US/Eastern? It shows timzones in UTC but it is not what I need.

ADDENDUM:

I tried the following based on @IanKenney 's comment:

nyse_business_days["market_open"] = nyse_business_days["market_open"].tz_localize(pytz.utc).tz_convert(pytz.timezone('US/Eastern'))

And when I print it out, here is what I get:

    market_open     market_close
2017-01-18  NaT     2017-01-18 21:00:00+00:00
2017-01-19  NaT     2017-01-19 21:00:00+00:00
2017-01-20  NaT     2017-01-20 21:00:00+00:00
2017-01-23  NaT     2017-01-23 21:00:00+00:00
2017-01-24  NaT     2017-01-24 21:00:00+00:00
...     ...     ...
2022-01-11  NaT     2022-01-11 21:00:00+00:00
2022-01-12  NaT     2022-01-12 21:00:00+00:00
2022-01-13  NaT     2022-01-13 21:00:00+00:00
2022-01-14  NaT     2022-01-14 21:00:00+00:00
2022-01-18  NaT     2022-01-18 21:00:00+00:00

1260 rows × 2 columns

Solution 1:

You can try the following to convert the timezones

nyse_business_days['market_open'] = [t.tz_convert(pytz.timezone('US/Eastern')) for t in nyse_business_days['market_open']]

How to change timezone of a pandas dataframe columnin Python

Solution 1:

Related

Recent Posts