python - Pandas - Get count of rows where all values are null except for a set of columns

Question

I have a dataframe that looks as follows (sample below for reference, original has many more columns):

I am trying to get a list of rows where all columns are null (NaN) except for some specific columns. For example, if those specific columns are col2 and col3, I would get the first and third rows. If the specific columns are just col1, I would get only the last row.

Having just a count of the rows that meet those criteria would work too.

I know how to do this by going through each row and comparing, but is there a quicker way to do this?

Thanks!

question from:https://stackoverflow.com/questions/65891429/pandas-get-count-of-rows-where-all-values-are-null-except-for-a-set-of-columns

Answer 1

You can try:

# specific columns
cols = ['col1','col2']

df[df.drop(cols, axis=1).isna().all(1)]

That would not check if you have data in cols. If you require that, you can do:

other_nan = df.drop(cols, axis=1).isna().all(1)
chosen_notna = df[cols].notna().any(1)

df[other_nan & chosen_notna]