如何遍历DataFrame多列值统计行数？多表特定列规则计数

To solve your problem of counting rows where a base prefix column is non-null and all other same-prefix columns are null (across multiple DataFrames), we can break this down into structured steps using pandas (assuming you're working with Python DataFrames). Here's a practical, efficient approach:

Step 1: Define Helper Functions & Sample Data #

First, we'll create helper functions to group columns by their prefixes and calculate the required counts. We'll also use sample DataFrames to demonstrate your scenario.

import pandas as pd
import re

def get_prefix_groups(df):
    """Group columns by their shared prefix (e.g., 'test' for 'test', 'test1', 'test2')"""
    prefix_groups = {}
    for col in df.columns:
        # Extract prefix by capturing all characters before trailing digits
        prefix_match = re.match(r'^(.*?)\d*$', col)
        if prefix_match:
            prefix = prefix_match.group(1)
            if prefix not in prefix_groups:
                prefix_groups[prefix] = []
            prefix_groups[prefix].append(col)
    return prefix_groups

def count_valid_rows_by_prefix(df):
    """Count rows where base prefix column is non-null, others in the same prefix group are null"""
    prefix_groups = get_prefix_groups(df)
    results = {}
    
    for prefix, cols in prefix_groups.items():
        base_col = prefix
        # Skip if base column doesn't exist (edge case handling)
        if base_col not in cols:
            print(f"Warning: Base column '{base_col}' not found in group {cols}")
            continue
        
        # Separate base column from other same-prefix columns
        other_cols = [col for col in cols if col != base_col]
        
        # Build the condition: base non-null AND all other prefix columns are null
        condition = df[base_col].notna()
        if other_cols:
            condition &= df[other_cols].isna().all(axis=1)
        
        # Count valid rows (sum converts True/False to 1/0)
        results[prefix] = condition.sum()
    
    return results

# Sample DataFrames matching your description
df_x = pd.DataFrame({
    'test': [1, None, 3, None],
    'test1': [None, 2, None, None],
    'test2': [None, None, None, 4],
    'test3': [None, None, 5, None]
})

df_y = pd.DataFrame({
    'rem': ['a', None, 'c'],
    'rem1': [None, 'b', None],
    'rem2': [None, None, 'd']
})

Step 2: Process Each Table #

Apply the function to each of your DataFrames to get the required counts:

# Calculate counts for each table
x_results = count_valid_rows_by_prefix(df_x)
y_results = count_valid_rows_by_prefix(df_y)

print("Table X Counts:", x_results)  # Output: {'test': 1}
print("Table Y Counts:", y_results)  # Output: {'rem': 1}

How It Works #

Let's break down the key parts:

• Prefix Grouping: The get_prefix_groups function uses regex to group columns by their shared prefix (e.g., all columns starting with 'test' are grouped together).
• Condition Building: For each prefix group, we create a boolean mask where:
  • The base column (exact prefix name, like 'test') is not null (df[base_col].notna()).
  • All other columns in the group are null (df[other_cols].isna().all(axis=1)—all(axis=1) checks that every value in the row is null).
• Counting: Summing the boolean mask gives the number of rows that meet both conditions (since True is treated as 1 and False as 0).

Iterating Through DataFrame Columns for Counts #

While the above approach uses vectorized operations (far more efficient for large datasets), if you need to iterate through columns directly:

• Loop through columns with for col in df.columns.
• For row-wise iteration (not recommended for large data), use df.iterrows()—but vectorized methods like notna() and isna() are always faster.

For example, a row-wise (slow) alternative for a single prefix group:

count = 0
for idx, row in df_x.iterrows():
    if pd.notna(row['test']) and all(pd.isna(row[col]) for col in ['test1', 'test2', 'test3']):
        count +=1
print(count)  # Output:1

Always prefer vectorized operations over row-wise loops for performance, especially with large datasets.

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