0

sorry if my title is not clear.

I am trying to get a count of how many people park at a distinct time in a distinct area.

I have this dataframe.

 UserId | Postcode | Time | 
   A        3000     20:00
   B        3000     20:00
   C        3100     20:00
   D        3000     1:00   
   E        1000     5:00
   F        4000     10.00

I have sliced the time to the nearest hour, so 13.20pm get sliced into 13.00 for easy grouping.

I want to count number of users per postcode at distinct hour.

For example, There is 2 users who parked at 20:00 - 21:00 at postcode 3000.

Here is I want for the result

 Count | Postcode | Time
   1      1000       5:00
   2      3000      20:00
   1      3000       1.00
   1      3100      20:00
   1      4000      10.00

Thank you

cs95
  • 379,657
  • 97
  • 704
  • 746
Phurich.P
  • 1,376
  • 5
  • 18
  • 33

1 Answers1

2

You can use groupby and count

df.groupby(['Postcode', 'Time']).UserId.count().reset_index()

You get

    Postcode    Time    UserId
0   1000        5:00    1
1   3000        1:00    1
2   3000        20:00   2
3   3100        20:00   1
4   4000        10.00   1
Vaishali
  • 37,545
  • 5
  • 58
  • 86
  • Using or not using UserId there won't change anything. `df.groupby(['Postcode', 'Time']).count().reset_index()` You can also replace this `count()` with `size()`, just keep in mind size will include `NaN` values while `count()` won't. – ksha Aug 14 '17 at 04:35