pandas.to_datetime() how to convert from local timezone to UTC unix timestamp?

user_12387950 0
Viewed 5269

I have a list of date values in form of 'YYYY-MM-DD'. These values are in timezone of local PC (that is not known for me). These values are loaded as a Series in Pandas dataframe and I want to convert them into UTC timestamp.

  1. If I use very simple code:
ts = pd.to_datetime(s, format="%Y-%m-%d")
print(ts[0])
print(int(ts[0].timestamp()))
print(ts[0].strftime("%d.%m.%Y %Z"))

It gives me output:

2021-01-01 00:00:00
1609459200   --> this is 'Friday, January 1, 2021 0:00:00 UTC'
01.01.2021

i.e. it takes source strings as UTC and put it in UTC. But my source string is in localtime and output is not correct.

  1. Then I found an option to provide timezone info in source strings this way:
s = pd.Series(['2021-01-01 Europe/Moscow', '2021-01-02 Europe/Moscow'])
ts = pd.to_datetime(s, format="%Y-%m-%d %Z")
print(ts[0])
print(int(ts[0].timestamp()))
print(ts[0].strftime("%d.%m.%Y %Z"))

And it gives me result that I really need:

2021-01-01 00:00:00+03:00
1609448400 --> this is correct 'Thursday, December 31, 2020 21:00:00 UTC'
01.01.2021 MSK

But timezone name is hardcoded here.

  1. So, I need to get a local timezone name from a PC where my code works. I tried this way:
from datetime import datetime
from dateutil import tz
print(datetime.now(tz.tzlocal()).tzname())

and it gives me output MSK. But the problem with this output - when I try to use it for Pandas.to_datetime - it gives me an error:

s = pd.Series(['2021-01-01 MSK', '2021-01-02 MSK'])
ts = pd.to_datetime(s, format="%Y-%m-%d %Z")

ValueError: time data '2021-01-01 MSK' does not match format '%Y-%m-%d %Z' (match)

So, I see following ways forward:

  • either somehow get a full timezone name Europe/Moscow instead of short MSK from my python code,
  • or make pandas.to_datetime() format option %Z recognize short form of timezone name MSK,
  • or somehow pre-process source strings before importing it to pandas (least desirable path, so I haven't studied it yet).

I'm a bit stuck to chose a way forward here. May you give me advice which way gives me better code?

pandas timezone python
1 Answers

I think you can use Series.dt.tz_localize:

from dateutil import tz

s = pd.Series(['2021-01-01', '2021-01-02'])
ts = pd.to_datetime(s, format="%Y-%m-%d").dt.tz_localize(tz.tzlocal())
user_29010020
Related