Saturday, 14 September 2019

Convert from naive local daylight time to naive local standard time in pandas

I have hourly data records that were recorded in local daylight time (for me this is US/Pacific). These will be read in through csv. A gap exists at the beginning of DST at 02:00 when we spring forward. In fall, I believe that the data collected at 01:00 PDT is labeled 01:00 and the next hour is labeled 02:00 (and assumes PST).

I would like to translate the timestamps so they play well with other data stored in PST. Below is my attempt, in which I have focused on only the index which should simplify discussion.

tndx = pd.DatetimeIndex(["2016-11-06 00:00",""2016-11-06 01:00","2016-11-06 02:00","2016-11-06 03:00"])
tndx.tz_localize('US/Pacific',ambiguous="NaT").tz_convert('Etc/GMT+8') 
print(tndx).tz_localize(None)

Output is:

DatetimeIndex(['2016-11-05 23:00:00-08:00',                       'NaT',
               '2016-11-06 02:00:00-08:00', '2016-11-06 03:00:00-08:00']

There are two things wrong about this. First, from the perspective of PST it seems like I am now missing two timestamps at 00:00 and 01:00. I get that the procedure is lossy, but I don't see that the procedure has to be lossy beyond one timestamp. I get an exception with ambiguous = "infer" because there are no redundant values. When I explicitly set this to a boolean array, as suggested by karajdaar, I don't lose the extra time point. However, the boolean list isn't that easy to come by -- I can't use tndx because it isn't tz aware yet. The only way I can think of is this circuitous route through datetime.dst that involves a separate DataFrame and conversion:

# Create a date range that spans the possible times and is hourly
ndx2 = pd.date_range(start=pd.Timestamp(2016,11,5), end =pd.Timestamp(2016,11,7),freq='H',tz='US/Pacific')

# Here is the determination of whether it is dst
isdst = [bool(x.dst()) for x in ndx2.to_pydatetime()]

# I use DataFrame indexing to perform the lookup 
# for values in my original index
df2 = pd.DataFrame({"isdst":isdst},index=ndx2.tz_localize(None))
df2 = df2.loc[~df2.index.duplicated(keep="last")]
ambig = df2[tndx]    # This is what I would use for ambiguous

Second, I used Etc/GMT+8 because I essentially blundered into discovering it gives the right offsets and timestamps, particularly after I make the stamps naive again. If I do not strip the time zone information (ie without the last tz_convert(None)) the output would be:

>>> tndx.tz_localize('US/Pacific',ambiguous='NaT').tz_convert('Etc/GMT+8')
DatetimeIndex(['2016-11-05 23:00:00-08:00',                       'NaT',
               '2016-11-06 02:00:00-08:00', '2016-11-06 03:00:00-08:00'],
              dtype='datetime64[ns, Etc/GMT+8]', freq=None)

The offsets in this case look fine, but the timezone in the dtype seems misleading and in any event why is a time zone called GMT+8 giving offsets of -8? What am I not understanding about these conversions?



from Convert from naive local daylight time to naive local standard time in pandas

No comments:

Post a Comment