Numpy Where Multiple Conditions , cannot compare a dtyped [object] array with a scalar of type [bool]

user_8462962 0
Viewed 250

I am trying to do multiple conditions in a numpy where but getting the error: cannot compare a dtyped [object] array with a scalar of type [bool]

Here is the line:

d7['CAD'] = np.where(d7['Category'] == 'Stack' & d7['Currency'] == fxratetable['from_currency'],d7['CAD'] * fxratetable['fx_rate'], d7['CAD'])

All dtypes are object except for fx_rate which is float64.

Also another thought is that I am looking for one value with Category = Stack but looking for multiple values with my Currency = from_currency.

Can anyone help with this?

Thanks.

new error

ValueError: Can only compare identically-labeled Series objects now.

This is my new statement

d7['CAD'] = np.where((d7['Category'] == 'Stack') & 
                     (d7['Currency'] == fxratetable['from_currency']),
                     d7['CAD'] * fxratetable['fx_rate'], 
                     d7['CAD'])

d7:

+--------------+----------+----------+
| CAD          | Currency | Category |
+--------------+----------+----------+
| -4350242.355 | GBP      | Stack    |
+--------------+----------+----------+
| 424223.7584  | AUD      | Stack    |
+--------------+----------+----------+

fxratetable:

+---------------+---------+
| from_currency | fx_rate |
+---------------+---------+
| GBP           | 1.367   |
+---------------+---------+
| AUD           | 0.7706  |
+---------------+---------+

Expected new CAD column.

+----------------+
| CAD (expected) |
+----------------+
| -5948957.275   |
+----------------+
| 326991.5663    |
+----------------+
numpy python
3 Answers

The issue is with the second part of the Boolean expression. Presumably d7 is more than just two rows, otherwise there wouldn't be this issue. d7['Currency'] == fxratetable['from_currency'] compares the two series row-by-row, and when the rows run out of fxratetable it doesn't know what to compare d7 to anymore.

I don't know if you are set on using NumPy or if there are limitations on what you can do, but a merge statement would handle this really easily:

# Merge the exchange rate
d7 = pd.merge(d7, fxratetable, left_on='Currency', right_on='from_currency', how='left')
# Find the rows which have a non-NaN exchange rate and multiply
d7['CAD'] = np.where(~np.isnan(d7['fx_rate']), d7['CAD']*d7['fx_rate'], d7['CAD'])
user_122291580

I personally prefer DataFrame.apply when dealing with conditions.

d7['CAD'] = d7.apply(lambda row: row.CAD * fxratetable[fxratetable.from_currency == row.Currency].fx_rate.item() \
                                 if row.Category == 'Stack' else row.CAD, axis=1)

fxratetable[fxratetable.from_currency == row.Currency] selects the row in fxratetable that corresponds to the correct currency, .fx_rate selects the rate column, and .item() gives the number, instead of a Series.

You can see that only the row.Category == 'Stack' condition remains, the problematic Series comparison is now part of the selection.

And finally, axis=1 is used to apply the function to DataFrame rows (docu).

I haven't tested this extensively, so let me know if it works.

user_150675580

I think this will be what you expected. I've written it in a comprehensible way.

d7 = pd.DataFrame({'CAD':[-4350242.355,424223.7584],'Currency':['GBP','AUD'],'Category':['Stack','Stack']})
fxratetable = pd.DataFrame({'from_currency':['GBP','AUD'],'fx_rate':[1.367,0.7706 ]})
_condition1 = d7['Category'] == 'Stack'
_condition2 = d7['Currency'] == fxratetable['from_currency']
d7['CAD (expected)'] = np.where(_condition1 & _condition2,d7['CAD'] * fxratetable['fx_rate'],d7['CAD'])
d7

OUTPUT enter image description here

user_115344930
Related