AFL Analytics¶

!pip install tweepy
!pip install pandas_datareader
!pip install xlrd
!pip install tables

from IPython.display import display
from scipy import stats
from itertools import groupby
import re
import matplotlib as mpl
import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import numpy as np
from pylab import rcParams # controls for plot rendering
import sqlite3
from datetime import datetime
import tweepy
import time
import os.path
import json
from pandas_datareader import data, wb
import requests
from bs4 import BeautifulSoup
import xlrd

mpl.style.use('ggplot')
pd.set_option('display.html.border', 1)
pd.set_option('max_columns', 12)
pd.set_option('max_rows', 100)
pd.set_option('display.width', 100)
pd.set_option('multi_sparse', False)
pd.set_option('expand_frame_repr', True)
pd.set_option('max_colwidth', 60)
pd.set_option('precision', 4)
%matplotlib inline

# Executes World Bank API query, removes the country information,
# renames the metric column and removes entries with no data,
# i.e. outside of the reportable time-frame.
dnp_pcap_au = wb.download(
    indicator = 'NY.GNP.PCAP.CD',
    country = ['AU'],
    start = 1960,
    end = 2018
).reset_index("country").drop("country", axis = 1).rename(
    columns = {"NY.GNP.PCAP.CD" : "GNI_per_capita"}
).dropna()

dnp_pcap_au = dnp_pcap_au.append(pd.DataFrame(data = {'GNI_per_capita' : 46560}, index = ['2017']))
dnp_pcap_au = dnp_pcap_au.sort_index(ascending = False)
dnp_pcap_au

# Plot GNI per capita in order of year.
dnp_pcap_au.sort_index().plot()

# PLEASE DO NOT SHARE THESE CREDENTIALS AS THEY ARE PRIVATE AND CONFIDENTIAL
consumer_key="XXYY"
consumer_secret="XXYY"
access_token="XXYY"
access_token_secret="XXYY"
# ==========================================================================

# Twiter public API documentation: https://dev.twitter.com/rest/public
# Tweepy documentation: http://tweepy.readthedocs.io/en/v3.5.0/index.html

# Set the credentials for accessing the Twitter API.

# Perform the authentication handshake with the Twitter API using the above credentials.
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.secure = True
auth.set_access_token(access_token, access_token_secret)

# Create an object to use as a connection to the authenticated API
api = tweepy.API(auth)

# Rate limit handler for the Twitter API.
def limit_handled(cursor):
    while True:
        try:
            yield cursor.next()
        except tweepy.RateLimitError:
            print "Rate Limited. Waiting 15 minutes..."
            time.sleep(15 * 60)
            print "OK, wait is over."
            continue
        except tweepy.TweepError as e:
            print "Tweep Error: {0}".format(str(e))
            break

afl_clubs = api.list_members("AFLPlayers", "clubs")

afl_clubs_df = pd.DataFrame()

# Define a function that will be used to add clubs to the above empty data frame of AFL club
# Twitter accounts along with a selection of associated details (including number of followers).
def add_club_to_df(df, club):
    df = df.append({
        "id" : club.id_str,
        "name" : club.name,
        "followers" : club.followers_count,
        "location" : club.location,
        "tz" : club.time_zone,
        "handle" : club.screen_name,
        "image" : club.profile_image_url
    }, ignore_index = True)
    return df

# The above function is then applied to each AFL account from the list returned from the
# Twitter API.
for afl_club in afl_clubs:
    afl_clubs_df = add_club_to_df(afl_clubs_df, afl_club)

# The resulting data frame is then tidied up
afl_clubs_df = afl_clubs_df.set_index("id")
afl_clubs_df.followers = afl_clubs_df.followers.astype("int")
afl_clubs_df = afl_clubs_df[["name", "location", "tz", "followers", "handle", "image"]]
afl_clubs_df = afl_clubs_df.rename(columns = {"name" : "club"})
afl_clubs_df.club = afl_clubs_df.club.str.replace("GWS GIANTS", "GWS")
afl_clubs_df.club = afl_clubs_df.club.str.replace("GC SUNS", "Gold Coast")
afl_clubs_df.club = afl_clubs_df.club.str.replace(" FC", "")
afl_clubs_df.club = afl_clubs_df.club.str.replace(" Dockers", "")
afl_clubs_df.club = afl_clubs_df.club.str.replace(" Lions", "")
afl_clubs_df.club = afl_clubs_df.club.str.replace(" Cats", "")
afl_clubs_df.club = afl_clubs_df.club.str.replace(" Eagles", "")
afl_clubs_df.club = afl_clubs_df.club.str.replace(" Crows", "")
afl_clubs_df.club = afl_clubs_df.club.str.replace(" Swans", "")
afl_clubs_df.club = afl_clubs_df.club.str.replace(r'\W+', "")
afl_clubs_df = afl_clubs_df.set_index('club')

