Back to Data Visualisation

Visualising the History of a Club

10/02/2020

A couple of years back I joined a Toastmasters club. Last year we celebrated our club's 20th anniversary, so I decided to pull data on the club from the club website and give a presentation on some statistics and visualisations of the club's history through its data.

I recently decided to tidy up my visualisations and make them into a blog post. There are a lot of aspects of an organisation which you might want to get insights on, and it can often be difficult to visually convey these aspects. In this post we will explore visualisations of

• People entering or leaving the organisation over time
• The length of people's stay within the organisaiton
• Current and cumulative total membership of the organisation
• The succession of roles people take on within the organisation It turns out that there are a lot of nice ways to plot these various aspects of an organisation. Each plot has its own strengths and weaknesses, and no single plot can tell you everything.

To start off, let's load up the libraries and data we'll need, and look at the dtypes of the data columns. I won't show any of the actual data here for privacy reasons.

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import re
from collections import Counter
import matplotlib
import datetime as dt
import seaborn as sns
from IPython.display import display
from collections import defaultdict

plt.style.use('ggplot')

The first table gives all the members who have been a part of the club, along with when they joined and when they quit. Well, not all the members. The online system seems to have been phased in after 2000, so the early data is a bit patchy.

members = pd.read_excel('Past-and-Present-Members-Jun.-9-2019.xlsx', header=1, parse_dates=['Begin', 'End'])
members.dtypes

Member ID                                                                          int64
Name                                                                              object
Address                                                                           object
Member has opted-out of Toastmasters WHQ mail marketing communication             object
Phone                                                                             object
Member has opted-out of Toastmasters WHQ phone marketing communication            object
Email Address                                                                     object
Member has opted-out of Toastmasters WHQ email marketing communication            object
Begin                                                                     datetime64[ns]
End                                                                       datetime64[ns]
dtype: object

The second table gives all the club's "officers" and when they were in office. Officers have such grandiose titles as "president", "sergeant at arms" (preparer of snacks), and "vice president public relations" (facebook page updater).

officers = pd.read_excel('Past and Present Club Officers - Aug. 4, 2019.xlsx', header=1, parse_dates=['Start', 'End'])
officers.dtypes

Member ID                                                                          int64
Name                                                                              object
Address                                                                           object
Member has opted-out of Toastmasters WHQ mail marketing communication             object
Phone                                                                             object
Member has opted-out of Toastmasters WHQ phone marketing communication            object
Email Address                                                                     object
Member has opted-out of Toastmasters WHQ email marketing communication            object
Position                                                                          object
Start                                                                     datetime64[ns]
End                                                                       datetime64[ns]
dtype: object

At Toastmasters you also get "education awards" as you move through the curriculum. The last table gives details about these awards.

awards = pd.read_excel('Past and Present Education Awards - Aug. 4, 2019.xlsx', header=1, parse_dates=['Award Date'])
awards.dtypes

Member ID               int64
Member Name            object
Award Name             object
Award Date     datetime64[ns]
dtype: object

Now that we've loaded the data, let's get stuck into some visualisation.

Membership Begin and End Dates

The first thing I want to look at is the number of members entering or leaving the club each year. Were there any mass exoduses? Were there any years where the club was unusually good at recruitment? Seaborn's jointplot seems like the ideal tool for this.

# Have ticks on alternate years
min_year = min(members.Begin.dt.year)
max_year = max(members.End.dt.year)+2
ticks = np.arange(min_year, max_year, 2)

def dts_to_float(dts):
    # Convert a datetime series to a float series.
    # Seaborn is not good at doing this by itself.
    return dts.dt.year + dts.dt.month/12 + dts.dt.day/365

# Point coordinates
begin_years = dts_to_float(members.Begin)
end_years = dts_to_float(members.End)

# Have one bin per year
bins = np.arange(min_year, max_year, 1)

# Create plot
g = sns.jointplot(x=begin_years, y=end_years, alpha=0.2, marginal_kws=dict(bins=bins))
plt.subplots_adjust(top=0.93)
g.ax_joint.set_xticks(ticks)
g.ax_joint.set_xlabel('Membership Begin Date')
g.ax_joint.set_ylabel('Membership End Date')
g.ax_joint.set_yticks(ticks);

Notice how the $x=y$ line is clearly visible. Points can't go below this line because you can't begin your membership before you end it. The greater the verticle height from the $x=y$ line, the longer that person was a member. It looks like the longest membership lasted from 2005 to 2013! The biggest exodus occurred in 2010, which probably prompted a big recruitment drive in 2011 where we see the largest uptake of new members.

