CODEWITHSUNDEEP
pythonpandas
jimmy15923
jimmy15923

Reputation: 291

pandas replace zero with next value in other column

I have a data frame

 A    B
10   20
20   25
30    0
35    40
45    0
60    70

I want to replace the 0 in column B with the next row value in A. So it will become

  A     B
 10    20
 20    25
 30   '35'
'35'   40
 45   '60'
'60'   70

Upvotes: 1

Views: 3743

Answers (2)

Little Bobby Tables
Little Bobby Tables

Reputation: 4744

Here we can use a method chain with the methods .replace() and .fillna(). This will work robustly when you have consecutive zeros and shall default to using the last non-zero reading.

import numpy as np
import pandas as pd

df.replace(0, np.nan).fillna(method='ffill')

If you wished for it to only fill forwards once then you can do the following,

df.replace(0, np.nan).fillna(method='ffill', limit=1)

Finally, if you can further chain these methods to also backfill, should you start with zero reads.

df.replace(0, np.nan).fillna(method='ffill').fillna(method='bfill')

Upvotes: 1

Zero
Zero

Reputation: 76917

You could shift and set using loc

In [219]: df.loc[df.B == 0, 'B'] = df.A.shift(-1)

In [220]: df
Out[220]:
    A     B
0  10  20.0
1  20  25.0
2  30  35.0
3  35  40.0
4  45  60.0
5  60  70.0

Upvotes: 2

Related Questions