ExcelWriter fails with columns with int32 dtype

[lbeltrame]

Test case

In [21]: df = pandas.DataFrame({"foo": [1,2,3]})

In [22]: df["foo"] = df["foo"].astype("int32")

In [23]: df.to_excel("foo.xls")
---------------------------------------------------------------------------
Exception                                 Traceback (most recent call last)
<ipython-input-23-a8d956752923> in <module>()
----> 1 df.to_excel("foo.xls")

/usr/lib64/python2.7/site-packages/pandas-0.11.0.dev_4e0465d-py2.7-linux-x86_64.egg/pandas/core/frame.pyc in to_excel(self, excel_writer, sheet_name, na_rep, float_format, cols, header, index, index_label, startrow, startcol)
   1406         formatted_cells = formatter.get_formatted_cells()
   1407         excel_writer.write_cells(formatted_cells, sheet_name,
-> 1408                                  startrow=startrow, startcol=startcol)
   1409         if need_save:
   1410             excel_writer.save()

/usr/lib64/python2.7/site-packages/pandas-0.11.0.dev_4e0465d-py2.7-linux-x86_64.egg/pandas/io/parsers.pyc in write_cells(self, cells, sheet_name, startrow, startcol)
   2194             self._writecells_xlsx(cells, sheet_name, startrow, startcol)
   2195         else:
-> 2196             self._writecells_xls(cells, sheet_name, startrow, startcol)
   2197
   2198     def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):

/usr/lib64/python2.7/site-packages/pandas-0.11.0.dev_4e0465d-py2.7-linux-x86_64.egg/pandas/io/parsers.pyc in _writecells_xls(self, cells, sheet_name, startrow, startcol)
   2270                 wks.write(startrow + cell.row,
   2271                           startcol + cell.col,
-> 2272                           val, style)

/usr/lib/python2.7/site-packages/xlwt/Worksheet.pyc in write(self, r, c, label, style)
   1030
   1031     def write(self, r, c, label="", style=Style.default_style):
-> 1032         self.row(r).write(c, label, style)
   1033
   1034     def write_rich_text(self, r, c, rich_text_list, style=Style.default_style):

/usr/lib/python2.7/site-packages/xlwt/Row.pyc in write(self, col, label, style)
    257             self.__rich_text_helper(col, label, style, style_index)
    258         else:
--> 259             raise Exception("Unexpected data type %r" % type(label))
    260
    261     def set_cell_rich_text(self, col, rich_text_list, style=Style.default_style):

Exception: Unexpected data type <type 'numpy.int32'>

[jreback]

I think Excel writer needs to do some type conversions as xlwt only seems to deal with pure python types
see

https://github.com/python-excel/xlwt/issues/15

[jreback]

prob can get done in 0.12

[lbeltrame]

In data giovedì 21 marzo 2013 07:41:18, jreback ha scritto:

prob can get done in 0.12

No big deal. I noticed it seems to work if I upcast to int64.

Luca Beltrame - KDE Forums team
KDE Science supporter
GPG key ID: 6E1A4E79

[jreback]

FYI...this actually wasn't a bug in 0.10.1 because the astype that you are doing, while converting the series, upcasted again when put back in the data frame (but in 0.11 the dtype in the frame is correct)

so going to leave open for fixing later

In [2]: import pandas

In [3]: df = pandas.DataFrame({"foo": [1,2,3]})

In [5]: df.dtypes
Out[5]: foo    int64

In [7]: df['foo'].astype('int32').dtype
Out[7]: dtype('int32')

In [8]: df['foo'] = df['foo'].astype('int32')

In [9]: df.dtypes
Out[9]: foo    int64

[jtratner]

I'm thinking we create a function that, based on numpy version/environment, converts columns to things that are picked up as int/float/long and that will be used by the cell formatter.

Does int64 need to be special-cased to long in 32 bit python?

[jreback]

this I think is an excel conversion issue in that it doesn't know how to deal with int32
(not sure if it can at all; if it cannot then the excel converters should ensure 64bit dtypes for float/int)

[jreback]

@jtratner I think it's the other way around - since we normally always use int64 wrt the python version
unless its explicitly converted

[jtratner]

@jreback well, it just needs to be converted to a format that's a subclass of int/float/whatever.

[jtratner]

so in 32bit python int32 is a subclass of int, in 64bit python int64 is a subclass of int.

[jreback]

ok so this IS platform dep on the windows side then? weird

[jtratner]

@jreback not sure...I'm just going off of what that guy showed was happening in the linked issue

[jtratner]

@lbeltrame - very soon you can use the xlsxwriter engine to handle this.

[jreback]

cc @jmcnamara, does xlxswriter handle int32 dtypes? (or is this an excel issue)