Efficient Splitting of Large Pandas DataFrames: Optimized Strategies Based on Column Values

Problem Background and Performance Bottleneck Analysis

When processing large-scale experimental data, it's often necessary to split DataFrames containing millions of records into multiple sub-DataFrames based on participant names. The original code uses row-by-row iteration and append operations, which is highly inefficient in Pandas because each append creates a new DataFrame object, resulting in significant memory allocation and copying overhead.

Optimization Strategy 1: Dictionary Comprehension and Boolean Indexing

By extracting unique name lists and using dictionary comprehension combined with boolean indexing, DataFrames can be efficiently split:

import pandas as pd
import numpy as np

# Create sample data
data = pd.DataFrame({
    'Names': ['Joe', 'John', 'Jasper', 'Jez'] * 4,
    'Ob1': np.random.rand(16),
    'Ob2': np.random.rand(16)
})

# Get unique names list
UniqueNames = data.Names.unique()

# Create DataFrame dictionary
DataFrameDict = {elem: pd.DataFrame() for elem in UniqueNames}

for key in DataFrameDict.keys():
    DataFrameDict[key] = data[:][data.Names == key]

This approach avoids repeated creation during loops and directly selects data through boolean indexing, significantly improving performance.

Optimization Strategy 2: Sorting and Index Setting

Further optimization of data access efficiency can be achieved through sorting and index setting:

# Sort by name
df.sort_values(by='name', inplace=True)

# Set index while preserving original column
df.set_index(keys=['name'], drop=False, inplace=True)

# Get unique names list
names = df['name'].unique().tolist()

# Fast query through index
joe_data = df.loc[df.name == 'joe']

This method leverages Pandas' indexing mechanism, returning data views instead of copies during queries, reducing memory usage.

Strategy 3: Flexible Application of Groupby Objects

Using groupby provides a more concise implementation for splitting:

# Directly generate sub-DataFrame list
dataframes_list = [v for k, v in df.groupby('name')]

# Or convert to dictionary
frames_dict = dict(tuple(df.groupby('name')))

This approach offers concise code but may involve data sorting during groupby operations.

Performance Comparison and Best Practices

Performance characteristics of three methods in million-row scenarios:

• Original Method: Execution time exceeds 1 hour, high memory usage
• Dictionary Comprehension: Execution time several seconds, moderate memory usage
• Sorted Index Method: Execution time 1-2 seconds, subsequent queries extremely fast
• Groupby Method: Execution time several seconds, most concise code

Practical Application Recommendations

Select appropriate method based on specific requirements:

• For frequent queries of specific participant data, recommend sorted index method
• For one-time retrieval of all sub-DataFrames, dictionary comprehension is more suitable
• When code conciseness is priority, choose groupby method

Extended Application: Row-based Splitting

Besides splitting by column values, sometimes row-based splitting is also needed:

# Split into two DataFrames
df1 = df.iloc[:6]
df2 = df.iloc[6:]

# Split into multiple DataFrames
df1 = df.iloc[:3]
df2 = df.iloc[3:6]
df3 = df.iloc[6:]

This method is suitable for scenarios where data is arranged in chronological order or other continuous features.

Conclusion

When processing large DataFrames, avoid row-by-row operations and fully utilize Pandas' vectorized operations and indexing mechanisms. Through proper sorting, index setting, and grouping operations, data processing efficiency can be significantly improved to meet the demands of large-scale data analysis.