BUG: `margins` value incorrect with `count` aggfunc and no index

[sfc-gh-rdurrani]

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

df = pd.DataFrame({'x': [1, 1, 2], 'y': [3, 3, 4], 'z': [5, 5, 6], 'w': [7, 8, 9]})
print(df.pivot_table(columns=["y", "z"], values="w", margins=True, aggfunc="count"))
# y  3      4
# z  5 All  6 All
# w  2   1  1   1

print(df.pivot_table(index="x", columns=["y", "z"], values="w", margins=True, aggfunc="count"))
# y      3    4 All
# z      5    6
# x
# 1    2.0  NaN   2
# 2    NaN  1.0   1
# All  2.0  1.0   3

Issue Description

The all column should be a subaggregation over the aggregations, but seems to perhaps be calling count on the result rather than aggregating the counts? E.g., the first result should look like this:

# y  3      4
# z  5 All  6 All
# w  2   2  1   1

Expected Behavior

Values should be aggregations computed over the original data pivoting only on the first pivot column.

Installed Versions

INSTALLED VERSIONS

commit : d9cdd2e
python : 3.11.5.final.0
python-bits : 64
OS : Darwin
OS-release : 23.4.0
Version : Darwin Kernel Version 23.4.0: Fri Mar 15 00:12:49 PDT 2024; root:xnu-10063.101.17~1/RELEASE_ARM64_T6020
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.2
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0.post0
setuptools : 68.0.0
pip : 23.2.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 : 8.22.2
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 : 0.19.0
tzdata : 2024.1
qtpy : None
pyqt5 : None

[rhshadrach]

The all column should be a subaggregation over the aggregations, but seems to perhaps be calling count on the result rather than aggregating the counts?

It appears to me the current behavior is how it's documented.

https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html

If margins=True, special All columns and rows will be added with partial group aggregates across the categories on the rows and columns.

If margin=True, aggfunc will be used to calculate the partial aggregates.

You seem to think pandas should always use sum instead of the provided aggfunc. Is there anything in the documentation that makes you think that should be the behavior?

[djpixles]

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

In [1]: import pandas as pd
   ...: import numpy as np
   ...: def df_data():
   ...:     return {
   ...:         "A": [
   ...:             "foo",
   ...:             "foo",
   ...:             "foo",
   ...:             "foo",
   ...:             "bar",
   ...:             "bar",
   ...:             "bar",
   ...:             "bar",
   ...:             "foo",
   ...:             "foo",
   ...:             "foo",
   ...:         ],
   ...:         "B": [
   ...:             "on.e",
   ...:             "on.e",
   ...:             "on.e",
   ...:             'tw"o',
   ...:             "on.e",
   ...:             "on.e",
   ...:             "on.e",
   ...:             'tw"o',
   ...:             'tw"o',
   ...:             'tw"o',
   ...:             "on.e",
   ...:         ],
   ...:         "C": [
   ...:             "dull",
   ...:             "dull",
   ...:             "shi'ny",
   ...:             "dull",
   ...:             "dull",
   ...:             "shi'ny",
   ...:             "shi'ny",
   ...:             "dull",
   ...:             "shi'ny",
   ...:             "shi'ny",
   ...:             "shi'ny",
   ...:         ],
   ...:         "D": np.arange(0, 11),
   ...:         "E": np.arange(1, 12),
   ...:         "F": np.arange(2, 13),
   ...:     }
   ...: df = pd.DataFrame(df_data())
   ...: pivot_kwargs = {
   ...:     "columns": ["B", "C"],
   ...:     "values": ["D", "E"],
   ...:     "aggfunc": "count",
   ...:     "dropna": True,
   ...:     "margins": True,
   ...: }
   ...: df.pivot_table(**pivot_kwargs)
Out[1]:
B on.e            tw"o
C dull shi'ny All dull shi'ny All
D    3      4   2    2      2   2
E    3      4   2    2      2   2

Issue Description

The all column should be a subaggregation over the aggregations, but seems to perhaps be calling count on the result rather than aggregating the counts? e.g. if aggfunc is prod instead, I get the following:

