CODEWITHSUNDEEP
pythonpandasdataframe
amstergc20
amstergc20

Reputation: 117

Pandas counting column values based on name

10  C   X   -
11  C   X   X
12  C   X   -
13  C   X   -
14  C   X   -
15  C   X   -
16  R   -   -
17  R   -   -
18  R   -   -
19  L   -   -
20  M   -   -
21  L   -   -
22  S   -   -
23  S   -   -
24  S   -   -
25  S   -   -
26  S   -   -
27  S   -   -
28  S   -   -
29  L   -   -
30  L   -   -
31  M   -   -
32  M   -   -
33  C   X   -

I have this data set and I want to count how many Xs for each letter value.

So for C I have this many Xs in column one and column 2

Upvotes: 1

Views: 55

Answers (1)

jezrael
jezrael

Reputation: 862406

Convert column col1 to index, compare all another columns by DataFrame.eq for boolean mask, convert to integers and for count is used sum per first level (here col1):

df1 = df.set_index('col1').eq('X').astype('int').sum(level=0).reset_index()
print (df1)
  col1  col2  col3
0    C     7     1
1    R     0     0
2    L     0     0
3    M     0     0
4    S     0     0

Upvotes: 3

Related Questions