Python pandas - Groupby + Conditional count of cells values

user_14330270 0
Viewed 54

I have a table that contains the list of parcel ids, their departure time, arrival time and type or parcel.

A minimum working example is given below to illustrate the table.

For each line, i am trying to get the number of parcels of similar type (i.e. TV or PC) which departure time is superior or equal to [the departure time of the considered line] and strictly inferior to [the arrival time of the considered line]

Example of input data

Parcel_id, departure_time, arrival_time, type
id_1, 07:00, 07:30, TV
id_2, 07:00, 07:15, PC
id_3, 07:05, 07:22, PC
id_4, 07:10, 07:45, TV
id_5, 07:15, 07:50, TV
id_6, 07:10, 07:26, PC
id_7, 07:40, 08:10, TV
id_8, 07:14, 07:46, TV
id_9, 07:14, 07:32, PC
id_10, 07:15, 07:30, PC

Example of desired output data

Parcel_id, departure_time, arrival_time, type, number_of_parcels
id_1, 07:00, 07:30, TV, 4
id_2, 07:00, 07:15, PC, 4
id_3, 07:05, 07:22, PC, 4
id_4, 07:10, 07:45, TV, 4
id_5, 07:15, 07:50, TV, 2
id_6, 07:10, 07:26, PC, 3
id_7, 07:40, 08:10, TV, 1
id_8, 07:14, 07:46, TV, 3
id_9, 07:14, 07:32, PC, 2
id_10, 07:15, 07:30, PC, 1

I am trying to use the groupby function and then apply conditions....without any success

table['number_of_parcels']= table.groupby(['type']).cond.apply(lambda g: (g>=table['departure`_time'] & g<table['arrival_time'])).count()

Does anyone have any idea on how to crack this ?

Thanks a lot

pandas-groupby counting conditional-statements python
1 Answers

This works

df['number_of_parcels'] = df.groupby('type').apply(lambda x: x.apply(lambda y:(
    (x['departure_time'] >= y['departure_time']) & (x['departure_time'] < y['arrival_time'])
    ).sum(), axis=1)).droplevel(level=0)
df

Out:

  Parcel_id departure_time arrival_time type  number_of_parcels
0      id_1          07:00        07:30   TV                  4
1      id_2          07:00        07:15   PC                  4
2      id_3          07:05        07:22   PC                  4
3      id_4          07:10        07:45   TV                  4
4      id_5          07:15        07:50   TV                  2
5      id_6          07:10        07:26   PC                  3
6      id_7          07:40        08:10   TV                  1
7      id_8          07:14        07:46   TV                  3
8      id_9          07:14        07:32   PC                  2
9     id_10          07:15        07:30   PC                  1
user_142777220
Related