CODEWITHSUNDEEP
pythonpandas
cincin21
cincin21

Reputation: 610

Pandas - get value of some no specified previous / next row with condition

I've got an example DataFrame:

import pandas as pd
import numpy as np
df = pd.DataFrame(
    {"col1": ["A", "B", "C", "D", "E", "A", "E", "B", "D"],
     "col2": [1, np.NAN, 3, 4, np.NAN, 6, np.NAN, np.NAN, 8],
    })

Id col1  col2
0    A   1.0
1    B   NaN
2    C   3.0
3    D   4.0
4    E   NaN
5    A   6.0
6    E   NaN
7    B   NaN
8    D   8.0

And would like the row with col1 "E" value to get the value for row's empty col2 from col2 of previous col1 "A", but we don't know if it is one row above or more.

The same thing I would want to do with row with col1 "B" to get the value for empty col2 from col2 of next col1 "D".

It would look like this:

id  col1  col2
0    A     1
1    B     4
2    C     3
3    D     4
4    E     1
5    A     6
6    E     6
7    B     8
8    D     8

Is it possible to be done in short script (not many if, else etc.?) Thank you for help!

Upvotes: 1

Views: 92

Answers (1)

jezrael
jezrael

Reputation: 862681

Idea is first replace non match values to missing values by Series.where, forward or back filling missing values and assign new rows by another masks:

m1 = df['col1'].eq('A')
m11 = df['col1'].eq('E')

m2 = df['col1'].eq('D')
m22 = df['col1'].eq('B')

df.loc[m11, 'col2'] = df['col2'].where(m1).ffill()
df.loc[m22, 'col2'] = df['col2'].where(m2).bfill()
print (df)
  col1  col2
0    A   1.0
1    B   4.0
2    C   3.0
3    D   4.0
4    E   1.0
5    A   6.0
6    E   6.0
7    B   8.0
8    D   8.0

Upvotes: 2

Related Questions