[Python] Pandas performance while iterating a state vector

I want to make a pandas dataframe that describes the state of a system at different times

• I have the initial state which describes the first row
• Each row correspond to a time
• I have reveserved the first two columns for "household" / statistics
• The following columns are state parameters
• At each iteration/row a number of parameters change - this could be just one or many

I have created a somewhat simplified version that simulates my change data : df_change

Question 1

Can you think of a more efficient way of generating the matrix than what i do in this code? i have a state that i update in a loop and insert

Question 2

This is what i discovered while trying to write the sample code for this discussion. I see 20 fold performanne boost in loop iteration performance if i do the assignments to the "household" columns after the loop. Why is this? I am using python = 3.12.4 and pandas 2.2.2.

df["product"] ="some_product"


#%%

import numpy as np
import pandas as pd
from tqdm import tqdm
num_cols =600
n_changes = 40000


# simulate changes

extra_colums = ["n_changes","product"]
columns = [chr(i+65) for i in range(num_cols)]

state = { icol : np.random.random() for icol in columns}

change_index = np.random.randint(0,4,n_changes).cumsum()
change_col = [columns[np.random.randint(0,num_cols)] for i in range(n_changes)]
change_val= np.random.normal(size=n_changes)

# create change matrix
df_change=pd.DataFrame(index= change_index )
df_change['col'] = change_col
df_change['val'] = change_val
index = np.unique(change_index)


# %%
# Slow approach gives 5 iterations/s
df = pd.DataFrame(index= index, columns=extra_colums + columns)
df["product"] ="some_product"
for i in tqdm(index):
state.update(zip(df_change.loc[,"col"] , df_change.loc[,"val"]))
df.loc[i,columns] = pd.Series(state)

# %%
# Fast approach gives 1000 iterations/sec
df2 = pd.DataFrame(index= index, columns=extra_colums + columns)
for i in tqdm(index):
state.update(zip(df_change.loc[,"col"] , df_change.loc[,"val"]))
df2.loc[i,columns] = pd.Series(state)
df2["product"] ="some_product"``

Continue reading...