pandas localize and convert datetime column instead of the datetimeindex

Question

I have the following dataframe, which is indexed by a 'tz-aware' Datetimeindex.

In [92]: df
Out[92]: 
                                                   last_time
ts_recv
2017-02-13 07:00:01.103036+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:03.065284+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:13.244515+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:17.562202+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:17.917565+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:21.985626+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:28.096251+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:32.087421+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:33.386040+01:00  2017-02-13 16:03:23.626000
2017-02-13 07:00:43.923534+01:00  2017-02-13 16:03:23.626000

I only have one column called last_time which also contains time but as strings and in a different timezone (America/New_York) than the one in the index (which is Europe/Paris).

My goal is to convert this column to a datetime, in the right timezone.

I've tried the following:

In [94]: pd.to_datetime(df['last_time'])
Out[94]: 
ts_recv
2017-02-13 07:00:01.103036+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:03.065284+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:13.244515+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:17.562202+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:17.917565+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:21.985626+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:28.096251+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:32.087421+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:33.386040+01:00   2017-02-13 16:03:23.626
2017-02-13 07:00:43.923534+01:00   2017-02-13 16:03:23.626
Name: last_time, dtype: datetime64[ns]

This effectively converts the column to datetime objects.

But the following fails

In [96]: pd.to_datetime(df['last_time']).tz_localize('America/New_York')

with the error

TypeError: Already tz-aware, use tz_convert to convert.

I manage to get the Series I want with the following

In [104]: pd.Series(pd.DatetimeIndex(df['last_time'].values)
          .tz_localize('America/New_York').tz_convert('Europe/Paris'))
Out[104]: 
0   2017-02-13 22:03:23.626000+01:00
1   2017-02-13 22:03:23.626000+01:00
2   2017-02-13 22:03:23.626000+01:00
3   2017-02-13 22:03:23.626000+01:00
4   2017-02-13 22:03:23.626000+01:00
5   2017-02-13 22:03:23.626000+01:00
6   2017-02-13 22:03:23.626000+01:00
7   2017-02-13 22:03:23.626000+01:00
8   2017-02-13 22:03:23.626000+01:00
9   2017-02-13 22:03:23.626000+01:00
dtype: datetime64[ns, Europe/Paris]

I can then reindex it using the original datetimeindex and plug it back to the dataframe.

However I find this solution quite dirty and I'm wondering if there's a better way to do it.

Answer 1

You were almost there - just add .dt accessor...

Source DF:

In [86]: df
Out[86]:
                                             last_time
ts_recv
2017-02-13 06:00:01.103036  2017-02-13 16:03:23.626000
2017-02-13 06:00:03.065284  2017-02-13 16:03:23.626000
2017-02-13 06:00:13.244515  2017-02-13 16:03:23.626000
2017-02-13 06:00:17.562202  2017-02-13 16:03:23.626000
2017-02-13 06:00:17.917565  2017-02-13 16:03:23.626000
2017-02-13 06:00:21.985626  2017-02-13 16:03:23.626000
2017-02-13 06:00:28.096251  2017-02-13 16:03:23.626000
2017-02-13 06:00:32.087421  2017-02-13 16:03:23.626000
2017-02-13 06:00:33.386040  2017-02-13 16:03:23.626000
2017-02-13 06:00:43.923534  2017-02-13 16:03:23.626000

In [87]: df.dtypes
Out[87]:
last_time    object
dtype: object

Converting to datetime + TZ:

In [88]: df['last_time'] = pd.to_datetime(df['last_time']) \
                             .dt.tz_localize('Europe/Paris') \
                             .dt.tz_convert('America/New_York')

In [89]: df
Out[89]:
                                                  last_time
ts_recv
2017-02-13 06:00:01.103036 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:03.065284 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:13.244515 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:17.562202 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:17.917565 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:21.985626 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:28.096251 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:32.087421 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:33.386040 2017-02-13 10:03:23.626000-05:00
2017-02-13 06:00:43.923534 2017-02-13 10:03:23.626000-05:00

In [90]: df.dtypes
Out[90]:
last_time    datetime64[ns, America/New_York]
dtype: object