count and list by same datetime

Question

I have the following dataframe

date    route_name  route_id    stop_id arrival_time    departure_time
0   06.04.2022  836 452 2176    04.06.2022 5:05 04.06.2022 5:05
1   06.04.2022  836 452 2176    04.06.2022 5:20 04.06.2022 5:20
2   06.04.2022  836 452 2176    04.06.2022 5:35 04.06.2022 5:35
3   06.04.2022  836 452 2176    04.06.2022 5:35 04.06.2022 5:35
4   06.04.2022  Т73 1877    2176    04.06.2022 5:39 04.06.2022 5:39
5   06.04.2022  Т73 1877    2176    04.06.2022 5:44 04.06.2022 5:44
6   06.04.2022  Т73 1877    2176    04.06.2022 5:47 04.06.2022 5:47
7   06.04.2022  836 452 2176    04.06.2022 5:48 04.06.2022 5:48
8   06.04.2022  836 452 2176    04.06.2022 5:50 04.06.2022 5:50
9   06.04.2022  Т73 1877    2176    04.06.2022 5:52 04.06.2022 5:52

what I want to get is the table with

(1) each unique arrival time column,

(2) count each observation of the same arrival_time (like on 04.06.2022 5:35 there are 2 rows),

(3) list every route_name of the same datetime (if the datetime is the same for 2 different route_name fields, I need to get like "836, T73" in the column)

expected output

arrival_time       counts  routes
0   04.06.2022 10:02    3  836, T73, T80
1   04.06.2022 10:03    1  836
2   04.06.2022 10:04    1  T73
3   04.06.2022 10:06    1  T80
4   04.06.2022 10:08    1  T73

yes, `size` works great, thank you! now, how do I list the route_name values of the same datetime? — barney stinson 2, May 20 '22 at 06:11
your code does not what I need :) I tried this `df.groupby(['arrival_time']).size().reset_index(name='counts')` but it only counts the values, and I also want to have route_name values of the same datetime listed in each unique datetime row. that `','.join` thing in your code separates the characters (i.e. 836 > '8,3,6'), I don't need that — barney stinson 2, May 20 '22 at 06:17
Can you add it to question? I think all rows in final DataFrame, or need only one row? — jezrael, May 20 '22 at 06:20
Or need only processing duplicated rows, here `04.06.2022 5:35` ? — jezrael, May 20 '22 at 06:21
need all rows in final DataFrame. added output to the question — barney stinson 2, May 20 '22 at 06:23
`04.06.2022 10:02` ? I cannot see this datetime in input datetimes. — jezrael, May 20 '22 at 06:40
also can you add how is wrong ouput from `df.groupby(['arrival_time']).agg({'arrival_time':'size','route_name':','.join})` ? — jezrael, May 20 '22 at 06:41
oh, I don't know why, but now this code works, that's exactly what I need, thank you! could you please make it as an answer? — barney stinson 2, May 20 '22 at 06:45

count and list by same datetime

0 Answers0