New Excel changes cause an extra line to be generated in the Excel file

[lbeltrame]

An example:

In [9]: data = pandas.ExcelFile("sample.xls") # generated with df.to_excel()

In [10]: data.parse(data.sheet_names[0], index_col=0)
Out[10]: 
         Value
ID         NaN
ID1  38.625700
ID2  44.691054

To be more clear, ID is the original index name, but it is shifted down one row when saving to Excel, and then it is picked up as a regular row by the Excel parser.

This kinds of complicates parsing if I'm expecting to read something generated by to_excel()...

[wesm]

There's an ambiguous case: hard to distinguish an index name row and an empty first row. @locojay added an argument has_index_names to ExcelFile.parse that makes it explicit. A somewhat tricky situation, hmm

[wesm]

In the absence of columns.name, I think it would be better to put the index name on the first line. Anyone disagree?

[wesm]

Ugh, I don't have the stomach for this. Please, someone help

I'll look into it.

I suggest the index names always appear on the first row, even if columns.name exists.
Being able to unambigously reconsturct the data, columns labels and index labels is the
vital bit. Other things are nice, but should not be allowed to violate that, certainly not as the
default.

Excel is not a full-fledged serialization format anyway, It's should be allowed to be lossy
with regards to some things, but not with the essential bits.
(FWIW, I don't think parsing out a multiindex from an excel sheet is a good idea either).

Does that sound right to you, @wesm ?
I can code that up if you don't want to fiddle with this. If not, it may be better to revert, and
make the decision either way in 0.11.

[changhiskhan]

I think it's possible but just really messy.
@y-p what do you think the following:

1. If there are explicit index_col then it's easy to check the first row
2. If the first row has data where the implicit indices are, then also use those names as index names
3. Otherwise treat as empty first row.

[jassinm]

I took the approach of not adding the indexnames in the first row as the HTML dumper uses the same. And when there is a MultiIndex in Column Names and Indexnames in the index its the way to go.
We could dump it on the same row as the column names if the column is not a Multindex (not supported really by the old version --> no merge ... dumps tuple).
I don't see any other way then adding an argument if one want's to have indexnames the same way as the Html dumper and beeing able to parse an empty first row with no names. Maybe using range names in excel or adding the indexnnames in the metada when dumping would not require the argument...

The easiest would be to always dump on the same row as columns if the columns are not a Multindex

to_html() is strictly for presentation, while to_excel() needs to be read back in.
I'd take the niceties of the former only if they don't hurt the latter. Complexity has
it's costs as well. I think we're all struggling to convince ourselves we're not missing
any corner cases, I know I am.

I"m really not sure what the best thing to do is, I would go with:

1. Do the simplest thing possible.
2. Or, if not sure, push the change in default behaviour back a release.

[jassinm]

then let's do 1) as I would really like to have the ability to dump MulitiIndex colums (pivot tables...)

1 woud be:

• check if columns is a MultiIndex. If not dump indexnames in same row as columnames , remove the has_idnexnames argument from the parser.

If you want the kitchen sink, to be really sure there is no ambiguity introduced:

1. you need to test to_exce/read_excel gets the data back without losing anything.
   and
2. you need to make sure it looks ok
   For all combinations of

• multindex on rows/columns/both/none.
• name on rows index/columns/both/none.
• Int64Index, DateTimeIndex, dtype=object index.
• presence of NaNs in one or or more locations.
• empty first line or not

egad.

[lbeltrame]

The situation is worse than it seems at first: now some files throw an exception when being saved:

      1 for item in results:
----> 2     print item; results[item].to_excel(excel, sheet_name=item, na_rep="NA")
      3 

/usr/lib64/python2.7/site-packages/pandas-0.10.0.dev_100fb8f-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)
   1443         formatted_cells = formatter.get_formatted_cells()
   1444         excel_writer.write_cells(formatted_cells, sheet_name,
-> 1445                                  startrow=startrow, startcol=startcol)
   1446         if need_save:
   1447             excel_writer.save()

/usr/lib64/python2.7/site-packages/pandas-0.10.0.dev_100fb8f-py2.7-linux-x86_64.egg/pandas/io/parsers.pyc in write_cells(self, cells, sheet_name, startrow, startcol)
   2066             self._writecells_xlsx(cells, sheet_name, startrow, startcol)
   2067         else:
-> 2068             self._writecells_xls(cells, sheet_name, startrow, startcol)
   2069 
   2070     def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):

/usr/lib64/python2.7/site-packages/pandas-0.10.0.dev_100fb8f-py2.7-linux-x86_64.egg/pandas/io/parsers.pyc in _writecells_xls(self, cells, sheet_name, startrow, startcol)
   2129                 wks.write(startrow + cell.row,
   2130                           startcol + cell.col,
-> 2131                           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)
    234         self.__adjust_height(style)
    235         self.__adjust_bound_col_idx(col)
--> 236         style_index = self.__parent_wb.add_style(style)
    237         if isinstance(label, basestring):
    238             if len(label) > 0:

/usr/lib/python2.7/site-packages/xlwt/Workbook.pyc in add_style(self, style)
    301 
    302     def add_style(self, style):
--> 303         return self.__styles.add(style)
    304 
    305     def add_font(self, font):

/usr/lib/python2.7/site-packages/xlwt/Style.pyc in add(self, style)
     88         if style == None:
     89             return 0x10
---> 90         return self._add_style(style)[1]
     91 
     92     def _add_style(self, style):

/usr/lib/python2.7/site-packages/xlwt/Style.pyc in _add_style(self, style)
    147         if xf_index >= 0xFFF:
    148             # 12 bits allowed, 0xFFF is a sentinel value
--> 149             raise ValueError("More than 4094 XFs (styles)")
    150 
    151         return xf, xf_index

ValueError: More than 4094 XFs (styles)

Needless to say, they worked OK before this change. excel is an ExcelWriter instance.

[jassinm]

@cswegger : seems like a problem with xlwt. (https://groups.google.com/forum/?fromgroups=#!topic/python-excel/WtkTgE08OFk) . Do you mind uploading your data? what xlwt version are you running (0.7.4) ?

[jassinm]

@y-p https://github.com/locojay/pandas/tree/2396. does not require to add has_index_names as it outputs indexlabels on the same rows as columns as long as the columns are not a multiindex