时间戳

首页 > 解决方案 > pandas 在两级 groupby 之后聚合列值

python - pandas 在两级 groupby 之后聚合列值

问题描述

我有一个 4 列的熊猫数据框。

时间值递增，但间隔不规则。我需要单独的信号到数据帧中的列，并以固定间隔重新采样每个信号的强度以进行互相关计算。重新采样的强度只是该信号的最后记录值（稍后可能使用插值）。事件计数是该时间间隔内所有事件的总和。

我按信号 id 对捕获的信号样本进行分组，然后使用 grouper 将每个信号的样本分组到时间间隔。获得重采样数据后，我再次将强度连接到数据帧中。

dfg = df.groupby('signalid')
signals_list = {g: dfg.get_group(g).groupby(pd.Grouper(key='time', freq='100ms')).agg({'intensity' : 'last', 'eventcount' : np.sum}) for g in dfg.groups}
intensity_list = [v['intensity'].to_frame(k) for k,v in signals_list.items()]
intensity_df = pd.concat(intensity_list).fillna(method='ffill')

event_list = [v['eventcount'].to_frame(k) for k,v in signals_list.items()]
event_df = pd.concat(event_list)

有没有更简洁的方法可以做到这一点而不将其转换为系列列表？

这是源数据框的一部分：

from pandas import Timestamp
d = {'time': {0: Timestamp('2018-10-23 06:09:29.803606'),
  1: Timestamp('2018-10-23 06:09:29.803611'),
  2: Timestamp('2018-10-23 06:09:29.803607'),
  3: Timestamp('2018-10-23 06:09:29.803614'),
  4: Timestamp('2018-10-23 06:09:29.803609'),
  5: Timestamp('2018-10-23 06:09:29.803617'),
  6: Timestamp('2018-10-23 06:09:29.803616'),
  7: Timestamp('2018-10-23 06:09:29.803621'),
  8: Timestamp('2018-10-23 06:09:29.813251'),
  9: Timestamp('2018-10-23 06:09:29.813253'),
  10: Timestamp('2018-10-23 06:09:30.376274'),
  11: Timestamp('2018-10-23 06:09:30.376275'),
  12: Timestamp('2018-10-23 06:09:30.386322'),
  13: Timestamp('2018-10-23 06:09:30.386323'),
  14: Timestamp('2018-10-23 06:09:30.386325'),
  15: Timestamp('2018-10-23 06:09:30.386327'),
  16: Timestamp('2018-10-23 06:09:30.407347'),
  17: Timestamp('2018-10-23 06:09:30.407346'),
  18: Timestamp('2018-10-23 06:09:30.492530'),
  19: Timestamp('2018-10-23 06:09:30.492532')},
 'signalid': {0: 1299,
  1: 1299,
  2: 1299,
  3: 1299,
  4: 1299,
  5: 1299,
  6: 1299,
  7: 1299,
  8: 27,
  9: 27,
  10: 1299,
  11: 1299,
  12: 1177,
  13: 1177,
  14: 1177,
  15: 1177,
  16: 5,
  17: 5,
  18: 2628,
  19: 2628},
 'intensity': {0: 63050,
  1: 63050,
  2: 63050,
  3: 63050,
  4: 63050,
  5: 63050,
  6: 63050,
  7: 63050,
  8: 44600,
  9: 44600,
  10: 63050,
  11: 63050,
  12: 7130,
  13: 7130,
  14: 7130,
  15: 7130,
  16: 63150,
  17: 63150,
  18: 17680,
  19: 17680},
 'eventcount': {0: 1000,
  1: 1000,
  2: 400,
  3: 400,
  4: 200,
  5: 200,
  6: 600,
  7: 600,
  8: 1000,
  9: 1000,
  10: 600,
  11: 600,
  12: 1000,
  13: 1000,
  14: 5000,
  15: 5000,
  16: 400,
  17: 400,
  18: 1000,
  19: 1000}}
df = pd.DataFrame.from_dict(d)

标签： pythonpandassampling

解决方案

推荐阅读