如何在Pandas DataFrame中最快地迭代执行自定义函数？

Hey there! The core problem with your current code is that iterrows() is extremely inefficient for large datasets—it runs a Python-level loop over every single row, which completely misses out on pandas' optimized, C-backed vectorized operations. For 40k rows it's slow, but for hundreds of thousands it'll be painful. Let's refactor this to use pandas' built-in tools, which will cut your runtime down to seconds instead of minutes.

Step 1: Filter Early to Reduce Data Volume #

First, we can immediately filter the DataFrame to only include the Stock Item values you care about. This reduces the total data we need to process in subsequent steps:

import pandas as pd

# Read and concatenate CSVs (add parse_dates here to avoid converting later!)
df = pd.concat(
    [pd.read_csv(filename, parse_dates=['TransDate']) for filename in args.csv],
    ignore_index=True
)

# Filter to only relevant Stock Items upfront
filtered_df = df[df['Stock Item'].isin(args.ID)].copy()

Note: I added parse_dates=['TransDate'] to pd.read_csv()—this converts the date column to datetime during reading, which is faster than doing it later row-by-row.

Step 2: Replace Loops with Groupby & Vectorized Operations #

Let's rebuild each of your dictionaries using pandas' groupby and aggregation functions, which are optimized for speed.

1. ID_Use_Totals (Collect 'Use' Quantities)

Instead of appending to a dictionary in a loop, we can group by Stock Item and aggregate the Qty values into a list directly:

ID_Use_Totals = (
    filtered_df[filtered_df['Action'] == 'Use']
    .groupby('Stock Item')['Qty']
    .apply(list)
    .to_dict()
)

2. ID_Order_Dates (Collect Order/Resupply Ref + Date)

First, filter for the relevant actions, then create a column of {Ref: TransDate} dictionaries, then group and aggregate into lists:

order_actions = ['Order/Resupply', 'Cons. Purchase']
order_df = filtered_df[filtered_df['Action'].isin(order_actions)]

# Create a column of the desired dictionary structure
order_df['ref_date_pair'] = order_df.apply(
    lambda row: {row['Ref']: row['TransDate']},
    axis=1
)

# Group and convert to dictionary
ID_Order_Dates = (
    order_df.groupby('Stock Item')['ref_date_pair']
    .apply(list)
    .to_dict()
)

3. ID_Received_Dates (Collect 'Received' Ref + Date)

This follows the exact same pattern as the order dates:

received_df = filtered_df[filtered_df['Action'] == 'Received']
received_df['ref_date_pair'] = received_df.apply(
    lambda row: {row['Ref']: row['TransDate']},
    axis=1
)

ID_Received_Dates = (
    received_df.groupby('Stock Item')['ref_date_pair']
    .apply(list)
    .to_dict()
)

Why This Is So Much Faster #

• Vectorized operations: Most of pandas' core functions (like groupby, boolean filtering) are implemented in C, which is orders of magnitude faster than Python loops.
• Reduced overhead: We're only looping over small groups (per Stock Item) instead of every single row in the entire DataFrame.
• Early filtering: By narrowing down the data first, we avoid processing rows that don't meet your criteria entirely.

Extra Optimization Tips #

• Specify dtypes when reading CSVs: If you know the data types of your columns (e.g., Stock Item is a string, Qty is an integer), pass dtype={'Stock Item': str, 'Qty': int, ...} to pd.read_csv(). This reduces memory usage and speeds up parsing.
• Avoid global variables: Instead of modifying global dictionaries, consider wrapping this logic in a function that returns all four dictionaries—this makes your code cleaner and avoids any overhead from global variable access.
• Test with chunks for extremely large files: If your CSVs are so big they don't fit in memory, use pd.read_csv(filename, chunksize=10_000) to process them in chunks, then aggregate results incrementally.

With these changes, you should see a massive speedup—even for 100k+ rows, this should run in just a few seconds instead of minutes.

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