# Plot number of followers for each club in order of lowest to highest.
afl_clubs_df['followers'].sort_values().plot.barh()

# Fetch the page with the club membership details on it
memberships_2006_2009_url = "http://www.footyindustry.com/wiki/index.php?title=Club_Memberships"
memberships_2006_2009_response = requests.get(memberships_2006_2009_url)

# Parse the page and extract the tables from it. Each table represents a single year.
memberships_2006_2009_response
memberships_2006_2009 = BeautifulSoup(memberships_2006_2009_response.content, "lxml")
memberships_2006_2009_tables = memberships_2006_2009.findAll("table")

# Convert the tables into data frames, selecting only the tables we require.
memberships_2006_2009_dfs = pd.read_html(
    str(memberships_2006_2009_tables[3:7]),
    header = 1,
    index_col = 0
)

# Remove rows with missing values and the final totals row from each table, keeping only the
# the number of members with 'game access' type memberships.
memberships_2006_2009_dfs = [df.dropna().iloc[0:-1, 3] for df in memberships_2006_2009_dfs]

# Combine all the tables into a single data frame with a column indicating the year
memberships_2006_2009_dfs = pd.concat(
    {year: df for year, df in zip(['2006', '2007', '2008', '2009'], memberships_2006_2009_dfs)},
    names = ['year', 'club']
)

# Convert the series into data frames with clubs as rows and years as columns
memberships_2006_2009_df = memberships_2006_2009_dfs.reset_index(name = "members").pivot_table(
    index = "club", columns = "year", values = "members", aggfunc = sum
)

# Download the webpage with the membership figures on it
memberships_2011_url = "https://en.wikipedia.org/wiki/2011_AFL_season"
memberships_2011_response = requests.get(memberships_2011_url)

# Parse the webpage, extracting the table found in the club membership section
memberships_2011 = BeautifulSoup(memberships_2011_response.content, "lxml")
memberships_2011 = memberships_2011.find(attrs={"id" : "Club_membership"}).find_next("table")

# Convert the table into a data frame, naming the columns and discarding the last
# column that we do not require.
memberships_2011_df = pd.read_html(str(memberships_2011), header = 0)[0].iloc[:, 0:-1]
memberships_2011_df.columns = ["club", "2011", "change_from_2010"]
# Clean up the data in the table to calculate the 2010 figures
# If the 'change' column does not start with a digit, then it must be a negative change.
# Extract the change from 2010 to 2011 to calculate the original 2010 figures.
memberships_2011_df.change_from_2010 = memberships_2011_df.change_from_2010.str.replace(
    r"^[^0-9]+", "-"
).str.extract(
    r"(.+) !", expand = False
).astype(np.number)
memberships_2011_df['2010'] = memberships_2011_df['2011'] - memberships_2011_df.change_from_2010
# Arrange the dataframe with club as the rows and keeping just the 2010 and 2011 columns.
memberships_2011_df = memberships_2011_df.set_index('club')
memberships_2011_df = memberships_2011_df[["2010", "2011"]]
# Tidy up the club names for consistency and remove the final 'total' row from the data frame.
memberships_2011_df.index = memberships_2011_df.index.str.replace(" Lions", "")
memberships_2011_df.index = memberships_2011_df.index.str.replace(" Eagles", "")
memberships_2011_df = memberships_2011_df.drop("Total")

# Download the webpage with the club membership stats on it
memberships_2012_url = "http://www.news.com.au/sport/afl/collingwood-and-hawthorn-record-membership-increases/story-fnelctok-1226466581212"
memberships_2012_response = requests.get(memberships_2012_url)

# Parse the webpage, defining a regular expression to extract the club name and membership
# size from the text contained in bulleted paragraphs.
memberships_2012_memberships = BeautifulSoup(memberships_2012_response.content, "lxml")
memberships_2012_regex = re.compile("([a-zA-Z ]+): ([0-9]{2},[0-9]{3})")
memberships_2012_tags = memberships_2012_memberships.find_all(string = memberships_2012_regex)
# Extract the club name and membership size seperately (using the regular expression already defined)
# for each paragraph as a list of tupples and coerce these into a Data Frame naming each column
# appropriately to 'club' and 'members'.
memberships_2012_df = pd.DataFrame(
    [re.match(memberships_2012_regex, club).groups() for club in memberships_2012_tags],
    columns = ["club", "members"]
)
# Set the club to index the rows, format the club sizes into numbers, set the column to the year
# and tidy the club names for consistency.
memberships_2012_df = memberships_2012_df.set_index("club")
memberships_2012_df.members = memberships_2012_df.members.str.replace(",", "").astype("int")
memberships_2012_df = memberships_2012_df.rename(columns = {"members": "2012"})
memberships_2012_df.index = memberships_2012_df.index.str.replace(" Giants", "")

