XLDateAmbiguous workaround

Question

Reading Excel files into Python often means tripping over the Excel leap year issue. This is described in many posts, but none offer a convenient solution. So this is what I'm asking here. With code such as:

import xlrd
from pandas import *

xlfile = 'test.xlsx'
wb = xlrd.open_workbook(xlfile)

sn = wb.sheet_names()
dfs = [read_excel(xlfile, x) for x in sn]

How could one avoid the resulting issue*:

---------------------------------------------------------------------------
XLDateAmbiguous                           Traceback (most recent call last)
<ipython-input-8-1db99305e2ac> in <module>()
      1 sn = wb.sheet_names()
      2 
----> 3 dfs = [read_excel(xlfile, x) for x in sn]

/R/.virtualenv/pydata/lib/python2.7/site-packages/pandas/io/excel.pyc in read_excel(path_or_buf, sheetname, kind, **kwds)
     50     """
     51     return ExcelFile(path_or_buf,kind=kind).parse(sheetname=sheetname,
---> 52                                                   kind=kind, **kwds)
     53 
     54 class ExcelFile(object):

/R/.virtualenv/pydata/lib/python2.7/site-packages/pandas/io/excel.pyc in parse(self, sheetname, header, skiprows, skip_footer, index_col, parse_cols, parse_dates, date_parser, na_values, thousands, chunksize, **kwds)
    138                                      chunksize=chunksize,
    139                                      skip_footer=skip_footer,
--> 140                                      **kwds)
    141 
    142     def _should_parse(self, i, parse_cols):

/R/.virtualenv/pydata/lib/python2.7/site-packages/pandas/io/excel.pyc in _parse_excel(self, sheetname, header, skiprows, skip_footer, index_col, has_index_names, parse_cols, parse_dates, date_parser, na_values, thousands, chunksize, **kwds)
    194                 if parse_cols is None or should_parse[j]:
    195                     if typ == XL_CELL_DATE:
--> 196                         dt = xldate_as_tuple(value, datemode)
    197                         # how to produce this first case?
    198                         if dt[0] < datetime.MINYEAR:  # pragma: no cover

/R/.virtualenv/pydata/lib/python2.7/site-packages/xlrd/xldate.pyc in xldate_as_tuple(xldate, datemode)
     78 
     79     if xldays < 61 and datemode == 0:
---> 80         raise XLDateAmbiguous(xldate)
     81 
     82     jdn = xldays + _JDN_delta[datemode]

XLDateAmbiguous: 1.0

* other than changing the date system manually in Excel prior to entering any data or searching/replacing 1/1/1900 with NAs...

Answer 1

I've had success with this:

#Set local time to dataframe index
dat['local_time']=pd.to_datetime(dat[local_time_column_name], format=date_format)
dat=dat.set_index('local_time')
dat=dat.tz_localize(timezone, ambiguous='infer')

Setting timezone-unknown date-time to the dataframe index, and then using the ambiguous='infer' flag.