unable to replace zeros in the last row of dataframe with mean of last three rows in respective columns while leaving non zero values as it is

user_8814173 0
Viewed 190

I have a dataset which looks like this:

import pandas as pd, numpy as np
df = pd.DataFrame([[1,0,3,0], [5,6,7,8], [9,10,11,12], [11, 14,15,16], [0,0,19,0]], columns=['a','b','c','d'])

So what I want to do is:

  1. in the last row, wherever value is 0s, replace with the mean of previous three rows of the same column
  2. if the value is not 0, then leave it as it is
  3. Also all other 0's elsewhere should remain 0 only.

So the end result should look something like this:

  a b   c   d
  1 0   3   0
  5 6   7   8
  9 10  11  12
 13 14  15  16
 9  10  19  12

Here, all three 0s are replaced with the previous three values' mean. And 19 remains as it is.

What I am trying to do is:

if (df.iloc[-1].any()==0):
    df.iloc[-1] = df[-4:-1].mean()
else:
   pass

This did not change the values and no error was returned as well. What am I doing wrong here?

pandas python
2 Answers

It'll be much easier if you just replace 0 with NaN then use fillna with rolling mean, and shift:

>>> df.iloc[-1]=df.iloc[-1].replace(0, np.nan)
>>> df=df.fillna(df.rolling(3, min_periods=1).mean().shift())

OUTPUT:

      a     b   c     d
0   1.0   0.0   3   0.0
1   5.0   6.0   7   8.0
2   9.0  10.0  11  12.0
3  13.0  14.0  15  16.0
4   9.0  10.0  19  12.0
user_91363480

With np.where:

last_row = df.iloc[-1]
df.iloc[-1] = np.where(last_row.eq(0), df.iloc[-4:-1].mean(), last_row)

This will take values from three previous rows' mean where last row is equal to 0 and from the last row itself otherwise, i.e., nonzero values will stay as is.

pandas' where can be similarly used:

last_row = df.iloc[-1]
df.iloc[-1] = last_row.where(last_row.ne(0), df.iloc[-4:-1].mean())

Where the last row's values are not equal to 0 will be replaced with the mean of previous three's mean.

user_93321870
Related