0

I want to group by Id and Vehicle and then return only vehicles whose Auction have only 'Merged Salvage' and 'Merged Salvage API' values. Vehicles who have Auction other than these two types will get filtered out. How can I achieve this?

Data:

Id      Vehicle            Auction      Offer
10  1984 Porsche 944     Copart             100
10  1984 Porsche 944     Merged Salvage     200
10  1984 Porsche 944     IAA                140
11  2000 Chevrolet G3500 Copart              80
11  2000 Chevrolet G3500 Merged Salvage     100
11  2000 Chevrolet G3500 Merged Salvage API 120
11  2000 Chevrolet G3500 SVP                130
12  2001 Honda CRV       Merged Salvage      50
12  2001 Honda CRV       Merged Salvage API 100

Expected output:

Id      Vehicle            Auction      Offer
12  2001 Honda CRV       Merged Salvage      50
12  2001 Honda CRV       Merged Salvage API 100
Improve this question
1
  • Can you please show your attempts at solving this? Commented Aug 22, 2017 at 10:31

1 Answer 1

Reset to default
1

I believe a df.groupby operation, followed by dfGroupBy.transform should do it:

In [1097]: status = ['Merged Salvage', 'Merged Salvage API']

In [1098]: df[df.groupby(['Id', 'Vehicle']).Auction\
                 .transform(lambda x: x.isin(status).min())]
Out[1098]: 
   Id         Vehicle             Auction  Offer
7  12  2001 Honda CRV      Merged Salvage     50
8  12  2001 Honda CRV  Merged Salvage API    100

The predicate checks for the status using pd.Series.isin.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.