Tenure

How long do members stick with the club? Here's a histogram of member "tenure":

members['Tenure'] = members.End - members.Begin

sns.distplot(members['Tenure'].dt.days / 365, bins=np.arange(0, 9, 0.5), kde=False)
plt.ylabel('Count')
plt.xlabel('Years')
plt.title('Member Tenure');

Each bin is one semester long. So most members drop out after only one semester, and after 3 semester's almost everyone is done, although there have been a few hardcore members who've stuck around for many years. The longest membership was 7 years; no doube the 2005 to 2012 member we noted from the previous plot.

Education Awards

Let's look at what education awards were obtained in each year. This data isn't all that interesting, so we won't do anything with the educational awards after this.

award_names = awards['Award Name'].unique()
bottom = np.zeros(len(award_names))
cmap = matplotlib.cm.get_cmap('hsv')
for i, award_name in enumerate(award_names):
    award_data = awards[ awards['Award Name']==award_name ]
    award_counts = award_data['Award Date'].dt.year.value_counts().reindex(range(2000, 2020))
    shape_color = cmap( i/(len(award_names)) )
    shape_bar = award_counts.plot(bottom=bottom, kind='bar', color=shape_color, label=award_name)
    bottom += award_counts.fillna(0)
    
plt.legend(bbox_to_anchor=(1.05, 0.9), ncol=2, title='Award Name')
plt.ylabel('Count')
plt.title('Education Awards by Year')

# Rescale axes
ax = plt.gca()
ax.relim()
ax.autoscale_view()

plt.show()

The only thing to note here is that the curriculum changed by 2019, so a different set of education awards were available.

Total Membership

How many members has Varsity Toastmasters had over its lifetime? How many presidents? How many officers? Let's take a look at a cumulative histogram.

member_begins = pd.DataFrame({'date': dts_to_float(members['Begin'])})
member_begins['Type'] = 'Member'

member_ends = pd.DataFrame({'date': dts_to_float(members['End'])})
member_ends['Type'] = 'ex-Member'

officer_begins = pd.DataFrame({'date': dts_to_float(officers['Start'])})
officer_begins['Type'] = officers.Position

president_begins = officer_begins[officer_begins['Type']=='President']
president_begins['Type'] = 'President'

officer_begins['Type'] = 'Officer'

all_begins = pd.concat([member_begins, member_ends, officer_begins, president_begins])

kwargs = {'cumulative': True, 'alpha': 1}

# Plot each type of member
for type_ in all_begins.Type.unique():
    ax = sns.distplot(all_begins[all_begins['Type']==type_].date, bins=100, hist_kws=kwargs, kde=False, label=type_)
    


# Prettify the plot
ax.legend()
ax.set_xticks(list(range(2000, 2022, 2)))
plt.xlabel('Year')
plt.ylabel('Total')
plt.title('Cumulative Membership');

/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:11: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  # This is added back by InteractiveShellApp.init_path()

Several things to note:

• The gap between the the Member and ex-Member cumulative distributions (i.e., the height of the visible red section) gives the total current members at a given time.
• We can see the spike in recruitment and terminated memberships around 2011. But it looks like the recruitment spike precedes the termination spike, which is the opposite of what it looked like in the previous plot?
• Over the course of 20 years, we've gone through about 350 members, 200 officers, and 40 presidents. That's about 20 members, 10 officers, and 2 president per year, which sounds about right: Toastmasters clubs tend to have 10-20 people, there are seven officer roles, and officers and members turnover on a roughly twice-a-year basis.

Officer Transitions

Let's now look at how members tend to move through the ranks of officer positions. We'll do this by creating calculating a transition matrix for officer roles.

members = officers['Member ID'].unique()
roles = ['Non-Officer', 'Sergeant at Arms', 'Secretary', 'Treasurer', 
     'Vice President Public Relations', 'Vice President Membership', 
     'Vice President Education', 'President']
counts = pd.DataFrame(0, columns=roles, index=roles)#np.zeros((len(roles), len(roles)))
for member in members:
    member_roles =  officers[officers['Member ID']==member].sort_values('Start')['Position']
    member_roles = ['Non-Officer'] + list(member_roles) + ['Non-Officer']
    for i in range(len(member_roles)-1):
        r1 = member_roles[i]
        r2 = member_roles[i+1]
        counts.loc[r1, r2] += 1

# Normalise rows to make transition matrix
transition_matrix = counts.div(counts.sum(axis=1), axis=0)
transition_matrix

	Non-Officer	Sergeant at Arms	Secretary	Treasurer	Vice President Public Relations	Vice President Membership	Vice President Education	President
