如何在Pandas groupby操作中识别非连续数据范围

解决方法 #

核心思路是新增连续组标识区分被其他数据隔断的相同标签组合，Pandas中可以通过shift()+cumsum()的经典方案实现连续块识别，再给重复出现的标签组合加序号后缀即可。
修改后的完整代码如下：

import sys
if sys.version_info[0] < 3:
    from StringIO import StringIO
else:
    from io import StringIO

import pandas as pd

TESTDATA = StringIO("""tstamp,numrun,test,valA,valB,valC
63180.6208982,0,AA,661,615,767
63180.9724180,0,AA,661,615,767
63181.3240476,0,AA,661,615,767
63181.7246253,0,ZZ,661,615,767
63182.0752904,0,BB,661,615,767
63182.4280107,0,BB,661,615,767
63182.8423443,0,ZZ,661,615,767
63183.1940174,0,ZZ,661,615,767
63183.6097631,0,ZZ,661,615,767
63183.9612234,1,AA,661,615,767
63184.3441680,1,AA,661,615,767
63184.6971598,1,AA,661,615,767
63185.0964634,1,BB,661,615,767
63185.4480095,1,BB,660,615,767
63185.8035320,1,BB,661,615,767
63186.1987603,1,BB,660,615,767
63186.5500834,1,BB,661,615,767
63186.9661803,1,BB,661,615,767
63187.3186681,2,AA,660,615,767
63187.7182817,2,AA,660,615,767
63188.0696054,2,AA,660,615,767
63188.4689450,2,ZZ,660,615,767
63188.8204257,2,ZZ,660,615,767
63189.1719920,2,ZZ,660,615,767
63189.5240004,2,BB,660,615,767
63189.9121130,2,BB,660,615,767
63190.2593203,2,BB,660,615,767
""")

df_data = pd.read_csv(TESTDATA)
# 先按时间戳排序，保证顺序正确
df_data = df_data.sort_values('tstamp').reset_index(drop=True)
# 识别连续组：当前行和上一行的numrun、test不一致时标记为组切换
df_data['group_change'] = (df_data['numrun'] != df_data['numrun'].shift()) | (df_data['test'] != df_data['test'].shift())
# 累加切换标记得到唯一连续组ID
df_data['group_id'] = df_data['group_change'].cumsum()
# 聚合得到每个连续组的区间信息
interval_df = df_data.groupby('group_id').agg(
    numrun=('numrun', 'first'),
    test=('test', 'first'),
    tstart=('tstamp', 'first'),
    tend=('tstamp', 'last')
).reset_index(drop=True)
# 统计同一标签组合的出现次数，用于加后缀
interval_df['occur_cnt'] = interval_df.groupby(['numrun', 'test']).cumcount()
# 构造显示名称
interval_df['display_name'] = interval_df.apply(
    lambda x: f"({x['numrun']}, '{x['test']}')" if x['occur_cnt'] == 0 else f"({x['numrun']}, '{x['test']}+{x['occur_cnt']}')",
    axis=1
)
# 输出结果
with pd.option_context('float_format', '{:f}'.format):
    for idx, row in interval_df.iterrows():
        delta = row['tend'] - row['tstart']
        print(f"{idx+1:02d}: {row['display_name']}: {delta:.2f} sec ({row['tstart']} - {row['tend']})")

运行后输出和预期完全一致：

01: (0, 'AA'): 0.70 sec (63180.6208982 - 63181.3240476)
02: (0, 'ZZ'): 0.00 sec (63181.7246253 - 63181.7246253)
03: (0, 'BB'): 0.35 sec (63182.0752904 - 63182.4280107)
04: (0, 'ZZ+1'): 0.77 sec (63182.8423443 - 63183.6097631)
05: (1, 'AA'): 0.74 sec (63183.9612234 - 63184.6971598)
06: (1, 'BB'): 1.87 sec (63185.0964634 - 63186.9661803)
07: (2, 'AA'): 0.75 sec (63187.3186681 - 63188.0696054)
08: (2, 'ZZ'): 0.70 sec (63188.468945 - 63189.171992)
09: (2, 'BB'): 0.74 sec (63189.5240004 - 63190.2593203)

内容的提问来源于stack exchange，提问作者sdbbs