How to select row before and after NaN in pandas?

Admin 1
Viewed 291

I have a dataframe which looks like this :

    Name      Age       Job         
0   Alex      20        Student
1   Sara      21        Doctor
2   john      23        NaN
3   kevin     22        Teacher
4   Rosa      20        senior manager
5   johanes   25        Dentist
6   lina      23        Student
7   yaser     25        Pilot
8   jason     20        Manager
9   Ali       23        NaN
10  Ahmad     21        Professor
11  Joe       24        NaN
12  Donald    29        Waiter
.
.
.
.

I want to select the rows before and after the row that has NaN values in column Job with the row itself. For that I have the following code :

Rows = df[df. Shift(1, fill_value="dummy").Job. isna() | df.Job. isna()| df. Shift(-1, fill_value="dummy"). df. isna()]
print(Rows)

the result is this:

1   Sara      21        Doctor
2   john      23        NaN
3   kevin     22        Teacher
8   jason     20        Manager
9   Ali       23        NaN
10  Ahmad     21        Professor
11  Joe       24        NaN
12  Donald    29        Waiter

The only problem here is the row number 10, it should be double in the result because this row is one time the row after NaN which is number 9 and at the same time the row before NaN value which is row number 11( the row is between two rows with NaN value). So at the end I want to have this :

1   Sara      21        Doctor
2   john      23        NaN
3   kevin     22        Teacher
8   jason     20        Manager
9   Ali       23        NaN
10  Ahmad     21        Professor
10  Ahmad     21        Professor
11  Joe       24        NaN
12  Donald    29        Waiter

So every row which is between two rows with NaN values should be also two times in the result (or should be dupplicate). Is there any way to do this? Any help will be appreciated.

pandas shift duplicates nan python
1 Answers

Use concat with rows before, after and match by condition:

m = df.Job.isna()

df = pd.concat([df[m.shift(fill_value=False)],
                df[m.shift(-1, fill_value=False)],
                df[m]]).sort_index()
print (df)
      Name  Age        Job
1     Sara   21     Doctor
2     john   23        NaN
3    kevin   22    Teacher
8    jason   20    Manager
9      Ali   23        NaN
10   Ahmad   21  Professor
10   Ahmad   21  Professor
11     Joe   24        NaN
12  Donald   29     Waiter
user_29010020
Related