Python - How to convert datetime object to timezone given as string in a column?

Question

I have a column of UTC times which I need to convert to 'local' times based on the UTC timezone provided in a second column as a string. How do I assign a timezone using pytz to the time in the column based on string UTC timezone provided?

Next time please add example data as text instead of image (see e.g. the dummy data in my answer). Copy&paste is much easier that way, and therefore it's more likely to get better solution. Also, it is helpful if you show what you have tried, so we know where to pick up. — FObersteiner, Jul 11 '22 at 13:02

score 1 · Answer 1 · answered Jul 11 '22 at 11:11

import pytz

# fmt: Etc/GMT{+,-}{tm}
def format_tz(s:str):
    icon = '+' # default to plus sign
    s = s.strip()
    if s[0] == '-':
        icon = '-' # change to minus if is minus
    if s[0] in ('+', '-'):
        s = s[1:] # strip pluses and minuses
    i:int = int(s) # change to integer
    return pytz.timezone(f'Etc/GMT{icon}{i}') # convert to timezone

and for the time(excl. milliseconds)

# fmt: yyyy-mm-dd hh:mm:ss.MMM
def format_time(s:str):
    return time.strptime(s[:-5], '%Y-%m-%d %H:%M:%S')

This solves it, but I forgot to mention that I'm using pandas, so other solution was better, but thank you — FL44ME, Jul 11 '22 at 13:50

FObersteiner · Accepted Answer · 2022-07-11T13:03:28.387

Parse the date/time columns to datetime; for the UTC (offset) column do the same but just keep the offset. Then convert to this offset as time zone. EX:

import pandas as pd

df = pd.DataFrame({"bed_time": ["2021-03-28 00:40:00.000","2021-03-28 06:00:00.000"],
                   "wake_time": ["2021-03-27 00:00:00.000","2021-03-27 08:10:00.000"],
                   "UTC": ["+0400","+0500"]}) # adjusted example here to take mixed UTC offsets in to account

# parse to UTC and get the tz as a fixed offset
df['bed_time'] = pd.to_datetime(df['bed_time'], utc=True)
df['wake_time'] = pd.to_datetime(df['wake_time'], utc=True)
df['offset'] = pd.to_datetime(df['UTC'], format='%z').apply(lambda t: t.tz)

Now we have UTC and a fixed offset time zone;

df
                   bed_time  ...                 offset
0 2021-03-28 00:40:00+00:00  ...  pytz.FixedOffset(240)
1 2021-03-28 06:00:00+00:00  ...  pytz.FixedOffset(300)

So we can convert to that;

# convert to tz individually
df['bed_time'] = df.apply(lambda row: row['bed_time'].tz_convert(row['offset']), axis=1)
df['wake_time'] = df.apply(lambda row: row['wake_time'].tz_convert(row['offset']), axis=1)

to get

df
                    bed_time  ...                 offset
0  2021-03-28 04:40:00+04:00  ...  pytz.FixedOffset(240)
1  2021-03-28 11:00:00+05:00  ...  pytz.FixedOffset(300)

This is perfect, thank you for the explanations – FL44ME Jul 11 '22 at 13:48 — FL44ME, Jul 11 '22 at 13:48

Python - How to convert datetime object to timezone given as string in a column?

2 Answers2