Python - pandas datetime column with multiple timezones

Question

I have a data frame with multiple users and timezones, like such:

cols = ['user', 'zone_name', 'utc_datetime']
data = [
    [1, 'Europe/Amsterdam', pd.to_datetime('2019-11-13 11:14:15')],
    [2, 'Europe/London', pd.to_datetime('2019-11-13 11:14:15')],
]

df = pd.DataFrame(data, columns=cols)

Based on this other post, I apply the following change to get the user local datetime:

df['local_datetime'] = df.groupby('zone_name')[
    'utc_datetime'
].transform(lambda x: x.dt.tz_localize(x.name))

Which outputs this:

    user    zone_name     utc_datetime          local_datetime
    1   Europe/Amsterdam  2019-11-13 11:14:15   2019-11-13 11:14:15+01:00
    2   Europe/London     2019-11-13 11:14:15   2019-11-13 11:14:15+00:00

However, the local_datetime column is an object and I cannot find a way to get it as datetime64[ns] and in the following format (desired output):

    user    zone_name     utc_datetime          local_datetime
    1   Europe/Amsterdam  2019-11-13 11:14:15   2019-11-13 12:14:15
    2   Europe/London     2019-11-13 11:14:15   2019-11-13 11:14:15

prp · Accepted Answer · 2020-01-29 14:23:18Z

4

I think you need Series.dt.tz_convert in lambda function:

df['local_datetime'] = (pd.to_datetime(df.groupby('zone_name')['utc_datetime']
    .transform(lambda x: x.dt.tz_localize('UTC').dt.tz_convert(x.name))
    .astype(str).str[:-6]))

print(df)
   user         zone_name        utc_datetime      local_datetime
0     1  Europe/Amsterdam 2019-11-13 11:14:15 2019-11-13 12:14:15
1     2     Europe/London 2019-11-13 11:14:15 2019-11-13 11:14:15

edited Jan 29, 2020 at 14:23

prp

9621 gold badge9 silver badges24 bronze badges

answered Jan 29, 2020 at 13:31

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

3 Comments

prp Over a year ago

The data type is correct now :), but it should add 1 hour to the Amsterdam timezone not substract it.

jezrael Over a year ago

@prp - hmmm, I am not expert for timezones, but it seems output is correct, if positive number after dates in utc are subtract values, if negative then are added values, check this - last 2 samples to end of page

prp Over a year ago

Amsterdam is +1 hour than London, but you can fix this by changing the arguments. I'll edit your answer. Thanks!

bhakyuz · Accepted Answer · 2020-02-20 15:36:26Z

Relatively shorter answer using DataFrame.apply:

df['local_datetime'] = df.apply(lambda x: x.utc_datetime.tz_localize(tz = "UTC").tz_convert(x.zone_name), axis = 1)
print(df)
   user         zone_name        utc_datetime             local_datetime
0     1  Europe/Amsterdam 2019-11-13 11:14:15  2019-11-13 12:14:15+01:00
1     2     Europe/London 2019-11-13 11:14:15  2019-11-13 11:14:15+00:00

If you want to remove the time zone information, you can localize times by passing None

df['local_datetime'] = df.apply(lambda x: x.utc_datetime.tz_localize(tz = "UTC").tz_convert(x.zone_name).tz_localize(None), axis = 1)
print(df)
   user         zone_name        utc_datetime      local_datetime
0     1  Europe/Amsterdam 2019-11-13 11:14:15 2019-11-13 12:14:15
1     2     Europe/London 2019-11-13 11:14:15 2019-11-13 11:14:15

Collectives™ on Stack Overflow

Python - pandas datetime column with multiple timezones

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related