get max and min values based on conditions in pandas dataframe

Question

I have a dataframe like this

count	A	B
yes	4900	0
yes	1000	1000
sum_yes	5900	1000
yes	4000	0
yes	1000	0
sum_yes	5000	0

I want result like this that is calculate max of column A and B only for rows where 'count' = 'sum_yes' if value of B =0 otherwise calculate minimum

count	A	B	Total
yes	4900	0	0
yes	1000	1000	0
sum_yes	5900	1000	1000
yes	4000	0	0
yes	1000	0	0
sum_yes	5000	0	5000

I have tried this so far:

df['Total'] = [df[['A', 'B']].where(df['count'] == 'sum_yes').max(axis=0) if 
                   'B'==0 else df[['A', 'B']]
                   .where(df['count'] == 'sum_yes').min(axis=0)]

But I am getting ValueError The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

Any idea how to solve this

Total in the first count row with 'sum_yes' should be 1000 i.e. min(5900, 1000) but youw show 0. — DarrylG
– DarrylG, Commented Mar 7, 2021 at 13:29

sammywemmy · Accepted Answer · 2021-03-07 13:04:54Z

3

You can use numpy.where:

new_values = np.where((df["count"] == "sum_yes") & (df.B == 0),
                       df.loc[:, ["A", "B"]].max(1),
                       df.loc[:, ["A", "B"]].min(1),
                      )

df.assign(Total = new_values)


     count     A     B  Total
0      yes  4900     0      0
1      yes  1000     0      0
2  sum_yes  5900  1000   1000
3      yes  4000  1000   1000
4      yes  1000     0      0
5  sum_yes  5000     0   5000

answered Mar 7, 2021 at 13:04

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

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

1 Comment

sammywemmy Over a year ago

Numpy where is similar to an if else clause... The first line is the condition, if the condition is met then the second line is selected, if it fails then the third line. Second and third lines simply pick the max or min of A and B

Collectives™ on Stack Overflow

get max and min values based on conditions in pandas dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related