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

Question

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.

Answer 1

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
```