BUG: pandas pivot_table count over a dataframe with 2 columns results in empty dataset when using aggfunc="count"

[luxspes]

Pandas version checks

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

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

• I have confirmed this bug exists on the main branch of pandas.

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

[yukikitayama]

When I read the documentation of pandas.pivot_table, I think it assumes that the dataframe has a value column(s) to aggregate, but your Value column works like a category (index or columns) to aggregate values.

I guess probably this is not what you are looking for, but here is one way to avoid empty dataframe.

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)
df["num"] = 1

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

print(pivot_table)

[luxspes]

Thanks for the folllow up @yukikitayama
Indeed, I am aware of that "dummy column" workaround, however I think that should not be necessary (as is not necessary in Polars and in SQL itself)

Maybe the solution is what @e-motta suggests in this Stackoverflow comment?