invoking pivot_table results in unwanted row

[yuylyp]

I have encounterd an issue with pd.pivot_table. this function adds unexpected row and I have no idea to remove it.

below is the code:

import pandas as pd
import numpy as np

dates = {
    'animal': ['cat', 'cat', 'wolf'],
    'color': ['white', 'black', 'black'],
    'age': np.random.randint(15, 30, 3)}

df = pd.DataFrame(dates)
dfp = pd.pivot_table(
    df,
    index='animal',
    columns='color',
    values='age',
    aggfunc=[len, np.mean],
    fill_value=0
)

excel_file = pd.ExcelWriter('result.xlsx', mode='w')

dfp.to_excel(
    excel_writer=excel_file,
    sheet_name='before'
)

the result is:

	len		mean
color	black	white	black	white
animal
cat	1	1	20	28
wolf	1	0	24	0

Problem description

pivot_table adds unwanted row below the MultiIndex. this row starts with 'animal' which is the index name.
I have read though related docs and tried possible parameters.
Finallly I found there is no way to remove it.

Expected Output

	len		mean
color	black	white	black	white
cat	1	1	20	28
wolf	1	0	24	0

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]
INSTALLED VERSIONS

commit : None
python : 3.7.4.final.0
python-bits : 64
OS : Windows
OS-release : 10
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 10, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None

pandas : 0.25.0
numpy : 1.17.0
pytz : 2019.2
dateutil : 2.8.0
pip : 19.2.1
setuptools : 41.0.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.10.1
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.1.1
numexpr : 2.6.9
odfpy : None
openpyxl : 2.6.2
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.3.0
sqlalchemy : None
tables : None
xarray : None
xlrd : 1.2.0
xlwt : 1.3.0
xlsxwriter : None

[yuylyp]

sorry, cause my english isn't very well, I have edit this issue for many times to correct some unwanted mistake.
Please ignore the history editions.

[TomAugspurger]

@yuylyp I'm not sure of to_excel has an option to not write the index name.

The easiest workaround is to set the name to None before writing

In [5]: dfp.index.name
Out[5]: 'animal'

In [6]: dfp.index.name = None

In [7]: dfp
Out[7]:
        len        mean
color black white black white
cat       1     1    18    25
wolf      1     0    23     0

[yuylyp]

@TomAugspurger
thanks for your suggestion
however, I have tried this solution. And the result is the same.
I thought that it is too rude to close this issue while you haven't tried all the steps I mentioned above.
Could you please to open it again?

[yuylyp]

@TomAugspurger
in fact, I've tried all the possible methods and params which seems to be related to this issue.
And finally this issue is still alive.
This is why I open this issue.
So could you please reopen this issue, maybe others have solution?
or somebody will treat this as an BUG or ISSUE?

[TomAugspurger]

Sorry, I didn't realize it still wrote a blank line. That's been reported in #27772

[yuylyp]

thanks for providing #27772, those are the same question totally.
emmmmm, it seems to be an issue not resolved in 0.25
maybe we should dig into source code to check the behavior of to_excel

[yuylyp]

@TomAugspurger
I apologize for what I said above since this issue has confused my friends and me for days.
Have a good day!

[yuylyp]

@TomAugspurger
I found the reason and pointed it out in #27772 (comment)
however it is just an workaround. it still needs more work.
could you please submit this issue and help improve the experience of to_excel while we are using multiindex column.

[TomAugspurger]

I don't plan to work on this. If you're interested, the contributing guide is at https://dev.pandas.io/development/contributing.html. Let us know if you need help getting started.

…

On Tue, Aug 13, 2019 at 6:54 AM Tom Li ***@***.***> wrote: @TomAugspurger <https://github.com/TomAugspurger> I found the reason and pointed it out in #27772 (comment) <#27772 (comment)> however it is just an workaround. it still needs more work. could you please submit this issue and help improve the experience of to_excel while we are using multiindex column. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#27842?email_source=notifications&email_token=AAKAOIWOL2JGYUJZDEF2J5TQEKOGTA5CNFSM4IKTADT2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4FNNEY#issuecomment-520803987>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAKAOIWD3DXIGKJDHOR2MHDQEKOGTANCNFSM4IKTADTQ> .