Useful data transformations

Given a list of students who’ve selected COMP 150, find all those who haven’t selected COMP 160 (can be generalised to any papers). Requires a TTRPT02 report for COMP 150 as a starting point, in XLSX format, with everything before the header row removed.

Python using PETL:

import petl as etl
import openpyxl

(
etl.fromxlsx("COMP150_2016.xlsx")
    .addfield("COMP160",
              lambda rec: len("".join(
                [paper.split(" ")[0]
                for paper in unicode(rec["Additional Paper(s)"]).split(", ")
                if paper.startswith("COMP160")]
              )) > 0)
    .select(lambda rec: unicode(rec["Major(s)"]).find("Information Science") >= 0)
    .select("{COMP160} == False")
    .toxlsx("COMP150_without_160_2016.xlsx")
)