我有这样的数据，
[
{
"arrivalDate": 1493611200000,
"price": 4588
},
{
"arrivalDate": 1493352000000,
"price": 4630
},
{
"arrivalDate": 1493179200000,
"price": 4553
},
{
"arrivalDate": 1493092800000,
"price": 4530
},
{
"arrivalDate": 1493006400000,
"price": 4578
},
{
"arrivalDate": 1490572800000,
"price": 4457
}
]
我想汇总并显示每日价格和每周平均价格的图表。
每日统计数据很容易，但我无法计算出每周平均值。请注意，我的纪元以毫秒为单位。
我试过了，
def get_daily_weekly_and_monthly_aggregates(datewise_prices):
p_dataframe = pandas.DataFrame.from_records(datewise_prices)
daily = p_dataframe.sort_values(by=['arrivalDate'], ascending=[False])[:6] # for 7 days
# TODO - This should be configurable. Not hardcoded.
daily['label'] = daily['arrivalDate'].map(lambda x: pytz.timezone('Asia/Kolkata').localize(datetime.fromtimestamp(x/1000)).strftime("%d %b"))
daily_list = daily.to_dict('records')
pandas.to_datetime(p_dataframe['arrivalDate'], unit='ms')
p_dataframe.set_index(pandas.DatetimeIndex(p_dataframe["arrivalDate"]), inplace=True)
weekly = p_dataframe.groupby(pandas.TimeGrouper("W")).agg(lambda grp: list(grp))
weekly.mean()
weekly_list = weekly.to_dict('records')
return daily_list, weekly_list
我得到了weekly_list，
[
{
"arrivalDate": [
1490054400000,
1490572800000,
1493006400000,
1493092800000,
1493179200000,
1493352000000,
1493611200000
],
"modalPrice": [
4357,
4457,
4578,
4530,
4553,
4630,
4588
]
}
]
这是不希望的。
我真正需要的是，
[
{
"week": "1 May to 7 May",
"avg": 4588
},
{
"week": "24 Apr to 30 Apr",
"avg": 4572
},
{
"week": "27 Mar to 02 Apr",
"avg": 4457
}
]
我该怎么做呢？
最佳答案
您可以先使用 to_datetime ，然后使用 resample 聚合 sum ，最后如果需要删除 NaN 添加 dropna :
#with borrowing data from piRSquared answer
df['arrivalDate'] = pd.to_datetime(df.arrivalDate, unit='ms')
df1 = df.resample('W', on='arrivalDate')['price'].sum().dropna().reset_index()
print (df1)
arrivalDate price
0 2017-04-02 4457.0
1 2017-04-30 18291.0
2 2017-05-07 4588.0
on 中没有参数 resample 的类似解决方案，而是将 set_index 用于 DatetimeIndex :df['arrivalDate'] = pd.to_datetime(df.arrivalDate, unit='ms')
df1 = df.set_index('arrivalDate').resample('W')['price'].sum().dropna().reset_index()
print (df1)
arrivalDate price
0 2017-04-02 4457.0
1 2017-04-30 18291.0
2 2017-05-07 4588.0
然后通过减去
6 天数转换日期时间范围，最后创建 list :df1['arrivalDate'] = (df1['arrivalDate']-pd.offsets.DateOffset(days=6)).dt.strftime('%d %b')+
' to ' +
df1['arrivalDate'].dt.strftime('%d %b')
#if prices are always int
df1['price'] = df1['price'].astype(int)
print (df1)
arrivalDate price
0 27 Mar to 02 Apr 4457
1 24 Apr to 30 Apr 18291
2 01 May to 07 May 4588
L = df1.to_dict(orient='record')
print (L)
[{'arrivalDate': '27 Mar to 02 Apr', 'price': 4457},
{'arrivalDate': '24 Apr to 30 Apr', 'price': 18291},
{'arrivalDate': '01 May to 07 May', 'price': 4588}]
编辑:
#https://bpaste.net/show/909564ed4cb1
data = [
{
"price": 3050,
"arrivalDate": 1489536000000
},
{
"price": 3240,
"arrivalDate": 1489622400000
},
...
...
{
"price": 3150,
"arrivalDate": 1493611200000
},
{
"price": 3150,
"arrivalDate": 1493697600000
}
]
df = pd.DataFrame(data)
df['arrivalDate'] = pd.to_datetime(df.arrivalDate, unit='ms')
df1 = df.resample('W', on='arrivalDate')['price'].sum().dropna().reset_index()
print (df1)
arrivalDate price
0 2017-03-19 12460.0
1 2017-03-26 15930.0
2 2017-04-02 15735.0
3 2017-04-09 18880.0
4 2017-04-30 12640.0
5 2017-05-07 6300.0
df1['arrivalDate'] = (df1['arrivalDate'] - pd.offsets.DateOffset(days=6)).dt.strftime('%d %b') + ' to ' + \
df1['arrivalDate'].dt.strftime('%d %b')
#if pries are always int
df1['price'] = df1['price'].astype(int)
print (df1)
arrivalDate price
0 13 Mar to 19 Mar 12460
1 20 Mar to 26 Mar 15930
2 27 Mar to 02 Apr 15735
3 03 Apr to 09 Apr 18880
4 24 Apr to 30 Apr 12640
5 01 May to 07 May 6300
L = df1.to_dict(orient='record')
print (L)
[{'arrivalDate': '13 Mar to 19 Mar', 'price': 12460},
{'arrivalDate': '20 Mar to 26 Mar', 'price': 15930},
{'arrivalDate': '27 Mar to 02 Apr', 'price': 15735},
{'arrivalDate': '03 Apr to 09 Apr', 'price': 18880},
{'arrivalDate': '24 Apr to 30 Apr', 'price': 12640},
{'arrivalDate': '01 May to 07 May', 'price': 6300}]
并检查
df :print (df)
arrivalDate price
0 2017-03-15 00:00:00 3050
1 2017-03-16 00:00:00 3240
2 2017-03-17 00:00:00 3120
3 2017-03-18 00:00:00 3050
4 2017-03-20 00:00:00 3200
5 2017-03-22 00:00:00 3200
6 2017-03-23 00:00:00 3210
7 2017-03-24 00:00:00 3200
8 2017-03-25 00:00:00 3120
9 2017-03-27 00:00:00 3100
10 2017-03-28 00:00:00 3240
11 2017-03-30 00:00:00 3100
12 2017-03-31 00:00:00 3145
13 2017-04-01 00:00:00 3150
14 2017-04-04 00:00:00 3150
15 2017-04-05 00:00:00 3150
16 2017-04-06 00:00:00 3110
17 2017-04-07 00:00:00 3210
18 2017-04-08 00:00:00 3210
19 2017-04-09 00:00:00 3050
20 2017-04-26 04:00:00 3110
21 2017-04-27 04:00:00 3210
22 2017-04-28 04:00:00 3200
23 2017-04-29 04:00:00 3120
24 2017-05-01 04:00:00 3150
25 2017-05-02 04:00:00 3150
关于python - 使用pandas根据纪元时间对数据进行每日和每周分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43741004/