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

byMR

August 19, 2022

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.

>Solution :

Consedering the given dataframe :

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

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['Occur'] = df[df['Status'] == 'Sold'].groupby('Car')['Model'].transform('size')

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

filtered_df