Can't read excel decimal seconds

[dershow]

related to #4332

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 contains hours:minutes:seconds.decimalseconds.

It seems that Pandas uses xldate_as_tuple() which apparently rounds all seconds, so the decimal part is dropped. So the above two data points both import as "50:59".

I suggest using a different conversion method, at least as an option. That way the data will not be dropped, as it is now.
I did also post this same issue to xlrd.

[jmcnamara]

Here is some background from my answer to this question on SO:

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() so it will have to be fixed in xlrd. It could be worked around in Pandas but Excel dates and times are a collection of edge cases so it wouldn't be worth doing.

I'll submit a PR to xlrd with a fix.

[jmcnamara]

I've submitted a PR to xlrd (python-excel/xlrd/pull/77).

However, it may need an API change to fix properly in which case pandas/io/excel.py may need a minor change to accommodate it. I'll update this issue when there is some progress on the xlrd side.

[jmcnamara]

Submitted updated patch to xlrd for this: python-excel/xlrd/pull/78

[jreback]

@jmcnamara

look at the install script here on a 3.3 build. I believe xlsxwriter had a setup error (which didn't cause it to ultimately fail), - could be random too: https://travis-ci.org/jreback/pandas/jobs/17360736

[jmcnamara]

@jreback

It could be a random error or an error in an unrelated thread.

Looking at the full log doesn't make it any clearer.

XlsxWriter has it's own Travis CI build and I haven't seen any issues there. However, if you see another one in Pandas let me know.

[jreback]

will do

[jmcnamara]

Before the patch in #6934:

>>> import pandas as pd
>>> pd.read_excel('decimal_seconds_1900.xls', 'Sheet1')
       Time
0  00:17:51
1  00:17:51
2  00:17:51
3  00:17:52
4  00:17:52
5  00:17:52

After the patch:

>>> import pandas as pd
>>> pd.read_excel('decimal_seconds_1900.xls', 'Sheet1')
              Time
0         00:17:51
1  00:17:51.200000
2  00:17:51.400000
3  00:17:51.600000
4  00:17:51.800000
5         00:17:52

[jreback]

closed by #6934