BUG: groupby agg fails silently with mixed dtypes
State: closed

- [x] closes #43209
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [x] whatsnew entry


Labels:
name: Groupby
name: Dtype Conversions
description: Unexpected or buggy dtype conversions
name: Regression
description: Functionality that used to work in a prior pandas version

Comments:
cc @rhshadrach @jbrockmendel for a look
ok 2 comments @debnathshoham pls ping when reasolved & green
@jreback added comments to both places + Green
sorry, I didn't follow it in the first comment.

Actually in master, I don't think dtype of the output is being maintained in any of these funcs
`'count', 'sum', 'std', 'var', 'sem', 'mean', 'median', 'prod', 'min', 'max'`

Is the expectation that the output will have the original dtypes, i.e. `int64, Int64, int64` ?
> Is the expectation that the output will have the original dtypes, i.e. `int64, Int64, int64` ?

for the code sample, that would be reasonable, although returning `float64` for the `Int64` columns would probably also be acceptable as that was the return type before the regression.

How does the changes in PR relate to the changes in the PR that caused the regression #41706? (or to put it a different way, which change in #41706 caused the regression and how does this PR rectify that?)
Before https://github.com/pandas-dev/pandas/pull/41706, the actual calculation was happening in `agg_general` in `result = self.aggregate(lambda x: npfunc(x, axis=self.axis))`.
Honestly, I didn't try to undo what that patch did, I went on to fix the inconsistency in the result coming post that patch.
Agreed @debnathshoham - on master, the dtypes in @simonjayhawkins' example come out at all float. What you have here is certainly an improvement over that, but it doesn't fully fix the regression. It's unclear to me if this patch is a good step in fully resolving the regression, or if fully solving it would make this patch obsolete. Will need to investigate more.
@simonjayhawkins 
getting the below now. Let me know if this works.
```
In [3]: import pandas as pd
   ...: 
   ...: print(pd.__version__)
   ...: df = pd.DataFrame([[1, 2, 3, 4, 5, 6]] * 3)
   ...: df.columns = pd.MultiIndex.from_product([["a", "b"], ["i", "j", "k"]])
   ...: for col in df.columns:
   ...:     if col[-1] == "j":
   ...:         df[col] = df[col].astype("Int64")
   ...: print("mixed int:")
   ...: result = df.groupby(level=1, axis=1).sum()
   ...: print(result)
   ...: print(result.dtypes)
   ...: 
1.4.0.dev0+659.g572f23c3ba
mixed int:
   i  j  k
0  5  7  9
1  5  7  9
2  5  7  9
i    int64
j    Int64
k    int64
dtype: object
```
Also, in `1.2.5` `var` and `std` were raising for the above `df`, and in `1.3.2` they were returning empty DataFrame.

Thanks @debnathshoham for all the effort here. I apologize that I've not looked in detail at the fix but a regression fix should probably not need so much new logic added. I could be wrong though!

1.3.3 is scheduled for tomorrow. moving to 1.3.4 to allow more time for discussion.
Trying to explain what changed in #41706, and how I tried to remediate that (I am sure there will be more elegant / better way to do this).

**Previous Behavious**
There are multiple funcs (e.g. `sum`, `var`) that rely on `_cython_agg_general` for calculation, and for `axis=1` with `mixed` it is transposed and hence is cast to `object` (xref #43337). But I think that might be the expected behaviour (mentioned under `Notes` in [docs](https://pandas.pydata.org/pandas-docs/dev/reference/api/pandas.DataFrame.transpose.html)).

Since `numeric_only` is `True`, these data s dropped.
Before #41706, this used to raise `DataError` and was handled separately in `_agg_general`. (But  this didn't cover all the cases and raised incorrectly in some. I have seen `groupby.std` and `groupby.var` but could be others).

Since this error was removed in that PR, it was returning empty dataframe silently.

**This PR**
I have firstly tried to cast the transposed dataframe to the `common_dtype`, so we get the correct result first. Then I tried to cast it back to the original dtypes in `_maybe_recast_columns_if_transposed`. So there can be a few cases where the dtype of result and input will differ (like in `std`/`var` where they mostly become floating).

I tried to look for a function to cast a dataframe to certain dtypes, and if not possible upcast (say int -> float, Int -> Float). Unfortunately I was not able to find that. So i tried to match the values of post_cast vs. pre_cast to check if something is truncated. If something is truncated, I returned the previous result w/o casting, otherwise cast it to match the input dtypes.

Hope this helps.

I another simple, alternative would be to amend `numeric_only` to account for the transpose and subsequent object casting (this may be better as well, but this won't exactly match `1.2.5`).
i think let's try just reverting https://github.com/pandas-dev/pandas/pull/41706 instead of trying to fix this. (and then deprecate what is needed instead)
Doesn't the PR that changes DataFrame.transpose fix this?  That seems like a much better option than reverting #41706, which fixed a bunch of bugs.

Again shout it from the rooftops: this is caused by the lack of 2D EAs.
yes, but that was casting the the result df to the `common_dtype` (which wasn't matching 1.2.5 behaviour)
can you show what these test functions return in 1.2.5 vs what this PR gives. and show any difference in a comment.
added comments in the tests.
To summarise, for df with mixed dtypes like below. 
For sum - Columns with `Int64` was cast to `float64` in 1.2.5, whereas in this PR it would cast to `int64`.
For std - ValueError : Length mismatch in 1.2.5. In this PR they come as `float64`.
Fot var - DataError : No numeric types to aggregate in 1.2.5. In this PR they come as `float64`
```
In [32]:     df = DataFrame(
    ...:         np.arange(12).reshape(3, 4),
    ...:         index=Index([0, 1, 0], name="y"),
    ...:         columns=Index([10, 20, 10, 20], name="x"),
    ...:         dtype="int64",
    ...:     ).astype({10: "Int64"})

In [33]: df.dtypes
Out[33]: 
x
10    Int64
20    int64
10    Int64
20    int64
dtype: object
@jreback @simonjayhawkins 
wondering if you got a chance to look into this? 
xref - #43501 (I think this is related)
@jreback Green
thanks @debnathshoham 
@meeseeksdev backport 1.3.x
Something went wrong ... Please have  a look at my logs.