BUG: timeseries.groupby(...).transform('mean') wrong when aggregating over pd.NaT

[behrenhoff]

Code Sample, a copy-pastable example

The problem is best shown in the following example:

import pandas as pd
s = pd.Series(['1 day', '3 days', pd.NaT], dtype='timedelta64[ns]')

print(f'{s.mean()=}\n')
# as expected: result is '2 days'

print(f"{s.apply('mean')=}\n")
# as expected: result is '2 days'

print("transform('mean')")
print(s.groupby([1,1,1]).transform('mean'))
# whoops: (a series of 3x) -35583 days +08:04:14.381741568
# expectation was: (a series of 3x) '2 days'

# Fixing the problem
print("\ntransform(pd.Series.mean)")
print(s.groupby([1,1,1]).transform(pd.Series.mean))
# (a series of 3x) '2 days', sanity restored :-)

Problem description

When passing a function name by string to transform, pd.NaT is not handled correctly. This not only happens for 'mean' but also for other functions like 'sum'.

I don't know how transform is looking up the function if a string is passed (and it's not really documented), but it certainly doesn't select pd.Series.mean but some other non-NaT-aware mean function.

This is surprising since NaT is handled correctly when calling apply('mean').

Tested with pd 1.3.2.

[AlexeyGy]

Can I work on this issue?

[simonjayhawkins]

s.groupby([1, 1, 1]).transform("mean") was raising DataError: No numeric types to aggregate with 1.2.5

I suspect this issue maybe related to or a duplicate of #42659. will label as a regression pending further investigation cc @jbrockmendel

[simonjayhawkins]

I suspect this issue maybe related to or a duplicate of #42659. will label as a regression pending further investigation cc @jbrockmendel

first bad commit: [fa8f68e] BUG/API: SeriesGroupBy reduction with numeric_only=True (#41342)

[AlexeyGy]

This happens because pd.Series.mean and 'mean' will lead to totally different paths through the code and hence different calculations:

• pd.Series.mean will use _transform_general and apply the given function which eventually gets us into DatetimeLikeArrayMixin#mean which fully supports nan values.
• 'mean' gets us into groupby#mean this in turn gets us to this line

values = values.view("int64")

which calls numpy and turns pd.NaT into a very low integer causing this problem.

Questions

@simonjayhawkins

1. Are there any plans to harmonize both paths? It would surely be easy to just make 'mean' take the same route as pd.Series.mean.

2. To mitigate, we could modify _resolve_numeric_only to return True if there are any NaN values in an otherwise numeric set. What do you think?

[simonjayhawkins]

Thanks @AlexeyGy for exploring this issue further.

• Are there any plans to harmonize both paths? It would surely be easy to just make 'mean' take the same route as pd.Series.mean.

refactoring, de-duplication, cleaning and maintaining api consistency is an ongoing process, but that may not be the best option if we wanted to backport any fixes to 1.3.x unless we limited the dispatch from groupby.mean to DatetimeLikeArrayMixin.mean @jbrockmendel?

• To mitigate, we could modify _resolve_numeric_only to return True if there are any NaN values in an otherwise numeric set.

There has been much discussion in #42395 and #43154 about changes to _resolve_numeric_only so I think further changes there is likely not desirable.

There is also the case that the 1.2.x behavior was incorrect and that the new behavior is a new bug and not a regression. But personally I would prefer to see 1.3.x patched to fix this.

#42659 is clearly a regression and it may well be that a fix for that case fixes this one too.

[jbrockmendel]

The correct long-term solution is for libgroupby.group_mean to take a is_datetimelike keyword like many of the other functions there do.

@AlexeyGy any interest in implementing this?

[AlexeyGy]

Yes, happy to help. Should not be a problem since I can peek at the other implementations in the file.

Awesome that there are even tests for both, libgroupby, and groupby already which I can extend.

[AlexeyGy]

I've drafted up #43467. Will want to add a couple more tests to ensure that nothing is breaking but want to share it out early. Open for feedback on it.