CODEWITHSUNDEEP
pythonpandasdataframena
nilsinelabore
nilsinelabore

Reputation: 5105

Fill NaN values with next value in same column in Python

I would like to fill NaN values with the next value in the column number for df:

        Id  Date        is_start        number
151256  30  2010-09-21  False           NaN
237558  30  2010-09-22  False           0.0
36922   120 2010-10-13  False           0.0
246284  80  2010-09-21  False           NaN
47655   80  2010-09-21  False           658.0

reproducible example:

import pandas as pd
import numpy as np
import datetime

sample_df = pd.DataFrame({'Id': {151256: 30, 237558: 30, 36922: 120, 246284: 80, 47655: 80},
 'Date': {151256: datetime.date(2010, 9, 21),
  237558: datetime.date(2010, 9, 22),
  36922: datetime.date(2010, 10, 13),
  246284: datetime.date(2010, 9, 21),
  47655: datetime.date(2010, 9, 21)},
 'is_start': {151256: False,
  237558: False,
  36922: False,
  246284: False,
  47655: False},
 'number': {151256: np.nan,
  237558: 0.0,
  36922: 0.0,
  246284: np.nan,
  47655: 658.0}})
sample_df

Expected output:

        Id  Date        is_start        number
151256  30  2010-09-21  False           0.0   (replaced)
237558  30  2010-09-22  False           0.0
36922   120 2010-10-13  False           0.0
246284  80  2010-09-21  False           658.0 (replaced)
47655   80  2010-09-21  False           658.0

I tried:

sample_df['number'] = sample_df.fillna(sample_df.number.shift())

but got output:

        Id  Date    is_start    number
151256  30  2010-09-21  False   30
237558  30  2010-09-22  False   30
36922   120 2010-10-13  False   120
246284  80  2010-09-21  False   80
47655   80  2010-09-21  False   80

where number took on values in the Id column. Why is this happening and what is the correct way?

Upvotes: 1

Views: 681

Answers (2)

David Erickson
David Erickson

Reputation: 16683

BEN_YO's solution is the answer, but here is an alternative with fillna and shift(-1):

sample_df['number'] = sample_df['number'].fillna(sample_df['number'].shift(-1))
sample_df
Out[1]: 
         Id        Date  is_start  number
151256   30  2010-09-21     False     0.0
237558   30  2010-09-22     False     0.0
36922   120  2010-10-13     False     0.0
246284   80  2010-09-21     False   658.0
47655    80  2010-09-21     False   658.0

Upvotes: 1

BENY
BENY

Reputation: 323226

Check bfill notice limit here is to only fill the next one NaN value

df.number = df.number.bfill(limit=1)
Out[138]: 
151256      0.0
237558      0.0
36922       0.0
246284    658.0
47655     658.0
Name: number, dtype: float64

Upvotes: 1

Related Questions