Pandas Dataframe, average non 0 value

user_15123783 0
Viewed 61

I have the following Pandas Dataframe 'df':

a1  a2  a3  b1
0   0   0   1
1   2   0   2
3   0   0   3
2   4   0   4

How do I get the average value of "a" for from a1, a2, a3, ignoring the 0 value?

a1  a2  a3  b1  avg(a)
0   0   0   1   0
1   2   0   2   1.5
3   0   0   3   3.0
2   4   0   4   3.0

I'm stuck with manual approach where I convert the value > 0 to 1

jupyter-notebook pandas google-colaboratory dataframe python
2 Answers

You can .filter the a like columns, then .mask the zeros in these columns and take .mean along axis=1:

a = df.filter(like='a')
df['avg'] = a.mask(a.eq(0)).mean(1).fillna(0)

# OR df['avg'] = a[a > 0].mean(1).fillna(0)

   a1  a2  a3  b1  avg
0   0   0   0   1  0.0
1   1   2   0   2  1.5
2   3   0   0   3  3.0
3   2   4   0   4  3.0
user_128331660 
import pandas as pd

df = pd.DataFrame({'a1': [0, 1, 3, 2],
                   'a2': [0, 2, 0, 4],
                   'a3': [0, 0, 0, 0],
                   'b1': [1, 2, 3, 4]})

# remove the last column
df1 = df.drop('b1', 1)

# counting non-zeros
df2 = df1.astype(bool).sum(axis=1)

# sum and divide
df3 = df1.sum(axis=1) / df2
df['avg(a)'] = df3.fillna(0)
print(df)


   a1  a2  a3  b1  avg(a)
0   0   0   0   1     0.0
1   1   2   0   2     1.5
2   3   0   0   3     3.0
3   2   4   0   4     3.0
user_126548850
Related