Count occurrences of items in Series in each row of a DataFrame

Question

I have a pandas.DataFrame that looks like this.

COL1    COL2    COL3
C1      None    None
C1      C2      None
C1      C1      None
C1      C2      C3

For each row in this dataframe I would like to count the occurrences of each of C1, C2, C3 and append this information as columns to this dataframe. For instance, the first row has 1 C1, 0 C2 and 0 C3. The final data frame should look like this

COL1    COL2    COL3    C1  C2  C3
C1      None    None    1   0   0
C1      C2      None    1   1   0
C1      C1      None    2   0   0
C1      C2      C3      1   1   1

So, I have created a Series with C1, C2 and C3 as the values - one way top count this is to loop over the rows and columns of the DataFrame and then over this Series and increment the counter if it matches. But is there an apply approach that can achieve this in a compact fashion?

score 22 · Accepted Answer · answered Jul 01 '14 at 18:01

You could apply value_counts:

In [11]: df.apply(pd.Series.value_counts, axis=1)
Out[11]: 
   C1  C2  C3  None
0   1 NaN NaN     2
1   1   1 NaN     1
2   2 NaN NaN     1
3   1   1   1   NaN

So you can fill the NaN and applend just the base values you want:

In [12]: df.apply(pd.Series.value_counts, axis=1)[['C1', 'C2', 'C3']].fillna(0)
Out[12]: 
   C1  C2  C3
0   1   0   0
1   1   1   0
2   2   0   0
3   1   1   1

Note: there's an open issue to have a value_counts method directly for a DataFrame (which I think should be introduced by pandas 0.15).

Actually this is a ridiculously cool answer. To the point. No distraction. Helpful. — Private, Jul 12 '15 at 08:11

score 4 · Answer 2 · answered Jul 01 '14 at 18:14

Andy's answer is spot on.

I'm adding this answer, if C1,C2...Cn list is huge and we want to view only subset of them.

dff = df.copy()
dff['C1']=(df == 'C1').T.sum()
dff['C2']=(df == 'C2').T.sum()
dff['C3']=(df == 'C3').T.sum()
dff
  COL1  COL2  COL3  C1  C2  C3
0   C1  None  None   1   0   0
1   C1    C2  None   1   1   0
2   C1    C1  None   2   0   0
3   C1    C2    C3   1   1   1

score 1 · Answer 3 · answered Jun 17 '19 at 22:18

Usually apply + serise function to whole dataframe will slowing down the whole process , Additional Reading : Link

df.mask(df.eq('None')).stack().str.get_dummies().sum(level=0)
Out[165]: 
   C1  C2  C3
0   1   0   0
1   1   1   0
2   2   0   0
3   1   1   1

Or you can do with Counter

from  collections import Counter

pd.DataFrame([ Counter(x) for x in df.values]).drop('None',1)
Out[170]: 
   C1   C2   C3
0   1  NaN  NaN
1   1  1.0  NaN
2   2  NaN  NaN
3   1  1.0  1.0

Count occurrences of items in Series in each row of a DataFrame

3 Answers3

Linked

Related