CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
MikeB2019x
MikeB2019x

Reputation: 1161

Pandas shift values in a column over intervening rows

I have a pandas data frame as shown below. One column has values with intervening NaN cells. The values are to be shifted ahead by one so that they replace the next value that follows with the last being lost. The intervening NaN cells have to remain. I tried using .shift() but since I never know how many intervening NaN rows it means a calculation for each shift. Is there a better approach?

enter image description here

Upvotes: 1

Views: 137

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150745

Another way:

s = df['y'].notnull()
df.loc[s,'y'] = df.loc[s,'y'].shift()

It would be easier to test if you paste your text data instead of the picture.

Input:

df = pd.DataFrame({'x':list('AAABBBBCCCC'),
                  'y':[5,np.nan,np.nan,10, np.nan,np.nan,np.nan,
                       20, np.nan,np.nan,np.nan]})

output:

    x     y
0   A   NaN
1   A   NaN
2   A   NaN
3   B   5.0
4   B   NaN
5   B   NaN
6   B   NaN
7   C  10.0
8   C   NaN
9   C   NaN
10  C   NaN

Upvotes: 1

rafaelc
rafaelc

Reputation: 59274

IIUC, you may just groupby by non-na values, and shift them.

df['y'] = df.y.groupby(pd.isnull(df.y)).shift()

    x     y
0   A   NaN
1   A   NaN
2   A   NaN
3   B   5.0
4   B   NaN
5   B   NaN
6   B   NaN
7   C  10.0
8   C   NaN
9   C   NaN
10  C   NaN

Upvotes: 2

Related Questions