What's the Most Forgotten State?

import pandas as pd
import matplotlib as mpl
import requests
import lxml.html
import itertools
from StringIO import StringIO

import mplstyle
import mplstyle.styles.simple
mplstyle.set(mplstyle.styles.simple)
mplstyle.set({ 
    "figure.figsize": (10, 8),
    "lines.markersize": 0
})

from IPython.display import HTML
from markdown import markdown
md = lambda x: HTML(markdown(x))

QUIZ_RESULTS_URL = "http://www.sporcle.com/games/g/states/results"
results_html = requests.get(QUIZ_RESULTS_URL).text
results_dom = lxml.html.fromstring(results_html)

times_played_str = results_dom.cssselect(".dividerText strong")[0].text

answer_tables = results_dom.cssselect("table.answer")
result_rows_nested = map(lambda x: x.cssselect("tr")[1:], answer_tables)
result_rows = list(itertools.chain.from_iterable(result_rows_nested))

def data_from_row(tr):
    return {
        "answer": tr.cssselect("td.answer a")[0].text,
        "pct_correct": float(tr.cssselect("td.percent")[0].text[:-1])
    }

sporcle_results = pd.DataFrame(map(data_from_row, result_rows))

md(u"""The answer, according to [Sporcle's "US States Quiz"](http://www.sporcle.com/games/g/states/results)
— which, the last time I checked, has been played %s times — is __%s__.""" % (
    times_played_str,
    sporcle_results.irow(-1)["answer"]
))

CENSUS_URL = "http://www.census.gov/popest/data/national/totals/2012/files/NST_EST2012_ALLDATA.csv"
census_csv = requests.get(CENSUS_URL).text
populations = pd.read_csv(StringIO(census_csv))

# The Census data also includes population figures for 
# the nation at-large, as well as regional groupings of states.
#
# Let's select just the states.
in_sporcle = populations["Name"].apply(lambda x: x in sporcle_results["answer"].values)
state_populations = populations[in_sporcle]

state_populations["Pop. Rank"] = state_populations["CENSUS2010POP"].rank(ascending=False)

sporcle_results["Sporcle Rank"] = sporcle_results["pct_correct"].rank(ascending=False)

rankings = sporcle_results.merge(
    state_populations[["Name", "Pop. Rank"]], 
    how="left", 
    left_on="answer", 
    right_on="Name")[["answer", "Sporcle Rank", "Pop. Rank"]]

rankings["diff"] = rankings["Pop. Rank"] - rankings["Sporcle Rank"]

def get_state(rows, name):
    return rows[rows["answer"] == name].irow(0).to_dict()

def rank_to_string(x):
    mod_10 = int(x) % 10
    ths = [ "th" for i in range(6) ]
    if x not in [ 11, 12, 13 ]:
        suffix = (["th", "st", "nd", "rd"] + ths)[mod_10]
    else:
        suffix = ths[mod_10]
    return "%d%s" % (x, suffix)

missouri = get_state(rankings, "Missouri")
md(u"""- __Missouri still does terribly__, ranking %s in population, 
for a difference of __%d__.""" % (rank_to_string(missouri["Pop. Rank"]), missouri["diff"]))

top_state = rankings.sort("diff", ascending=False).irow(0).to_dict()
md(u"""- __%s is the most over-performing state__, ranking %s in population but
%s in the Sporcle quiz, for a difference of __+%d__.""" % (
    top_state["answer"],
    rank_to_string(top_state["Pop. Rank"]),
    rank_to_string(top_state["Sporcle Rank"]), 
    top_state["diff"])
)

rankings.sort("diff", ascending=False).reset_index(drop=True)

slope = rankings.set_index("answer")[["Pop. Rank", "Sporcle Rank"]]\
    .sort("Pop. Rank", ascending=False)\
    .apply(lambda x: 51 - x)

# Make the chart more vertical
prev_styles = mplstyle.get()
mplstyle.set({ "figure.figsize": (6, 12) })

# Color the lines based on direction and magnitude
color_cycle = ["gray"] + mplstyle.get("axes.color_cycle")
pos_neg = (slope["Pop. Rank"] < slope["Sporcle Rank"])
major = (slope["Pop. Rank"] - slope["Sporcle Rank"]).apply(abs) > 9
colors = (major * (pos_neg + 1))\
    .apply(lambda x: color_cycle[int(x)])

# Plot the slopegraph
ax = slope.T.plot(
    colors=colors,
    alpha=0.7,
    legend=False, 
    xticks=[0,1],
    ylim=(0, 51))

intflo = lambda x: x if x - int(x) else int(x)
yticks = [ x + 1 for x in range(50) ]

# Set the left y-axis
ax.set_yticks(yticks)
ax.set_yticklabels([ "%s (%s)" % (
    slope.index[i],
    51 - intflo(slope["Pop. Rank"][i])
) for i in range(50) ])

# Set the right y-axis
ax2 = ax.twinx()
ax2.set_yticks(yticks)
ax2.set_ylim((0, 51))
ax2.set_yticklabels([ "%s (%s)" % (
    slope.sort("Sporcle Rank").index[i], 
    51 - intflo(slope.sort("Sporcle Rank")["Sporcle Rank"][i])
) for i in range(50) ])

mplstyle.set(prev_styles)
pass