BUG: `margins` value incorrect with `count` aggfunc and no index about pandas HOT 7 OPEN

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

from pandas.

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?

from pandas.

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

from pandas.

@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.

from pandas.

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

from pandas.

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

from pandas.

take

from pandas.