Drop row with NaN value in a Dataframe, only after the first non NaN Value

user_10240072 0
Viewed 117

I have a Dataframe with different columns. Some columns might start with a serie of NaN value before the real values starts. However, after the first non NaN value in each columns, some NaN value can also appear. For example :

              A    B    C
2021-08-31  1.0  NaN  5.0
2021-09-01  2.0  NaN  NaN
2021-09-02  4.0  3.0  NaN
2021-09-03  NaN  7.0  5.0
2021-09-06  2.0  5.0  5.0
2021-09-07  9.0  NaN  5.0
2021-09-08  4.0  5.0  NaN

I would like to remove all the line where there is a NaN value but only after the first non NaN value in the column. Said differently, the NaN values before the first non NaN value are not taken into account in the removal process.

So the previous example, would look something like this :

              A    B    C
2021-08-31  1.0  NaN  5.0
2021-09-06  2.0  5.0  5.0

I started looking for a solution using the list of 'first_valid_date' and then removing with conditions on the index being after the first_valid_date of the column plus the value being NaN but I have problem with the removal of value with 2 conditions (NaN and index) :

df.drop(df[df.isna().any(axis=1) & df.index > mydateindex].index)

dataframe python
2 Answers

Try using this with loc and isna with notna, and shift:

>>> df.loc[(~(df.shift().notna() & df.isna() & df.shift(-1).notna())).all(1)]
              A    B
2021-08-31  1.0  NaN
2021-09-01  2.0  NaN
2021-09-02  4.0  3.0
2021-09-06  2.0  5.0
2021-09-08  4.0  5.0
>>>
user_87083640

I think I found the correct way to do it :

df.loc[~(df.fillna(method='ffill').notna() & ~df.notna()).max(axis=1)]
user_102400720
Related