Timestamp with timezone column in Pandas

I am reading data from a Java source. I end up with the following dataframe:

df.head()

    open        timestamp
0   1.13550     2019-02-24T17:00-06:00[US/Central]
1   1.13570     2019-02-24T17:05-06:00[US/Central]
2   1.13560     2019-02-24T17:10-06:00[US/Central]
3   1.13565     2019-02-24T17:15-06:00[US/Central]
4   1.13570     2019-02-24T17:20-06:00[US/Central]

df.dtypes

open        float64
timestamp   object
dtype: object

How can I convert column timestamp to Datetime with timezone in Pandas? Is there such thing in Pandas?

I found this post but it does not seem to parse timezone, just add a timezone later. How to read datetime with timezone in pandas

Any help/hint is welcomed

2 answers

  • answered 2019-07-10 22:15 Quang Hoang

    You can try remove the [...] part, then pass it to to_datetime:

    pd.to_datetime(df.timestamp.str.extract('(.*)\[.*\]')[0])
    

    returns:

    0   2019-02-24 17:00:00-06:00
    1   2019-02-24 17:05:00-06:00
    2   2019-02-24 17:10:00-06:00
    3   2019-02-24 17:15:00-06:00
    4   2019-02-24 17:20:00-06:00
    Name: 0, dtype: datetime64[ns, pytz.FixedOffset(-360)]
    

    You can keep the timezone label by adding one more capture group in the regex pattern:

    pattern = '(?P<time>.*)\[(?P<zone>.*)\]'
    new_df = df.timestamp.str.extract(pattern)
    

    Then new_df is:

                         time        zone
    0  2019-02-24T17:00-06:00  US/Central
    1  2019-02-24T17:05-06:00  US/Central
    2  2019-02-24T17:10-06:00  US/Central
    3  2019-02-24T17:15-06:00  US/Central
    4  2019-02-24T17:20-06:00  US/Central
    

    which you can convert time by pd.to_datetime.

  • answered 2019-07-10 22:21 WeNYoBen

    One way

    pd.to_datetime(df.timestamp.str.split('[').str[0])
    Out[137]: 
    0   2019-02-24 17:00:00-06:00
    1   2019-02-24 17:05:00-06:00
    2   2019-02-24 17:10:00-06:00
    3   2019-02-24 17:15:00-06:00
    4   2019-02-24 17:20:00-06:00
    Name: timestamp, dtype: datetime64[ns, pytz.FixedOffset(-360)]