Python Pandas Sumif by conditional column value

Question

I have the following dataframe and I am trying to add a new column df['count'] that returns the count from each row based on the condition specified in df['E'].

df = pd.DataFrame({"A": [14, 4, 5, 4, 1],
                   "B": [5, 2, 54, 3, 2],
                   "C": [20, 20, 7, 3, 8],
                   "D": [14, 3, 6, 2, 6],
                   "E":[14, 2, 5, 3, 1]})

The output would look something like this:

    A   B   C   D   E  count
0  14   5  20  14  14      2
1   4   2  20   3   2      1
2   5  54   7   6   5      1
3   4   3   3   2   3      2
4   1   2   8   6   1      1

I think the function should look something like df['count'] = df[df == df['E']].count(axis=1). However, that is only returning 0s and includes df['E] in the output.

mozway · Accepted Answer · 2021-10-17 16:01:16Z

1

You could use:

df['count'] = df.drop('E', axis=1).eq(df['E'], axis=0).sum(axis=1)

or:

# count all including E and remove 1
df['count'] = df.eq(df['E'], axis=0).sum(axis=1).sub(1)

output:

    A   B   C   D   E  count
0  14   5  20  14  14      2
1   4   2  20   3   2      1
2   5  54   7   6   5      1
3   4   3   3   2   3      2
4   1   2   8   6   1      1

note on the speed

Timing on 500k rows:

%%timeit
df.drop('E', axis=1).eq(df['E'], axis=0).sum(axis=1)
# 7.27 ms ± 324 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
df.eq(df['E'], axis=0).sum(axis=1).sub(1)
# 5.88 ms ± 335 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
df.apply(lambda x:sum(x["A":"D"]==x["E"]), axis=1)
# still running after 5 minutes… (takes ~10s on only 50k rows)

edited Oct 17, 2021 at 16:01

answered Oct 17, 2021 at 15:34

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nikeros Over a year ago

the last one you proposed is by far the most elegant and fast

mozway Over a year ago

Thanks! Yes, I assumed it would be faster to just subtract 1 than to regenerate a dataframe in memory ;)

mozway Over a year ago

@nikeros I added some timings

nikeros · Accepted Answer · 2021-10-17 15:35:29Z

0

An alternative could be:

df.loc[:,"count"]=df.apply(lambda x:sum(x["A":"D"]==x["E"]), axis=1)

answered Oct 17, 2021 at 15:35

nikeros

3,3792 gold badges12 silver badges27 bronze badges

1 Comment

mozway Over a year ago

Using apply in such a simple case, when the code can be vectorized, is not efficient

Collectives™ on Stack Overflow

Python Pandas Sumif by conditional column value

2 Answers 2

note on the speed

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

note on the speed

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related