# Download the webpage
memberships_2013_2014_url = "http://www.afl.com.au/news/2014-08-02/club-memberships-rise"
memberships_2013_2014_response = requests.get(memberships_2013_2014_url)

# Parse the webpage by extracting the table and converting into a data frame with appropraitely
# named columns, setting the club name as the row index and order the years from left to right.
memberships_2013_2014 = BeautifulSoup(memberships_2013_2014_response.content, "lxml")
memberships_2013_2014_table = memberships_2013_2014.find("table")
memberships_2013_2014_df = pd.read_html(str(memberships_2013_2014_table), header = 0)[0]
memberships_2013_2014_df.columns = ["club", "2013", "2014"]
memberships_2013_2014_df = memberships_2013_2014_df.set_index("club")
memberships_2013_2014_df[["2013", "2014"]] = memberships_2013_2014_df[["2013", "2014"]].apply(
    # Format the club sizes into numbers.
    lambda s: s.astype(str).str.replace(r"[*,]", "").astype(int),
    axis = 1
)
# Tidy up the club names to be consistent
memberships_2013_2014_df.index = memberships_2013_2014_df.index.str.replace(" Lions", "")
memberships_2013_2014_df.index = memberships_2013_2014_df.index.str.replace(" Swans", "")

def get_memberships_article(year = "2016"):
  # Download the webpage
  memberships_url = "http://www.aflonline.com.au/afl-news/current-afl-membership-numbers-for-" + str(year) + "/"
  memberships_response = requests.get(memberships_url)
  # Parse the webpage by extracting the table, converting into a data frame and naming the columns.
  memberships = BeautifulSoup(memberships_response.content, "lxml")
  memberships_table = memberships.find("table")
  memberships_df = pd.read_html(str(memberships_table), header = 0)[0]
  memberships_df = memberships_df.iloc[:, 0:3]
  memberships_df.columns = ["club", "year2", "year1"]
  # Set the club name as the row index.
  memberships_df = memberships_df.set_index("club")
  # Tidy the club names for consistency.
  memberships_df.index = memberships_df.index.str.replace(" Lions", "")
  memberships_df.index = memberships_df.index.str.replace(" Giants", "")
  memberships_df.index = memberships_df.index.str.replace(r"Melb$", "Melbourne")
  # Order the years from left to right.
  memberships_df = memberships_df[['year1', 'year2']]
  memberships_df = memberships_df.iloc[memberships_df.index != "TOTAL", :]
  return memberships_df

memberships_2015_2016_df = get_memberships_article("2016")
memberships_2016_2017_df = get_memberships_article("2017")
memberships_2015_2016_df.columns = ["2015", "2016"]
memberships_2016_2017_df.columns = ["2016", "2017"]
memberships_2015_onwards = memberships_2015_2016_df[["2015"]]
memberships_2015_onwards = memberships_2015_onwards.join(memberships_2016_2017_df, how = "outer")
memberships_2015_onwards

# Concatenate the scraped membership stats into a single data frame with a multi-index 
# for clubs and year forming the rows.
club_memberships_df = memberships_2011_df.join([
        memberships_2006_2009_df,
        memberships_2012_df,
        memberships_2013_2014_df,
        memberships_2015_onwards
], how = "outer")
club_memberships_df.index.name = "club"
club_memberships_df = club_memberships_df.stack()
club_memberships_df.index.names = ['club', 'year']
club_memberships_df = club_memberships_df.reset_index(name = "members")
club_memberships_df = club_memberships_df.set_index(['year', 'club'])

# Transform the memberships data frame into wide format.
memberships_club_year_pivot = club_memberships_df.reset_index().pivot_table(
    values = "members", index = "club", columns = "year", aggfunc = sum, dropna = False
)
# Produce a heatmap, with the clubs sorted in order of total membership renewals
sns.heatmap(
    memberships_club_year_pivot.loc[
        memberships_club_year_pivot.sum(axis = 1).sort_values(ascending = False).index
    ],
    robust = True
)
plt.yticks(rotation = 0) 
plt.show()