Non-Officer	0.000000	0.193878	0.153061	0.183673	0.204082	0.132653	0.102041	0.030612
Sergeant at Arms	0.437500	0.062500	0.062500	0.031250	0.000000	0.125000	0.187500	0.093750
Secretary	0.343750	0.093750	0.125000	0.093750	0.062500	0.062500	0.093750	0.125000
Treasurer	0.451613	0.000000	0.000000	0.225806	0.064516	0.064516	0.096774	0.096774
Vice President Public Relations	0.516129	0.032258	0.064516	0.000000	0.064516	0.193548	0.032258	0.096774
Vice President Membership	0.363636	0.060606	0.090909	0.000000	0.090909	0.090909	0.121212	0.181818
Vice President Education	0.424242	0.060606	0.121212	0.030303	0.000000	0.030303	0.181818	0.151515
President	0.500000	0.088235	0.058824	0.029412	0.058824	0.058824	0.000000	0.205882

fig, ax = plt.subplots()
plt.imshow(transition_matrix)
plt.colorbar()
ax.xaxis.tick_top()
ax.set_xticks(range(len(roles)))
ax.set_yticks(range(len(roles)))
ax.set_yticklabels(roles)
ax.set_xticklabels(roles)
plt.xticks(rotation=90)
plt.show()

Observations:

• For a member in any given officer role, the most likely next state of the member is non-officer. The officers who are most likely to retire are presidents - which makes sense because at this stage you've reached the top of the hierarchy - and VP public relations - perhaps because this is a cushy role which non-serious newbies tend to get shuffled into.
• The most popular starting officer role is VP public relations. From there, a member is most likely to transition to VP Membership (assuming they continue to be an officer), and from there to presidency.

Individual Member Plots

Now we get to the interesting bit. I wanted to make a plot where we can see information about each member individually. This turned out to be surprisingly hard: there are so many members that to visually summarise what's going on with all of them normally results in the graph looking chaotic and ugly. But after several hours of trial-and-error, I eventually hit upon a visualisation that I absolutely love.

members2 = members.sort_values(by=['End', 'Begin'], ascending=[False, True])

dates = sorted(np.append(members2.Begin.unique(), members2.End.unique()))

#############################################
# Remove dates which are too close together 
# to prevent any gradients from being too
# steep.
#############################################

min_sep = np.timedelta64(2, 'M').astype('timedelta64[ns]') # 2 months
date_dic = {}
i = 0

while i < len(dates):
    
    date_i = dates[i]
    
    members2.loc[ (date_i < members2['Begin']) & (members2['Begin'] <= date_i + min_sep), 'Begin' ] = date_i
    members2.loc[ (date_i < members2['End']) & (members2['End'] <= date_i + min_sep), 'End' ] = date_i
    
    while i < len(dates) and dates[i] < date_i + min_sep:
        i += 1
    
dates = sorted(np.append(members2.Begin.unique(), members2.End.unique()), reverse=True)

#############################################
# Move the begin dates forward a bit so that 
# if Alice finishes when Bob starts then 
# we don't need to stack them on top of
# each other.
#############################################
    
members2['Begin'] += np.timedelta64(1, 'M')

tl_df = pd.DataFrame(columns=['Index', 'Pos', 'Date', 'Member ID', 'Tenure']) # NOTE: if you use 'Member ID' rath than index, then disconintuities in members2hip get lines which is really messy

dates = sorted(np.append(members2.Begin.unique(), members2.End.unique()), reverse=True)
for i, dt_i in enumerate(dates[:-1]):
    
    dt_j = dates[i+1]
    
    current_members2 = members2[ (~(members2['Begin'] > dt_i) & ~(members2['End'] < dt_i)) ]
    
    dldf_i = pd.DataFrame({'Index': list(current_members2.index), 'Pos': np.arange(len(current_members2)),
                          'Member ID': current_members2['Member ID'],
                          'Tenure': current_members2['Tenure'].dt.days / 365,
                          'Begin': current_members2['Begin'].dt.year + current_members2['Begin'].dt.month/12 + current_members2['Begin'].dt.day/365})
    dldf_i['Date'] = dt_i
    
    tl_df = tl_df.append(dldf_i)
    
#     dt_i = dt_j

fig, ax = plt.subplots(figsize=(80,10))
sns.axes_style("darkgrid")
g = sns.lineplot(x='Date', y='Pos', units='Index', hue='Begin', palette="hsv", estimator=None, data=tl_df);
plt.gca().set_ylabel('Member Count');