CODEWITHSUNDEEP
pythonpandas
Windstorm1981
Windstorm1981

Reputation: 2680

Python Pandas Count Number of DataFrame Columns in which a Particular Value Occurs

I have a dataframe:

              A       B       C

1/2/2007    np.Nan   6.3     np.Nan
1/3/2007    np.Nan   np.Nan   3.2
1/4/2007    np.Nan   np.Nan   np.Nan
1/5/2007     -999    np.Nan   7.3
1/6/2007    np.Nan    -999    np.Nan
1/7/2007    np.Nan   np.Nan   3.1
1/8/2007    np.Nan    -999    np.Nan

How can I get a count of columns with the value -999?

Not the number of occurrences in the dataframe or the number of occurrances per column but the number of the columns with the value?

The correct answer here would be 2 since columns A and B have at least one occurance of -999 which column C has 0 occurrances.

Upvotes: 1

Views: 208

Answers (1)

Allen Qin
Allen Qin

Reputation: 19947

You can first compare your entire df with -999, then count the number of none zero columns.

df.eq(-999).any().sum()
Out[302]: 2

A bit explanation:

df.eq(-999) gives a True/False matrix, indicating if the element is equal to -999:

Out[308]: 
              A      B      C
1/2/2007  False  False  False
1/3/2007  False  False  False
1/4/2007  False  False  False
1/5/2007   True  False  False
1/6/2007  False   True  False
1/7/2007  False  False  False
1/8/2007  False   True  False

.any() checks if any element in a column is True, which returns:

Out[309]: 
A     True
B     True
C    False
dtype: bool

In this example, Column A and B are true since they have at least one Trues.

Finally, .sum() counts the number of Trues, (True is automatically cast to 1 and False to 0 in a sum operation)

Upvotes: 3

Related Questions