Xlsxwriter, Py27 and Linux Failure on to_excel with Timedelta

[WillAyd]

See this comment in #19829. The test to write Timedelta data to an excel file is failing only with the combination of xlsxwriter as the engine using Py27 and on Linux. All other engines (save openpyxl which has an issue referenced in #19900), Python versions and OSes appear to work, but this particular combination kept failing on Travis.

Need help from someone running Linux to debug further. Right now the test cases are imperatively skipping this combination:

pandas/pandas/tests/io/test_excel.py

Line 1378 in 1e4c50a

if engine == 'xlsxwriter' and (sys.version_info[0] == 2 and

Here's the Travis build that was failing and I've provided the traceback of the test below for reference

=================================== FAILURES ===================================
�[31m�[1m________ TestExcelWriter.test_to_excel_timedelta[xlsxwriter-.xlsx-True] ________�[0m
[gw0] linux2 -- Python 2.7.14 /home/travis/miniconda3/envs/pandas/bin/python

self = <pandas.tests.io.test_excel.TestExcelWriter object at 0x7f44821a0b50>
merge_cells = True, engine = 'xlsxwriter', ext = '.xlsx'

�[1m    def test_to_excel_timedelta(self, merge_cells, engine, ext):�[0m
�[1m        # GH 19242, GH9155 - test writing timedelta to xls�[0m
�[1m        if engine == 'openpyxl':�[0m
�[1m            pytest.xfail('Timedelta roundtrip broken with openpyxl')�[0m
�[1m        frame = DataFrame(np.random.randint(-10, 10, size=(20, 1)),�[0m
�[1m                          columns=['A'],�[0m
�[1m                          dtype=np.int64�[0m
�[1m                          )�[0m
�[1m        expected = frame.copy()�[0m
�[1m        frame['new'] = frame['A'].apply(lambda x: timedelta(seconds=x))�[0m
�[1m        expected['new'] = expected['A'].apply(�[0m
�[1m            lambda x: timedelta(seconds=x).total_seconds() / float(86400))�[0m
�[1m>       frame.to_excel(self.path, 'test1')�[0m

�[31m�[1mpandas/tests/io/test_excel.py�[0m:1384: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
�[31m�[1mpandas/core/frame.py�[0m:1599: in to_excel
�[1m    engine=engine)�[0m
�[31m�[1mpandas/io/formats/excel.py�[0m:652: in write
�[1m    freeze_panes=freeze_panes)�[0m
�[31m�[1mpandas/io/excel.py�[0m:1772: in write_cells
�[1m    val, style)�[0m
�[31m�[1m../../../miniconda3/envs/pandas/lib/python2.7/site-packages/xlsxwriter/worksheet.py�[0m:57: in cell_wrapper
�[1m    return method(self, *args, **kwargs)�[0m
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <xlsxwriter.worksheet.Worksheet object at 0x7f44821a04d0>, row = 1
col = 2, args = (Timedelta('-1 days +23:59:52'), None)
token = Timedelta('-1 days +23:59:52')
num_types = (<type 'float'>, <type 'int'>, <type 'long'>, <class 'decimal.Decimal'>, <class 'fractions.Fraction'>)
str_types = <type 'basestring'>

�[1m    @convert_cell_args�[0m
�[1m    def write(self, row, col, *args):�[0m
�[1m        """�[0m
�[1m            Write data to a worksheet cell by calling the appropriate write_*()�[0m
�[1m            method based on the type of data being passed.�[0m
�[1m    �[0m
�[1m            Args:�[0m
�[1m                row:   The cell row (zero indexed).�[0m
�[1m                col:   The cell column (zero indexed).�[0m
�[1m                *args: Args to pass to sub functions.�[0m
�[1m    �[0m
�[1m            Returns:�[0m
�[1m                 0:    Success.�[0m
�[1m                -1:    Row or column is out of worksheet bounds.�[0m
�[1m                other: Return value of called method.�[0m
�[1m    �[0m
�[1m            """�[0m
�[1m        # Check the number of args passed.�[0m
�[1m        if not len(args):�[0m
�[1m            raise TypeError("write() takes at least 4 arguments (3 given)")�[0m
�[1m    �[0m
�[1m        # The first arg should be the token for all write calls.�[0m
�[1m        token = args[0]�[0m
�[1m    �[0m
�[1m        # Types to check in Python 2/3.�[0m
�[1m        if sys.version_info[0] == 2:�[0m
�[1m            num_types = (float, int, long, Decimal, Fraction)�[0m
�[1m            str_types = basestring�[0m
�[1m            date_types = (datetime.datetime, datetime.date, datetime.time)�[0m
�[1m        else:�[0m
�[1m            num_types = (float, int, Decimal, Fraction)�[0m
�[1m            str_types = str�[0m
�[1m            date_types = (datetime.datetime, datetime.date, datetime.time)�[0m
�[1m    �[0m
�[1m        # Write None as a blank cell.�[0m
�[1m        if token is None:�[0m
�[1m            return self.write_blank(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write boolean types.�[0m
�[1m        if isinstance(token, bool):�[0m
�[1m            return self.write_boolean(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write datetime objects.�[0m
�[1m        if isinstance(token, date_types):�[0m
�[1m            return self.write_datetime(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write number types.�[0m
�[1m        if isinstance(token, num_types):�[0m
�[1m            return self.write_number(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write string types.�[0m
�[1m        if isinstance(token, str_types):�[0m
�[1m            # Map the data to the appropriate write_*() method.�[0m
�[1m            if token == '':�[0m
�[1m                return self.write_blank(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_formulas and token.startswith('='):�[0m
�[1m                return self.write_formula(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_urls and re.match('(ftp|http)s?://', token):�[0m
�[1m                return self.write_url(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_urls and re.match('mailto:', token):�[0m
�[1m                return self.write_url(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_urls and re.match('(in|ex)ternal:', token):�[0m
�[1m                return self.write_url(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_numbers:�[0m
�[1m                try:�[0m
�[1m                    f = float(token)�[0m
�[1m                    if not self._isnan(f) and not self._isinf(f):�[0m
�[1m                        return self.write_number(row, col, f, *args[1:])�[0m
�[1m                except ValueError:�[0m
�[1m                    # Not a number, write as a string.�[0m
�[1m                    pass�[0m
�[1m    �[0m
�[1m                return self.write_string(row, col, *args)�[0m
�[1m    �[0m
�[1m            else:�[0m
�[1m                # We have a plain string.�[0m
�[1m                return self.write_string(row, col, *args)�[0m
�[1m    �[0m
�[1m        # We haven't matched a supported type. Try float.�[0m
�[1m        try:�[0m
�[1m>           f = float(token)�[0m
�[31m�[1mE           TypeError: float() argument must be a string or a number�[0m

�[31m�[1m../../../miniconda3/envs/pandas/lib/python2.7/site-packages/xlsxwriter/worksheet.py�[0m:417: TypeError
�[31m�[1m_______ TestExcelWriter.test_to_excel_timedelta[xlsxwriter-.xlsx-False] ________�[0m
[gw0] linux2 -- Python 2.7.14 /home/travis/miniconda3/envs/pandas/bin/python

self = <pandas.tests.io.test_excel.TestExcelWriter object at 0x7f4481b83750>
merge_cells = False, engine = 'xlsxwriter', ext = '.xlsx'

�[1m    def test_to_excel_timedelta(self, merge_cells, engine, ext):�[0m
�[1m        # GH 19242, GH9155 - test writing timedelta to xls�[0m
�[1m        if engine == 'openpyxl':�[0m
�[1m            pytest.xfail('Timedelta roundtrip broken with openpyxl')�[0m
�[1m        frame = DataFrame(np.random.randint(-10, 10, size=(20, 1)),�[0m
�[1m                          columns=['A'],�[0m
�[1m                          dtype=np.int64�[0m
�[1m                          )�[0m
�[1m        expected = frame.copy()�[0m
�[1m        frame['new'] = frame['A'].apply(lambda x: timedelta(seconds=x))�[0m
�[1m        expected['new'] = expected['A'].apply(�[0m
�[1m            lambda x: timedelta(seconds=x).total_seconds() / float(86400))�[0m
�[1m>       frame.to_excel(self.path, 'test1')�[0m

�[31m�[1mpandas/tests/io/test_excel.py�[0m:1384: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
�[31m�[1mpandas/core/frame.py�[0m:1599: in to_excel
�[1m    engine=engine)�[0m
�[31m�[1mpandas/io/formats/excel.py�[0m:652: in write
�[1m    freeze_panes=freeze_panes)�[0m
�[31m�[1mpandas/io/excel.py�[0m:1772: in write_cells
�[1m    val, style)�[0m
�[31m�[1m../../../miniconda3/envs/pandas/lib/python2.7/site-packages/xlsxwriter/worksheet.py�[0m:57: in cell_wrapper
�[1m    return method(self, *args, **kwargs)�[0m
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <xlsxwriter.worksheet.Worksheet object at 0x7f4481a754d0>, row = 1
col = 2, args = (Timedelta('-1 days +23:59:54'), None)
token = Timedelta('-1 days +23:59:54')
num_types = (<type 'float'>, <type 'int'>, <type 'long'>, <class 'decimal.Decimal'>, <class 'fractions.Fraction'>)
str_types = <type 'basestring'>

�[1m    @convert_cell_args�[0m
�[1m    def write(self, row, col, *args):�[0m
�[1m        """�[0m
�[1m            Write data to a worksheet cell by calling the appropriate write_*()�[0m
�[1m            method based on the type of data being passed.�[0m
�[1m    �[0m
�[1m            Args:�[0m
�[1m                row:   The cell row (zero indexed).�[0m
�[1m                col:   The cell column (zero indexed).�[0m
�[1m                *args: Args to pass to sub functions.�[0m
�[1m    �[0m
�[1m            Returns:�[0m
�[1m                 0:    Success.�[0m
�[1m                -1:    Row or column is out of worksheet bounds.�[0m
�[1m                other: Return value of called method.�[0m
�[1m    �[0m
�[1m            """�[0m
�[1m        # Check the number of args passed.�[0m
�[1m        if not len(args):�[0m
�[1m            raise TypeError("write() takes at least 4 arguments (3 given)")�[0m
�[1m    �[0m
�[1m        # The first arg should be the token for all write calls.�[0m
�[1m        token = args[0]�[0m
�[1m    �[0m
�[1m        # Types to check in Python 2/3.�[0m
�[1m        if sys.version_info[0] == 2:�[0m
�[1m            num_types = (float, int, long, Decimal, Fraction)�[0m
�[1m            str_types = basestring�[0m
�[1m            date_types = (datetime.datetime, datetime.date, datetime.time)�[0m
�[1m        else:�[0m
�[1m            num_types = (float, int, Decimal, Fraction)�[0m
�[1m            str_types = str�[0m
�[1m            date_types = (datetime.datetime, datetime.date, datetime.time)�[0m
�[1m    �[0m
�[1m        # Write None as a blank cell.�[0m
�[1m        if token is None:�[0m
�[1m            return self.write_blank(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write boolean types.�[0m
�[1m        if isinstance(token, bool):�[0m
�[1m            return self.write_boolean(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write datetime objects.�[0m
�[1m        if isinstance(token, date_types):�[0m
�[1m            return self.write_datetime(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write number types.�[0m
�[1m        if isinstance(token, num_types):�[0m
�[1m            return self.write_number(row, col, *args)�[0m
�[1m    �[0m
�[1m        # Write string types.�[0m
�[1m        if isinstance(token, str_types):�[0m
�[1m            # Map the data to the appropriate write_*() method.�[0m
�[1m            if token == '':�[0m
�[1m                return self.write_blank(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_formulas and token.startswith('='):�[0m
�[1m                return self.write_formula(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_urls and re.match('(ftp|http)s?://', token):�[0m
�[1m                return self.write_url(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_urls and re.match('mailto:', token):�[0m
�[1m                return self.write_url(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_urls and re.match('(in|ex)ternal:', token):�[0m
�[1m                return self.write_url(row, col, *args)�[0m
�[1m    �[0m
�[1m            elif self.strings_to_numbers:�[0m
�[1m                try:�[0m
�[1m                    f = float(token)�[0m
�[1m                    if not self._isnan(f) and not self._isinf(f):�[0m
�[1m                        return self.write_number(row, col, f, *args[1:])�[0m
�[1m                except ValueError:�[0m
�[1m                    # Not a number, write as a string.�[0m
�[1m                    pass�[0m
�[1m    �[0m
�[1m                return self.write_string(row, col, *args)�[0m
�[1m    �[0m
�[1m            else:�[0m
�[1m                # We have a plain string.�[0m
�[1m                return self.write_string(row, col, *args)�[0m
�[1m    �[0m
�[1m        # We haven't matched a supported type. Try float.�[0m
�[1m        try:�[0m
�[1m>           f = float(token)�[0m
�[31m�[1mE           TypeError: float() argument must be a string or a number�[0m

�[31m�[1m../../../miniconda3/envs/pandas/lib/python2.7/site-packages/xlsxwriter/worksheet.py�[0m:417: TypeError

[jreback]

I closed this, but lmk if fixed

[WillAyd]

Yep should be fixed. Could never reproduce this locally anyway but I'm assuming the explicitness of the Timdelta -> float conversion prevented the failure from happening on Travis for all versions / platforms