Iam newbie in python. I have numerous data. I need to eliminate some of them by time. I have "first" and "last" appear ID. example ID_A first appeared at 17:31:14 and last appeared at 17:31:25.
my data looks like this:
df
ID Time
ID_A 17:31:14
ID_A 17:31:25
ID_B 10:16:55
ID_B 15:50:15
ID_C 12:34:57
ID_C 12:41:07
ID_D 15:37:47
ID_D 15:37:56
ID_E 16:25:00
ID_E 18:37:13
ID_F 11:35:05
ID_F 11:40:15
I want to eliminate the ID's based on the time. I consider dropping the ID's row data first and last appear less than 2 minutes and above than 60 minutes. After eliminating, I want to sort the IDs by the time value.
my expected result looks like this:
ID Time
ID_F 11:35:05
ID_F 11:40:15
ID_C 12:34:57
ID_C 12:41:07
please help me on how to do that. Thank you
IIUC using ptp from numpy , then using isin after between
import numpy as np
df.Time=pd.to_timedelta(df.Time)
s=df.groupby('ID')['Time'].apply(np.ptp).dt.total_seconds().between(2*60,90*60)
df_sub=df.loc[df.ID.isin(s[s].index)]
df_sub
Out[35]:
ID Time
4 ID_C 12:34:57
5 ID_C 12:41:07
10 ID_F 11:35:05
11 ID_F 11:40:15
Now let us look at the sort part , i am using transform to get the min of each group ID, the using argsort get the position
df_sub=df_sub.iloc[df_sub.groupby('ID')['Time'].transform('min').argsort()]
Out[38]:
ID Time
10 ID_F 11:35:05
11 ID_F 11:40:15
4 ID_C 12:34:57
5 ID_C 12:41:07