I have a pandas dataframe

Here I have events at two levels L1 eg: L1_1,L1_2 and L2

How to calculate number of L2 events between L1 event pairs for each ID

ie Here for ID A9 , there is one L2_1 between L1_3 and L1_8

CodePudding user response：

Idea is to sort the api_timestamp column and find the L2 event between L1 event by comparing the index of L2 event and the max and min index of L1 event.

df['api_timestamp'] = pd.to_datetime(df['api_timestamp'])

for name, g in df.dropna(subset='event_level').sort_values('api_timestamp', ignore_index=True).groupby('ID'):
    l1_indices = g[g['event_level'].str[:2].eq('L1')].index.tolist()
    l2_indices = g[g['event_level'].str.startswith('L2')].index.tolist()
    l2_between = [l2 for l2 in l2_indices if l2 > min(l1_indices) and l2 < max(l1_indices)]
    print(name, len(l2_between))

A9 1
B1 0