CODEWITHSUNDEEP
pythonpandas
user1642513
user1642513

Reputation:

Find integer index of rows with NaN in pandas dataframe

I have a pandas DataFrame like this:

                    a         b
2011-01-01 00:00:00 1.883381  -0.416629
2011-01-01 01:00:00 0.149948  -1.782170
2011-01-01 02:00:00 -0.407604 0.314168
2011-01-01 03:00:00 1.452354  NaN
2011-01-01 04:00:00 -1.224869 -0.947457
2011-01-01 05:00:00 0.498326  0.070416
2011-01-01 06:00:00 0.401665  NaN
2011-01-01 07:00:00 -0.019766 0.533641
2011-01-01 08:00:00 -1.101303 -1.408561
2011-01-01 09:00:00 1.671795  -0.764629

Is there an efficient way to find the "integer" index of rows with NaNs? In this case the desired output should be [3, 6].

Upvotes: 128

Views: 217763

Answers (16)

Archie
Archie

Reputation: 2387

A DataFrame object has a built in function isna() these days, which means you could also solve it as follows:

In case one NaN value is sufficient to return the index:

index_na = df.index[df.isna().any(1)]

In case all of them have to be NaN:

index_na = df.index[df.isna().all(1)]

To return the numeric index for the first case:

index_na_num = np.where(df.isna().any(1)[0])

Upvotes: 0

Marcio Bernardo
Marcio Bernardo

Reputation: 73

Easy solution:

# Find the index of nulls

indx = df[df.isnull()].index

# Find the index of nulls of a column or a group of columns

indx_A = df[df['A'].isnull()].index 

col_list = ['A','B','C']

indx_col_list = df[df[col_list].isnull()].index

Upvotes: 0

Mainland
Mainland

Reputation: 4564

The quick and fast solution to the question is:

# Find the integer index of nulls
nan_idx = np.where(df['column_name'].isnull())[0]

# Find actual index of the nan's
nan_idx = df.iloc[nan_idx].index

Upvotes: 0

KRUNALg
KRUNALg

Reputation: 1

    index_nan = []
        for index, bool_v in df["b"].iteritems().isna():
           if bool_v == True:
               index_nan.append(index)
    print(index_nan)

Upvotes: 0

Xpie
Xpie

Reputation: 43

This will give you the index values for nan in every column:

df.loc[pd.isna(df).any(1), :].index

Upvotes: 3

karthikeyan
karthikeyan

Reputation: 235

Another simple solution is list(np.where(df['b'].isnull())[0])

Upvotes: 3

Adam Erickson
Adam Erickson

Reputation: 6363

Here are tests for a few methods:

%timeit np.where(np.isnan(df['b']))[0]
%timeit pd.isnull(df['b']).nonzero()[0]
%timeit np.where(df['b'].isna())[0]
%timeit df.loc[pd.isna(df['b']), :].index

And their corresponding timings:

333 µs ± 9.95 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
280 µs ± 220 ns per loop (mean ± std. dev. of 7 runs, 1000 loops each)
313 µs ± 128 ns per loop (mean ± std. dev. of 7 runs, 1000 loops each)
6.84 ms ± 1.59 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

It would appear that pd.isnull(df['DRGWeight']).nonzero()[0] wins the day in terms of timing, but that any of the top three methods have comparable performance.

Upvotes: 5

Stone Austin
Stone Austin

Reputation: 1

Let the dataframe be named df and the column of interest(i.e. the column in which we are trying to find nulls) is 'b'. Then the following snippet gives the desired index of null in the dataframe:

   for i in range(df.shape[0]):
       if df['b'].isnull().iloc[i]:
           print(i)

Upvotes: 0

Amirkhm
Amirkhm

Reputation: 1096

in the case you have datetime index and you want to have the values:

df.loc[pd.isnull(df).any(1), :].index.values

Upvotes: 6

naturesenshi
naturesenshi

Reputation: 340

Don't know if this is too late but you can use np.where to find the indices of non values as such:

indices = list(np.where(df['b'].isna()[0]))

Upvotes: 12

Vasyl Vaskivskyi
Vasyl Vaskivskyi

Reputation: 957

One line solution. However it works for one column only.

df.loc[pandas.isna(df["b"]), :].index

Upvotes: 30

murthy10
murthy10

Reputation: 156

I was looking for all indexes of rows with NaN values.
My working solution:

def get_nan_indexes(data_frame):
    indexes = []
    print(data_frame)
    for column in data_frame:
        index = data_frame[column].index[data_frame[column].apply(np.isnan)]
        if len(index):
            indexes.append(index[0])
    df_index = data_frame.index.values.tolist()
    return [df_index.index(i) for i in set(indexes)]

Upvotes: 1

nonya beeswax
nonya beeswax

Reputation: 11

Here is another simpler take:

df = pd.DataFrame([[0,1,3,4,np.nan,2],[3,5,6,np.nan,3,3]])

inds = np.asarray(df.isnull()).nonzero()

(array([0, 1], dtype=int64), array([4, 3], dtype=int64))

Upvotes: 1

Filippo Mazza
Filippo Mazza

Reputation: 4377

And just in case, if you want to find the coordinates of 'nan' for all the columns instead (supposing they are all numericals), here you go:

df = pd.DataFrame([[0,1,3,4,np.nan,2],[3,5,6,np.nan,3,3]])

df
   0  1  2    3    4  5
0  0  1  3  4.0  NaN  2
1  3  5  6  NaN  3.0  3

np.where(np.asanyarray(np.isnan(df)))
(array([0, 1]), array([4, 3]))

Upvotes: 12

Wes McKinney
Wes McKinney

Reputation: 105531

Here is a simpler solution:

inds = pd.isnull(df).any(1).nonzero()[0]

In [9]: df
Out[9]: 
          0         1
0  0.450319  0.062595
1 -0.673058  0.156073
2 -0.871179 -0.118575
3  0.594188       NaN
4 -1.017903 -0.484744
5  0.860375  0.239265
6 -0.640070       NaN
7 -0.535802  1.632932
8  0.876523 -0.153634
9 -0.686914  0.131185

In [10]: pd.isnull(df).any(1).nonzero()[0]
Out[10]: array([3, 6])

Upvotes: 160

diliop
diliop

Reputation: 9451

For DataFrame df:

import numpy as np
index = df['b'].index[df['b'].apply(np.isnan)]

will give you back the MultiIndex that you can use to index back into df, e.g.:

df['a'].ix[index[0]]
>>> 1.452354

For the integer index:

df_index = df.index.values.tolist()
[df_index.index(i) for i in index]
>>> [3, 6]

Upvotes: 55

Related Questions