[Python] pandas dataframe slicing performance is affected by how subset was previously assigned

In a recent post Pandas performance while iterating a state vector, I noticed a performance when slicing pandas dataframes that i do not understand.

The code presented here does not do anything usefull, but highlight the issue:

• I create a dataframe with two areas of columns named extra_columns and columns
• The part of the code which takes time to execute is the loop, where slices in columns are assigned.

What baffles me that the way i assign values to extra_columns before the loop affects the loop performance

Python code

import timeit

setup_stmt ="""
import pandas as pd
num_cols = 500
n_iter = 100
extra_column = [ "product"]
columns = [chr(i+65) for i in range(num_cols)]
index= range(n_iter)
"""

stmt1 ="""
df = pd.DataFrame(index = index, columns=extra_column + columns)
df["product"] = "x"
for i in index:
df.loc[i,columns] = 0
"""

stmt2 ="""
df = pd.DataFrame(index = index, columns=extra_column + columns)
df.product = "x"
for i in index:
df.loc[i,columns] = 0
"""

stmt3 ="""
df = pd.DataFrame(index= index, columns=extra_column + columns)
df.loc[index,"product"] = "x"
for i in index:
df.loc[i,columns] = 0
"""

stmt4 ="""
df = pd.DataFrame(index = index, columns=extra_column + columns)
for i in index:
df.loc[i,columns] = 0
df["product"] = "x"
"""

print(f" stmt1 takes { timeit.timeit(setup= setup_stmt, stmt= stmt1, number=10):2.2f} seconds" )
print(f" stmt2 takes { timeit.timeit(setup= setup_stmt, stmt= stmt2, number=10):2.2f} seconds" )
print(f" stmt3 takes { timeit.timeit(setup= setup_stmt, stmt= stmt2, number=10):2.2f} seconds" )
print(f" stmt4 takes { timeit.timeit(setup= setup_stmt, stmt= stmt4, number=10):2.2f} seconds" )


Output

stmt1 takes 20.30 seconds
stmt2 takes 0.45 seconds
stmt3 takes 0.45 seconds
stmt4 takes 0.45 seconds

Continue reading...