如何在Pandas DataFrame中按ID检测连续3行及以上相同值并实现标记与统计

Got it, let's work through this problem together. The goal is to identify rows where each id has 3 or more consecutive identical values in the value column, flag those rows with a pattern column (1 for qualifying rows, 0 otherwise), and optionally count those qualifying rows per id.

Step 1: Set Up the Example Data #

First, let's recreate your sample DataFrame to test the solution:

import pandas as pd

data = {
    'id': [1001, 1001, 1001, 1002, 1002, 1015, 1015, 1015, 2001, 2001, 2001, 2001, 2001],
    'date': ['1-04-2021', '3-04-2021', '10-04-2021', '11-04-2021', '12-04-2021', 
             '18-04-2021', '20-04-2021', '21-04-2021', '8-04-2021', '11-04-2021', 
             '12-04-2021', '27-04-2021', '29-04-2021'],
    'value': [61, 61, 61, 13, 12, 42, 42, 43, 27, 27, 27, 27, 27]
}

df = pd.DataFrame(data)

Step 2: Implement the Logic #

Here's the code to add the pattern column and calculate qualifying rows:

# 1. Create a unique identifier for each block of consecutive same values within an id
df['value_group'] = df.groupby('id')['value'].apply(lambda x: x.diff().ne(0).cumsum())

# 2. Calculate how many rows are in each (id, value_group) block
group_sizes = df.groupby(['id', 'value_group']).size().reset_index(name='group_size')

# 3. Merge the block sizes back to the original DataFrame and set the pattern flag
df = df.merge(group_sizes, on=['id', 'value_group'], how='left')
df['pattern'] = df['group_size'].map(lambda x: 1 if x >= 3 else 0)

# 4. Clean up helper columns (optional)
df = df.drop(['value_group', 'group_size'], axis=1)

# Optional: Count total qualifying rows per id
qualifying_row_counts = df[df['pattern'] == 1].groupby('id').size().reset_index(name='qualifying_rows')
print("Qualifying Rows per ID:\n", qualifying_row_counts)

Step 3: View the Final Result #

After running the code, your DataFrame will look exactly like the desired output:

How It Works #

• x.diff().ne(0).cumsum(): This creates a unique group number for each consecutive block of identical values within an id. The diff() checks if the current value is different from the previous, ne(0) converts differences to True/False, and cumsum() increments the group number each time a new value starts.
• Group Size Calculation: We count how many rows are in each (id, value group) block to determine if it meets the ≥3 threshold.
• Pattern Flag: We map block sizes to 1 or 0 based on whether they qualify.

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