[FIXED] How to group by hour in pandas and check data for every hour?

Issue

I have a dataframe df

hour  calls  received appointment
6:48  4        2         2
4:02  21       3         2
12:52 31       7         4
2:14  32       5         2
6:45  13       3         2

The hour column is string

I want to calculate the sum and group by the hour in the format like 1-2,2-3.

My approach would be:

df[['hour','calls','received','appointment']].groupby('hour').sum()   

Also, I would like to check for every hour, if data is not present for any hour, fill it by zero.

I want the output as :

hour calls received appointment
0-1   0     0         0
1-2   0     0         0
2-3   32    5         2
3-4   0     0         0
4-5   21    3         2
5-6   0     0         0
6-7   17    5         4
...

Solution

You can use pandas.resmaple base hour then compute sum on ['calls','received','appointment'] and at the end rename datetime to desired format.

df['time'] = pd.to_datetime(df['hour'])
df = df.set_index('time').resample('H')[['calls','received','appointment']].sum().reset_index()

# rename 2022-07-24 02:00:00 -> (2-3)
df['time'] = df['time'].apply(lambda x: f"{x.hour}-{x.hour+1}")
print(df)

---

     time  calls  received  appointment
0     2-3     32         5            2
1     3-4      0         0            0
2     4-5     21         3            2
3     5-6      0         0            0
4     6-7     17         5            4
5     7-8      0         0            0
6     8-9      0         0            0
7    9-10      0         0            0
8   10-11      0         0            0
9   11-12      0         0            0
10  12-13     31         7            4

Answered By - I'mahdi