How to calculate the geometric mean and ignore 0s in python

user_5917787 0
Viewed 1697

I have a pandas dataframe consisting of 13 columns of daily stock returns for certain stocks. I want to calculate the geometric mean of each column but some have zeros in the column as those businesses materialized on the stock market at different times.

I know numpy's arithmetic mean will ignore NaNs. Is there some way to calculate the geometric mean and ignore zeros at the same time?

sample df:

import pandas as pd
dictA = {'AAPL': [.02, -.001, .05, .43], 'ABC':[.03, -.02, -.05, 0], 'DEF': [.045, 0, -.10, .63]}
df = pd.DataFrame(dictA)

The geometric mean for AAPL would be .02 * -.001 * .05 * .43**(1/N) where N is the number of observations.

Is there some sort of slick code that can calculate the geometric mean while ignoring zeros?

pandas python
4 Answers

One way is using np.multiply.reduce and np.where to replace those 0 to 1 so they do not modify the result, and divide by the amount of non-zero values per column:

a = df.values
m = (a!=0)
np.multiply.reduce(np.where(m, a, 1), axis=0)**(1/m.sum(0))
user_96986840

Geometric means isn't good for lists with negative values in them (some of these results return imaginary numbers), but that being said, here's one answer to your question:

import pandas as pd
import numpy as np


def geometric_mean(values):
    return float(np.prod([x for x in values])) ** (1 / len([x for x in values]))

dictA = {'AAPL': [.02, -.001, .05, .43], 'ABC': [.03, -.02, -.05, 0], 'DEF': [.045, 0, -.10, .63]}
df = pd.DataFrame(dictA)

cols = ['AAPL', 'ABC', 'DEF']
for col in cols:
    # exclude 0s from being passed to the function
    print(geometric_mean(df.loc[df[col] != 0, col]))

EDIT: I originally had return np.prod([x for x in values]) ** (1 / len([x for x in values])). I changed this to return float(np.prod([x for x in values])) ** (1 / len([x for x in values])) so the function will now return imaginary numbers if the product of the list is negative.

user_64804020

Make a function that takes all elements in a column and returns one element. Apply it to every column (in the axis=0 direction).

from functools import reduce

def g_mean(n):
    """Find the geometric mean for iterable n."""

    # Make a list with every element in n that != 0.
    l = [e for e in n if e !=0]

    tot = reduce(lambda a,b: a*b, l) # Multiply all elements in l.
    return tot**(1/(len(l)))

df.apply(g_mean) # Apply g_mean(column) to every column.
user_108334920

I figured this out for negative numbers. If I have a data frame of stock returns with negative numbers, I do the following:

from scipy.stats import gmean
gmean(1+df, axis = 0) - 1
user_59177870
Related