CODEWITHSUNDEEP
pythonnumpypandas
Vishal
Vishal

Reputation: 999

mean and stddev of non-zero columns of dataframe

I have a dataframe with several columns, with each column having some positive, negative and zero values. For each column, I want to calculate x+y, where x and y are mean and standard deviation of absolute non-zero values of each column. How to do this in python?

Upvotes: 1

Views: 2440

Answers (2)

mrkbutty
mrkbutty

Reputation: 599

I was looking for an answer to a similar question but to produce a mean etc on nonzero items.

After playing around for a while the answer was quite simple:

In [3]: df = pd.DataFrame({'a':np.random.randint(-5,5,10), 'b':np.random.randint(-5,5,10), 'c':np.random.randint(-5,5,10)})

In [4]: df
Out[4]:
a  b  c
0  3 -5 -2
1  0 -2  1
2 -1  1 -4
3 -3  0 -4
4 -5 -3  0
5 -1  4  1
6  0 -5 -4
7  2  0 -5
8  4  0  2
9 -1  1 -4

In [5]: df[df <> 0].describe()   # or use .mean() etc.
Out[5]:
              a         b         c
count  8.000000  7.000000  9.000000
mean  -0.250000 -1.285714 -2.111111
std    3.058945  3.401680  2.713137
min   -5.000000 -5.000000 -5.000000
25%   -1.500000 -4.000000 -4.000000
50%   -1.000000 -2.000000 -4.000000
75%    2.250000  1.000000  1.000000
max    4.000000  4.000000  2.000000

I also needed the mean for timeseries data but to ignore zero values (response times) and found another solution;

In [6]: df = pd.DataFrame({'a':np.random.randint(0,5,5), 'b':np.random.randint(0,5,5), 'c':np.random.randint(0,5,5)})

In [7]: df['Time'] = pd.date_range('2015/01/01',periods=5)

In [8]: df2 = pd.DataFrame({'a':np.random.randint(0,5,5), 'b':np.random.randint(0,5,5), 'c':np.random.randint(0,5,5)})

In [9]: df2['Time'] = pd.date_range('2015/01/01',periods=5)

In [10]: df=pd.concat([df,df2]).set_index('Time').sort_index()

In [11]: df
Out[11]:
            a  b  c
Time
2015-01-01  0  0  1
2015-01-01  4  3  3
2015-01-02  2  3  4
2015-01-02  3  0  4
2015-01-03  3  4  4
2015-01-03  1  1  3
2015-01-04  4  2  2
2015-01-04  3  1  2
2015-01-05  3  2  0
2015-01-05  2  2  1

In [12]: df[df<>0].groupby(df.index).mean()
Out[12]:
              a    b    c
Time
2015-01-01  4.0  3.0  2.0
2015-01-02  2.5  3.0  4.0
2015-01-03  2.0  2.5  3.5
2015-01-04  3.5  1.5  2.0
2015-01-05  2.5  2.0  1.0

Note if all items in the same time are zero the mean evaluates as Nan.

Upvotes: 1

EdChum
EdChum

Reputation: 394041

You can filter the df using a boolean condition and then iterate over the cols and call describe and access the mean and std columns:

In [103]:

df = pd.DataFrame({'a':np.random.randn(10), 'b':np.random.randn(10), 'c':np.random.randn(10)})
df
Out[103]:
          a         b         c
0  0.566926 -1.103313 -0.834149
1 -0.183890 -0.222727 -0.915141
2  0.340611 -0.278525 -0.992135
3  0.380519 -1.546856  0.801598
4 -0.596142  0.494078 -0.423959
5 -0.064408  0.475466  0.220138
6 -0.549479  1.453362  2.696673
7  1.279865  0.796222  0.391247
8  0.778623  1.033530  1.264428
9 -1.669838 -1.117719  0.761952
In [111]:

for col in df[df>0]:
    print('col:', col, df[col].describe()[['mean','std']])
col: a mean    0.028279
std     0.836804
Name: a, dtype: float64
col: b mean   -0.001648
std     1.014950
Name: b, dtype: float64
col: c mean    0.297065
std     1.159999
Name: c, dtype: float64

Upvotes: 1

Related Questions