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
– FObersteiner, Commented Jul 11, 2022 at 13:02

FObersteiner · Accepted Answer · 2022-07-11 13:03:28Z

1

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)

edited Jul 11, 2022 at 13:03

answered Jul 11, 2022 at 11:54

FObersteiner

26.1k9 gold badges60 silver badges94 bronze badges

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

1 Comment

FL44ME Over a year ago

This is perfect, thank you for the explanations

openwld · Accepted Answer · 2022-07-11 11:11:30Z

1

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')

answered Jul 11, 2022 at 11:11

openwld

1,1945 silver badges21 bronze badges

1 Comment

FL44ME Over a year ago

This solves it, but I forgot to mention that I'm using pandas, so other solution was better, but thank you

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related