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
import pandas as pd
df = pd.DataFrame({"C1": ["a", "b", "c"],
"C2": [1, 2, 3]})
table = pd.pivot_table(df, columns=['C2'])
Issue Description
Getting FutureWarning:
"
Expected Behavior
pivot_table is internally using DataFrameGroupBy.mean, but does not allow a user to pass a numeric_only argument as suggested in the FutureWarning
Installed Versions
Comment From: phofl
cc @rhshadrach we should probably specify numeric_only, correct?
Comment From: rhshadrach
What's the expected output here? Currently on 1.5.x I get an empty DataFrame. Within the __internal_pivot_table
function, the groupby is using the entire frame (i.e. C1 is included) and grouping on C2
. Taking the mean of a string column fails, hence the warning.
If this is the desired behavior for pivot table - that is, this call should group on C2 and use all other columns to aggregate, then I think everything here is correct. Namely, this is going to fail in 2.0 instead of silently dropping columns, hence the warning. But the groupby call itself (in particular, grouping on C2) looks odd to me, though I'm not very familiar with pivot tables.
Comment From: phofl
Not sure, but I think we should raise a more specific warning, if this is not an unintended side effect.
Edit: To keep compatibility, we should probably specify numeric_only so that behaviour does not change in 2.0. is this possible?
Comment From: rhshadrach
Not sure, but I think we should raise a more specific warning, if this is not an unintended side effect.
Yea, agreed. I can take this up.
To keep compatibility, we should probably specify numeric_only so that behaviour does not change in 2.0. is this possible?
Assuming the intention of this op is "groupby by C2 and aggregate all other columns", I don't we should be specifying numeric_only=True
internally (this is how I interpret your suggestion above - but let me know if I got it wrong). You run into issues with dtypes that are non-numeric but can still be aggregated. In particular, first
or last
with any dtype, but also sum
with strings and and timedeltas. Specifying numeric_only=True
, such columns would disappear on the user even though they can be aggregated, and I think that would be unexpected. Also, if my description is the operation is correct, then I think pandas should fail when it is asked to do something that it is not able to do.
One thing we could do here is add numeric_only
to pivot_table
(defaulting to False for consistency with the rest of the API). Not averse to this, but I'm not able to really reason about it without understanding more about pivot tables.
Comment From: Sierra-MC
The example I provided was the most simple I could come up with to create the warning: a dataframe with a numeric and non-numeric column. It is not the best example to show how pivot_tables are supposed to work/what they are for. Because I'm grouping by the numeric column and the non-numeric column can't be grouped (can't take the mean of a non-numeric column) it should come back as an empty dataframe in this case. (Not sure if this is desired behavior without a warning to the user of why they've received an empty dataframe back, but that's another discussion). The issue is that the FutureWarning coming up can't be avoided by the user based on the suggested action (adding a numeric_only keyword). And the warning appears to be for a function the user is not entering (it's used within the pivot_table function).
The more realistic version of this case (and what is coming up in my code) would be when you have a larger dataframe with many numeric and non-numeric columns. In this case, when creating your pivot table, it is expected that pandas will ignore all non-numeric columns and output the pivot table with the numeric values only. Some examples of this are shown here: https://datagy.io/python-pivot-tables/ (the first example under "Creating a Pivot Table in Pandas" shows the columns with non-numeric data are simply not in the pivot table).
Hopefully, this helps clear up any confusion. Apologies for the unclear example.
Comment From: rhshadrach
Hopefully, this helps clear up any confusion. Apologies for the unclear example.
Thanks! For the record, I don't think your example is unclear.
The more realistic version of this case (and what is coming up in my code) would be when you have a larger dataframe with many numeric and non-numeric columns. In this case, when creating your pivot table, it is expected that pandas will ignore all non-numeric columns and output the pivot table with the numeric values only.
pandas is able to support an arbitrary aggregation with pivot tables. If the aggregation was first
or last
, would you still expect it to drop any non-numeric column? What about a time delta with sum
?