Import Excel Time Into Pandas With Decimal Seconds

I have an excel spread sheet (.xls) that contains a time column. The time is displayed in Excel as minutes:seconds.tenths of seconds. Such as '50:59.2' '50:59.4'. The raw data c

Solution 1:

Pandas used xlrd to read Excel files and the xlrd.xldate_as_tuple() function to get the date components to feed into datetime.time().

However, xlrd.xldate_as_tuple() only returns seconds and not microseconds so that information is lost to pandas.

For example, say you have an Excel file like this (Number is the same as time but without a format):

Time            Number
0:17:51.000     0.012395833
0:17:51.200     0.012398148
0:17:51.400     0.012400463
0:17:51.600     0.012402778
0:17:52.800     0.012416667
0:17:53.000     0.012418981

Then, if you read the data with the following program:

import xlrd

workbook = xlrd.open_workbook('minutes.xls')
worksheet = workbook.sheet_by_name('Sheet1')

cell =  worksheet.cell(2,0)

# Print the A2 cell value as a number.
print cell.value

# Print the seconds part of the A2 cell value.
print (cell.value * (24*60*60)) % 60

# Print the xldate_as_tuple output.
print xlrd.xldate_as_tuple(cell.value, workbook.datemode)

You get the following output:

0.0123981481481
51.2
(0, 0, 0, 0, 17, 51)

So, the decimal part of the seconds is read (51.2) but not returned by xldate_as_tuple() and thus not available to pandas.

This is the documented behaviour of xldate_as_tuple() but you could submit a feature request or a pull request.

Update: I submitted a fix for this to xlrd.