# The following function can be used to scrape a series of pages from the same website, where the
# difference in extracting the crowd attendance data is the year given in the URL.
def get_attendance(year = "2016"):
  # Download the required page
  url = "http://afltables.com/afl/crowds/" + str(year) + ".html"
  response = requests.get(url)
  # Parse the page by extracting the table and converting into a data frame, keeping only the required
  # columns.
  attendances = BeautifulSoup(response.content, "lxml")
  attendance_by_team = attendances.find("table")
  df = pd.read_html(str(attendance_by_team), header = 0)[0].iloc[:-1, 0:7]
  # Name the columns
  df.columns = [
      "club",
      "home_attendance", "home_games", "home_avg",
      "away_attendance", "away_games", "away_avg"
  ]
  # Use club name as the index for the rows.
  df = df.set_index("club")
  # Remove these columns we later decided we are not interested in them:
  df = df.drop(["home_avg", "away_avg"], axis = 1)
  df = df.drop(index="Team")
  # Coerce the numbers into integers.
  df.home_attendance = df.home_attendance.astype("int")
  df.home_games = df.home_games.astype("int")
  df.away_attendance = df.away_attendance.astype("int")
  df.away_games = df.away_games.astype("int")
  # Rename clubs to be consistent with other datasets.
  df.index = df.index.str.replace(" Lions", "")
  df.index = df.index.str.replace("Greater Western Sydney", "GWS")
  df.index = df.index.str.replace("Kangaroos", "North Melbourne")
  return df

# Apply the above function to scrape the website for attendance data for the years 2006 to 2017 (inclusive)
attendance_by_year = {str(year): get_attendance(year) for year in range(2006, 2018)}
attendance_by_year = pd.concat(attendance_by_year, names = ["year"])

# Calculate new fields for total attendees, total games and average attendance, keeping only
# the calculated columns in the data frame already indexed by year and club name.
attendance_by_year['attendees'] = attendance_by_year['home_attendance'] + attendance_by_year['away_attendance']
attendance_by_year['games'] = attendance_by_year['home_games'] + attendance_by_year['away_games']
attendance_by_year = attendance_by_year[['attendees', 'games']]
attendance_by_year['avg attendance'] = attendance_by_year['attendees'] / attendance_by_year['games']

# Transform the attendance by club and year into wide format
attendance_club_year_pivot = attendance_by_year.reset_index().pivot_table(
    values = "avg attendance", index = "club", columns = "year", dropna = False
)
# Plot a heatmap of attendance by year and club. Sort the clubs by highest total spectators to least
sns.heatmap(
    attendance_club_year_pivot.loc[
        attendance_club_year_pivot.sum(axis = 1).sort_values(ascending = False).index
    ],
    robust = True
)
plt.yticks(rotation = 0) 
plt.show()

# Open the spreadsheet file, selecting only the columns we are interested in.
odds_df = pd.read_excel(
    "afl.xlsx",
    skiprows = 1,
    usecols = "A:AY"
)
# Tidy up the team names to be consistent
odds_df[['Home Team', 'Away Team']] = odds_df[['Home Team', 'Away Team']].apply(
    lambda club: club.str.replace(" Giants", ""),
    axis = 1
)
# Keep the columns we are interested in and rename the column indicating whether the game is a final.
odds_df = odds_df[['Date', 'Home Team', 'Away Team', 'Home Score', 'Away Score', 'Play Off Game?']]
odds_df = odds_df.rename(columns = {"Play Off Game?": "Final"})
# Encode the Final column into Y and N.
odds_df.Final = odds_df.Final.fillna(value = "N")
odds_df = odds_df[odds_df.Final == "N"]

# Identify games where the home team won, the away team won and where there were draws.
odds_df['Home Win'] = odds_df['Home Score'] > odds_df['Away Score']
odds_df['Away Win'] = odds_df['Home Score'] < odds_df['Away Score']
odds_df['Draw'] = odds_df['Home Score'] == odds_df['Away Score']
# Identify the names of the teams that won each game
odds_df['Home Win'] = odds_df['Home Team'].where(odds_df['Home Win'])
odds_df['Away Win'] = odds_df['Away Team'].where(odds_df['Away Win'])

# A function to return the name of the team that won a game (represented as a row)
def decide_winning_team(row):
    if row['Draw'] == True:
        return "Draw"
    elif pd.isnull(row['Home Win']):
        return row['Away Win']
    elif pd.isnull(row['Away Win']):
        return row['Home Win']

# Reduce the columns down to just one column using the above function that indicates which
# team was the winning team, otherwise a draw.
odds_df['Winning Team'] = odds_df[['Home Win', 'Away Win', 'Draw']].apply(
    decide_winning_team,
    axis = 1
)

