How to remove or drop all rows after first occurrence of `NaN` from the entire DataFrame

user_5704863 0
Viewed 419

I am looking forward to remove/drop all rows after first occurrence of NaN based on any of dataFrame column.

I have created two sample DataFrames as illustrated Below, the first dataframe the dtypes are for initial two columns are object while the last one in int, while in the Second dataframe these are float, obj and int.

First:

>>> df = pd.DataFrame({"A": (1,2,3,4,5,6,7,'NaN','NaN','NaN','NaN'),"B": (1,2,3,'NaN',4,5,6,7,'NaN',"9","10"),"C": range(11)})
>>> df
      A    B   C
0     1    1   0
1     2    2   1
2     3    3   2
3     4  NaN   3
4     5    4   4
5     6    5   5
6     7    6   6
7   NaN    7   7
8   NaN  NaN   8
9   NaN    9   9
10  NaN   10  10

Dtypes:

>>> df.dtypes
A    object
B    object
C     int64
dtype: object

While carrying out index based approach as follows based on a particular, it works Just fine as long as dtype is obj and int but i'm looking for dataFrame level action merely not limited to a column.

>>> df[:df[df['A'] == 'NaN'].index[0]]
   A    B  C
0  1    1  0
1  2    2  1
2  3    3  2
3  4  NaN  3
4  5    4  4
5  6    5  5
6  7    6  6

>>> df[:df[df['B'] == 'NaN'].index[0]]
   A  B  C
0  1  1  0
1  2  2  1
2  3  3  2

Second:

Another interesting fact while creating DataFrame with np.nan where we get different dtype, then even index based approach failed for a single column operation s well.

>>> df = pd.DataFrame({"A": (1,2,3,4,5,6,7,np.nan,np.nan,np.nan,np.nan),"B": (1,2,3,np.nan,4,5,6,7,np.nan,"9","10"),"C": range(11)})
>>> df
      A    B   C
0   1.0    1   0
1   2.0    2   1
2   3.0    3   2
3   4.0  NaN   3
4   5.0    4   4
5   6.0    5   5
6   7.0    6   6
7   NaN    7   7
8   NaN  NaN   8
9   NaN    9   9
10  NaN   10  10

dtypes:

>>> df.dtypes
A    float64
B     object
C      int64
dtype: object

Error:

>>> df[:df[df['B'] == 'NaN'].index[0]]
IndexError: index 0 is out of bounds for axis 0 with size 0
>>> df[:df[df['A'] == 'NaN'].index[0]]
IndexError: index 0 is out of bounds for axis 0 with size 0

Expected should be for the Second DataFrame:

>>> df
      A    B   C
0   1.0    1   0
1   2.0    2   1
2   3.0    3   2

So, i am looking a way around to check across the entire DataFrame regardless of dtype and drop all rows from the first occurrence of NaN in the DataFrame.

pandas dataframe python
2 Answers

You can try:

out=df.iloc[:df.isna().any(1).idxmax()]

OR

via replace() make your string 'NaN's to real 'NaN's then check for missing values and filter rows:

df=df.replace({'NaN':float('NaN'),'nan':float('NaN')})
out=df.iloc[:df.isna().any(1).idxmax()]

output of out:

    A       B   C
0   1.0     1   0
1   2.0     2   1
2   3.0     3   2
user_142898920

Just for posterity ...

>>> df.iloc[:df.isna().any(1).argmax()]
     A  B  C
0  1.0  1  0
1  2.0  2  1
2  3.0  3  2

>>> df.iloc[:df.isnull().any(1).argmax()]
     A  B  C
0  1.0  1  0
1  2.0  2  1
2  3.0  3  2
user_57048630
Related