按日期计算平均价格

Question

,Unnamed: 0,#=TimeAndSale,EventSymbol,EventTime,Time,Sequence,ExchangeCode,Price,Size,BidPrice,AskPrice,SaleConditions,Flags
0,367,TimeAndSale,ZNGA,2015-03-30 09:30:00.405,20150331-093000-0400,395:2,Q,2.72,138914,2.76,2.8,@O X,22596
1,368,TimeAndSale,ZNGA,2015-03-30 09:30:00.405,20150331-093000-0400,395:3,Q,2.71,138914,2.76,2.8,@  Q,8256
2,391,TimeAndSale,ZNGA,2015-03-30 09:30:01.000,20150331-093000-0400,990:4,D,2.73,300,2.75,2.76,@4  ,22592
3,399,TimeAndSale,ZNGA,2015-03-30 09:30:02.115,20150331-093002-0400,105:5,D,2.43,200,2.75,2.76,@4  ,22592
4,401,TimeAndSale,ZNGA,2015-03-30 09:30:02.201,20150331-093002-0400,192:6,D,2.23,2,2.75,2.76,@4 I,22592
5,404,TimeAndSale,ZNGA,2015-03-30 09:30:02.660,20150331-093002-0400,649:7,Q,2.54,4,2.75,2.76,@  I,8256
6,422,TimeAndSale,ZNGA,2015-03-30 09:30:04.337,20150331-093004-0400,327:9,D,2.23,287,2.75,2.76,@   ,8260
7,433,TimeAndSale,ZNGA,2015-03-30 09:30:06.814,20150331-093006-0400,803:10,D,2.23,17,2.75,2.76,@  I,8256
8,437,TimeAndSale,ZNGA,2015-03-30 09:30:07.922,20150331-093007-0400,912:12,Q,2.742,100,2.75,2.76,@F  ,22596
9,444,TimeAndSale,ZNGA,2015-03-30 09:30:09.081,20150331-093009-0400,71:13,D,2.731,3,2.75,2.76,@  I,8256
10,445,TimeAndSale,ZNGA,2015-03-31 09:30:09.468,20150331-093009-0400,458:15,Q,2.72,100,2.75,2.76,@F  ,22596
11,448,TimeAndSale,ZNGA,2015-03-31 09:30:15.828,20150331-093015-0400,818:16,D,2.72,5,2.75,2.76,@4ZI,22592
12,450,TimeAndSale,ZNGA,2015-03-31 09:30:15.855,20150331-093015-0400,845:17,D,2.71,100,2.75,2.76,@4Z ,22592
13,451,TimeAndSale,ZNGA,2015-03-31 09:30:15.858,20150331-093015-0400,848:18,D,2.75,66,2.75,2.76,@4ZI,22592
14,452,TimeAndSale,ZNGA,2015-03-31 09:30:15.858,20150331-093015-0400,848:19,D,2.714,8,2.75,2.76,@4ZI,22592
15,453,TimeAndSale,ZNGA,2015-03-31 09:30:15.858,20150331-093015-0400,848:20,D,2.732,2,2.75,2.76,@4ZI,22592
16,454,TimeAndSale,ZNGA,2015-03-31 09:30:15.858,20150331-093015-0400,848:21,D,2.732,2,2.75,2.76,@4ZI,22592
17,455,TimeAndSale,ZNGA,2015-03-31 09:30:15.858,20150331-093015-0400,848:22,D,2.714,10,2.75,2.76,@4ZI,22592
18,459,TimeAndSale,ZNGA,2015-03-31 09:30:20.840,20150331-093020-0400,830:23,D,2.742,40,2.75,2.76,@  I,8256
19,460,TimeAndSale,ZNGA,2015-03-31 09:30:21.659,20

我准备了这个小样本，因为我想按日期计算熊猫的平均价格，但我不知道该怎么做。

尝试：

df.groupby(df['EventTime']).mean('Price')

我该如何解决它？

Answer 1

如果您的列EventTime已经是datetime类型，则可以执行以下操作：

df.groupby(df['EventTime'].dt.date).mean()['Price']

这给了你这个：

>>> df.groupby(df['EventTime'].dt.date).mean()['Price']
EventTime
2015-03-30    2.5293
2015-03-31    2.7260

如果没有，只需先将其转换为pd.datetime：

df['EventTime'] = pd.to_datetime(df['EventTime'])
df.groupby(df['EventTime'].dt.date).mean()['Price']

解释：

使用 df['EventTime'].dt.date 作为 GroupBy 的参数可确保仅按日期分组(忽略时间戳(

Answer 2

@sacul答案是正确的，但我更喜欢以下步骤：

将列转换为日期时间

df.EventTime = pd.to_datetime(df.EventTime)

使用频率为"D"的石斑鱼分组(您可以在此处查看可用别名列表(

>>> df.groupby(pd.Grouper(key='EventTime', freq='D')).mean()['Price']
EventTime
2015-03-30    2.5293
2015-03-31    2.7260