如何在Pandas DataFrame中检测连续行≤指定值并计算结果（超700万列）

Hey there! Let's break down how to solve this problem—especially since we're dealing with a massive DataFrame (7 million+ columns), efficiency has to be front and center. First, let's align on the requirements using the sample data provided.

Clarifying the Logic (From Your Example) #

Looking at your sample input and expected output, here's the exact rule set we need to implement for each column:

1. Check if there’s a sequence of 4 or more consecutive rows where values ≤ 10.
2. If such a sequence exists:
   • If there’s any value >10 after this sequence, output the row index of the first such value.
   • If the sequence extends to the end of the column (no values >10 after), output the first value in the sequence.
3. If no qualifying sequence exists, output 0.

Let’s verify this against your example:

• Column A: Has a 4-row sequence ≤10 (rows 3-6) followed by 11 (row7 >10) → Outputs 7 (matches your expected result).
• Column D: Has a 5-row sequence ≤10 ending at the last row → Outputs the first value in the sequence (9, matches your expected result).
• Columns C/E: No qualifying sequences → Output 0 (matches).
• Column B: The 4-row sequence ends at the last row—your expected output is 0, which suggests an extra unstated rule (like sequences ending at the last row should return 0). We’ll cover how to adjust for that later.

Solution Code (Sample Data First) #

Let’s start with code that works for your sample dataset, then optimize it for 7M+ columns.

Basic Implementation (For Testing) #

import pandas as pd
import numpy as np

# Your sample dataset
datasetA = pd.DataFrame(data={
    'A':[1,100,80,10,8,8,9,11], 
    'B':[1,100,90,12,7,8,9,10], 
    'C':[1,100,80,13,12,11,12,13], 
    'D':[1,100,90,9,8,7,10,10], 
    'E':[1,100,90,19,18,17,9,10]
})

def process_single_column(col):
    # Mark values ≤10
    mask = col <= 10
    # Group consecutive True/False values
    groups = mask.ne(mask.shift()).cumsum()
    # Get length of each group
    group_lengths = groups.value_counts().sort_index()

    # Check if any group of ≤10 values has length ≥4
    qualifying_groups = [g for g in groups[mask].unique() if group_lengths[g] >=4]
    if not qualifying_groups:
        return 0

    # Focus on the first qualifying sequence
    q_group = qualifying_groups[0]
    group_indices = groups[groups == q_group].index
    start_idx = group_indices.min()
    end_idx = group_indices.max()

    # Check for values >10 after the sequence
    after_group = col.loc[end_idx+1:]
    if len(after_group) > 0 and (after_group >10).any():
        # Return first index of value >10 after the sequence
        return after_group[after_group>10].index[0]
    else:
        # To match your expected B=0, uncomment the line below and comment the one after
        # return 0
        return col.loc[start_idx]

# Apply to all columns and format the result
result = datasetA.apply(process_single_column).to_frame().T
print(result)

Optimized for 7M+ Columns #

Using apply on 7 million columns will be slow. We’ll switch to numpy vectorization for speed:

import pandas as pd
import numpy as np

def process_large_dataframe(df, threshold=10, min_consecutive=4):
    arr = df.values
    n_rows, n_cols = arr.shape

    # Create mask where values ≤ threshold are 1, others 0
    mask = (arr <= threshold).astype(int)
    # Use convolution to detect sequences of min_consecutive 1s
    kernel = np.ones(min_consecutive, dtype=int)
    conv = np.apply_along_axis(lambda x: np.convolve(x, kernel, mode='valid'), axis=0, arr=mask)
    # Flag columns with at least one qualifying sequence
    has_qualifying = (conv == min_consecutive).any(axis=0)

    # Initialize result with 0s
    result = np.zeros(n_cols, dtype=int)

    # Process only columns with qualifying sequences
    for col_idx in np.where(has_qualifying)[0]:
        col = arr[:, col_idx]
        mask_col = mask[:, col_idx]

        # Find start/end indices of all consecutive 1s runs
        diff = np.diff(np.concatenate([[0], mask_col, [0]]))
        run_starts = np.where(diff == 1)[0]
        run_ends = np.where(diff == -1)[0]

        # Check each run until we find the first qualifying one
        for start, end in zip(run_starts, run_ends):
            run_length = end - start
            if run_length >= min_consecutive:
                # Adjust here to match your expected B=0:
                # If sequence ends at last row, return 0
                if end == n_rows:
                    result[col_idx] = 0
                else:
                    after_run = col[end:]
                    if (after_run > threshold).any():
                        first_over = end + np.argmax(after_run > threshold)
                        result[col_idx] = first_over
                    else:
                        result[col_idx] = col[start]
                break

    # Convert back to DataFrame
    return pd.DataFrame([result], columns=df.columns)

# Test with sample data
large_result = process_large_dataframe(datasetA)
print(large_result)

This optimized version uses numpy’s vectorized operations, which drastically speeds up processing for millions of columns.

内容的提问来源于stack exchange，提问作者fareed khan normal dist