Pandas: drop columns with all NaN's

user_7231526 0
Viewed 147085

I realize that dropping NaNs from a dataframe is as easy as df.dropna but for some reason that isn't working on mine and I'm not sure why.

Here is my original dataframe:

fish_frame1:                       0   1   2         3   4       5   6          7
0               #0915-8 NaN NaN       NaN NaN     NaN NaN        NaN
1                   NaN NaN NaN  LIVE WGT NaN  AMOUNT NaN      TOTAL
2               GBW COD NaN NaN     2,280 NaN   $0.60 NaN  $1,368.00
3               POLLOCK NaN NaN     1,611 NaN   $0.01 NaN     $16.11
4                 WHAKE NaN NaN       441 NaN   $0.70 NaN    $308.70
5           GBE HADDOCK NaN NaN     2,788 NaN   $0.01 NaN     $27.88
6           GBW HADDOCK NaN NaN    16,667 NaN   $0.01 NaN    $166.67
7               REDFISH NaN NaN       932 NaN   $0.01 NaN      $9.32
8    GB WINTER FLOUNDER NaN NaN       145 NaN   $0.25 NaN     $36.25
9   GOM WINTER FLOUNDER NaN NaN    25,070 NaN   $0.35 NaN  $8,774.50
10        GB YELLOWTAIL NaN NaN        26 NaN   $1.75 NaN     $45.50

The code that follows is an attempt to drop all NaNs as well as any columns with more than 3 NaNs (either one, or both, should work I think):

fish_frame.dropna()
fish_frame.dropna(thresh=len(fish_frame) - 3, axis=1)

This produces:

fish_frame1 after dropna:                       0   1   2         3   4       5   6          7
0               #0915-8 NaN NaN       NaN NaN     NaN NaN        NaN
1                   NaN NaN NaN  LIVE WGT NaN  AMOUNT NaN      TOTAL
2               GBW COD NaN NaN     2,280 NaN   $0.60 NaN  $1,368.00
3               POLLOCK NaN NaN     1,611 NaN   $0.01 NaN     $16.11
4                 WHAKE NaN NaN       441 NaN   $0.70 NaN    $308.70
5           GBE HADDOCK NaN NaN     2,788 NaN   $0.01 NaN     $27.88
6           GBW HADDOCK NaN NaN    16,667 NaN   $0.01 NaN    $166.67
7               REDFISH NaN NaN       932 NaN   $0.01 NaN      $9.32
8    GB WINTER FLOUNDER NaN NaN       145 NaN   $0.25 NaN     $36.25
9   GOM WINTER FLOUNDER NaN NaN    25,070 NaN   $0.35 NaN  $8,774.50
10        GB YELLOWTAIL NaN NaN        26 NaN   $1.75 NaN     $45.50

I'm a novice with Pandas so I'm not sure if this isn't working because I'm doing something wrong or I'm misunderstanding something or misusing a function. Any help is appreciated thanks.

pandas dataframe in-place python
4 Answers

Another solution would be to create a boolean dataframe with True values at not-null positions and then take the columns having at least one True value. Below line removes columns with all NaN values.

df = df.loc[:,df.notna().any(axis=0)]

If you want to remove columns having at least one missing (NaN) value;

df = df.loc[:,df.notna().all(axis=0)]

This approach is particularly useful in removing columns containing empty strings, zeros or basically any given value. For example;

df = df.loc[:,(df!='').all(axis=0)]

removes columns having at least one empty string.

user_88897270
Related