Remove column if all rows contain NaN, [] (empty list) or {} (empty dict)

user_5409591 0
Viewed 5661

If all rows in the particular column are either null, [](empty array) or {} (empty dict), the column will be dropped. I am aware of dropna() function but it seems only dropping NA, how about empty array and empty dict?

pandas dataframe python
1 Answers

Use where and astype(bool) to handle empty containers, then isna and all to get a mask to drop columns accordingly.

df.loc[:, ~df.where(df.astype(bool)).isna().all(axis=0)]

Caveat: This will pick up false positives such as a column full of zeros or False (falsy values, effectively).

df = pd.DataFrame({'A': [np.nan, [], {}], 'B': range(3), 'C': [1, 2, np.nan]})
df

     A  B    C
0  NaN  0  1.0
1   []  1  2.0
2   {}  2  NaN

df.loc[:, ~df.where(df.astype(bool)).isna().all(axis=0)]

   B    C
0  0  1.0
1  1  2.0
2  2  NaN

To get over the caveat I mentioned, we can use select_dtypes to select only object columns, then repeat the process and call drop at the end.

df = pd.DataFrame({
    'A': [np.nan, [], {}], 
    'B': range(3), 
    'C': [1, 2, np.nan], 
    'D': 0
})    
df

     A  B    C  D
0  NaN  0  1.0  0
1   []  1  2.0  0
2   {}  2  NaN  0

u = df.select_dtypes(object)
df.drop(u.columns[u.where(u.astype(bool)).isna().all(axis=0)], axis=1)

   B    C  D
0  0  1.0  0
1  1  2.0  0
2  2  NaN  0
user_49090870
Related