python - 使用 pandas 根据纪元时间每天和每周对数据进行分组

Question

我有这样的数据，

[
  {
    "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
    }
] 

我该怎么做？

Answer 1

您可以使用 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

---

在 resample 中没有参数 on 的类似解决方案，而是使用 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