Select dataframe columns which contain the most valid values

user_12966591 0
Viewed 93

I have my dataframe:

enter image description here

And I would like to create a subset of this dataframe with the columns in which there are the fewest NaN values (i.e. the most "valid" values)

In this case, I would only select the "A3" column as there is only one NaN and there are 3 in the others.

If there are two columns (or more) with the same number of NaN values, just select one of them (for example the first it does not matter).

The code for creating the dataframe:

df = pd.DataFrame({"A1":[np.NaN,1,0,0,np.NaN,0,1,np.NaN,0,0,0,1],
                   "A2":[0,1,np.NaN,0,1,np.NaN,1,0,np.NaN,0,0,1],
                   "A3":[0,1,np.NaN,0,1,0,1,0,0,0,0,2]})
df
pandas series dataframe nan python
2 Answers

You can sum up the number of null values in each column using pd.isnull and .sum() , then pick the column with the lowest count using .idxmin() and select just that column from your dataframe:

df[pd.isnull(df).sum().idxmin()]

Output:

0     0.0
1     1.0
2     NaN
3     0.0
4     1.0
5     0.0
6     1.0
7     0.0
8     0.0
9     0.0
10    0.0
11    2.0
Name: A3, dtype: float64
user_144633960

You can use pd.isnull(df['A']).sum() on your columns and work your way around with some logic to keep the column that has least NaNs in it.

Refer isnull() function

z = list()
for column in df:
  z.append(pd.isnull(df[column]).sum())

list(df.iloc[:,z.index(min(z))].values)

[0.0, 1.0, nan, 0.0, 1.0, 0.0, 1.0, 0.0, 0.0, 0.0, 0.0, 2.0]
user_149356490
Related