如何加速大型Pandas DataFrame中类COUNTIFS逻辑的循环计算？

Your current approach is slow because you're performing a full table scan 10,000 times (once per date+category pair), which is incredibly inefficient for an 800k-row DataFrame. Let's fix this by leveraging vectorized operations and preprocessing to eliminate redundant work entirely.

Problem Breakdown #

First, let's simplify your condition to make optimization easier. Your original logic:

(category == a AND dateA < specifiedDate AND dateB > specifiedDate) 
OR 
(category == a AND theme == "Blue" AND dateA < specifiedDate)

Can be rewritten as:

category == a AND dateA < specifiedDate AND (dateB > specifiedDate OR theme == "Blue")

This makes it clearer we can group calculations by category and handle all dates in one pass per category.

Optimized Solution #

The key idea is to process all your target dates at once for each category using vectorized operations (no more looping through every date+category pair). Here's the step-by-step implementation:

Step 1: Preprocess Your Data #

First, clean and filter your data to only keep what we need:

import pandas as pd
import numpy as np

# Ensure date columns are datetime types (critical for comparisons)
df['dateA'] = pd.to_datetime(df['dateA'])
df['dateB'] = pd.to_datetime(df['dateB'])

# Define your target categories and dates (replace with your actual lists)
categorylist = ['cat1', 'cat2', 'cat3', 'cat4', 'cat5']
datelist = pd.date_range(start='2013-01-01', end='2017-12-31').tolist()

# Filter to only relevant rows/columns to reduce computation size
filtered_df = df[df['category'].isin(categorylist)][['dateA', 'dateB', 'category', 'theme']].copy()
filtered_df['is_blue'] = filtered_df['theme'].str.lower() == 'blue'  # Normalize case for consistency

Step 2: Batch-Compute Counts for All Dates #

We'll use NumPy broadcasting to compare every row's dates against all your target dates in one go, then sum the valid rows per date:

# Sort and convert dates to a DatetimeIndex for efficient comparisons
sorted_dates = sorted(datelist)
date_index = pd.DatetimeIndex(sorted_dates)

# Initialize result DataFrame (dates as index, categories as columns)
result = pd.DataFrame(index=date_index, columns=categorylist, dtype=int)

for cat in categorylist:
    # Get all rows for the current category
    group = filtered_df[filtered_df['category'] == cat]
    if group.empty:
        result[cat] = 0
        continue
    
    # Extract arrays for vectorized operations
    dateA = group['dateA'].values
    dateB = group['dateB'].values
    is_blue = group['is_blue'].values
    
    # Broadcast comparisons: compare every row's date to all target dates
    dateA_lt_date = dateA[:, np.newaxis] < date_index.values  # Shape: (n_rows, n_dates)
    dateB_gt_date = dateB[:, np.newaxis] > date_index.values
    
    # Apply our simplified condition: dateA < dt AND (dateB > dt OR is_blue)
    valid_rows = dateA_lt_date & (dateB_gt_date | is_blue[:, np.newaxis])
    
    # Sum valid rows for each date
    result[cat] = valid_rows.sum(axis=0)

# Convert to your desired output format
final_result = result.reset_index().rename(columns={'index': 'Date'})

Why This Is Faster #

• No more repeated full table scans: Instead of 10,000 passes over your 800k rows, we only make 5 passes (one per category).
• Vectorized operations: NumPy broadcasting handles date comparisons in bulk, which is orders of magnitude faster than row-by-row checks.
• Reduced data size: We filter irrelevant rows/columns upfront to minimize computation.

Example Output #

Using your sample data, the final_result would look like this (for dates 2015-04-10 to 2015-04-12):

This matches the logic of your original function but runs in seconds instead of minutes.

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