BUG: pandas pivot_table count over a dataframe with 2 columns results in empty dataset when using aggfunc="count" · Issue #57876 · pandas-dev/pandas (original) (raw)

Pandas version checks

Reproducible Example

Run:

import pandas as pd

data = {'Category': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'], 'Value': [10, 20, 30, 40, 50, 60, 70, 80, 90]}

df = pd.DataFrame(data)

pivot_table = df.pivot_table(index='Category', columns='Value', values='Value', aggfunc="count")

print(pivot_table)

and you will get:

Empty DataFrame
Columns: []

Change to

pivot_table = df.pivot_table(index='Category', columns='Value', values='Value', aggfunc=len)

and you will get:

Value      10   20   30   40   50   60   70   80   90
Category                                             
A         1.0  NaN  NaN  1.0  NaN  NaN  1.0  NaN  NaN
B         NaN  1.0  NaN  NaN  1.0  NaN  NaN  1.0  NaN
C         NaN  NaN  1.0  NaN  NaN  1.0  NaN  NaN  1.0

Issue Description

Try to use df.pivot_table over a dataframe with 2 columns, using same column name for the columns and values paramete, aggregate using "count" gets you an empty dataset. Switch to len or size and you get the right result.

Furthermore, strangely, this workaround somehow fixes "count":

df["ValueCopy"] = df["Value"] pivot_table = df.pivot_table(index='Category', columns='Value', values='ValueCopy', aggfunc="count")

ValueCopy and Value contain identical data, so they should be interchangeable, that is, using either should lead to the same result, but they do not.

For sanity checking I tried the same pivoting in Polars, and there, count works fine:

import polars as pl

Create a DataFrame

data = { 'Category': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'], 'Value': [10, 20, 30, 40, 50, 60, 70, 80, 90] } df = pl.DataFrame(data)

Pivot the DataFrame

pivot_table = df.pivot(index='Category', columns='Value', values='Value', aggregate_function="count")

Print the pivot table

print(pivot_table)

results in:

Shape: (3, 10)
┌──────────┬──────┬──────┬──────┬───┬──────┬──────┬──────┬──────┐
│ Category ┆ 10   ┆ 20   ┆ 30   ┆ … ┆ 60   ┆ 70   ┆ 80   ┆ 90   │
│ ---      ┆ ---  ┆ ---  ┆ ---  ┆   ┆ ---  ┆ ---  ┆ ---  ┆ ---  │
│ str      ┆ u32  ┆ u32  ┆ u32  ┆   ┆ u32  ┆ u32  ┆ u32  ┆ u32  │
╞══════════╪══════╪══════╪══════╪═══╪══════╪══════╪══════╪══════╡
│ A        ┆ 1    ┆ null ┆ null ┆ … ┆ null ┆ 1    ┆ null ┆ null │
│ B        ┆ null ┆ 1    ┆ null ┆ … ┆ null ┆ null ┆ 1    ┆ null │
│ C        ┆ null ┆ null ┆ 1    ┆ … ┆ 1    ┆ null ┆ null ┆ 1    │
└──────────┴──────┴──────┴──────┴───┴──────┴──────┴──────┴──────┘

Likewise with DuckDb:

import pandas as pd import duckdb

Your existing DataFrame

data = {'Category': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'], 'Value': [10, 20, 30, 40, 50, 60, 70, 80, 90]} df = pd.DataFrame(data)

Create a DuckDB connection

con = duckdb.connect()

Register the DataFrame with DuckDB

con.register('df_pivot', df)

Perform the pivot operation in DuckDB

query = """

PIVOT df_pivot ON Value USING Count(Value) GROUP BY Category

""" pivot_table = con.execute(query).fetchdf()

print(pivot_table)

it works out all-right:

  Category  10  20  30  40  50  60  70  80  90
0        A   1   0   0   1   0   0   1   0   0
1        B   0   1   0   0   1   0   0   1   0
2        C   0   0   1   0   0   1   0   0   1

Expected Behavior

pivot_table = df.pivot_table(index='Category', columns='Value', values='Value', aggfunc="count")

and

pivot_table = df.pivot_table(index='Category', columns='Value', values='Value', aggfunc="size")

and

pivot_table = df.pivot_table(index='Category', columns='Value', values='Value', aggfunc=len)

should all produce an non empty dataset like the one below (but count somehow fails and produces an empty one):

Value      10   20   30   40   50   60   70   80   90
Category                                             
A         1.0  NaN  NaN  1.0  NaN  NaN  1.0  NaN  NaN
B         NaN  1.0  NaN  NaN  1.0  NaN  NaN  1.0  NaN
C         NaN  NaN  1.0  NaN  NaN  1.0  NaN  NaN  1.0

Installed Versions

INSTALLED VERSIONS

commit : bdc79c1
python : 3.10.13.final.0
python-bits : 64
OS : Linux
OS-release : 6.5.0-1015-gcp
Version : #15~22.04.1-Ubuntu SMP Wed Feb 14 21:22:00 UTC 2024
machine : x86_64
processor :
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.1
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0.post0
setuptools : 69.0.2.post0
pip : 23.3.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 : None
IPython : None
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2024.1
qtpy : None
pyqt5 : None