BUG: pivot broken on the latest version 1.1.0

[WindSoilder]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

# Your code here
import pandas as pd
from datetime import datetime
df = pd.DataFrame.from_records([{"date": datetime(2016, 3, 2), "col1": 1, "col2": 2}], index=["date"])
df.pivot(index=df.index, columns="col1")

Problem description

The exmple code raise a KeyError:

KeyError: "None of [Timestamp('2016-03-02 00:00:00')] are in the columns"

It works on version 1.0.5

Expected Output

The output is something like this in version 1.0.5

Output of pd.show_versions()

INSTALLED VERSIONS

commit : d9fff27
python : 3.7.7.final.0
python-bits : 64
OS : Darwin
OS-release : 19.4.0
Version : Darwin Kernel Version 19.4.0: Wed Mar 4 22:28:40 PST 2020; root:xnu-6153.101.6~15/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : zh_CN.UTF-8
LOCALE : zh_CN.UTF-8

pandas : 1.1.0
numpy : 1.19.0
pytz : 2020.1
dateutil : 2.8.1
pip : 20.1.1
setuptools : 47.3.0.post20200616
Cython : None
pytest : 5.4.3
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : 0.10.0
psycopg2 : None
jinja2 : 2.11.2
IPython : 7.12.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.2.2
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : 1.3.18
tables : None
tabulate : 0.8.7
xarray : None
xlrd : None
xlwt : None
numba : None

[simonjayhawkins]

It works on version 1.0.5

Thanks @WindSoilder for the report.

can confirm that this is a regression in #30928 cc @charlesdong1991

11f1500 is the first bad commit
commit 11f1500
Author: Kaiqi Dong [email protected]
Date: Sun Feb 9 22:57:28 2020 +0100

ENH: Allow multi values for index and columns in df.pivot (#30928)

[jreback]

this may have accidentally worked before but is incorrect

index must be a list of column labels not an index of values

[charlesdong1991]

emm, thanks for reporting @WindSoilder, and indeed it is a behavior change from the previous version.

However, I am not very sure the previous behavior is correct given the documentation of pivot:
Reshape data (produce a “pivot” table) based on column values. Uses unique values from specified index / columns to form axes of the resulting DataFrame.

So the input for index or column should be from column names

Alternatively, if you would like to still get the result, you could either reset index to make date in the columns, or just not to assign any value to index:

# solution 1
df.reset_index().pivot(index='date', columns='col1')

# solution 2
df.pivot(columns='col1')

[simonjayhawkins]

Thnaks @charlesdong1991 and @jreback for looking at this.

can this be closed?

[charlesdong1991]

@simonjayhawkins @jreback I think it can be closed from a bug-reporting perspective.

However, I am not sure if a better error reporting message is preferred (and it might be slightly better if we do this check right at the top of the function)? Or a similar issue related test can be added to the tests?

[jreback]

yeah it's likely not easy to actually give a better message because you can pass an Index if labels (though not very likely)

but i suppose that you could catch the KeyError and enhance it to say - did you pass a list or label to pivot and not the index itself

[WindSoilder]

Thanks @charlesdong1991 @simonjayhawkins @jreback for quickly looking into this :) I will change my code to fit this behavior.

[farhoud]

Can i take it?