count number specific value within columns for each row in pandas

Question

Hello I have a dataframe such as :

Species COL1 COL2 COL3 COL4 COL5
SP1     0    0    0    1-2  0-1-2
SP2     1-2  2    0    1    0
SP3     0-1  1    2    0    1-2

and I would like to add new columns to count for each row the number of specific unique values such as :

Species COL1 COL2 COL3 COL4 COL5  count_0 count_1-2 count_0-1-2 count_1 count_2 
SP1     0    0    0    1-2  0-1-2 3       1         1           0       0
SP2     1-2  2    0    1    0     2       1         0           1       1
SP3     0-1  1    2    0    1-2   1       1         0           2       1

Does someone have na idea please ?

Does this answer your question? [Count occurrences of items in Series in each row of a DataFrame](https://stackoverflow.com/questions/24516361/count-occurrences-of-items-in-series-in-each-row-of-a-dataframe) — ouroboros1, Dec 04 '22 at 11:58
So, using the solution from the suggested duplicate and adding a prefix to the col names and then merging the result with the original `df`, you could use: `df = df.merge(df.set_index('Species').apply(pd.Series.value_counts, axis=1).fillna(0).add_prefix('count_'), left_on='Species', right_index=True)`. — ouroboros1, Dec 04 '22 at 12:03

score 1 · Accepted Answer · answered Dec 04 '22 at 12:15

Example

data = {'Species': {0: 'SP1', 1: 'SP2', 2: 'SP3'},
        'COL1': {0: '0', 1: '1-2', 2: '0-1'},
        'COL2': {0: '0', 1: '2', 2: '1'},
        'COL3': {0: '0', 1: '0', 2: '2'},
        'COL4': {0: '1-2', 1: '1', 2: '0'},
        'COL5': {0: '0-1-2', 1: '0', 2: '1-2'}}
df = pd.DataFrame(data)

Code

df1 = (df.set_index('Species').apply(lambda x: x.value_counts(), axis=1)
       .add_prefix('count_').fillna(0).astype('int'))

df1

         count_0    count_0-1   count_0-1-2 count_1 count_1-2   count_2
Species                     
SP1      3          0            1          0       1           0
SP2      2          0            0          1       1           1
SP3      1          1            0          1       1           1

make desired output

concat df & df1

pd.concat([df.set_index('Species'), df1], axis=1)

count number specific value within columns for each row in pandas

1 Answers1