Replacing a value in a column based on the value in the column above-CodePudding

I have a data frame that has a column where some values are See Above or See Below. The data frame looks something like this:

In[1]: df = pd.DataFrame([[1, 'Cat'], [1, 'See Above'], [4, 'See Below'],[2, 'Dog']], columns=['A','B'])

In[2]: df
Out[2]: 

    A   B
0   1   Cat
1   1   See Above
2   4   See Below
3   2   Dog

How could I update these values based on the value in the row above or below? I have ~2300k rows for context.

CodePudding user response：

# mask as nan value that are 'see above' and then ffill
df['B']=df['B'].mask(df['B'].eq('See Above'), np.nan).ffill()

# mask as nan value that are 'see below' and then bfill
df['B']=df['B'].mask(df['B'].eq('See Below'), np.nan).bfill()
df

    A   B
0   1   Cat
1   1   Cat
2   4   Dog
3   2   Dog

CodePudding user response：

In a single line with replace:

df['B'] = df['B'].replace('See Above',np.nan).ffill().replace('See Below',np.nan).bfill()
print(df)

Result

   A    B
0  1  Cat
1  1  Cat
2  4  Dog
3  2  Dog