BUG: pivot_table downcasting dtypes even if not necessary

[phofl]

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

df = pd.DataFrame({"x": "a", "y": "b", "age": [20, 40]})
result = df.pivot_table(
    index='x', columns='y', values='age', aggfunc='mean', dropna=True
)

result = df.pivot_table(
    index='x', columns='y', values='age', aggfunc='mean', dropna=False
)

Issue Description

with dropna=True this returns int64 dtype, while we get float64 with dropna=False. This happens because we try to downcast if we set dropna, because we drop all nan rows which cast our dtypes to float.

But the downcast path is also hit, when we don't have all nan rows and hence the aggregation function returned the correct dtype all along.

Expected Behavior

I think both cases should be consistent if no nans are dropped, e.g. we should not try to downcast.

If we want to do this, we should probably deprecate or changing in 2.0, but not in a minor release

Installed Versions

main

[rhshadrach]

Related: #53521

[jorisvandenbossche]

Both cases seem to result now in a float result:

In [14]: df = pd.DataFrame({"x": "a", "y": "b", "age": [20, 40]})
    ...: result = df.pivot_table(
    ...:     index='x', columns='y', values='age', aggfunc='mean', dropna=True
    ...: )
    ...: 

In [15]: result
Out[15]: 
y     b
x      
a  30.0

In [16]: result = df.pivot_table(
    ...:     index='x', columns='y', values='age', aggfunc='mean', dropna=False
    ...: )

In [17]: result
Out[17]: 
y     b
x      
a  30.0

So we could close this issue after adding an explicit test for this.

[Swati-Sneha]

take

[AbhishekChaudharii]

Hi @jorisvandenbossche, it seems that this issue is the same as #47477. The test case was also added with the bug fix and while writing the test case and whatsnew @phofl has mentioned #47477 instead of this one(#47971). I think we can close this issue.

[rhshadrach]

@AbhishekChaudharii - the test added in #47477 is with Int64 and not int64. I think we should also have a test with int64.