Pandas Timedelta mean returns error "No numeric types to aggregate". Why?

From reading the discussion of this issue on Github here, you can solve this issue by specifying numeric_only=False for mean calculation as follows

pd.concat([A,B], axis = 1).groupby("status_reason")["closing_time"] \
    .mean(numeric_only=False)

The problem might be In Progress only have NaT time, which might not allowed in groupby().mean(). Here's the test:

df = pd.DataFrame({'closing_time':['11:35:00', '07:13:00', np.nan,np.nan, np.nan],
                   'status_reason':['Won','Canceled','In Progress', 'In Progress', 'In Progress']})
df.closing_time = pd.to_timedelta(df.closing_time)
df.groupby('status_reason').closing_time.mean()

gives the exact error. To overcome this, do:

def custom_mean(x):
    try:
        return x.mean()
    except:
        return pd.to_timedelta([np.nan])

df.groupby('status_reason').closing_time.apply(custom_mean)

which gives:

status_reason
Canceled      07:13:00
In Progress        NaT
Won           11:35:00
Name: closing_time, dtype: timedelta64[ns]

I cannot say why groupby's mean() method does not work, but the following slight modification of your code should work: First, convert timedelta column to seconds with total_seconds() method, then groupby and mean, then convert seconds to timedelta again:

pd.to_timedelta(pd.concat([ A.dt.total_seconds(), B], axis = 1).groupby("status_reason")["closing_time"].mean(), unit="s")

For example dataframe below, the code -

df = pd.DataFrame({'closing_time':['2 days 11:35:00', '07:13:00', np.nan,np.nan, np.nan],'status_reason':['Won','Canceled','In Progress', 'In Progress', 'In Progress']})

df.loc[:,"closing_time"] = \
          pd.to_timedelta(df.closing_time).dt.days*24*3600 \
          + pd.to_timedelta(df.closing_time).dt.seconds

# or alternatively use total_seconds() to get total seconds in timedelta as follows
# df.loc[:,"closing_time"] = pd.to_timedelta(df.closing_time).dt.total_seconds()

pd.to_timedelta(df.groupby("status_reason")["closing_time"].mean(), unit="s")

produces

status_reason
Canceled      0 days 07:13:00
In Progress               NaT
Won           2 days 11:35:00
Name: closing_time, dtype: timedelta64[ns]

After a few investigation, here is what I found:

Most of the confusion comes from the fact that in one case I was calling SeriesGroupBy.mean() and in the other case Series.mean()

These functions are actually different and have different behaviours. I was not realizing that

The second important point is that converting to numeric, or to seconds, leads to a totally different behaviour when it comes to handling NaNs value.

To overcome this situation, the first thing you have to do is deciding how to handle NaN values. The best approach depends on what we want to achieve. In my case, it's fine to have even a simple categorical result, so I can do something like this:

import datetime

def define_time(row):
    if pd.isnull(row["closing_time"]):
        return "Null"
    elif row["closing_time"] < datetime.timedelta(days=100):
        return "<100"
    elif row["closing_time"] > datetime.timedelta(days=100):
        return ">100"


time_results = pd.concat([A,B], axis = 1).apply(lambda row:define_time(row), axis = 1)

In the end the result is like this:

In : 
    time_results.value_counts()
Out : 
    >100    1452
    <100    1091
    Null    1000
    dtype: int64