Find number of entries per hour over 24 hour

Q&A
#1

need to find a number left from the station of bike per hour

image
image832×233 60.7 KB

#2

Hi @inform.swapnilnikam,

Can you please add more details to your post? This would allow us to help you better. If this is your personal project, I would recommend you to attach the jupyter notebook file (.ipynb) and the dataset file to your post.

Best,
Sahil

#3

Hi Sahil,

Based on above data set i need to find number of bikes left from station 3049 during day per hour.
Suppose consider station Id(3049) first bike left at 3:10 so it should show me 1 bike between 3-4.
Similarly for others for 0-24 hr.

#4

Hi @inform.swapnilnikam,

I hope this is what you are trying to achieve.

import pandas as pd

df = pd.DataFrame({
    'start_station': [3049, 3049, 3049, 3042, 3042],
    'Start_date': ['2019-10-01'] * 4 + ['2019-10-02'] * 1,
    'start_time': ['03:03:10', '03:07:43', '03:17:30', '04:18:20', '04:22:09'],
    'end_station': [3038, 3029, 4416, 3006, 4314],
    'End_date': ['2019-10-01'] * 4 + ['2019-10-02'] * 1,
    'end_time': ['03:28:40', '03:09:44', '03:32:01', '04:22:34', '04:42:53'],
    'bike_id': [16580, 19587, 15625, 12450, 17519]
})

df['start_date_hour'] = (df['Start_date'] + ' ' + df['start_time']).apply(pd.to_datetime).apply(lambda x: x.replace(second = 0, minute = 0))

n_df = df[['start_station', 'start_date_hour', 'bike_id']].groupby(['start_station', 'start_date_hour']).count().reset_index()

n_df.columns = n_df.columns.str.replace('bike_id', '#bikes')

print(n_df)

Output

   start_station     start_date_hour  #bikes
0           3042 2019-10-01 04:00:00       1
1           3042 2019-10-02 04:00:00       1
2           3049 2019-10-01 03:00:00       3

Best,
Sahil

1 Like