Pythonic way to group by a pandas table

user_4360433 0
Viewed 66

I have the following table:

    dt  type    item
0   2021-02-08 20:43:15 Requesting  1111
1   2021-02-08 20:43:25 Response    1111
2   2021-02-08 20:43:15 Requesting  2222
3   2021-02-08 20:43:27 Response    2222
4   2021-02-08 20:43:16 Requesting  3333
5   2021-02-08 20:43:19 Response    3333
6   2021-02-08 20:43:16 Requesting  4444
7   2021-02-08 20:43:21 Response    4444
8   2021-02-08 20:43:16 Requesting  5555
9   2021-02-08 20:43:23 Failed      5555
8   2021-02-08 20:43:17 Requesting  5555
9   2021-02-08 20:43:29 Response    5555

It is already sorted by item and then second sort by time.

I want to do a group-by and get a new table that will have the amount of time it took between a request to the response, and another column for how many rows it included (so for item 5555 it took 4 rows)

What's a good pythonic way to do it?

pandas python
1 Answers

Since your DataFrame is already sorted on Item and time, we just need to add a column that indicates the row number (with a simple range(len(df)))

Then we can groupby head and tail (also can drop_duplicates with keep='first' or 'last') to get the first request (time and row number) and the last response and subtract (aligning on 'item') to find the time difference and number of rows.

#df = df.sort_values(['item', 'dt'])
df['dt'] = pd.to_datetime(df['dt'])
df['Nrows'] = range(len(df))

# First request row
reqs = (df.query('type == "Requesting"')
          .groupby('item').head(1)
          .set_index('item').drop(columns='type'))

# Last response row
resp = (df.query('type == "Response"')
          .groupby('item').tail(1)
          .set_index('item').drop(columns='type'))

result = resp - reqs
result['Nrows'] = result['Nrows']+1   # To get counting correct

                  dt  Nrows
item                       
1111 0 days 00:00:10      2
2222 0 days 00:00:12      2
3333 0 days 00:00:03      2
4444 0 days 00:00:05      2
5555 0 days 00:00:13      4
user_43333590
Related