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:

":1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.mean is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function."

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

INSTALLED VERSIONS ------------------ commit : 91111fd99898d9dcaa6bf6bedb662db4108da6e6 python : 3.9.13.final.0 python-bits : 64 OS : Linux OS-release : 4.4.0-19041-Microsoft Version : #1237-Microsoft Sat Sep 11 14:32:00 PST 2021 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : C.UTF-8 LOCALE : en_US.UTF-8 pandas : 1.5.1 numpy : 1.23.4 pytz : 2022.5 dateutil : 2.8.2 setuptools : 65.5.0 pip : 22.3 Cython : None pytest : 7.1.3 hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : None html5lib : None pymysql : None psycopg2 : None jinja2 : 3.1.2 IPython : 8.5.0 pandas_datareader: None bs4 : 4.11.1 bottleneck : None brotli : fastparquet : None fsspec : 2022.10.0 gcsfs : None matplotlib : 3.6.1 numba : None numexpr : None odfpy : None openpyxl : None pandas_gbq : None pyarrow : None pyreadstat : None pyxlsb : None s3fs : None scipy : 1.9.2 snappy : None sqlalchemy : None tables : None tabulate : None xarray : None xlrd : None xlwt : None zstandard : None tzdata : None

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?