B on.e               tw"o
C dull shi'ny    All dull shi'ny   All
D    0    600      0   21     72  1512
E   10   1386  13860   32     90  2880

Expected Behavior

Values should be 7 and 4 respectively.

Installed Versions

[sfc-gh-rdurrani]

@rhshadrach to clarify my question was more on whether the inputs to the aggfunc in the margin were the original values, or the aggregated values. For context, take the following dataframe:

      A    B      C   D     E     F
0   foo  baz   dull   0   0.0  None
1   foo  baz   dull   1   1.0  None
2   foo  baz  shiny   2   2.0  None
3   foo  baz   dull   3   3.0  None
4   bar  baz   dull   4   NaN  None
5   bar  baz  shiny   5   5.0  None
6   bar  baz  shiny   6   6.0  None
7   bar  baz   dull   7   7.0  None
8   foo  baz   dull   8   8.0  None
9   foo  baz  shiny   9   NaN  None
10  foo  buz  shiny  10  10.0  None
11  foo  buz  shiny  11   NaN  None
12  foo  baz   spot  12   NaN  None
13  bar  baz   spot  13   NaN  None

if we call pivot_table with the following kwargs:

In [26]: pivot_kwargs
Out[26]:
{'index': None,
 'columns': ['B', 'C'],
 'values': 'D',
 'dropna': False,
 'fill_value': None,
 'margins': True}

In [27]: df.pivot_table(**pivot_kwargs)
Out[27]:
B       baz                        buz
C      dull shiny  spot       All dull shiny spot   All
D  3.833333   5.5  12.5  7.277778  NaN  10.5  NaN  10.5

We get 7.28 for the margins value of baz. If I modify the pivot kwargs like so:

In [28]: pivot_kwargs['columns'] = 'B'

In [29]: df.pivot_table(**pivot_kwargs)
Out[29]:
B       baz       All   buz   All
D  5.833333  5.833333  10.5  10.5

We get 5.83 for the aggregate value of baz. My confusion is as follows: is margins supposed to be the result of the aggfunc applied over all values (grouped by baz (as would be the case with sum)), or is it supposed to be the aggfunc applied over the aggregations from the first pivot? I ask because when index is present, the behavior seems to be different:

In [40]: pivot_kwargs
Out[40]:
{'index': 'A',
 'columns': ['B', 'C'],
 'values': 'D',
 'dropna': False,
 'fill_value': None,
 'margins': True}

In [41]: df.to_pandas().pivot_table(**pivot_kwargs)
Out[41]:
B         baz              buz                  All
C        dull shiny  spot dull shiny spot
A
bar  5.500000   5.5  13.0  NaN   NaN  NaN  7.000000
foo  3.000000   5.5  12.0  NaN  10.5  NaN  6.222222
All  3.833333   5.5  12.5  NaN  10.5  NaN  6.500000

if we follow the aggregation over aggregation rule from above, the margin for baz dull should be (5.5 + 3)/2 = 4.25, but is instead 3.83.

[rhshadrach]

Thanks - agree there is an inconsistency here. Am I correct in saying that the OP example does not demonstrate the issue? it would be helpful to post reproducible and minimal examples. Can you update the OP? I'd recommend something like this:

df = pd.DataFrame({'x': [1, 1, 2], 'y': [3, 3, 4], 'z': [5, 5, 6], 'w': [7, 8, 9]})
print(df.pivot_table(columns=["y", "z"], values="w", margins=True, aggfunc="count"))
# y  3      4
# z  5 All  6 All
# w  2   1  1   1

print(df.pivot_table(index="x", columns=["y", "z"], values="w", margins=True, aggfunc="count"))
# y      3    4 All
# z      5    6
# x
# 1    2.0  NaN   2
# 2    NaN  1.0   1
# All  2.0  1.0   3

[rhshadrach]

It appears to me that the index="x" method is implemented correct - it uses the original data when computing the margins, whereas leaving index unspecified gives the incorrect result. The example above should be:

# y  3      4
# z  5 All  6 All
# w  2   2  1   1

[sfc-gh-rdurrani]

Thank you @rhshadrach for confirming - I'll go ahead and update the OP with the example you suggest!

[matiaslindgren]

take