Drop rows in a group when timestamp is higher then a row with a value in the same group

user_3880331 0
Viewed 56
  • Consider it is a pandas dataframe ordered by User and Timestamp
  • See the 1st table - There are two groups of users - User 1 and 2
  • The content Plus is part of User 1 and comes after the content END of User 1
  • The content Cast is part of User 2 and comes after the content END of User 2

Q: How do I drop all rows that could exist after the content END, based on it's timestamp for each user group like demonstrating in the 2nd table.

  1. Please, do not understand that I just want to drop the last row of each group. Imagine that I would have many others.

  2. I would not like want to use a loop and if/else structure for this purpose. Instead, I was trying without success something like this

df[df['timestamp'] < (df.groupby('user')['timestamp'].transform('max'))]

But I need to add the content condition (END), and consider the comparisons IN THE SAME GROUP. In this case it's been considered the entire df timestamp.

Regards

enter image description here

pandas-groupby pandas dataframe list-comprehension python
1 Answers
myDataFrame = pd.DataFrame(columns=["Content", "User", "Timestamp"], data=[["Airplane", 1, 1515356018],
                                                                           ["Car", 1, 1515356025],
                                                                           ["END", 1, 1515356027],
                                                                           ["Plus", 1, 1515356028],
                                                                           ["Train", 2, 1515356031],
                                                                           ["Trying", 2, 1515356039],
                                                                           ["View", 2, 1515356047],
                                                                           ["END", 2, 1515356050],
                                                                           ["Cast", 2, 1515356055]])

last_considered = myDataFrame["Timestamp"].where(myDataFrame['Content'] == "END").groupby(myDataFrame["User"]).transform("max").astype(int)
to_drop = myDataFrame[myDataFrame['Timestamp'] > last_considered]
to_keep = myDataFrame[myDataFrame['Timestamp'] <= last_considered]
user_38803310
Related