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

[sfc-gh-rdurrani]

Pandas version checks

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

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

• [X] 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 : d9cdd2ee5a58015ef6f4d15c7226110c9aab8140 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

• [x] I have checked that this issue has not already been reported.
• [x] I have confirmed this bug exists on the latest version of pandas.
• [x] 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