Liza Liza -4 years ago 116
Python Question

How to delete a group of rows from pandas data frame under specific conditions?

I am dealing with a pandas data frame as shown below.

ProjID Xcoord Ycoord
0 2 -7.863509 5.221327
1 2 some_val some_val
2 2 some_val some_val
3 2 some_val some_val
4 2 some_val some_val
5 0 0 0
6 3 some_val some_val
7 3 some_val some_val
8 0 0 0
9 5 some_val some_val
10 5 some_val some_val
11 5 some_val some_val
12 0 0 0
13 6 some_val some_val
14 6 some_val some_val
15 6 some_val some_val
16 6 some_val some_val


My original data frame was the data frame without the rows with all '0' values. As per the project requirement I had to insert the rows with all 0's value whenever the "ProjID" changed ( How to insert a row in a data frame under specific conditions? )

Now as per my further requirements, I want to delete all the rows of any "ProjID" which has 3 and less than 3 rows. From the above data frame, I would want to delete all the respective rows of ProjID- "3" and "5" . My resultant data frame should look like below:

ProjID Xcoord Ycoord
0 2 -7.863509 5.221327
1 2 some_val some_val
2 2 some_val some_val
3 2 some_val some_val
4 2 some_val some_val
5 0 0 0
6 6 some_val some_val
7 6 some_val some_val
8 6 some_val some_val
9 6 some_val some_val


Kindly suggest me a way to obtain this result. Also, whether I should first drop the rows of the ProjID's with <=3 rows and then insert 0's whenever the ProjID changes or vice-versa ?

Thanks in advance.

Answer Source

The simplest answer is to remove the zero rows because they may get in the way of the calculation if you have more than 3 of them. then do a group by. then filter. then add back zeros like you did in other question/answer

d1 = df.query('ProjID != 0').groupby('ProjID').filter(lambda df: len(df) > 3)
d1

    ProjID     Xcoord    Ycoord
0        2  -7.863509  5.221327
1        2   some_val  some_val
2        2   some_val  some_val
3        2   some_val  some_val
4        2   some_val  some_val
13       6   some_val  some_val
14       6   some_val  some_val
15       6   some_val  some_val
16       6   some_val  some_val

Then add back

pidv = d1.ProjID.values
pid_chg = np.append(pidv[:-1] != pidv[1:], True)

i = d1.index.repeat(pid_chg + 1)

d2 = d1.loc[i, :].copy()

d2.loc[i.duplicated()] = 0

d2.reset_index(drop=True)

    ProjID     Xcoord    Ycoord
0        2  -7.863509  5.221327
1        2   some_val  some_val
2        2   some_val  some_val
3        2   some_val  some_val
4        2   some_val  some_val
5        0          0         0
6        6   some_val  some_val
7        6   some_val  some_val
8        6   some_val  some_val
9        6   some_val  some_val
10       0          0         0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download