Pandas not returning values in same row on conditional

Question

Let's say I create a simple dataframe and I add on some columns

import pandas as pd

data = {
    'price': [6, 5.5, 5, 4.8],
    'amount': [10, 12, 8, 6]
}
df = pd.DataFrame(data=data)
df['total'] = df.price * df.amount
df['running_total'] = df.total.cumsum().round(2)

my dataframe now looks like this:

   amount  price  total  running_total
0      10    6.0   60.0           60.0
1      12    5.5   66.0          126.0
2       8    5.0   40.0          166.0
3       6    4.8   28.8          194.8

I want the row from the dataframe with running total just under 160:

row_under_160 = df[df['running_total'] < 160].max()

This row (series) looks like this:

amount            12.0
price              6.0
total             66.0
running_total    126.0
dtype: float64

All the values look good EXCEPT PRICE. For some reason it's returning the top price (6.0) when it should return 5.5 which is the corresponding value.

Am I missing something/doing this entirely wrong?

jezrael · Accepted Answer · 2018-03-21 08:46:41Z

2

It working very nice, but max value is count from filtered DataFrame:

row_under_160 = df[df['running_total'] < 160]
print (row_under_160)
   amount  price  total  running_total
0      10    6.0   60.0           60.0
1      12    5.5   66.0          126.0

Get max values of each column:

print (row_under_160.max())
amount            12.0
price              6.0
total             66.0
running_total    126.0
dtype: float64

But it seems need index of filtered DataFrame where running_total is max by idxmax and select by loc:

print (row_under_160['running_total'].idxmax())
1

print (row_under_160.loc[row_under_160['running_total'].idxmax()])
amount            12.0
price              5.5
total             66.0
running_total    126.0
Name: 1, dtype: float64

If need one row DataFrame add []:

print (row_under_160.loc[[row_under_160['running_total'].idxmax()]])
   amount  price  total  running_total
1      12    5.5   66.0          126.0

Or compare by max value:

print (row_under_160[row_under_160['running_total'] == row_under_160['running_total'].max()])
   amount  price  total  running_total
1      12    5.5   66.0          126.0

edited Mar 21, 2018 at 8:46

answered Mar 21, 2018 at 8:41

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Lloyd_Xmas Over a year ago

This not only answered the question, but provided some great clarification for using methods on dataframes (I thought incorrectly that max was returning a row, but it was returning max values in all columns for the resulting dataframe). Thank you very much!!!

Collectives™ on Stack Overflow

Pandas not returning values in same row on conditional

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