Python股票数据组合筛选效率优化问询：60万行数据提速方案

Let's break down why your current code is running slow and fix it step by step—600k rows shouldn't be a problem with pandas if we optimize operations properly.

Key Bottlenecks in Your Current Code #

• Unnecessary DataFrame Copies: Every iteration starts with dfTemp = dfData, creating a full copy of your 600k-row dataset. This wastes memory and adds redundant overhead.
• Redundant str.contains Calculations: You’re re-running the same string matching logic for each combination. str.contains is relatively slow, so we should precompute these checks once.
• Inefficient File I/O: Opening/closing the result file on every iteration adds significant lag. It’s far faster to collect all results first, then write them in one go.
• Logical Filtering Error: Your code uses dfData[col_name].str.contains(...) to filter dfTemp—this applies the original dataset’s condition to the filtered subset, which is both incorrect and inefficient. It should use dfTemp[col_name].str.contains(...) instead.
• Clunky AAA Handling: The current check for "AAA" adds unnecessary branching; we can skip filtering for this condition cleanly.

Optimized Solution #

Here’s a revised version that addresses all these issues, with precomputed masks, vectorized operations, and batch I/O:

import pandas as pd

# Input paths
data_file = "ReferenceFile.txt"
combination_file = "Combination.csv"
result_file = "Result.csv"

# Load and preprocess data
df_data = pd.read_csv(data_file, sep=",")
df_data.fillna("", inplace=True)
df_data["AAA"] = "AAA"  # Keep this for combination grouping

# Step 1: Precompute masks for all unique conditions (big performance win!)
condition_masks = {}
all_conditions = set()

# First, collect all unique conditions from the combination file
with open(combination_file, "r") as f:
    for line in f:
        conditions = line.strip().split(",")
        all_conditions.update(cond.strip() for cond in conditions if cond.strip() != "AAA")

# Precompute boolean mask for each condition (run once, reuse everywhere)
for condition in all_conditions:
    condition_masks[condition] = df_data[condition].str.contains(condition)

# Precompute Profit mask once (reused for every combination)
profit_mask = df_data.apply(lambda row: any("Profit" in val for val in row), axis=1)
# If Profit is only in a specific column, use this faster version instead:
# profit_mask = df_data["YourProfitColumn"].str.contains("Profit")

# Step 2: Read all combinations into memory
combinations = []
with open(combination_file, "r") as f:
    for line in f:
        combo = [cond.strip() for cond in line.strip().split(",")]
        combinations.append(combo)

# Step 3: Process all combinations and collect results
results = []
for combo in combinations:
    # Start with a mask that includes all rows
    current_mask = pd.Series([True] * len(df_data), index=df_data.index)
    
    # Apply each condition in the combination (skip AAA)
    for cond in combo:
        if cond == "AAA":
            continue
        current_mask &= condition_masks[cond]
    
    # Calculate matching rows and profit rows
    total_matches = current_mask.sum()
    if total_matches == 0:
        profit_matches = 0
        profit_ratio = 0.0
    else:
        profit_matches = (current_mask & profit_mask).sum()
        profit_ratio = profit_matches / total_matches
    
    # Format result line
    combo_str = "|".join(combo)
    results.append(f"{total_matches}|{profit_matches}|{combo_str}")

# Step 4: Write all results to file in one batch
with open(result_file, "w") as f:
    f.write("\n".join(results) + "\n")

print("Complete")

What Changed & Why #

1. Precomputed Masks: We calculate boolean masks for every unique condition once, then reuse them across all combinations. This eliminates repeated slow str.contains calls.
2. Vectorized Operations: Instead of slicing the DataFrame repeatedly, we combine boolean masks with bitwise operations (&), which are far faster than iterative filtering.
3. Batch I/O: We collect all results in a list and write to the file once, avoiding the overhead of frequent file openings/closing.
4. Fixed Filtering Logic: Mask combination correctly applies all conditions upfront, which is equivalent to iterative filtering but much more efficient.
5. Cleaner AAA Handling: We skip applying a mask for "AAA", aligning with your original intent of using it for grouping.

Extra Speed Tips #

• If your condition strings are exact matches (not substrings), replace str.contains with ==—it’s significantly faster.
• For extremely large datasets, consider dask.dataframe to parallelize operations across data chunks.
• Ensure ReferenceFile.txt has no extra commas or formatting issues to avoid slowdowns during read_csv.

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