Skip to content

Instantly share code, notes, and snippets.

@glasnt glasnt/pretalx-xls.py Secret
Created May 12, 2019

Embed
What would you like to do?
# 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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.