Is there a function in pandas to convert timestamps with CST as the time zone?

Question

I have a data frame which has the time_zone and the date in different columns. I want to get the local time corresponding to the time_zone.

I have the data frame as below:

df = pd.DataFrame({'CREATED_DT':['2017-01-01 20:24:21','2017-01-01 21:10:54','2017-01-02 11:48:12','2017-01-02 19:30:53','2017-01-02 21:06:55'], 'Time_Zone':['EST','EST','CET','EST','CST']})

df['CREATED_DT'] = pd.to_datetime(df['CREATED_DT']).dt.tz_localize('UTC')

I have converted the created date to UTC and the time_zone is in a different column. I want to get the local time corresponding to the timezone. So the code for it is :

df['Local_Time'] = df.apply(lambda x: x['CREATED_DT'].tz_convert(x['timezone']), axis = 1)

Which works fine in case the time_zone is EST,CET but gives an error for CST which says:

UnknownTimeZoneError: ('CST', u'occurred at index 4')

I am not sure if there is a way to handle this other than hard coding ... please suggest if there is any other library or function that could be used?

Answer 1

The reason why CST as a timezone throws an error is because it can refer to 3 different timezones: Central Standard Time, which is North America's Central Time Zone (UTC -0600), China Standard Time (UTC +0800) and Cuba Standard Time (UTC -0400). I'm assuming you want to use Central Standard Time.

An easy way to converge time, is the use the pytz library, which has a constantly updated database of most timezones.

In your case, the line that converts the date to the timezone could be changed to this line:

import pytz
df['Local_Time'] = df.apply(lambda x: pytz.timezone("US/Central" if x['Time_Zone'] == 'CST' else x['Time_Zone']).normalize(x['CREATED_DT']), axis = 1)

(sidenote: in pytz, Central Standard Time is 'US/Central', China Standard Time is 'Asia/Shanghai' and Cuba Standard Time is 'Cuba'. For any other times, you can check this stackoverflow question or this wikipedia page)
It might also make your code a bit more readable if you made it a function instead of a lambda at this point though.

Answer 2

The following worked for me:

    df['Local_Time'] = pd.to_datetime(df,unit='s', utc=True)\
                          .map(lambda x: x.tz_convert('America/Chicago'))

There is no timezone named CST, CDT, or EST. This is the list of the name of timezones in pytz.

Hope this helps someone!