英文:
Why does pandas mean, on datetime, work on a series but not on a groupby object
问题
我正在尝试按组计算日期的均值。
import pandas as pd
df = pd.DataFrame({'Id': ['A', 'A', 'B', 'B'],
'Date': [pd.datetime(2000, 12, 31), pd.datetime(2002, 12, 31),
pd.datetime(2000, 6, 30), pd.datetime(2002, 6, 30)]})
这一直是一个让人头疼的问题,所以我很高兴地了解到这似乎在pandas 0.25中已经修复了 https://stackoverflow.com/questions/27907902/datetime-objects-with-pandas-mean-function。
df['Date'].mean()
Out[45]: Timestamp('2001-09-30 00:00:00') # 这个可以工作
然而,使用groupby
无法做到这一点。
df.groupby('Id')['Date'].mean()
Traceback (most recent call last):
File "<ipython-input-46-5fae5ffac6c6>", line 1, in <module>
df.groupby('Id')['Date'].mean()
File "C:\Users\xxx\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\groupby\groupby.py", line 1205, in mean
"mean", alt=lambda x, axis: Series(x).mean(**kwargs), **kwargs
File "C:\Users\xxx\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\groupby\groupby.py", line 888, in _cython_agg_general
raise DataError("No numeric types to aggregate")
DataError: No numeric types to aggregate
发生了什么情况,是否有一个简单的解决方法?
<details>
<summary>英文:</summary>
I am trying to take the mean of dates in by groups.
import pandas as pd
df = pd.DataFrame({'Id': ['A', 'A', 'B', 'B'],
'Date': [pd.datetime(2000, 12, 31), pd.datetime(2002, 12, 31),
pd.datetime(2000, 6, 30), pd.datetime(2002, 6, 30)]})
This has always been a pain to do, so I was pleased to learn that this had apparntly been fixed in pandas 0.25 https://stackoverflow.com/questions/27907902/datetime-objects-with-pandas-mean-function.
df['Date'].mean()
Out[45]: Timestamp('2001-09-30 00:00:00') # This works
However, this cant be done using ´groupby´
df.groupby('Id')['Date'].mean()
Traceback (most recent call last):
File "<ipython-input-46-5fae5ffac6c6>", line 1, in <module>
df.groupby('Id')['Date'].mean()
File "C:\Users\xxx\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\groupby\groupby.py", line 1205, in mean
"mean", alt=lambda x, axis: Series(x).mean(**kwargs), **kwargs
File "C:\Users\xxx\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\groupby\groupby.py", line 888, in _cython_agg_general
raise DataError("No numeric types to aggregate")
DataError: No numeric types to aggregate
What is going on here, and is there an easy workaround?
</details>
# 答案1
**得分**: 2
使用lambda函数与[`GroupBy.agg`](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.agg.html)或[`GroupBy.apply`](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.apply.html):
```python
print(df.groupby('Id')['Date'].agg(lambda x: x.mean()))
print(df.groupby('Id')['Date'].agg(pd.Series.mean))
print(df.groupby('Id')['Date'].apply(lambda x: x.mean()))
print(df.groupby('Id')['Date'].apply(pd.Series.mean))
Id
A 2001-12-31
B 2001-06-30
Name: Date, dtype: datetime64[ns]
区别在于如果有多个列:
df = pd.DataFrame({'Id': ['A', 'A', 'B', 'B'],
'Date': [pd.datetime(2000, 12, 31), pd.datetime(2002, 12, 31),
pd.datetime(2000, 6, 30), pd.datetime(2002, 6, 30)]})
df['Date1'] = df['Date']
print(df.groupby('Id').agg(lambda x: x.mean()))
Date Date1
Id
A 2001-12-31 2001-12-31
B 2001-06-30 2001-06-30
print(df.groupby('Id').agg(pd.Series.mean))
Date Date1
Id
A 2001-12-31 2001-12-31
B 2001-06-30 2001-06-30
print(df.groupby('Id').apply(lambda x: x.mean()))
Empty DataFrame
Columns: []
Index: []
print(df.groupby('Id').apply(pd.Series.mean))
Empty DataFrame
Columns: []
Index: []
为什么pandas中的datetime上的mean在Series上有效,但在groupby对象上无效?
一段时间以前,对于Series、Datetime的mean
存在问题,可以查看这里,所以在pandas的一些未来版本中,这个问题可能已经解决。
英文:
Use lambda function with GroupBy.agg
or GroupBy.apply
:
print (df.groupby('Id')['Date'].agg(lambda x: x.mean()))
print (df.groupby('Id')['Date'].agg(pd.Series.mean))
print (df.groupby('Id')['Date'].apply(lambda x: x.mean()))
print (df.groupby('Id')['Date'].apply(pd.Series.mean))
Id
A 2001-12-31
B 2001-06-30
Name: Date, dtype: datetime64[ns]
Difference is if multiple columns:
df = pd.DataFrame({'Id': ['A', 'A', 'B', 'B'],
'Date': [pd.datetime(2000, 12, 31), pd.datetime(2002, 12, 31),
pd.datetime(2000, 6, 30), pd.datetime(2002, 6, 30)]})
df['Date1'] = df['Date']
print (df.groupby('Id').agg(lambda x: x.mean()))
Date Date1
Id
A 2001-12-31 2001-12-31
B 2001-06-30 2001-06-30
print (df.groupby('Id').agg(pd.Series.mean))
Date Date1
Id
A 2001-12-31 2001-12-31
B 2001-06-30 2001-06-30
print (df.groupby('Id').apply(lambda x: x.mean()))
Empty DataFrame
Columns: []
Index: []
print (df.groupby('Id').apply(pd.Series.mean))
Empty DataFrame
Columns: []
Index: []
>Why does pandas mean, on datetime, work on a series but not on a groupby object
Some time ago it was problem with mean
for Series, Datetimes, check this, so possible in some next versions of pandas this should working well.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论