How to get rows which only have a certain value after group by, in Pandas-CodePudding

For example after grouping two columns the result is:

Car 1. Nissan Purchased Sold

Car 2 Nissan Sold

Car 3 Nissan Purchased Sold Rented

I would like to retrieve the Cars which have ONLY been sold. So just return Car 2. However everything I have tried returns Car 1 and Car 3 as well, as they have both been sold as well.

CodePudding user response：

Consedering the given dataframe :

import pandas as pd

df = pd.DataFrame({"Car": ['Car 1', 'Car 2', 'Car 3', 'Car 2', 'Car 2'],
                   "Model": ['Nissan', 'Nissan', 'Nissan', 'Nissan', 'Nissan'],
                   "Status": ['Purchased', 'Sold', 'Sold', 'Sold', 'Rented']})

>>> `df`

You can get the cars that have been sold (and not purchased or rented) by running this code :

df['Occur'] = df[df['Status'] == 'Sold'].groupby('Car')['Model'].transform('size')

filtered_df = df.loc[df['Occur'] == 1][['Car', 'Model', 'Status']]

filtered_df

>>> df

>>> filtered_df

>>> `df`

>>> `filtered_df`