Fastest way to multiply 2 Pandas columns with each other and get the sum of the values

Question

I am doing a lot of calculations multiplying one pandas column named "factor" with another called "value", and then calculate the sum of the multiplication.

The length of both columns is usually around 200 rows. Given that this is a calculation that I am doing thousands of times in my current project, I need it to be as fast as possible

A scaled down version of the code would look like this (only 4 rows)

  dict = {'factor': [0.25,0.25,0.25,0.25],
        'value': [22000,25000,27000,35000] }

df = pd.DataFrame(dict, columns= ['factor', 'value'])

print((df['factor'] * df['value']).sum())

With it printing out 27250.

Is there a way to get the same result faster?

Answer 1

You can use numpy - convert columns to 1d arrays by values and then numpy.sum:

np.random.seed(456)

d = {'factor': np.random.rand(200),
     'value': np.random.randint(1000, size=200)}

df = pd.DataFrame(d, columns= ['factor', 'value'])
#print (df)

In [139]: %timeit ((df['factor'] * df['value']).sum())
245 µs ± 2.64 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [140]: %timeit (np.sum((df['factor'].values * df['value'].values)))
20.6 µs ± 328 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)

If possible some missing values get NaN in output, so need numpy.nansum for prevent it:

np.random.seed(456)

d = {'factor': np.random.rand(200),
     'value': np.random.randint(1000, size=200)}

df = pd.DataFrame(d, columns= ['factor', 'value'])
df['value'] = df['value'].mask(df['value'] > 700)
#print (df)

In [144]: %timeit ((df['factor'] * df['value']).sum())
235 µs ± 8.65 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [145]: %timeit (np.nansum((df['factor'].values * df['value'].values)))
33.3 µs ± 1.28 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)