My data contained the time when the field force visited a client. What I need to do, is to compute for each day and for each client the occurrences of the visits (in between specific time ranges - for example, every 15minutes from 8am to 8pm.) Ideally, to draw the distribution of an histogram with the time interval on the x-axis and the occurrences on the y-axis.

This how my current data frame looks like:

The output should be something like this, than I can use to plot an histogram:

Thanks in advance!

CodePudding user response：

Admittedly hacky, but should work. If anyone has a better solution, please let me know. This would be way easier if you had actual date-times instead of a mix between a time interval and day names.

Here is the data I am using:

df = pd.DataFrame({'Client':['A', 'A', 'B', 'B', 'B', 'B'],
                   'Hour': ['11:14:48', '11:24:34', '15:34:34', '13:34:35', '15:10:22', '15:01:02'],
                   'Day':['Monday', 'Monday', 'Tuesday', 'Tuesday', 'Tuesday', 'Tuesday']})

Here the code:

TIME_START = '08:00:00'
TIME_END = '20:00:00'
INTERVAL = '15min'

def reindex_by_date(df):
    df['Hour'] = pd.to_datetime('1970-1-1 '   df['Hour'].astype(str))
    dt_index = pd.DatetimeIndex(pd.date_range(start=f'1970-1-1 {TIME_START}', end=f'1970-1-1 {TIME_END}', freq=INTERVAL))
    resampled_df = df.resample('15min', on='Hour').count().reindex(dt_index).fillna(0).rename(columns={'Hour':'Occurrences'}).rename_axis('Hour').reset_index()
    resampled_df['Client'] = df['Client'].iat[0]
    resampled_df['Day'] = df['Day'].iat[0]
    resampled_df['Hour'] = resampled_df['Hour'].dt.strftime('%H:%M:%S')   ' - '   (resampled_df['Hour']   pd.Timedelta(minutes=15)).dt.strftime('%H:%M:%S')
    return resampled_df.rename(columns={'Hour':'Interval'})

result = df.groupby(['Client', 'Day'], as_index=False).apply(reindex_by_date).reset_index(0, drop=True)

result looks like this:

               Interval Client  Occurrences      Day
0   08:00:00 - 08:15:00      A          0.0   Monday
1   08:15:00 - 08:30:00      A          0.0   Monday
2   08:30:00 - 08:45:00      A          0.0   Monday
3   08:45:00 - 09:00:00      A          0.0   Monday
4   09:00:00 - 09:15:00      A          0.0   Monday
..                  ...    ...          ...      ...
44  19:00:00 - 19:15:00      B          0.0  Tuesday
45  19:15:00 - 19:30:00      B          0.0  Tuesday
46  19:30:00 - 19:45:00      B          0.0  Tuesday
47  19:45:00 - 20:00:00      B          0.0  Tuesday
48  20:00:00 - 20:15:00      B          0.0  Tuesday

[98 rows x 4 columns]

While the nonzero entries are:

               Interval Client  Occurrences      Day
12  11:00:00 - 11:15:00      A          1.0   Monday
13  11:15:00 - 11:30:00      A          1.0   Monday
22  13:30:00 - 13:45:00      B          1.0  Tuesday
28  15:00:00 - 15:15:00      B          2.0  Tuesday
30  15:30:00 - 15:45:00      B          1.0  Tuesday