CODEWITHSUNDEEP
pythonpandaspandas-groupby
machinery
machinery

Reputation: 6290

Selecting rows which match condition of group

I have a Pandas DataFrame df which looks as follows:

ID    Timestamp    x    y
1     10           322  222
1     12           234  542           
1     14           22   523
2     55           222  76
2     56           23   87
2     58           322  5436
3     100          322  345
3     150          22   243
3     160          12   765
3     170          78   65

Now, I would like to keep all rows where the timestamp is between 12 and 155. This I could do by df[df["timestamp"] >= 12 & df["timestamp"] <= 155]. But I would like to have only rows included where all timestamps in the corresponding ID group are within the range. So in the example above it should result in the following dataframe:

ID    Timestamp    x    y
2     55           222  76
2     56           23   87
2     58           322  5436

For ID == 1 and ID == 3 not all timestamps of the rows are in the range that's why they are not included.

How can this be done?

Upvotes: 1

Views: 591

Answers (2)

anky
anky

Reputation: 75150

Use transform with groupby and using all() to check if all items in the group matches the condition:

df[df.groupby('ID').Timestamp.transform(lambda x: x.between(12,155).all())]

   ID  Timestamp    x     y
3   2         55  222    76
4   2         56   23    87
5   2         58  322  5436

Upvotes: 2

w-m
w-m

Reputation: 11232

You can combine groupby("ID") and filter:

df.groupby("ID").filter(lambda x: x.Timestamp.between(12, 155).all())

   ID  Timestamp    x     y
3   2         55  222    76
4   2         56   23    87
5   2         58  322  5436

Upvotes: 3

Related Questions