export-responses

#!/usr/bin/env python
import argparse, simplejson, codecs, datetime, sys, xlsxwriter, copy, os

supported_formats = [ ".xlsx" ]
output_format = None

def main():
    #parse arguments
    args = parseArguments()

    try:
        output_format = checkArguments(args)
    except Exception,e:
        print "ERROR: %s\n" % e
        sys.exit(1)

    #read and convert JSON data
    fs = open(args.schema, "r")
    schema = simplejson.load(fs)
    fs.close()

    #store information about the study
    study_id = schema['id']
    nforms = len(schema['forms'])
    print "Loaded schema for study " + study_id + " with " + str(nforms) + " forms"

    #read and convert JSON data
    fr = open(args.responses, "r")
    responses = simplejson.load(fr)
    fr.close()

    #store information about responses
    nresponses = len(responses)
    print "Found " + str(nresponses) + " responses"

    #    {
    #        "<session_id>": {
    #            id: value,
    #            title: value,
    #            forms: [
    #                {
    #                    id: value,
    #                    fields: [
    #                        {
    #                            id: value,
    #                            type: value,
    #                            value: value
    #                        }
    #                    ]
    #            ]
    #        }
    #    }
    schemata = {}

    #iterate over all responses
    for response in responses:
        try:
            add_values(schemata, schema, response, args.skipped, args.missing, args.mseparator)
        except Exception,e:
            print e

    table = table_by_form(schemata, args.missing)
    remove_duplicates(table, args.duplicates, args.dseparator)
    form_labels = get_form_labels(schema)

    if output_format == ".xlsx":
        #write output as Excel file
        writeXLSX(args.output, table, form_labels)
    else:
        print "ERROR: Unknown output format:", output_format

    print "Wrote", args.output

#Decides what to do with duplicate answers based on the user chosen strategy
def remove_duplicates(table, duplicate_strategy, separator):

    for sheet in table:
        for row in sheet:
            for i in range(len(row)):
                cell = row[i]
                if type(cell) != list:
                    continue

                if duplicate_strategy == "first":
                    row[i] = cell[0]
                elif duplicate_strategy == "last":
                    row[i] = cell[-1]
                elif duplicate_strategy == "merge":
                    row[i] = separator.join(cell)
                else:
                    row[i] = cell[0]

#returns a list of field labels for 
def get_form_labels(schema):
    labels = []

    for form in schema["forms"]:
        labels.append(form["id"])

    return labels

#convert the schema to tabular format, with each table entry representing a sheet
#with all subjects who submitted responses for those forms
def table_by_form(schemata, missing):

    #list of sheets
    table = []
    #headers
    headers = {}

    
    for session_id, schema in schemata.items():
        forms = schema["forms"]
        nforms = len(forms)

        if len(table) == 0:
            #allocate a list containing nforms number of lists
            for i in range(nforms):
                table.append([])

        for i in range(nforms):
            form = forms[i]
            form_id = form["id"]

            sheet = table[i]
            nrows = len(sheet)

            #build the headers, if necessary
            if nrows == 0:
                row = ["session_id"]
                #loop over all fields and use their 'id' as column headers
                for field in form["fields"]:
                    row.append(field["id"])

                sheet.append(row)

            #build the row by looping over all fields
            row = [session_id]

            for field in form["fields"]:
                if "value" not in field:
                    row.append(missing)
                else:
                    row.append(field["value"])

            sheet.append(row)
    return table

def writeXLSX(filename, table, sheet_labels):

    workbook = xlsxwriter.Workbook(filename)

    nsheets = len(table)

    for i in range(nsheets):
        sheet = table[i]
        sheet_label = sheet_labels[i]
        worksheet = workbook.add_worksheet(sheet_label)

        nrows = len(sheet)

        for row_index in range(nrows):
            worksheet.write_row(row_index, 0, sheet[row_index])

def add_values(schemata, schema, response, skipped, missing, separator):
    #extract info about the current response
    session_id = response["id"]["session"]
    form_id = response["id"]["form"]

    study_id = schema["id"]

    if session_id not in schemata:
        schemata[session_id] = copy.deepcopy(schema)

    session_schema = schemata[session_id]
    
    #search the schema for the form this response contains values for
    schema_form = find_schema_form(session_schema, form_id)

    if schema_form == None:
        raise Exception("%s: Received response for form '%s', but study %s contains no such form." % (session_id, form_id, study_id))

    try:
        populate_fields(schema_form, response, skipped, missing, separator)
    except Exception,e:
        raise Exception("study %s, session %s, form %s: %s" % (study_id, session_id, form_id, str(e)))

#transfer values from response.data to schema.fields
def populate_fields(schema, response, skipped, missing, separator):

    data = response["data"]
    populated = ("populated" in schema)
    schema["populated"] = True

    for field in schema["fields"]:
        field_id = field["id"]

        if field_id not in data:
            value = missing
        else:
            value = data[field_id]

        if type(value) == dict:
            if value["skipped"] == True:
                value = skipped
            else:
                value = value["value"]

        elif type(value) == list:
            curated = []
            #we have to iterate over this list because some items might have been skipped
            #and string.join() expects a list of strings, without any dict's
            for response in value:
                #skipped responses will show up as 'dict's
                if type(response) == dict:
                    if response["skipped"] == True:
                        curated.append(skipped)
                    else:
                        curated.append(response["value"])
                else:
                    curated.append(str(response))

            value = separator.join(curated)

        #if already populated, simply convert the value to a list, and append responses to that list
        if populated:
            if type(field["value"]) != list:
                field["value"] = [field["value"]]
            field["value"].append(value)
        else:
            field["value"] = value


#search the schema for a form object with the given id
def find_schema_form(schema, form_id):
    for form in schema["forms"]:
        if form["id"] == form_id:
            return form

    return None

#parse arguments
def parseArguments():
    parser = argparse.ArgumentParser()

    parser.add_argument("-o", "--output", required=True, metavar="FILE", help="Output file")
    parser.add_argument("-r", "--responses", required=True, metavar="FILE", help="File containing responses")
    parser.add_argument("-s", "--schema", required=True, metavar="FILE", help="Study schema file")
    parser.add_argument("-k", "--skipped", metavar="VALUE", help="Value for skipped fields", default="9999")
    parser.add_argument("-m", "--missing", metavar="VALUE", help="Value for missing fields", default="")
    parser.add_argument("-d", "--duplicates", metavar="VALUE", help="How to handle multiple form submissions (first, last, merge, skip)", default="first")
    parser.add_argument("--mseparator", metavar="CHAR", help="Multi-value separator", default=",")
    parser.add_argument("--dseparator", metavar="CHAR", help="Duplicate response separator", default="|")

    return parser.parse_args()

def checkArguments(args):
    basename, ext = os.path.splitext(args.output)

    if ext not in supported_formats:
        raise Exception("I don't know how to output " + output_format + " files. Check the output filename. By the way, I only understand: " + ", ".join(supported_formats))

    return ext

if __name__ == "__main__":
    main()