BUG: Fix .to_excel() for MultiIndex containing a NaN value #13511
State: closed

- [X] closes #13511
- [X] tests added / passed
- [X] passes `git diff upstream/master | flake8 --diff`
- [X] whatsnew entry


Labels:
name: Bug
name: IO Excel
description: read_excel, to_excel
name: MultiIndex

Comments:
## [Current coverage](https://codecov.io/gh/pydata/pandas/pull/13551?src=pr) is 85.32% (diff: 100%)

> Merging [#13551](https://codecov.io/gh/pydata/pandas/pull/13551?src=pr) into [master](https://codecov.io/gh/pydata/pandas/branch/master?src=pr) will not change coverage

``` diff
@@             master     #13551   diff @@
==========================================
  Files           141        141          
  Lines         50679      50679          
  Methods           0          0          
  Messages          0          0          
  Branches          0          0          
==========================================
  Hits          43240      43240          
  Misses         7439       7439          
  Partials          0          0          
```

> Powered by [Codecov](https://codecov.io?src=pr). Last update [474fd05...2335cee](https://codecov.io/gh/pydata/pandas/compare/474fd0519b768efd1c7ec6555930873bf9910e00...2335ceefa7b8647fcc191cb065a4ed87b3cba88f?src=pr)

@mpuels I think there went something wrong with merge/rebase. Normally, running the following should do exactly what you want to clean it up here:

```
git checkout fix-multiindex-nan-label-to-excel
git fetch upstream
git rebase upstream/master
git push -f origin/fix-multiindex-nan-label-to-excel
```

@jorisvandenbossche Thanks for the help on git! Additionally I wanted to change the commit message of commit 7272a96 to something like 'CLN: Break line to avoid long line.'. I tried `git rebase -i HEAD~3` and changed the commit message there, but it didn't work. Or is it impossible?

@mpuels No, it is certainly possible. Normally using `git rebase -i` should work to rename a commit (I think there is the option 'reword' to select?). But if it is the latest commit, you can also do `git commit --amend -m "new message"`.

Further, when this PR is merged, the commits will be squased any way and the PR's title is used for the squashed commit (so it is not that important to change the latest commit's message)

@jreback I used

```
frame.iloc[0, 0] = ...
```

to assign to the first row of column `A`, because `self.frame`'s index contains random values. Would it be OK if I don't use `self.frame` but instead construct new test data? Something like

```
df = pd.DataFrame({'A': [1,2,3],
                   'B': [10,20,30],
                   'C': np.random.sample(3)})
df.loc[0, 'A'] = None
df = df.set_index(['A', 'B'])
```

Then the values of the Index would be predictable and I could refer to the first row of `A` using `df.loc`.

And can you please give me a rule of thumb when to use test data which is already available and when to construct new test data? Thanks!

@mpuels best to use a sample frame that you construct as the expected (almost always).

@mpuels Can you rebase and update according to the comments?

@jorisvandenbossche Sorry for the long delay. I don't have time today, but will do it tomorrow.

No problem!

lgtm. @jorisvandenbossche 

@mpuels Thanks a lot!