1
$\begingroup$

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.

$\endgroup$

1 Answer 1

1
$\begingroup$

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
```
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.