Pandas: replace zero value with value of another column

Question

How to replace zero value in a column with value from same row of another column where previous row value of column is zero i.e. replace only where non-zero has not been encountered yet? For example: Given a dataframe with columns a, b and c:

+----+-----+-----+----+
|    |   a |   b |  c |
|----+-----+-----|----|
|  0 |   2 |   0 |  0 |
|  1 |   5 |   0 |  0 |
|  2 |   3 |   4 |  0 |
|  3 |   2 |   0 |  3 |
|  4 |   1 |   8 |  1 |
+----+-----+-----+----+

replace zero values in b and c with values of a where previous value is zero

+----+-----+-----+----+
|    |   a |   b |  c |
|----+-----+-----|----|
|  0 |   2 |   2 |  2 |
|  1 |   5 |   5 |  5 |
|  2 |   3 |   4 |  3 |
|  3 |   2 |   0 |  3 | <-- zero in this row is not replaced because of  
|  4 |   1 |   8 |  1 |     non-zero value (4) in row before it.
+----+-----+-----+----+

MaxU - stand with Ukraine · Accepted Answer · 2017-05-30 11:06:39Z

2

In [90]: (df[~df.apply(lambda c: c.eq(0) & c.shift().fillna(0).eq(0))]
    ...:    .fillna(pd.DataFrame(np.tile(df.a.values[:, None], df.shape[1]),
    ...:                         columns=df.columns, index=df.index))
    ...:    .astype(int)
    ...: )
Out[90]:
   a  b  c
0  2  2  2
1  5  5  5
2  3  4  3
3  2  0  3
4  1  8  1

Explanation:

In [91]: df[~df.apply(lambda c: c.eq(0) & c.shift().fillna(0).eq(0))]
Out[91]:
   a    b    c
0  2  NaN  NaN
1  5  NaN  NaN
2  3  4.0  NaN
3  2  0.0  3.0
4  1  8.0  1.0

now we can fill NaN's with the corresponding values from the DF below (which is built as 3 concatenated a columns):

In [92]: pd.DataFrame(np.tile(df.a.values[:, None], df.shape[1]), columns=df.columns, index=df.index)
Out[92]:
   a  b  c
0  2  2  2
1  5  5  5
2  3  3  3
3  2  2  2
4  1  1  1

edited May 30, 2017 at 11:06

answered May 30, 2017 at 10:46

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DougKruger Over a year ago

Really nice explanation. thank you. The only issue here is that if b has no 0, it will use it to fill c, rather than use values from a..isn't it?

MaxU - stand with Ukraine Over a year ago

@DougKruger, i guess you are talking about my previous answer, which used .ffill(axis=1). I've updated it - please check

DougKruger Over a year ago

in the explanation, all values in b and c have been completely overwritten by c.

Yash Ghorpade Over a year ago

Hey, I have same issue but using the above solution all values are replaced by a.

Collectives™ on Stack Overflow

Pandas: replace zero value with value of another column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related