Python：高效处理百万级DataFrame的ID字段值迭代更新

Hey there! Let's tackle this performance headache with your 15-million-row DataFrame—for loops are a total non-starter here, but pandas has vectorized operations that’ll handle this way faster than you might expect.

First, Let's Recap Your Requirements #

• Sort the entire DataFrame globally by Date in ascending order
• For each unique ID, set the current record's Old_Value to the previous record's New_Value (keep the original Old_Value for the first record of each ID)
• Handle 100k+ unique IDs with irregular occurrence frequencies efficiently

Step-by-Step Solution #

1. Ensure Proper Sorting

First, make sure your DataFrame is sorted by Date—this is critical because we need the correct chronological order for each ID's records. If your Date column is stored as a string, convert it to datetime first to avoid sorting errors:

import pandas as pd

# Convert Date to datetime (if not already)
df['Date'] = pd.to_datetime(df['Date'])

# Sort globally by Date and reset index
df = df.sort_values(by='Date').reset_index(drop=True)

2. Vectorized Update with Groupby + Shift

Instead of looping through each ID, use pandas' built-in groupby and shift() functions—these are implemented in C under the hood, so they’re orders of magnitude faster than Python loops.

# Get the previous record's New_Value for each ID (shift down by 1)
prev_new_values = df.groupby('ID')['New_Value'].shift(1)

# Update Old_Value: use previous New_Value if available, else keep original
df['Old_Value'] = prev_new_values.combine_first(df['Old_Value'])

Why This Works #

• groupby('ID')['New_Value'].shift(1) creates a series where each value is the New_Value from the prior row in the same ID group. For the first row of an ID, this will be NaN.
• combine_first() fills those NaN values with the original Old_Value (preserving the first record's initial value), exactly matching your desired output.

Bonus Optimizations for Large Datasets #

If you’re tight on memory (which is common with 15M rows), try these tweaks:

• Use smaller data types for numeric columns (if precision allows):

  df[['Old_Value', 'New_Value']] = df[['Old_Value', 'New_Value']].astype('float32')
  

• Process the DataFrame in chunks (if even the vectorized approach hits memory limits):

  chunk_size = 1_000_000
  updated_chunks = []
  
  for chunk in pd.read_csv('your_large_file.csv', chunksize=chunk_size):
      chunk['Date'] = pd.to_datetime(chunk['Date'])
      chunk = chunk.sort_values(by='Date')
      prev_new = chunk.groupby('ID')['New_Value'].shift(1)
      chunk['Old_Value'] = prev_new.combine_first(chunk['Old_Value'])
      updated_chunks.append(chunk)
  
  final_df = pd.concat(updated_chunks, ignore_index=True)
  

This approach should handle your 15M rows in minutes (not hours) compared to a for loop.

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