# Remove the redundant 'Draw' column as we have this encoded in the above already.
odds_df = odds_df.drop("Draw", axis = 1)
# Determine the year (season) of the game.
odds_df['year'] = odds_df['Date'].dt.year.astype("str")
# Determine the number of games won, grouped by each team
winning_team_by_year = odds_df.groupby(['year', 'Winning Team']).size()
winning_team_by_year = winning_team_by_year.reset_index(name = "wins")
# Format the number of games won by team data frame into the same structure as our other
# datasets, i.e. rows indexed by year and club.
winning_team_by_year = winning_team_by_year.rename(columns = {"Winning Team": "club"})
winning_team_by_year = winning_team_by_year.set_index(['year', 'club'])
# Any years where a team is not mentioned in this must be a team that had zero wins that year.
winning_team_by_year = winning_team_by_year.reset_index().pivot_table(
     values = "wins", index = "club", columns = "year", fill_value = 0, dropna = False
).stack().reset_index(name = "wins").rename(
    columns = {'level_1' : 'year'}
).set_index(['year', 'club']).sort_index()

# Transform from long to wide format.
winning_team_by_year_pivot = winning_team_by_year.reset_index().pivot_table(
    values = "wins", index = "club", columns = "year", dropna = False
)
# Plot heatmap, in order of club with most wins to least.
sns.heatmap(
    winning_team_by_year_pivot.loc[
        winning_team_by_year_pivot.sum(axis = 1).sort_values(ascending = False).index
    ],
    robust = True
)
plt.yticks(rotation = 0) 
plt.show()

winning_team_by_year_pivot

# Join attendance data with club memberships and wins
year_club_stats = club_memberships_df.join(
    attendance_by_year, how = "inner"
).join(
    winning_team_by_year, how = "left"
)
year_club_stats = year_club_stats.reset_index()
year_club_stats

# Calculate the totals for each year across the combined clubs
year_stats = year_club_stats.groupby("year").sum()
# As there is always two clubs in every game, the total games should be halved.
year_stats.games = year_stats.games / 2
# Similarly attendees are spectators of both teams at the same game.
year_stats.attendees = year_stats.attendees / 2
# Calculate average attendance at each game
year_stats['avg attendance'] = year_stats.attendees / year_stats.games

# Exclude 2016 from the plot as that is an incomplete year
p_data = year_stats#[year_stats.index != "2016"]
p = p_data.plot.scatter(x = "members", y = "avg attendance")

# Annotate the plot with the year
# Following solution from: http://stackoverflow.com/questions/32154518/annotate-labels-in-pandas-scatter-plot
def annotate_df(row):  
    p.annotate(row.name, row[['members', 'avg attendance']].values)

p_data.apply(annotate_df, axis = 1)

plt.show()

# Similar code to the above, same explanation.
p_data = year_stats#[year_stats.index != "2016"]
p = p_data.plot.scatter(x = "members", y = "attendees")

def annotate_df(row):  
    p.annotate(row.name, row[['members', 'attendees']].values)

p_data.apply(annotate_df, axis = 1)

plt.show()

# Similar code to the above, same explanation.
p_data = year_stats.join(dnp_pcap_au, how = "inner")
p =  p_data.plot.scatter(x = "GNI_per_capita", y = "members")

def annotate_df(row):  
    p.annotate(row.name, row[['GNI_per_capita', 'members']].values)

p_data.apply(annotate_df, axis = 1)

plt.show()

# Similar code to the above, same explanation.
p_data = year_stats.join(dnp_pcap_au, how = "inner")
p = p_data.plot.scatter(x = "GNI_per_capita", y = "attendees")

def annotate_df(row):  
    p.annotate(row.name, row[['GNI_per_capita', 'attendees']].values)

p_data.apply(annotate_df, axis = 1)

plt.show()

# As we only have current Twitter followers we must compare to the most up to date
# club stats. The last complete year for club stats is 2015 so we compare with that year.

# Join the Twitter data with the year_club_stats for 2015.
club_stats_2015 = year_club_stats[year_club_stats.year == "2015"].join(
    afl_clubs_df, how = "outer", on = "club"
)

# Keep only the columns we are interested in.
club_stats_2015 = club_stats_2015[
    ['club', 'members', 'attendees', 'games', 'wins', 'followers', 'avg attendance']
]

# Plot two metrics from the above
club_stats_2015.plot.scatter(x = "members", y = "followers")

club_stats_2015.plot.scatter(x = "avg attendance", y = "followers")

year_club_stats.plot.scatter(x = "members", y = "avg attendance")

year_club_stats.plot.scatter(x = "members", y = "wins")

year_club_stats.plot.scatter(x = "avg attendance", y = "wins")