How do I get unique values in a Pandas DataFrame that match criteria?

user_13012915 0
Viewed 256

Suppose I have the following DataFrame:

df_amazon = pd.DataFrame ({
    'order_no':[1, 2, 3],
    'category':['Books', 'Toys', 'Books'],
    'seller':['Amazon.com', 'AZ Toys', 'amazon'] })

I want to obtain all of the unique values in the seller column if they relate to Amazon.

In SQL, there is a LIKE Keyword. For example:

SELECT seller FROM df_amazon WHERE LOWER(seller) LIKE 'amazon%'

The code above will return all records where the lowercase text in the seller column starts with 'amazon'.

Is there something similar in Pandas?

I've tried the following:

df_amazon.loc[df_amazon.seller.str.contains('amazon')]

but this matches strictly on the third row and leaves out the first row with 'Amazon.com'.

I then tried the following:

df_amazon.loc[df_amazon.seller.str.lower().contains('amazon')]

but this returns an error: AttributeError: 'Series' object has no attribute 'contains'

I would like to quickly obtain all unique values where the seller is Amazon.

pandas dataframe python
2 Answers

Use case=False parameter in Series.str.contains with Series.unique:

df_amazon.loc[df_amazon.seller.str.contains('amazon', case=False), 'seller'].unique()

Your second solution is modified by another str:

df_amazon.loc[df_amazon.seller.str.lower().str.contains('amazon'), 'seller'].unique()
user_29010020

You have to have one more .str:

df_amazon.loc[df_amazon.seller.str.lower().str.contains('amazon')]

Or use case=False:

df_amazon.loc[df_amazon.seller.str.contains('amazon', case=False)]

Or specify the flags:

import re
df_amazon.loc[df_amazon.seller.str.lower().str.contains('amazon',  flags=re.IGNORECASE)]
user_87083640
Related