Pandas生成DataFrame随循环变慢问题及性能优化求助

Looks like you're hitting a classic pandas performance pitfall here—appending DataFrames in a loop with pd.concat gets exponentially slower as your dataset grows, and that's exactly what's happening in your code. Let's break down why this is happening and fix it.

Why Your Current Code Is Slow #

• Every time you run pd.concat([new_df, df]), pandas has to create an entirely new DataFrame by copying all existing data plus the new row. As new_df grows, this copy operation takes longer and longer (resulting in O(n²) time complexity).
• Adding reset_index(inplace=True, drop=True) on every iteration adds unnecessary overhead, as it reindexes the entire DataFrame each time.

Optimized Solution 1: Collect Rows in a List First (Most Flexible) #

Instead of creating tiny DataFrames and concatenating them, collect all your row data in a Python list first, then create the final DataFrame once at the end. This avoids repeated expensive copy operations.

import pandas as pd
import time

start1 = time.perf_counter()
step_size = 5
windowsize = 100
start = 0

# Initialize a list to store all row data
data_rows = []
i = 0

# Simplify timing tracking with a dictionary
timing_markers = {
    10000: None, 20000: None, 30000: None, 40000: None, 50000: None,
    60000: None, 70000: None, 80000: None, 90000: None, 100000: None,
    110000: None, 120000: None, 130000: None, 140000: None, 150000: None,
    160000: None, 170000: None, 180000: None, 190000: None
}

while i <= 200000:
    # Record timing at specified points
    if i in timing_markers:
        timing_markers[i] = time.perf_counter()
    
    # Append row data as a list to our collection
    row = [
        'chr1',
        start + i,
        start + i + windowsize - 1,
        start + i + round(windowsize/2) - 1
    ]
    data_rows.append(row)
    i += step_size

# Create the full DataFrame in one go
new_df = pd.DataFrame(
    data_rows,
    columns=['RNAME', 'start', 'end', 'central']
)

# Save to file
new_df.to_csv(
    f'chr1_{start}_window_{windowsize}_step_{step_size}.bed',
    header=False,
    index=False,
    sep='\t'
)

# Print results
end1 = time.perf_counter()
print(f'process finished in {round(end1 - start1, 2)} second(s)')

prev_time = timing_markers[10000]
for count in sorted(timing_markers.keys())[1:]:
    current_time = timing_markers[count]
    print(f'the {count//10000}th 10000 lines finished in {round(current_time - prev_time, 2)} secs')
    prev_time = current_time

Why This Works #

• Appending to a Python list is amortized O(1) (fast and constant-time for most operations).
• Creating the DataFrame once avoids all the repeated copying from pd.concat, dropping your runtime from hundreds of seconds to just a few.

Optimized Solution 2: Use Numpy Vectorization (Fastest for Regular Data) #

If your row values follow a mathematical pattern (like your sliding window), use numpy to generate columns as arrays, then convert to a DataFrame. This eliminates Python loop overhead entirely.

import pandas as pd
import numpy as np
import time

start1 = time.perf_counter()
step_size = 5
windowsize = 100
start = 0

# Calculate total number of rows
total_rows = (200000 // step_size) + 1

# Generate all 'i' values as a numpy array
i_array = np.arange(0, 200001, step_size)

# Compute columns using vectorized operations
rname_col = np.full(total_rows, 'chr1', dtype=str)
start_col = start + i_array
end_col = start_col + windowsize - 1
central_col = start_col + round(windowsize/2) - 1

# Create DataFrame from numpy arrays
new_df = pd.DataFrame({
    'RNAME': rname_col,
    'start': start_col,
    'end': end_col,
    'central': central_col
})

# Save to file
new_df.to_csv(
    f'chr1_{start}_window_{windowsize}_step_{step_size}.bed',
    header=False,
    index=False,
    sep='\t'
)

end1 = time.perf_counter()
print(f'process finished in {round(end1 - start1, 2)} second(s)')

Why This Works #

• Numpy operations are implemented in C, so they're orders of magnitude faster than Python loops for numerical computations.
• This approach is perfect for datasets with predictable, formula-based values.

Final Notes #

Both methods will drastically improve performance—you’ll be able to generate 10^7 rows in seconds instead of minutes. The numpy method is fastest for your use case, while the list approach is more flexible if your row generation logic becomes more complex later.

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