What's the best way to sum all values in a Pandas dataframe?

Question

I figured out these two methods. Is there a better one?

>>> import pandas as pd
>>> df = pd.DataFrame({'A': [5, 6, 7], 'B': [7, 8, 9]})
>>> print df.sum().sum()
42
>>> print df.values.sum()
42

Just want to make sure I'm not missing something more obvious.

Solution 1

Updated for Pandas 0.24+

df.to_numpy().sum()

---

Prior to Pandas 0.24+

df.values

Is the underlying numpy array

df.values.sum()

Is the numpy sum method and is faster

Solution 2

Adding some numbers to support this:

import numpy as np, pandas as pd
import timeit
df = pd.DataFrame(np.arange(int(1e6)).reshape(500000, 2), columns=list("ab"))

def pandas_test():
    return df['a'].sum()

def numpy_test():
    return df['a'].to_numpy().sum()

timeit.timeit(numpy_test, number=1000)  # 0.5032469799989485
timeit.timeit(pandas_test, number=1000)  # 0.6035906639990571

So we get a 20% performance on my machine just for Series summations!

Comments

[Ramon Crehuet]

Be careful, because if there are nan values df.sum().sum() ignores the nan and returns a float whereas df.values.sum() returns nan. So the 2 methods are not equivalent.

[Bill]

Thanks. That's what I thought!

[kuanb]

Is it faster purely because one function calls the other or is there some more fundamental difference?

[piRSquared]

@kuanb two reasons. One, df.values.sum() is a numpy operation and most of the time, numpy is more performant. Two, numpy sums over all elements in an array regardless of dimensionality. pandas requires two separate calls to sum one for each dimension.

[Bill]

But is df['a'].sum() the same as df['a'].to_numpy().sum()? I think df['a'].sum() only sums the columns doesn't it?

[Raven]

yeah, this is just comparison for a sigle series smmation, I wasn't summing the whole df

[Bill]

Oh I see. But this question is about summing the whole dataframe, not one series.

[Bill]

Can you report your pandas and numpy versions? I get a much bigger speed difference on your tests with Pandas 0.24.2 and Numpy 1.16.2.