"The truth value of a Series is ambiguous" error when iterating through a dataframe

Question

I am attempting to add to a list "quantities" on the conditions that the column "Todays Price" is above the column "Target Price" in a dataframe entitled active_positions.

I tried the following code:

for index, row in active_positions.iterrows():
    if row['Todays Price'] >= row['Target Price']:
        quantities.append(row['Quantity'])

When I attempt this, I get the following error:

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

I've looked up other people who have this issue, and the advice I've seen is to use "&" instead of "and", which does not apply to this situation ( I don't think).

I've also seen the suggestion of using np.where(condition, effect-A, effect-B). This wouldn't work for me, as I don't want to add anything to the list "quantities" if the conditions are not met, and when I attempted to do:

for index, row in active_positions.iterrows():
above_target = row['Todays Price'] >= row['Target Price']
quantities.append(np.where(above_target, row['Quantity'],))

Traceback (most recent call last):

  File "<ipython-input-656-9c9f6030d250>", line 3, in <module>
    quantities.append(np.where(above_target, row['Quantity'],))

ValueError: either both or neither of x and y should be given

I know I could fix this by putting a zero after the "row['Quantity]" expression in the np.where clause, however like I said, I don't want to be adding zeroes to the quantities list.

Please advise, thank you!

Answer 1

In the interests of speed, you should not be attempting to iterate through your dataframe. If you only want values of Quantity where your condition is True, you can apply a mask as below:

import pandas as pd
import numpy as np

a = {'Todays Price': [1, 2, 1, 5, 6], 'Target Price': [1, 3, 2, 4, 3], 
     'Quantity': [10, 11, 12, 13, 15]}

df = pd.DataFrame(a)

quantities = df[df['Todays Price'] >= df['Target Price']]['Quantity']
quantities_list = quantities.values.tolist() # For pure Python list result

# Or perhaps more clearly for the same result:

mask = df['Todays Price'] >= df['Target Price']

quantities = df[mask]['Quantity']
quantities_list = quantities.values.tolist()