pd.pivot_table() is unable to compute statistics on datetime64[ns]

user_6096464 0
Viewed 29

The following code creates a pd.DataFrame with two columns, date and category. The date column is set to use the datetime64[ns] type.

>>> df = pd.DataFrame(data={'date': pd.Series(['2021-01-01', '2021-01-02', 
                      '2021-01-03'], dtype='datetime64[ns]'), 'category': ['A', 'B', 'C']})

Pandas allows computing numeric statistics over the date column:

>>> df['date'].min()
Timestamp('2021-01-01 00:00:00')
>>> df['date'].median()
Timestamp('2021-01-02 00:00:00')
>>> df['date'].mean()
Timestamp('2021-01-02 00:00:00')

However, when I create a pivot_table, it is unable to compute statistics, like mean or median, over the date column:

>>> df.pivot_table(['date'], ['category'], aggfunc='median')
Traceback (most recent call last):
...
  File "/home/dmitry/.local/lib/python3.8/site-packages/pandas/core/groupby/generic.py", line 1121, in _cython_agg_blocks
    raise DataError("No numeric types to aggregate")
pandas.core.base.DataError: No numeric types to aggregate

However, min/max works just fine:

>>> df.pivot_table(['date'], ['category'], aggfunc='min')
               date
category           
A        2021-01-01
B        2021-01-02
C        2021-01-03

Do you know what the issue is?

pandas
1 Answers

You can convert the datetime to int64 and then turn it back after the pivot_table.

df.astype({'date': 'int64'}).pivot_table(values=['date'], index=['category'], aggfunc='median').astype('datetime64[ns]')
user_141756010
Related