Pandas: converting timestamp into datetime

Question

I have a column of date time strings in UTC which I need to convert to a viable datetime format in EST using pandas. I successfully converted the column, however I feel that my solution is long-winded and can be simplified, but I'm not sure how. Here is the way I'm currently converting the dates:

df['datetime'] = pd.to_datetime(df['datetime'])
df['datetime'] = df['datetime'].dt.strftime('%m/%d/%Y %H:%M:%S')
df['datetime'] = pd.to_datetime(df['datetime'])
df['datetime'] = df['datetime'] - pd.Timedelta(hours=5)

Here are a few sample of the original string format:

2014-02-07T00:25:40Z
2014-02-07T00:25:40Z
2014-02-07T00:25:41Z
2014-02-07T00:25:42Z
2014-02-07T00:25:42Z
2014-02-07T00:25:43Z
2014-02-07T00:25:43Z
2014-02-07T00:25:44Z
2014-02-07T00:25:44Z    
2014-02-07T00:25:44Z

Can I convert this column with one or two lines? I tried performing the Timedelta() in the same step as the datetime formatting but received an error. Alternately I tried using tz_convert() and tz_localize() but received error with tz_convert() saying that my datetime wasn't a viable datetime format (hence why I reset the column to datetime after formatting). With tz_localize() it just added -5:00 to the end of my datetime instead of actually subtracting the 5 hours from the UTC time.

This is how the output should look:

2014-02-06 19:25:40
2014-02-06 19:25:40
2014-02-06 19:25:41
2014-02-06 19:25:42
2014-02-06 19:25:42
2014-02-06 19:25:43
2014-02-06 19:25:43
2014-02-06 19:25:44
2014-02-06 19:25:44
2014-02-06 19:25:44

BENY · Accepted Answer · 2019-04-02 02:35:42Z

2

I will do tz_localize and tz_localize

pd.to_datetime(df.date).dt.tz_localize('UTC').\
     dt.tz_convert('EST').\
      dt.strftime('%Y-%m-%d %H:%M:%S')
0    2014-02-06 19:25:40
1    2014-02-06 19:25:41
2    2014-02-06 19:25:42
3    2014-02-06 19:25:42
4    2014-02-06 19:25:43
5    2014-02-06 19:25:43
6    2014-02-06 19:25:44
7    2014-02-06 19:25:44
8    2014-02-06 19:25:44
Name: date, dtype: object

edited Apr 2, 2019 at 2:35

answered Apr 2, 2019 at 1:59

BENY

324k22 gold badges176 silver badges250 bronze badges

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

4 Comments

andrewr Over a year ago

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

BENY Over a year ago

@andrrvt15 I am just using your input data and this is working fine on my side

BENY Over a year ago

@andrrvt15 github.com/influxdata/influxdb-python/issues/671, try to update your pandas

andrewr Over a year ago

I removed dt.tz_localize() and it looks to have worked!

Collectives™ on Stack Overflow

Pandas: converting timestamp into datetime

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related