columns keyword has no effect in DataFrame.to_excel() in 1.0.1

[ehowatt]

The columns keyword argument to the DataFrame.to_excel() method in version 1.0.1 of pandas does not restrict the columns in the Excel sheet to the specified column list. This is different from the behavior in 0.25.2 and previous versions, and also differs from the to_csv() method.

This can be replicated using:

import pandas as pd

df = pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]],columns=['a','b','c'])
df.to_excel('columns_output.xlsx',columns=['a','c'])
w.save()

(I'm using the xlsxwriter engine)

In previous versions of pandas the output from that code would be an Excel file that only contains the index (no header), column a, and column c:

	a	c
0	1	3
1	4	6
2	7	9

In the new version, all three columns are outputted, yielding an Excel file with all three columns:

	a	b	c
0	1	2	3
1	4	5	6
2	7	8	9

(emphasis mine)

Not all columns in the dataframe need to be shared with the end user (internal values, processing/filtering/sorting columns, etc.) and some end users prefer columns in a specific order. Restoring the previous functionality would make that possible without having to change the dataframe itself.

Also, if the headers keyword is specified, matching the number of columns listed, like so:

df.to_excel('columns_output_headers.xlsx',columns=['a','c'],header=['column A','column C'])

A ValueError is returned as the column count does not match the header list length:

ValueError: Writing 3 cols but got 2 aliases

Output of pd.show_versions()

1.0.1

Replicated in a local version, an aws lambda python 3.7 docker image, and in aws.

Local version:
INSTALLED VERSIONS

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

pandas : 1.0.1
numpy : 1.16.4
pytz : 2019.1
dateutil : 2.8.0
pip : 19.2.2
setuptools : 40.8.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 1.2.7
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.4 (dt dec pq3 ext lo64)
jinja2 : 2.10.1
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : 2.6.3
pandas_gbq : None
pyarrow : None
pytables : None
pytest : None
pyxlsb : None
s3fs : None
scipy : 1.3.0
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : 1.2.7
numba : None

Docker image
INSTALLED VERSIONS

commit : None
python : 3.7.6.final.0
python-bits : 64
OS : Linux
OS-release : 4.9.184-linuxkit
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.0.1
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 19.2.3
setuptools : 41.2.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 1.2.7
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : 1.2.7
numba : None

If I force the version on pandas (and change nothing else) when building and running on the local Docker image, the proper column set appears.

[WillAyd]

Thanks - this is a duplicate of #31677 and should already be solved on master. Will be fixed with 1.0.2 release