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

463 views Asked by At

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
1

There are 1 answers

1
Jeff On BEST ANSWER

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