优化读取CSV生成DataFrame并构建X/Y列的Python脚本

Hey there, let's break down why your script is dragging its feet with 500k rows and fix it with pandas-native optimizations—no more slow loops and inefficient dataframe operations. Here's the breakdown:

Key Bottlenecks in Your Original Code #

• Manual CSV parsing: Using the csv module to split rows and convert dates is way slower than pandas' optimized, C-backed readers.
• iterrows() loop: Iterating over every single row in a large dataframe is one of the slowest ways to process data in pandas—we need bulk/grouped operations instead.
• Repeated df.append(): Every append() creates a brand new dataframe, which piles up unnecessary memory overhead as your maindfObj grows.
• Nested while loops: Building X/Y lists manually for each incident adds avoidable computational overhead that pandas can handle in bulk.

Optimized Solution #

Here's the revised code that leverages pandas' strengths to handle your dataset efficiently:

import pandas as pd
from google.colab import drive

# Mount drive (keep this part as-is)
drive.mount('/content/drive')

# Step 1: Read and preprocess CSV directly with pandas
# Let pandas handle parsing dates and columns automatically
df = pd.read_csv(
    '/content/drive/My Drive/public/Incident_Activity.csv',
    delimiter=';',
    header=0,
    # Adjust column names to match your actual CSV headers if needed
    names=['Incident ID', 'Incident_Date', 'IncidentActivity_Type'],
    parse_dates=['Incident_Date'],
    date_format="%d/%m/%Y %H:%M"
)

# Convert date to Unix timestamp (seconds since epoch)
df['UnixEquiv'] = df['Incident_Date'].view('int64') // 10**9

# Step 2: Sort by Incident ID and timestamp (cleaner version of your original logic)
df = df.sort_values(by=['Incident ID', 'UnixEquiv'])

# Step 3: Process each Incident ID group efficiently
def process_incident_group(group):
    # Generate X: cumulative list of activities from start to current row
    group['X'] = group['IncidentActivity_Type'].expanding().apply(lambda x: x.tolist(), raw=False)
    
    # Generate Y: next activity type, or "NULL" for the last row of the group
    group['Y'] = group['IncidentActivity_Type'].shift(-1).fillna("NULL")
    
    return group[['X', 'Y']]

# Process all groups at once and combine results (no more incremental appends!)
maindfObj = df.groupby('Incident ID', group_keys=False).apply(process_incident_group)

# Check final dataframe size
print(f"Final processed dataframe size: {maindfObj.shape}")

# Optional: Save the full result to CSV if needed
# maindfObj.to_csv('processed_incidents.csv', index=False)

Why This Is Way Faster #

1. Pandas read_csv: Built on optimized C extensions, it parses and processes CSV data exponentially faster than manual Python loops.
2. groupby + apply: Grouping by Incident ID lets us process each incident's data in bulk, ditching the slow row-by-row iterrows() loop.
3. Vectorized operations: expanding() and shift() run in C under the hood, not Python—this is where pandas shines for large datasets.
4. Single bulk combine: Instead of appending dataframes one by one, we process all groups first and merge them once, eliminating repeated memory allocations.

Extra Tips for Even Better Performance #

• If memory is tight, process groups in chunks and write to CSV incrementally instead of storing the entire maindfObj in memory.
• Double-check that your column names in read_csv match your actual CSV headers—mismatches can cause silent errors.
• Avoid global variables (like your original maindfObj)—they make code harder to debug and can lead to unexpected memory leaks.

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