Comments (7)
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 2Issue 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 2880Expected 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.
Related Issues (20)
- BUG: OverflowError: value too large to convert to int when manipulating very large dataframes HOT 6
- DOC: Wrong bug number in what's new v3.0.0 HOT 3
- ENH: Checking gaps for time series HOT 2
- ENH: Support non-categorical values for pandas bar plots when x axis is datetime values HOT 2
- ENH: In pandas.testing.assert_frame_equal, support per-column configuration HOT 5
- DOC: read_csv: date_format HOT 2
- ENH: Breakpoint method for dataframes HOT 1
- BUG: inconsistency when `read_csv` reads MultiIndex with empty values HOT 1
- BUG: .str.contains `na` validation HOT 4
- ENH: Option to configure line wrapping for columns HOT 4
- BUG: inconsistent indices in `GroupByRolling` when selecting or not selecting subset of columns HOT 1
- BUG: provide better error message for pd.Timedelta - pd.Series[Timestamp] HOT 4
- BUG: Wrong result of Kurtosis HOT 2
- BUG: StringDtype conversion to bool changes False to True HOT 3
- Development with Visual Studio Tools 2022 not possible? HOT 3
- ERR: consistent error messages for unsupported reduction operations
- ENH: `DataFrame.struct.explode(column, *, separator=".")` method to pull struct subfields into the parent DataFrame HOT 2
- BUG: `to_markdown` raises `ValueError` when values are `np.array`s with more than one element HOT 3
- DOC: fix docstring validation errors for pandas.Series HOT 18
- ENH: Drop list menu lost when reading xlsx files from pandas HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pandas.