Synthesize reminder messages for students who haven't submitted pull requests for the required number of toolbox projects.

nagbot.py

# -*- coding: utf-8 -*-
"""Print email nags regarding toolbox pull requests.

The current directory should contain a file `Master Grades.xlsx` with two sheets, and columns
`First Name`, `Last Name`, `E-mail`, and `GitHub`.

Author: Oliver Steele <oliver.steele@olin.edu>
"""

import os
from functools import partial

import arrow
import github3
import pandas as pd
import xlrd

CARDINALS = ['no', 'one', 'two', 'three']
COURSE_NAME = 'sd16spring'
DUE_DATE = arrow.get('2016-03-14T00:00:00-10:00')
REPO_PREFIX = 'ToolBox-'
REQUIRED_PRS = 3
URL = 'https://sites.google.com/site/sd16spring/home/project-toolbox'
NAGBOT_URL = 'https://gist.github.com/osteele/78ecee3f711aba61ebfc'

GITHUB_API_TOKEN = os.environ.get('HOMEBREW_GITHUB_API_TOKEN')

gh = github3.login(token=GITHUB_API_TOKEN) if GITHUB_API_TOKEN else github3

# Combine the sheets into a single table, and drop the empty rows
students = pd.concat([pd.read_excel('Master Grades.xlsx', sheetname=section) for section in [0, 1]])
students = students[students['GitHub'].notnull()].reset_index(drop=True)

# a list of Toolbox repos
repos = [r for r in gh.iter_user_repos(COURSE_NAME) if r.name.startswith(REPO_PREFIX)]

# a dict of {repo_name: [github_username]} of users who have submitted pull requests against the repo
prs = {repo.name: [pr.user.login for pr in repo.iter_pulls()] for repo in repos}

# a DataFrame whose rows are GitHub usernames, columns are repositories, and cells are 1 if a pull requests exists
df = reduce(partial(pd.merge, how='outer'),
            [pd.DataFrame({repo_name: [1] * len(users), 'GitHub': users})
             for repo_name, users in prs.items()]) \
  .fillna(0)

# add a `Count` column
count_df = pd.DataFrame(df.set_index('GitHub').sum(axis=1), columns=['Count'])
df = students.merge(count_df, how='outer', left_on='GitHub', right_index=True).fillna(0).sort_values('Count')

dt = DUE_DATE - arrow.now()
remaining_time = ' and '.join('{} {}{}'.format(n, unit, 's'[n == 1:])
                              for n, unit in [(dt.days, 'day'), (int(round(dt.seconds / 3600)), 'hour')] if n)

for completed in range(REQUIRED_PRS):
    remaining = REQUIRED_PRS - completed
    recipients = ', '.join('{First Name} {Last Name} <{E-mail}>'.format(**row)
                           for _, row in df[df['Count'] == completed].iterrows())
    if recipients:
        print 'Bcc:', recipients
        print 'Subject:', 'Nudge: Project Toolbox exercises'
        print
        print 'This is a friendly reminder from the SoftDes teaching team'
        print 'that you must complete three Project Toolbox <{}>'.format(URL)
        print 'exercises by midnight Sunday March 13 Hawaian Daylight Time ({}).'.format(DUE_DATE.to('US/Hawaii'))
        print

        if completed:
            print 'You currently have {} GitHub pull request{} for Toolbox repositories.'.format(
                CARDINALS[completed], 's'[completed == 1:])
            print

        print 'Please remember to create the {}{} pull request{} within the next {}.'.format(
            'remaining ' if completed else '',
            CARDINALS[remaining], 's'[remaining == 1:],
            remaining_time)
        print
        print '– SoftDes Nagbot <{}>'.format(NAGBOT_URL)