Data cleaning with np.where() - ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

user_11902119 0
Viewed 200

I'm looking to clean a column ('Price') in a pandas data frame.

I want to set any values in the 'Price' column that are larger than 24,000 and smaller than 28,000 to NaN.

I tried to do this using np.where(), but I get an error message.

Thanks for any help!

import pandas as pd
import numpy as np

cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
    'Price': [22000,25000,27000,35000]
    }

df = pd.DataFrame(cars, columns = ['Brand', 'Price'])


df['Price'] = np.where(( 24000 < df.Price < 28000), np.nan,df.Price)

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

# DESIRED OUTPUT 
            Brand  Price
0     Honda Civic  22000
1  Toyota Corolla  NaN
2      Ford Focus  NaN
3         Audi A4  35000
data-science pandas numpy python
1 Answers

In pandas exist function Series.between:

df['Price'] = np.where(df.Price.between(24000,28000, inclusive=False), np.nan,df.Price)
print(df)
            Brand    Price
0     Honda Civic  22000.0
1  Toyota Corolla      NaN
2      Ford Focus      NaN
3         Audi A4  35000.0

Or if want use conditions is necessary split them and chain by & for bitwise AND:

df['Price'] = np.where(( 24000 < df.Price) & (df.Price < 28000), np.nan,df.Price)
print(df)
            Brand    Price
0     Honda Civic  22000.0
1  Toyota Corolla      NaN
2      Ford Focus      NaN
3         Audi A4  35000.0
user_29010020
Related