Convert timezone of multiple columns in a pandas DataFrame according to a third column?

Question

I have a dataframe with multiple columns of timestamps in UTC and a column of the timezone it should be converted to. How would I write a function to map this?

           created_at            ended_at             timezone
0 2014-11-19 16:11:45 2014-11-19 16:30:31     America/New_York
1 2014-11-19 18:37:47 2014-11-19 18:57:55     America/New_York
2 2014-11-19 18:59:21 2014-11-19 19:51:29  America/Los_Angeles
3 2014-11-19 19:47:35 2014-11-19 20:58:04     America/New_York
4 2014-11-19 20:29:46 2014-11-19 20:40:36     America/New_York
5 2014-11-19 22:23:42 2014-11-19 22:58:43  America/Los_Angeles
6 2014-11-20 16:31:24 2014-11-20 17:49:12     America/New_York

Jeff · Accepted Answer · 2014-11-21 15:17:28Z

2

You can prob do this. But keep in mind that have a column that is a single timezone is somewhat more efficient. So you may want to organize your data differently.

In [16]: def conv(col, tzs):
   ....:     return [ d.tz_localize(tz) for d, tz in zip(col, tzs) ]
   ....: 

In [17]: df
Out[17]: 
                date1               date2                   tz
0 2014-11-19 16:11:45 2014-11-19 16:30:31     America/New_York
1 2014-11-19 18:37:47 2014-11-19 18:57:55     America/New_York
2 2014-11-19 18:59:21 2014-11-19 19:51:29  America/Los_Angeles
3 2014-11-19 19:47:35 2014-11-19 20:58:04     America/New_York
4 2014-11-19 20:29:46 2014-11-19 20:40:36     America/New_York
5 2014-11-19 22:23:42 2014-11-19 22:58:43  America/Los_Angeles
6 2014-11-20 16:31:24 2014-11-20 17:49:12     America/New_York

In [18]: df['date1_tz'] = conv(df['date1'],df['tz'])

In [19]: df['date2_tz'] = conv(df['date2'],df['tz'])

In [20]: df
Out[20]: 
                date1               date2                   tz                   date1_tz                   date2_tz
0 2014-11-19 16:11:45 2014-11-19 16:30:31     America/New_York  2014-11-19 16:11:45-05:00  2014-11-19 16:30:31-05:00
1 2014-11-19 18:37:47 2014-11-19 18:57:55     America/New_York  2014-11-19 18:37:47-05:00  2014-11-19 18:57:55-05:00
2 2014-11-19 18:59:21 2014-11-19 19:51:29  America/Los_Angeles  2014-11-19 18:59:21-08:00  2014-11-19 19:51:29-08:00
3 2014-11-19 19:47:35 2014-11-19 20:58:04     America/New_York  2014-11-19 19:47:35-05:00  2014-11-19 20:58:04-05:00
4 2014-11-19 20:29:46 2014-11-19 20:40:36     America/New_York  2014-11-19 20:29:46-05:00  2014-11-19 20:40:36-05:00
5 2014-11-19 22:23:42 2014-11-19 22:58:43  America/Los_Angeles  2014-11-19 22:23:42-08:00  2014-11-19 22:58:43-08:00
6 2014-11-20 16:31:24 2014-11-20 17:49:12     America/New_York  2014-11-20 16:31:24-05:00  2014-11-20 17:49:12-05:00

edited Nov 21, 2014 at 15:17

answered Nov 20, 2014 at 23:12

Jeff

130k21 gold badges223 silver badges189 bronze badges

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

1 Comment

Victor Uriarte Over a year ago

One thing to note about the new columns being created is they are of dtype('O') instead of datetime64[ns]. So many operations that are native to the datetime64[ns] won't work as expected anymore. As @Jeff said, reorganizing the data might be more beneficial. Personally I am using a combination of date1_UTC and data1_localTZ so that the columns can remain as datetime64[ns] dtypes

Collectives™ on Stack Overflow

Convert timezone of multiple columns in a pandas DataFrame according to a third column?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related