pretalx-xls.py

# Using the PreTalx API, export Submission and Review data, pre-process slightly, 
# and save as a large XLSX file for further data manipulation in Google Sheets, Microsoft Excel, Apple Numbers, etc. 

#
# NOTE
#
# This code probably won't *just work* for you, but serves as a assistant file to a personal blog post, only. 
#
#
# Licence: BSD-3


import requests
import json
import os
import csv
import glob
import pandas as pd
import sys

EVENT = "YourEventNameHere"
PRETALX_TOKEN = os.environ.get("PRETALX_API", None)

def slug(s):
    return "https://pretalx.com/api/events/%s/%s?limit=100" % (EVENT, s)

def get(uri):
    resp = requests.get(uri, headers={"Authorization": "Token %s" % PRETALX_TOKEN})
    if resp.ok:
        return resp.json()
    else:
        print(resp.text)

        return {}


def get_data(name):
    resp = get(uri=slug(name))
    results = resp["results"]
    while resp["next"]:
        resp = get(uri=resp["next"])
        results += resp["results"]

    return resp
    
submissions = get_data("submissions")
reviews = get_data("reviews")

# Preprocess submissions

subs = {}
for s in submissions: 
    s["total_score"] = 0
    s["count_score"] = 0
    speakers = []
    for x in s["speakers"]:
        speakers.append(x["name"])
    s["speakers"] = " & ".join(speakers)
    s["track"] = s["track"]["en"]
    
    subs[s["code"]] = s

# Preprocess reviewers
    
reviewers = {}

for r in reviews: 
    subs[r["submission"]]["total_score"] += r["score"]
    subs[r["submission"]]["count_score"] += 1

    if r["user"] not in reviewers.keys():
        reviewers[r["user"]] = []
    r["code"] = subs[r["submission"]]["code"]
    r["title"] = subs[r["submission"]]["title"]
    r["speakers"] = subs[r["submission"]]["speakers"]
    r["track"] = subs[r["submission"]]["track"]
    reviewers[r["user"]].append(r)

# Write reviewer data to csv files, separated by reviewer

for idx, r in enumerate(reviewers): 
    fn = "data/%s_%s.csv" % (str(idx + 1).zfill(2), r.replace(" ", ""))
    f = csv.writer(open(fn, "w"))
    keys = ["code", "title", "track", "speakers", "score", "text"]
    f.writerow(keys)
    for x in reviewers[r]:
        data = []
        for k in keys:
            data.append(x[k])
        f.writerow(data)
    print(r, len(reviewers[r]))

# write submission data to csv file
    
f = csv.writer(open("data/00_submissions.csv", "w"))
keys = ["code", "title", "track", "speakers", "total_score", "count_score"]
f.writerow(keys + ["avg"])
for idx, s in enumerate(subs):
    data = []
    for k in keys:
        data.append(subs[s][k])
    t = subs[s]["total_score"] 
    c = subs[s]["count_score"]
    if c == 0:
        data.append("-")
    else: 
        data.append(t/c)
    f.writerow(data)
   

# Save all the files as one XLSX, with each file as a different sheet/tab

writer = ExcelWriter("compiled.xlsx")

for filename in sorted(glob.glob("data/*.csv")):
    print(filename)
    
    df_csv = pd.read_csv(filename)
    (_, f_name) = os.path.split(filename)
    (f_shortname, _) = os.path.splitext(f_name)
    if not f_shortname:
        f_shortname = f_name

    df_csv.to_excel(writer, f_shortname, index=False)

writer.save()