Fix for BUG: multi-index excel header fails if all numeric
State: closed

replaces #11317
closes #11408

This includes updates to 3 Excel files, plus a test in test_excel.py,
plus the fix in parsers.py


Labels:
name: Bug
name: IO CSV
description: read_csv, to_csv
name: IO Excel
description: read_excel, to_excel
name: MultiIndex

Comments:
ok, pls add a whatsnew note in bug fixes (reference this PR number)

ideally I'd like a round-trip test, e.g. take a csv write to excel, read it back and compare

The check failed for Python 2.7, so I have committed a change for that to parsers.py and also updated what's new for v0.17.1. 

pls squash. ping when green.

sorry, meant to have you add a round-trip test as well.

I've added a round trip test. Discovered an issue where the Excel file isn't properly formatted when you have a MultiIndex column and merge_cells=false.  Not sure how to test for that.  Submitted code and assuming the tests will kick in

@Dr-Irv minor fix squash

@chris-b1 can you have a look

Seems fine to me.  It's not introduced by this PR, but one inconsistency I just noticed is that `read_csv` seems to always converts numeric columns to strings (or maybe never converts to numeric in the first place), where `read_excel` will leave as numeric types.

@chris-b1 can u file an issue about your last
and show an example

needs a squash

@jreback I think I have done everything that is needed. (Note - this is my first time doing any bug fixing with pandas or any open source project for that matter!) There is one issue and I don't know how I can debug it. In the test test_to_excel_multiindex_cols, if merge_cells is False, it fails on Python 2.7 and Python 3.5 according to the Travis CI testing. What is happening is that the multiindex is made from tuples [(40,1),(40,2),(50,1),(50,2)].  When merge_cells is False, the created column names are [ "40.1", "40.2", "50.1", "50.2" ].  On Python 2.7 and 3.5, it is reading those as floats rather than strings.  I did set up a Python 2.7 environment for debugging, but could not replicate. I could not set up a Python 3.5 environment for debugging (This is all on Windows).  So I have put something in there that makes the tests pass, but someone who knows how to do cross-platform debugging should look at this.

Thanks for your patience in letting me figure this out.

http://pandas.pydata.org/pandas-docs/stable/contributing.html#creating-a-development-environment for creating an environment on windows is quite easy (for any python).

As fair as the issue, maybe @chris-b1 has a thought?

That is along the same lines as  #11357.

Basically the type inference would need to happen after the splitting,
probably worth checking how csv handles it?

http://pandas.pydata.org/pandas-docs/stable/contributing.html#creating-a-development-environment
for creating an environment on windows is quite easy (for any python).

As fair as the issue, maybe @chris-b1 https://github.com/chris-b1 has a
thought?

—
Reply to this email directly or view it on GitHub
https://github.com/pydata/pandas/pull/11328#issuecomment-149354108.

@jreback I'm unsure how to figure this out.  I built a Python 2.7 environment on Linux, and can't replicate the bug.  But the travis-ci build failed.  Here is the part that is confusing.  One of the Python 2.7 runs succeeded and the other one failed.  See https://travis-ci.org/Dr-Irv/pandas/builds/86225791 . Job 5.2 is Python 2.7 and succeeded.  Job 5.3 is Python 2.7 and failed. How can I replicate the failure that corresponds to Job 5.3?  When I run the nosetests on test_excel.py on Python 2.7, it passes (with my next-to-last version of test_excel.py).

Do you have all the dependencies (xlrd, xlsxwriter, openpyxl, xlst) installed locally? Might be skipping the failing test. 

@chris-b1 Yes.  I even explicitly ran the test that failed (according to the log) and it passes (no skip message). I turned on the "-v" flag and see that it is run.  Also, it doesn't explain why in the Travis-CI tests, one 2.7 run succeeded and the other 2.7 run failed.

It looks like the passing 2.7 is just the "slow' tests.  Try installing the specific version of openpyxl in travis `openpyxl=1.6.2`  

https://github.com/pydata/pandas/blob/master/ci/requirements-2.7_LOCALE.run

@chris-b1 That was what I needed.  I can reproduce so I will now work on it.

@Dr-Irv yep this is why conda works well for this, I just create a separate environment with the contents of a particular travis run - then easy to repro

@chris-b1 @jreback So with Chris's hint, I was able to get it to fail on Linux. But on Windows, ci/requirements-2.7_LOCALE.run is not working with conda.  I'm trying to hack it to get it to work. Question for either of you - how do you tell from the Travis-CI runs which ci/requirements\* file corresponds to which run? 

in `.travis.yml`, their is a JOB_NAME tag which corresponds to the run

LOCALE should work (though setting the LOCALE itself is tricky), see `ci/script.py`

@jreback @chris-b1 I finally have it on Windows where it fails under a 2.7 configuration and succeeds under a 3.4 configuration.  Something to do with openpyxl writer (I think).  At least now I can compare things side by side to see what is going on. Thanks for the pointers.

@jreback I have figured out the issue, but am unsure what the right solution is.  Here is a table that shows the combinations that succeeded and failed, which shows the Python version, whether OpenPyxl version was specified in the requirements file, which version of OpenPyxl was used, and whether the Travis-CI run succeeded or failed

| Python Version | Openpyxl version specified | Openpyxl version used | Succeeded? |
| --- | --- | --- | --- |
| 2.6 | None | 2.2.6 | YES |
| 2.7 | None | 2.2.6 | YES |
| 2.7 | 1.6.2 | 1.6.2 | NO |
| 3.4 | None | 2.0.3 | YES |
| 3.5 | None | 1.8.3 | NO |

What is happening is that Openpyxl versions < 2.0 convert the string '40.1' to a float before writing it to the Excel file, while Openpyxl versions > 2.0 leave the string '40.1' as '40.1' .  The writer based on xlwt writes it out as a string.

These strings are created when merge_cells=false, where a '.' is used as a separator to join the values of the hierarchical index.

Possible solutions are 
a) change the openpyxl version used in the tests, (but that is a call someone else should make)
b) change the separator used when merge_cells=False to '/' or '_'
c) change the test so that the MultiIndex used are the tuples [(40,'A'),(40,'B'),(50,'A'),(50,'B')] rather than  [(40,1),(40,2),(50,1),(50,2)] 
d) Skip the test if the writer is openpyxl

Let me know which direction to go to.

@Dr-Irv - it looks like you could add in a workaround for `openpyxl < 2` by using [`set_explicit_value`](http://openpyxl.readthedocs.org/en/latest/api/openpyxl.cell.html#openpyxl.cell.cell.Cell.set_explicit_value) - basically would call that instead of `cell.value = ..` when writing the un-merged columns.

@jreback I think this is ready to be merged into the main branch.  Also, I included a fix for https://github.com/pydata/pandas/issues/11408 so once you do the merge, you can close that issue.

pls squash when you have make corrections

I gave this another try, looks good to me!

@jreback I believe I've done everything you have asked, and the tests passed.

lgtm just need a squash 

```
git rebase -i master
```

then s everything (or use f)

then 

```
git push forkname branchname -f
```

@jreback OK, I think I did the squash correctly. If everything is OK, you should also close issue https://github.com/pydata/pandas/issues/11408