Plot multiple bar plots by groupby

Question

I would like to plot bar chart when the field force visited the clients. I have defined some time intervals and in the occurrences column, it refers to the number of time a client was visited during this time interval.

Now, what I would like to have, is 6 bar chart that represent the distribution per day (Monday to Saturday, thus 6) per client. Something like below:

The problem is, I have more than 375 clients and I would like to have a file output for each client with the graphs above.

This how my dataframe looks like:

Interval	Client	Occurrences	Day
08:00:00 - 08:15:00	A	1	Monday
...	...	...	...
19:45:00 - 20:00:00	A	10	Monday
08:00:00 - 08:15:00	A	2	Tuesday
...	...	...	...
08:00:00 - 08:15:00	B	4	Monday

And this is the code that gives me the output I would like to plot:

result.groupby(['Client','Day','Interval'])['Occurrences'].sum()

Thanks in advance!

Answer 1

I have created and coded something similar to the sample data presented. The outermost loop is processed by client and the inner rule process is processed by day of the week. However, the grouping results do not necessarily have the same interval name, so we join them with a common interval name and update the missing values as 0. At the end of the loop, the graph is saved by client name. I think a horizontal graph, rather than a vertical bar graph, would be more effective for visualization if the subplots are in chronological order, with interval values on the y-axis and counts on the x-axis. If you need a horizontal bar graph, please modify it yourself.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import random
import datetime

df = pd.DataFrame({'Date': pd.date_range('2021-01-01 08:00:00', '2021-04-30 20:00:00', freq='15min'),
                  'Client': random.choices(list('ABCDEFGHIJK'), k=11473),
                   'Occurrences': np.random.randint(0,10,11473)})

df.set_index('Date', inplace=True)
df = df.between_time('08:00:00', '19:45:00')
df.reset_index(inplace=True)

weekdays = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday']
df['day'] = df['Date'].apply(lambda x:weekdays[x.weekday()])

df['Interval'] = df['Date'].apply(lambda x:x.strftime('%Y-%m-%d %H:%M:%S')[-8:]+'-'+(x+datetime.timedelta(minutes=15)).strftime('%Y-%m-%d %H:%M:%S')[-8:])

idx_df = pd.DataFrame(df['Interval'].unique())
for c in df['Client'].unique():
    dff = df.query('Client == @c')
    fig, axs = plt.subplots(1,6, figsize=(20,15), sharey=True)
    fig.subplots_adjust(wspace=0.05)
    for wd, ax in zip(weekdays, axs.flatten()):
        dfs = dff.query('day == @wd').groupby(['Interval'])['Occurrences'].sum().to_frame('cnt')
        idx_df = pd.DataFrame(index=df['Interval'].unique())
        dfm = idx_df.merge(dfs, left_index=True, right_index=True, how='outer').fillna(0)
        ax.barh(np.arange(0,48,1), dfm['cnt'], height=0.9)
        ax.invert_yaxis() 
        ax.set_yticks(np.arange(0,48,1))
        ax.set_xticks(np.arange(0,35,5))
        ax.set_ylim(-0.5,47.5)
        ax.set_title(wd)
        fig.savefig(c+'.png')