Script to process attendance files to generate various listings

attendproc

#!/usr/bin/env python3
"""Process attendance data

Start by downloading an attendance sheet in HTML or Excel format.

You can download attendance data using the ".." icon in the Attendance
interface.

Download in Excel format using the "Download Excel" option.

Download in HTML by selecting "View Attendance Sheet".  This will give
you an HTML file download.

In either case, note the filename.  Then run this program, passing the filename
of the downloaded file.

With the "chat" action, the command will will print out a URL you can paste
into your browser.  If you do, your browser will open Teams with a chat ready
to fill out, and all the people listed anywhere on the attendance sheet added
(regardless of whether they attended or not).

For the "chat" action, you can also (optionally) add a title for the Teams
chat.

The "email" action prints out a list of emails for all possible attendees.

The "users" action prints out a list of users for all possible attendees, where
the user is the part of their email before the `@` character.

The "ghuser" action prints out a list of Github users for all possible
attendees.  You will need to provide a file mapping emails to Github users.
"""

from pathlib import Path
from argparse import ArgumentParser, RawDescriptionHelpFormatter


import pandas as pd


# https://techcommunity.microsoft.com/t5/microsoft-teams/how-to-add-multiple-users-in-teams-chat-in-one-go/m-p/1827194
URL_PREFIX = "https://teams.microsoft.com/l/chat/0/0?users="

# Separator default for not-chat actions.
SEP_DEFAULT = '\n'


def get_table(afile):
    tables = pd.read_html(afile)
    if not tables:
        raise RuntimeError(f'No table in {afile}')
    if len(tables) > 1:
        raise RuntimeError(f'More than one table in {afile}')
    return tables[0]


def get_parser():
    parser = ArgumentParser(description=__doc__,  # Usage from docstring
                            formatter_class=RawDescriptionHelpFormatter)
    parser.add_argument('--sort', action='store_true',
                        help='If set, sort result alphabetically')
    subparsers = parser.add_subparsers(
        dest='action',
        title='subcommands',
        help='sub-command help')
    chat_parser = subparsers.add_parser('chat', help='chat help')
    chat_parser.add_argument('--chat-name',
                             help='Name for chat')
    email_parser = subparsers.add_parser('email', help='email help')
    email_parser.add_argument(
        '--separator', default=SEP_DEFAULT,
        help=f'Separator for emails (default={SEP_DEFAULT!r})')
    user_parser = subparsers.add_parser('user', help='user help')
    user_parser.add_argument(
        '--separator', default=SEP_DEFAULT,
        help=f'Separator for users (default={SEP_DEFAULT!r})')
    gh_parser = subparsers.add_parser('ghuser', help='ghuser help')
    gh_parser.add_argument(
        '--separator', default=SEP_DEFAULT,
        help=f'Separator for Github users (default={SEP_DEFAULT!r})')
    gh_parser.add_argument(
        '--with-email', action='store_true',
        help='Append commented email to Github user')
    gh_parser.add_argument('gh_file',
                        help='Filename for Github user lookup file')
    parser.add_argument('attendance_file',
                        help='Filename for attendance file')
    return parser


def main():
    args = get_parser().parse_args()
    afile = Path(args.attendance_file).expanduser()
    df = (get_table(afile) if afile.suffix == '.html' else
          pd.read_excel(afile))
    if 'Email' not in df:
        raise RuntimeError(f'No "Email" column in table from {afile}')
    emails = df['Email']
    if args.sort:
        emails = emails.sort_values()
    if args.action == 'chat':
        out = URL_PREFIX + ','.join(emails) + (
            "&topicName=" + args.chat_name if args.chat_name
            else '')
    elif args.action == 'email':
        out = args.separator.join(emails)
    elif args.action == 'user':
        fields = emails.str.split('@', n=1, expand=True)
        out = args.separator.join(fields.iloc[:, 0])
    elif args.action == 'ghuser':
        gh_users = pd.read_csv(args.gh_file)
        gh_df = pd.merge(emails, gh_users, how='left', on='Email')
        if args.sort:
            gh_df = gh_df.sort_values('gh_user')
        out_users = gh_df['gh_user']
        if args.with_email:
            out_users = out_users + '  # ' + gh_df['Email']
        out = args.separator.join(out_users)
    print(out)


if __name__ == '__main__':
    main()