Pandas: Drop Rows, Columns If More Than Half Are NaN

user_1316501 0
Viewed 5460

I have a Pandas DataFrame called df with 1,460 rows and 81 columns. I want to remove all columns where at least half the entries are NaN and to do something similar for rows.

From the Pandas docs, I attempted this:

train_df.shape //(1460, 81)
train_df.dropna(thresh=len(train_df)/2, axis=1, inplace=True)
train_df.shape //(1460, 77)

Is this the correct way of doing it? It seems to remove 4 columns but I'm surprised. I would have thought len(train_df) gets me the number of rows so I've passed the wrong value to thresh...?

How would I do the same thing for rows (removing rows where at least half the columns are NaN)?

Thanks!

pandas python
4 Answers

Using count and loc. count(axis=) ignores NaNs for counting.

In [4135]: df.loc[df.count(1) > df.shape[1]/2, df.count(0) > df.shape[0]/2]
Out[4135]:
          0
0  0.382991
1  0.428040
7  0.441113

Details

In [4136]: df
Out[4136]:
          0         1         2         3
0  0.382991  0.658090  0.881214  0.572673
1  0.428040  0.258378  0.865269  0.173278
2  0.579953       NaN       NaN       NaN
3  0.117927       NaN       NaN       NaN
4  0.597632       NaN       NaN       NaN
5  0.547839       NaN       NaN       NaN
6  0.998631       NaN       NaN       NaN
7  0.441113  0.527205  0.779821  0.251350

In [4137]: df.count(1) > df.shape[1]/2
Out[4137]:
0     True
1     True
2    False
3    False
4    False
5    False
6    False
7     True
dtype: bool

In [4138]: df.count(0) < df.shape[0]/2
Out[4138]:
0    False
1     True
2     True
3     True
dtype: bool
user_21372550

I guess you did the right thing but forgot to add the .index. The line should look like this:

train_df.dropna(thresh=len(train_df.index)/2, axis=1, inplace=True)

Hope that helps.

user_103936850

Setup 

np.random.seed([3,14159])
df = pd.DataFrame(np.random.choice([1, np.nan], size=(10, 10)))

df

     0    1    2    3    4    5    6    7    8    9
0  1.0  1.0  NaN  NaN  NaN  1.0  1.0  NaN  1.0  NaN
1  NaN  1.0  1.0  1.0  1.0  1.0  1.0  1.0  NaN  1.0
2  NaN  1.0  1.0  NaN  NaN  NaN  NaN  1.0  1.0  1.0
3  1.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  1.0  NaN
4  1.0  1.0  1.0  1.0  1.0  1.0  NaN  NaN  1.0  NaN
5  1.0  NaN  NaN  1.0  NaN  NaN  1.0  NaN  NaN  1.0
6  NaN  NaN  1.0  NaN  NaN  1.0  1.0  NaN  NaN  1.0
7  NaN  NaN  NaN  1.0  NaN  1.0  NaN  1.0  NaN  NaN
8  1.0  1.0  1.0  NaN  1.0  NaN  1.0  NaN  NaN  1.0
9  NaN  NaN  NaN  1.0  1.0  1.0  1.0  1.0  1.0  1.0

Solution 1
This assumes you make the calculation for rows and columns before you drop either rows or columns. 

n = df.notnull()

df.loc[n.mean(1) > .5, n.mean() > .5]

     5    6    9
1  1.0  1.0  1.0
4  1.0  NaN  NaN
8  NaN  1.0  1.0
9  1.0  1.0  1.0

Solution 2
Similar concept but using numpy tools.

v = np.isnan(df.values)

r = np.count_nonzero(v, 1) < v.shape[1] // 2
c = np.count_nonzero(v, 0) < v.shape[0] // 2
df.loc[r, c]

     5    6    9
1  1.0  1.0  1.0
4  1.0  NaN  NaN
8  NaN  1.0  1.0
9  1.0  1.0  1.0
user_23366540

Try this code, it would do !

df.dropna(thresh = df.shape[1]/3, axis = 0, inplace = True)

user_143638260
Related