Convert column of epoch timestamps to datetime with timezone

Question

I have a dataframe in my Jupyter notebook with a column of epoch timestamps, it looks like this:

 Name    stop      ts            remark
 A       01        1546470653    -
 B       032       1546470969    Not listed
 C       022       1546471069    Not listed
 D       045       1546471238    Not listed

What I'm trying to do is to convert the epoch timestamp to human readable date & time with time zone (utc+8), the result should look like this:

 Name    stop      ts            remark       timestamp
 A       01        1546470653    -            2019-01-03T07:10:53+08:00
 B       032       1546470969    Not listed   2019-01-03T07:16:09+08:00
 C       022       1546471069    Not listed   2019-01-03T07:17:49+08:00
 D       045       1546471238    Not listed   2019-01-03T07:20:38+08:00

Can someone help me achieve this? Thanks a lot!

score 10 · Accepted Answer · answered Apr 01 '19 at 07:23

10

Use pd.to_datetime with unit='s'. You can then set the timezone using the tz_* methods.

df['timestamp'] = (pd.to_datetime(df['ts'], unit='s')
                     .dt.tz_localize('utc')
                     .dt.tz_convert('Asia/Hong_Kong'))    
df

  Name  stop          ts      remark                 timestamp
0    A     1  1546470653           - 2019-01-03 07:10:53+08:00
1    B    32  1546470969  Not listed 2019-01-03 07:16:09+08:00
2    C    22  1546471069  Not listed 2019-01-03 07:17:49+08:00
3    D    45  1546471238  Not listed 2019-01-03 07:20:38+08:00

answered Apr 01 '19 at 07:23

cs95

379,657
97
704
746

1

How can we remove the +08:00 from timestamp ? – Pranav101py Apr 16 '21 at 12:38
1

@Pranav101py add a `.dt.tz_localize(None)` at the end after `tz_convert` – cs95 Nov 07 '21 at 00:13
When I ran this I needed to remove the 'dt' but otherwise worked perfectly. – John Conor Aug 02 '22 at 14:49

Convert column of epoch timestamps to datetime with timezone

1 Answers1