CODEWITHSUNDEEP
pythonpandasdataframe
Scott Woodhall
Scott Woodhall

Reputation: 305

Count number of occurrences of first column value respectively across each row

data = [['BAL', 'BAL', 'NO', 'DAL'], ['DAL', 'DAL', 'TEN', 'SF']]
df = pd.DataFrame(data)

I want to count the number of occurrences of the value in the first column in each row, across that row.

In this example, the number of times "BAL" appears in the first row, "DAL" in the second row, etc.

Then assign that count to a new column df['Count'].

Upvotes: 0

Views: 368

Answers (2)

Henry Ecker
Henry Ecker

Reputation: 35636

We can compare the first column to all the remaining columns with DataFrame.eq then sum across the rows to add up the number of True values (matches):

df['count'] = df.iloc[:, 1:].eq(df.iloc[:, 0], axis=0).sum(axis=1)

df:

     0    1    2    3  count
0  BAL  BAL   NO  DAL      1
1  DAL  DAL  TEN   SF      1

*Note this output is slightly different than the accepted answer in that it does not include the column containing the reference values in the row count.

Upvotes: 3

Scott Boston
Scott Boston

Reputation: 153460

You could do something like this:

df.assign(count=df.eq(df.iloc[:,0], axis=0).sum(axis=1))

Create a series using iloc of the first column of your dataframe, then compare values use pd.DataFrame.eq with axis=0 and sum along axis=1.

Output:

     0    1    2    3  count
0  BAL  BAL   NO  DAL      2
1  DAL  DAL  TEN   SF      2

Upvotes: 3

Related Questions