Time Wheel in python3 pandas

Question

How can I create a timewheel similar to below with logon/logoff event times? Specifically looking to correlate mean login/logoff time correlated to the day of the week in a time wheel fashion? The Picture below is an example but I am looking for times going around the clock with days of the week where the times are now in the picture. I have python available to me and data sets that include login times. I would also like to correlate colors to user types such as admins vs regular users or something of that nature. Any thoughts on how to accomplish this would be great.

Some sample data is below in a pandas dataframe

df:

TimeGenerated        EventID  Username  Message
2012-04-01 00:00:13  4624     Matthew   This guy logged onto the computer for the first time today
2012-04-01 00:00:14  4624     Matthew   This guy authenticated for some stuff 
2012-04-01 00:00:15  4624     Adam      This guy logged onto the computer for the first time today
2012-04-01 00:00:16  4624     James     This guy logged onto the computer for the first time today
2012-04-01 12:00:17  4624     Adam      This guy authenticated for some stuff
2012-04-01 12:00:18  4625     James     This guy logged off the computer for the last time today
2012-04-01 12:00:19  4624     Adam      This guy authenticated for some stuff
2012-04-01 12:00:20  4625     Adam      This guy logged off the computer for the last time today 
2012-04-01 12:00:21  4625     Matthew   This guy logged off the computer for the last time today

Answer 1

Basically, you need to do 2 disjoint tasks:

• create a frequency table you are going to visualize
• define a function to visualize a given table

For the first task, I assume you need just a pivot table with weekdays and hours. I generate a random one:

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.cm as cm
import calendar

# generate the table with timestamps
np.random.seed(1)
times = pd.Series(pd.to_datetime("Nov 1 '16 at 0:42") + pd.to_timedelta(np.random.rand(10000)*60*24*40, unit='m'))
# generate counts of each (weekday, hour)
data = pd.crosstab(times.dt.weekday, times.dt.hour.apply(lambda x: '{:02d}:00'.format(x))).fillna(0)
data.index = [calendar.day_name[i][0:3] for i in data.index]
print(data.T)

It looks like this. Each number is a counter of logins at this time:

       Mon  Tue  Wed  Thu  Fri  Sat  Sun
col_0                                   
00:00   55   56   67   60   60   62   45
01:00   51   65   70   65   60   59   40
02:00   47   76   67   68   61   63   51
....

Now, let's draw the wheel for this table! It will consist of multiple pie charts:

# make a heatmap building function 
def pie_heatmap(table, cmap=cm.hot, vmin=None, vmax=None,inner_r=0.25, pie_args={}):
    n, m = table.shape
    vmin= table.min().min() if vmin is None else vmin
    vmax= table.max().max() if vmax is None else vmax

    centre_circle = plt.Circle((0,0),inner_r,edgecolor='black',facecolor='white',fill=True,linewidth=0.25)
    plt.gcf().gca().add_artist(centre_circle)
    norm = mpl.colors.Normalize(vmin=vmin, vmax=vmax)
    cmapper = cm.ScalarMappable(norm=norm, cmap=cmap)
    for i, (row_name, row) in enumerate(table.iterrows()):
        labels = None if i > 0 else table.columns
        wedges = plt.pie([1] * m,radius=inner_r+float(n-i)/n, colors=[cmapper.to_rgba(x) for x in row.values], 
            labels=labels, startangle=90, counterclock=False, wedgeprops={'linewidth':-1}, **pie_args)
        plt.setp(wedges[0], edgecolor='white',linewidth=1.5)
        wedges = plt.pie([1], radius=inner_r+float(n-i-1)/n, colors=['w'], labels=[row_name], startangle=-90, wedgeprops={'linewidth':0})
        plt.setp(wedges[0], edgecolor='white',linewidth=1.5)



plt.figure(figsize=(8,8))
pie_heatmap(data, vmin=-20,vmax=80,inner_r=0.2)

plt.show();

I hope this helps you.

Answer 2

Taking the data generation from @DavidDale's answer, one may plot a pcolormesh plot of the table on a polar axes. This would directly give the desired plot.

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import calendar

# generate the table with timestamps
np.random.seed(1)
times = pd.Series(pd.to_datetime("Nov 1 '16 at 0:42") + 
                  pd.to_timedelta(np.random.rand(10000)*60*24*40, unit='m'))
# generate counts of each (weekday, hour)
data = pd.crosstab(times.dt.weekday, 
                   times.dt.hour.apply(lambda x: '{:02d}:00'.format(x))).fillna(0)
data.index = [calendar.day_name[i][0:3] for i in data.index]
data = data.T

# produce polar plot
fig, ax = plt.subplots(subplot_kw=dict(projection='polar'))
ax.set_theta_zero_location("N")
ax.set_theta_direction(-1)

# plot data
theta, r = np.meshgrid(np.linspace(0,2*np.pi,len(data)+1),np.arange(len(data.columns)+1))
ax.pcolormesh(theta,r,data.T.values, cmap="Reds")

# set ticklabels
pos,step = np.linspace(0,2*np.pi,len(data),endpoint=False, retstep=True)
pos += step/2.
ax.set_xticks(pos)
ax.set_xticklabels(data.index)

ax.set_yticks(np.arange(len(data.columns)))
ax.set_yticklabels(data.columns)
plt.show()