如何用Pandas创建新列合并相邻破碎的ON信号分组

问题说明 #

我有一个包含ON（1）和OFF（0）信号的DataFrame，数据存在噪声，导致原本连续的ON信号中间夹杂零散的0，被分割成多个假片段，无法统计真实的ON信号总数。需要实现：

• 填充ON信号中间的小间隙，合并被分割的真实ON段
• 创建新列，标记截至当前行的累计ON信号次数

示例原始信号（对应df['ONOFF_Signal']）：

original_signal = [0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,1,1,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,1,1,1,1,0,1,1,0,0,0,0,0,0]

期望填充后的信号（合并噪声间隙后的ON段）：

[0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0]

期望的累计ON次数列：

[0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2]

解决方案 #

通过信号段标记、噪声间隙填充、累计计数三步实现：

代码实现

import pandas as pd
import numpy as np

# 构造示例DataFrame
df = pd.DataFrame({
    'ONOFF_Signal': [0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,1,1,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,1,1,1,1,0,1,1,0,0,0,0,0,0]
})

# 1. 标记连续信号段：生成每个连续段的唯一ID
df['segment_id'] = (df['ONOFF_Signal'].diff() != 0).cumsum()
# 统计每个段的信号类型和长度
segment_info = df.groupby('segment_id').agg(
    signal_type=('ONOFF_Signal', 'first'),
    length=('ONOFF_Signal', 'size')
).reset_index()

# 2. 填充噪声间隙：设定阈值（这里设为1，即长度≤1的OFF段视为噪声）
gap_threshold = 1
# 筛选需要替换为ON的短OFF段ID
replace_segments = segment_info[
    (segment_info['signal_type'] == 0) & 
    (segment_info['length'] <= gap_threshold)
]['segment_id'].tolist()
# 替换间隙为ON，生成清洗后的信号
df['cleaned_signal'] = df.apply(
    lambda row: 1 if row['segment_id'] in replace_segments else row['ONOFF_Signal'],
    axis=1
)

# 3. 生成累计ON次数列
# 标记新ON段的起始点
df['is_new_on'] = (df['cleaned_signal'] == 1) & (df['cleaned_signal'].shift(1) != 1)
# 累加起始点得到累计次数，并用前向填充补全OFF区域的计数
df['cumulative_on_count'] = df['is_new_on'].cumsum()
df['cumulative_on_count'] = df['cumulative_on_count'].mask(df['cleaned_signal'] == 0, method='ffill').fillna(0).astype(int)

# 输出结果
print("填充后的信号：")
print(df['cleaned_signal'].tolist())
print("\n累计ON次数：")
print(df['cumulative_on_count'].tolist())

代码解释

• 信号段标记：通过diff()判断信号变化，cumsum()生成连续段的唯一ID，再分组统计每个段的类型和长度，为后续处理做准备
• 噪声间隙填充：根据实际噪声情况调整gap_threshold，将短于阈值的OFF段替换为ON，合并被分割的真实ON段
• 累计计数：识别清洗后信号中每次新ON段的起始点，累加得到累计次数，再用ffill()将OFF区域的计数填充为最近的ON次数，符合需求

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