CODEWITHSUNDEEP
pythonpandas
Rahul Agarwal
Rahul Agarwal

Reputation: 4100

Filter rows with only Zero values with 2 columns

Sample DF:

ID   Name        Price     Amount      Fit_Test
1    Apple         10      15        Super_Fit
2    Apple         10      0          Super_Fit
3    Apple         10      0          Super_Fit
4    Orange        12      20          Not_Fit
5    Orange        12      0          Not_Fit
6    Banana        15      17          Medium_Fit
7    Banana        15      0         Medium_Fit
8    Pineapple     25      19          Medium_Fit
9    Pineapple     25      18          Medium_Fit
10   Cherry        30      56         Super_Fit
11   XXX           50      0          Medium_Fit
12   XXX           50      0          Medium_Fit

Expected DF:

    ID   Name        Price     Amount           Fit_Test
1    Apple         10           15              Super_Fit
2    Apple         10           0               Super_Fit
3    Apple         10           0               Super_Fit
4    Orange        12           20              Not_Fit

6    Banana        15           17              Medium_Fit

8    Pineapple     25           19              Medium_Fit
9    Pineapple     25           18              Medium_Fit
10   Cherry        30           56              Super_Fit
11   XXX           50            0          Medium_Fit
12   XXX           50             0          Medium_Fit

Problem Statement:

I want to group-by by Name and Price and then filter based on Amount with Fit_Test as conditional column.

  1. If Fit_Test is Super_Fit then don't the no operation is needed. (Rows 1,2,3 and 10 is same in Input and Expected DF)

  2. If within a Name and Price conditions and Fit_Test is not Super_Fit, check if it has a Amount as 0 then and only then remove that row (ID 4,5,6,7 and in Expected 5 & 7 are deleted)

  3. If within a Name and Price conditions & Fit_Test is not Super_Fit if contract amount has values greater than zero then don't remove any rows (Rows 8 & 9 is same in Input and Expected DF)

  4. If within a Name and Price conditions & Fit_Test is not Super_Fit if contract amount has all values equal to zero then don't remove any rows (Rows 11 & 12 is same in Input and Expected DF)

I can do solution where it removes all zero but no help with conditional column

Upvotes: 1

Views: 73

Answers (1)

jezrael
jezrael

Reputation: 862611

You can chain 2 conditions - compare Fit_Test and check if all Trues per groups by GroupBy.transform and GroupBy.all and for second compare for not equal:

m1 = df['Fit_Test'].eq('Super_Fit').groupby([df['Name'],df['Price']]).transform('all')
m2 = df['Amount'].ne(0)

df = df[m1 | m2]
print (df)
   ID       Name  Price  Amount    Fit_Test
0   1      Apple     10      15   Super_Fit
1   2      Apple     10       0   Super_Fit
2   3      Apple     10       0   Super_Fit
3   4     Orange     12      20     Not_Fit
5   6     Banana     15      17  Medium_Fit
7   8  Pineapple     25      19  Medium_Fit
8   9  Pineapple     25      18  Medium_Fit
9  10     Cherry     30      56   Super_Fit

Upvotes: 1

Related Questions