How do I count specific values across multiple columns in pandas

Question

I have the DataFrame

df = pd.DataFrame({
    'colA':['?',2,3,4,'?'],
    'colB':[1,2,'?',3,4],
    'colC':['?',2,3,4,5]
})

I would like to get the count the the number of '?' in each column and return the following output -

colA - 2
colB - 1
colC - 1

Is there a way to return this output at once. Right now the only way I know how to do it is write a for loop for each column.

Brown Bear · Accepted Answer · 2020-07-26T17:38:40.180

10

looks like the simple way is

df[df == '?'].count()

the result is

colA    2
colB    1
colC    1
dtype: int64

where df[df == '?'] give us DataFrame with ? and Nan

  colA colB colC
0    ?  NaN    ?
1  NaN  NaN  NaN
2  NaN    ?  NaN
3  NaN  NaN  NaN
4    ?  NaN  NaN

and the count non-NA cells for each column.

Please, look on the other solutions: good readable and the most faster

edited Jul 26 '20 at 17:38

answered Jul 26 '20 at 17:01

Brown Bear

19,655
10
58
76

1

Nice solution. Just wonder what'll be the best way to print all rows/columns where '?' exist? (omitting all the NaN ones) – Danail Petrov Jul 26 '20 at 17:14
I tried add the dropna but it works slowly on my laptop, or what you mean? – Brown Bear Jul 26 '20 at 17:24
dropna() is definitely an option, but it will drop any row/column that contains any NaN and you won't get any response in this particular case. – Danail Petrov Jul 26 '20 at 18:24

Ch3steR · Answer 2 · 2021-04-21T08:45:35.663

You can use numpy.count_nonzero here.

pd.Series(np.count_nonzero(df.to_numpy()=='?', axis=0), index=df.columns)
# pd.Series((df.values == '?').sum(0), index=df.columns)

colA    2
colB    1
colC    1
dtype: int64

Timeit results:

Benchmarking with df of shape (1_000_000, 3)

big_df = pd.DataFrame(df.to_numpy().repeat(200_000,axis=0))
big_df.shape
(1000000, 3)

In [186]: %timeit pd.Series(np.count_nonzero(big_df.to_numpy()=='?', axis=0), index=big_df.columns)
53.1 ms ± 231 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [187]: %timeit big_df.eq('?').sum()
171 ms ± 7.42 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [188]: %timeit big_df[big_df == '?'].count()
314 ms ± 4.24 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [189]: %timeit pd.Series(np.apply_along_axis(lambda x: Counter(x)['?'], 0, big_df.values), index=big_df.columns)
174 ms ± 3.05 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

great solution on my machine it is the faster way of all – Brown Bear Jul 26 '20 at 17:29 — Brown Bear, Jul 26 '20 at 17:29

score 3 · Answer 3 · answered Jul 26 '20 at 17:15

3

We can do sum

df.eq('?').sum()
Out[182]: 
colA    2
colB    1
colC    1
dtype: int64

answered Jul 26 '20 at 17:15

BENY

317,841
20
164
234

score 1 · Answer 4 · edited Jul 26 '20 at 17:16

@Bear Brown Answer is probably the most elegant, a faster option is to use numpy:

from collections import Counter    

%%timeit
df[df == '?'].count()

5.2 ms ± 646 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
pd.Series(np.apply_along_axis(lambda x: Counter(x)['?'], 0, df.values), index=df.columns)

218 µs ± 19 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

score 0 · Answer 5 · answered Jun 04 '21 at 14:09

0

Variation to BENY's answer:

(df=='?').sum()
Out[182]: 
colA    2
colB    1
colC    1
dtype: int64

answered Jun 04 '21 at 14:09

Mishal Ahmed

191
2
11

How do I count specific values across multiple columns in pandas

5 Answers5

Linked