如何在Pandas中筛选指定行并根据条件生成Position列

步骤说明 #

1. 筛选出Pillar为Block的行，同时识别被非Block行隔开的同Pillar ID分组
2. 对每个包含至少2行的有效分组，按Distance排序后标记最小值为Start、最大值为Stop

完整代码 #

import pandas as pd

# 原始数据
df = pd.DataFrame({
    "Pillar": ["block", "segment", "block", "block", "segment",
              "segment", "block",  "block", "block"],
    "Pillar ID": [1, 1, 1, 2, 2, 3, 2, 1, 1],
    "Distance" : [1.5, 3, 4, 5, 7, 7.8, 9, 3, 6]
})

# 标记是否为Block行（统一大小写匹配）
df['is_block'] = df['Pillar'].str.lower() == 'block'

# 创建分组标识：区分被非Block行隔开的同ID Block组
# 当前行是Block，且前一行不是Block 或 前一行是Block但ID不同时，视为新组开始
mask = df['is_block'] & (df['Pillar ID'].ne(df['Pillar ID'].shift()) | ~df['is_block'].shift())
df['group'] = mask.cumsum()

# 筛选Block行，仅保留组内行数≥2的分组
block_df = df[df['is_block']].copy()
valid_groups = block_df.groupby('group').filter(lambda x: len(x) >= 2)

# 对每个分组，按Distance标记Start/Stop
valid_groups['Position'] = valid_groups.groupby('group')['Distance'].transform(
    lambda x: ['Start' if val == x.min() else 'Stop' for val in x]
)

# 整理输出格式，统一Pillar首字母大写
result = valid_groups[['Pillar', 'Pillar ID', 'Distance', 'Position']]
result['Pillar'] = result['Pillar'].str.title()

print(result)

输出结果 #

Pillar  Pillar ID  Distance Position
0   Block          1       1.5    Start
2   Block          1       4.0     Stop
3   Block          2       5.0    Start
6   Block          2       9.0     Stop
7   Block          1       3.0    Start
8   Block          1       6.0     Stop

代码解释 #

• 分组标识创建：通过shift()对比前后行状态，把被非Block行打断的同ID Block行分成独立分组，确保ID=1的Block行被Segment分成两组的需求被满足
• 有效分组筛选：过滤掉仅含单个Block行的分组，避免无法生成Start/Stop标记
• Position标记：用transform()对每个分组的Distance值判断，自动为最小值标记Start、其余值标记Stop

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