Pandas: Set locale to data frame with ambiguous local time from CSV-file

user_8126904 0
Viewed 531

I read CSV-files with ambiguous local time -- ambiguous due to daylight saving time.

When I apply tz_localize() I run in the following error:

AmbiguousTimeError: There are %i dst switches when there should only be 1.

Here is my code to reproduce the error. Note that from 2am to 3am every datetime is a duplicate due to daylight saving time.

index = pd.DatetimeIndex(['2018-10-28 01:00:00', '2018-10-28 01:15:00',
                          '2018-10-28 01:30:00', '2018-10-28 01:45:00',
                          '2018-10-28 02:00:00', '2018-10-28 02:00:00',
                          '2018-10-28 02:15:00', '2018-10-28 02:15:00',
                          '2018-10-28 02:30:00', '2018-10-28 02:30:00',
                          '2018-10-28 02:45:00', '2018-10-28 02:45:00',
                          '2018-10-28 03:00:00', '2018-10-28 03:15:00',
                          '2018-10-28 03:30:00', '2018-10-28 03:45:00',
                          '2018-10-28 04:00:00'], freq='infer')

data = list(range(len(index)))

df = pd.DataFrame(data=data,index=index)
df.index = df.index.tz_localize('Europe/Berlin', ambiguous='infer')

I don't quite understand why the error occurs even with parameter ambiguous='infer'.
Does my dataframe have to be sorted in any way?
How can I create sorted data frames with pd.read_csv()
I wish to apply ambiguous='infer' with respect to the order of the CSV file. Precisly I mean: the order of the ambiguous time format helps to differ between summer and winter time.

I would be glad about help for a solution and about a broader explanation. I couldn't find much about how order can play a role in data frames--does it sometimes?

pandas python
3 Answers

The way you have stored your data, there is simply not enough information to infer which datetime value belongs to summer time and which to winter time.

A naive approach would be to say you have ambiguous datetime values, and just set ambiguous=True instead of ambiguous='infer' on the last line:

df.index = df.index.tz_localize('Europe/Berlin', ambiguous=True)

This would cause all times before 3 AM to be assigned to summer time, instead of splitting the duplicate values between summer and winter time.

user_62564820 
index.tz_localize('Europe/Berlin', ambiguous=np.array([True]*17))

I think some of your dst time needs to be inferred, but you have duplicate time index causing failure to infer, use optional np.array would work

user_85603820

The issue you're seeing in the sample dataset provided is caused by the order of the data. The ambiguous='infer' option requires the rows to be in correct chronological order, your sample data has the rows alternating between before and after the DST change. The following code works, changing only the initial order of the times in index.

index = pd.DatetimeIndex(['2018-10-28 01:00:00', '2018-10-28 01:15:00',
                          '2018-10-28 01:30:00', '2018-10-28 01:45:00',
                          '2018-10-28 02:00:00', '2018-10-28 02:15:00', 
                          '2018-10-28 02:30:00', '2018-10-28 02:45:00', 
                          '2018-10-28 02:00:00', '2018-10-28 02:15:00',
                          '2018-10-28 02:30:00', '2018-10-28 02:45:00',
                          '2018-10-28 03:00:00', '2018-10-28 03:15:00',
                          '2018-10-28 03:30:00', '2018-10-28 03:45:00',
                          '2018-10-28 04:00:00'], freq='infer')

data = list(range(len(index)))

df = pd.DataFrame(data=data,index=index)
df.index = df.index.tz_localize('Europe/Berlin', ambiguous='infer')
user_68960610
Related