Convert unix timestamp in known timezone to datetime

user_18194226 0
Viewed 508

I have a data download that supplies timestamps in unix form. They don't have any timezone awareness - they're simply a number - but they're localised to US/Pacific. I want to end up with them in Europe/London time. Don't ask me why it's set up like this.... I wouldn't have done it myself but not in my power to change!

I have tried

    import datetime as dt, pandas as pd
    df['time'] = pd.to_datetime(df['timestamp'], unit = 's')
    df['London time'] = df['time'].dt.tz_localize('US/Pacific').dt.tz_convert('Europe/London')

and this works absolutely fine 99% of the time. The issue is with the DST transition where it throws an ambiguous error. Is there any way of dealing with this given that the unix time is ultimately just counting seconds and shouldn't be inherently ambiguous?

I would prefer a pandas solution, but can always use apply if need be. I had thought to use

    dt.datetime.fromtimestamp(timestamp, pytz.timezone('US/Pacific'))

as the initial conversion from unix but this gives me the Pacific equivalent of the timestamp if it were localised on my machine.

This is a sample of the data set which throws the error.

    import pandas as pd
    test_times = [1636246173, 1636248552, 1636248678, 1636251161, 1636251689]
    df = pd.DataFrame(test_times, columns = ['timestamp'])
pandas python-3.x python
1 Answers

just you this snippet, added ambiguous=True to tz_localize

import datetime as dt, pandas as pd
df['time'] = pd.to_datetime(df['timestamp'], unit = 's')
df['London time'] = df['time'].dt.tz_localize('US/Pacific', ambiguous=True).dt.tz_convert('Europe/London')

The error message says that it cannot infer Daylight Saving Time. Now, as probably everybody knows, the change from Daylight Saving Time to normal time occurs at the end of October at 2:00 am/3:00 am, and in fact, a quick look at the calendar tells us that DST change in 2017 was on October, 29th.

user_129005620
Related