Python：从DataFrame更新字典速度差异过大问题排查

Let's break down exactly why your second code is taking hours instead of seconds—even with the same 150k row count. The differences you mentioned (storing more values, some as strings) are key, but there are also pandas-specific pitfalls with iterrows() that make this slowdown way worse when you're doing more work per row.

Core Reasons for the Speed Gap #

1. iterrows() has hidden overhead that adds up fast
   When you use iterrows(), pandas returns each row as a Series object. For a simple two-numeric-value update, this overhead is negligible. But when you're pulling multiple values (especially strings) from each Series, that per-row overhead multiplies exponentially across 150k rows. Numeric types are stored in compact, efficient arrays in pandas, while strings (often stored as object dtype) require more memory and processing to access and copy.

2. String operations are inherently more expensive than numeric ones
   Python strings are immutable, so every time you copy a string from the row to your dictionary, there's extra memory allocation and copying involved compared to small numeric types. If your second code handles longer strings or does implicit type conversions (even ones you don't notice), that adds even more processing time per row.

3. Multiple dictionary updates per row compound the slowdown
   In your first code, you're updating the dictionary with one key-value pair per row. If your second code is adding multiple entries (e.g., update({k1:v1, k2:v2, k3:v3})), each update() call has to hash multiple keys, check for existing entries, and insert multiple values. Multiply that by 150k rows, and those small per-iteration costs turn into hours of runtime.

How to Fix This (And Get Back to Fast Execution) #

The solution is to avoid iterrows() for heavy per-row work. Here are three optimized approaches:

Option 1: Convert the DataFrame to plain Python dicts first

to_dict('records') converts your DataFrame into a list of plain Python dictionaries, which are way faster to iterate over than pandas Series objects:

# Convert entire DataFrame to list of dicts
row_dicts = data_engineconfig.to_dict('records')

engineconfig_data = {}
for row in row_dicts:
    # Use vehicleid as the key, store a sub-dict of all needed values
    vehicle_key = row['vehicleid-h']
    engineconfig_data[vehicle_key] = {
        'engineconfigid': row['engineconfigid-h'],
        'string_col1': row['your-string-column-1'],
        'string_col2': row['your-string-column-2']
        # Add other columns here
    }

Option 2: Use vectorized pandas operations (fastest method)

If you're using one column as the key and the rest as values, you can do this in a single optimized step—no loops needed:

# Set vehicleid as the index, then convert to a dict of dicts
engineconfig_data = data_engineconfig.set_index('vehicleid-h').to_dict('index')

This uses pandas' C-backed operations under the hood, which are orders of magnitude faster than Python-level loops.

Option 3: Switch to itertuples() if you must loop

itertuples() returns named tuples instead of Series objects, which have far less overhead:

# First, rename columns to remove hyphens (since named tuples don't like them)
data_engineconfig.columns = [col.replace('-', '_') for col in data_engineconfig.columns]

engineconfig_data = {}
for row in data_engineconfig.itertuples():
    engineconfig_data[row.vehicleid_h] = {
        'engineconfigid': row.engineconfigid_h,
        'string_col': row.your_string_column
    }

Final Takeaway #

iterrows() is fine for quick, simple row tasks, but it doesn't scale when you're doing heavy lifting per row—especially with strings. By switching to vectorized operations or plain Python dict iteration, you'll cut your runtime from hours back to seconds.

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