# Best way to optimize dataframe row by row sum of squared errors calculation?

I am seeing a significant slowdown with the following small snippet of code which computes the sum of squared errors between two dataframes - e.g. it takes approximately 2.5 seconds to run when combopd has a length of 1140.

In the example below, target is a dataframe with one row and 8 columns, and combopd is a dataframe with i rows and 8 columns. The goal is to compute the sum of squared errors of each column value between target and combopd and then create a new column in combopd called "SSE" which stores the value of the error calculation:

for i in range(len(combopd)):
row = combopd.iloc[i]
sse = ((target["x1"] - row["x1"]) ** 2) + ((target["x2"] - row["x2"]) ** 2) + ((target["x3"] - row["x3"]) ** 2) + ((target["x4"] - row["x4"]) ** 2) + ((target["x5"] - row["x5"]) ** 2) + ((target["x6"] - row["x6"]) ** 2) + ((target["x7"] - row["x7"]) ** 2) + ((target["x8"] - row["x8"]) ** 2)
combopd.at[row.name, 'SSE'] = sse.values[0]


Any thoughts on a more faster/efficient/better way of accomplishing this would be much appreciated.

Iterating through Dataframes is (generally speaking) an anti-pattern. Always try to avoid it if you can!

You can easily vectorize this operation by subtracting the scalar value from target rather than treating target like another array:

# Vectorized squared errors
combopd["SSE"] = sum(
(target[f'x{n}'].values[0] - combopd[f'x{n}'])**2   # squared error
for n in range(1, 9)  # for each of the columns x1, x2, ... x8
)


Time comparison with 10K rows shows a pretty dramatic speedup :)

import pandas as pd
import numpy as np
import timeit

ROW_COUNT = 10000
combopd = pd.DataFrame(
np.random.random(size=(ROW_COUNT, 8)), columns=[f'x{n}' for n in range(1, 9)]
)
target = pd.DataFrame(
np.random.random(size=(1, 8)), columns=[f'x{n}' for n in range(1, 9)]
)

def vectorized():
return sum(
(target[f'x{n}'].values[0] - combopd[f'x{n}'])**2
for n in range(1, 9)
)

def original():
for i in range(len(combopd)):
row = combopd.iloc[i]
sse = (
((target["x1"] - row["x1"]) ** 2)
+ ((target["x2"] - row["x2"]) ** 2)
+ ((target["x3"] - row["x3"]) ** 2)
+ ((target["x4"] - row["x4"]) ** 2)
+ ((target["x5"] - row["x5"]) ** 2)
+ ((target["x6"] - row["x6"]) ** 2)
+ ((target["x7"] - row["x7"]) ** 2)
+ ((target["x8"] - row["x8"]) ** 2)
)
combopd.at[row.name, 'SSE'] = sse.values[0]

return combopd['SSE']

assert np.array_equal(vectorized(), original())
vectorized_time = timeit.timeit('vectorized()', globals=globals(), number=10)
original_time = timeit.timeit('original()', globals=globals(), number=10)
print(f'Vectorized time: {vectorized_time:0.4f}s'
f'\nOriginal time:   {original_time:0.4f}s')

# Vectorized time: 0.0230s
# Original time:   192.0022s
$$$$
`