Data Wrangling With Fantasy Football and Pandas
Posted on Wed 21 March 2018 in Data-Science • 14 min read
Data Wrangling with Pandas¶
A couple of simple examples using some of the powerful built in pandas functions. The data is taken from a DraftFantasy.com league.
import urllib
from urllib.request import urlopen
import json
import pandas as pd
import numpy as np
from time import sleep
Data Retrieval¶
Retrieve data from a GraphQL endpoint. There's no authentication/token required to query the endpoint, so we just need to know the correct query, which in this case is encoded as a single string.
Data is stored as a list of dicts, which a Pandas dataframe is created from.
base_url="https://draftfantasyfootball.co.uk/graphql"
gw=1
latest_gw=32
gw_data=[]
while gw <= latest_gw:
#Let's be a friendly scraper and throw a sleep in. 3 seconds should do the trick.
sleep(3)
try:
d={"operationName":"getHeadToHeadMatches","variables":{"leagueId":"ABWkR4hBw4GWmSp8q","gameweek":gw},"query":"query getHeadToHeadMatches($leagueId: String!, $gameweek: Int!) {\n headToHeadMatches(leagueId: $leagueId, gameweek: $gameweek) {\n _id\n gameweek\n leagueId\n team1 {\n _id\n name\n liveGameweekPoints(gameweek: $gameweek)\n user {\n profile {\n firstName\n lastName\n __typename\n }\n __typename\n }\n __typename\n }\n team2 {\n _id\n name\n liveGameweekPoints(gameweek: $gameweek)\n user {\n profile {\n firstName\n lastName\n __typename\n }\n __typename\n }\n __typename\n }\n __typename\n }\n}\n"}
hdr = {'content-type': 'application/json'}
req = urllib.request.Request(base_url,headers=hdr, data=bytes(json.dumps(d), encoding="utf-8"))
site_data = urlopen(req)
data = json.load(site_data)
gw_data+=data['data']['headToHeadMatches']
gw+=1
except Exception as e:
print("error retrieving : {}".format(base_url))
print(e)
break
df = pd.DataFrame(gw_data)
df.head()
Data Wrangling¶
The data is created from a hierarchical JSON format which is reflected in the data frame. Team information is stored within a dict within the data frame. The first thing we need to do is expand the dict and flatten the data.
Our observations are based around each team's gameweek, so that's what each row should reflect.
Metling data¶
We want a single team colum. The melt makes our dataframe 'less wide' i.e. unpivots our team1/team2 column and preserves the remaining columns.
We can then drop a bunch of columns that we're not interested in and expand the dict in the resuling 'value' column to the values that we're interested in
df['match_id'] = df.index
df_1 = pd.melt(df, id_vars=["__typename", "_id", "gameweek","leagueId","match_id"], var_name="team")
df_2 = pd.concat([df_1.drop(['value','leagueId','team','__typename'], axis=1), df_1['value'].apply(pd.Series)], axis=1)
df_2.head(8)
Handling Missing Average Data¶
The data we retrieved from the site is missing team information for the 'Average' team. These need to be calculated and filled in.
The name is trivial, we can just use the built in fillna function on the name column.
Calculating the average score is slightly less obvious, but is a good opportunity to use the transform function. We call transform on our groupby object which which returns a NDFrame. The method we call replaces the NaN in each group with the mean of the other values.
We want an int for our scope so apply np.round to the resulting score.
df_3 = df_2.drop(["_id","__typename","user"], axis=1).sort_values(by='match_id').reset_index(drop=True)
df_3[['name']] = df_3[['name']].fillna(value='Average')
df_3["liveGameweekPoints"] = df_3.groupby("gameweek")["liveGameweekPoints"].transform(lambda x: x.fillna((x.mean())))
df_3["liveGameweekPoints"] = df_3["liveGameweekPoints"].apply(np.round)
df_3.head()
Calculating Opponent Score and Resulting Points¶
We duplicate the gameweek points column and group by our "match_id" (i.e. the duplicate of the initial dataframe index). We then want to reverse each grouped object and return a dataframe - this is what the apply and reset_index functions do.
We then apply the logic to calculate score i.e. 3 points for a win, 1 for a draw, 0 for a loss.
The rolling score for each week as calculated using the cumsum function.
df_4 = df_3
df_4['opponent_score'] = df_4['liveGameweekPoints']
df_4['opponent_name'] = df_4['name']
df_4['opponent_score'] = df_4.groupby("match_id")['opponent_score'].apply(lambda x: x.iloc[::-1]).reset_index(drop=True)
df_4a=df_4
df_4a['opponent_name'] = df_4.groupby("match_id")['opponent_name'].apply(lambda x: x.iloc[::-1]).reset_index(drop=True)
df_4.head()
df_5=df_4
df_4['score'] = df_4.apply(lambda x: 3 if x['liveGameweekPoints'] > x['opponent_score'] else 1 if x['liveGameweekPoints'] == x['opponent_score'] else 0, axis=1)
df_6=df_5
df_6['rolling_score']=df_6.groupby(by="name")['score'].cumsum()
Flattening and Graphing the Data¶
Group by manager name and gameweek. Expand the dataframe using the unstack function.
Finally plot the data into a graph.
# use unstack()
df_6_actual = df_6[df_6['gameweek']<latest_gw]
df_6_projected = df_6[df_6['gameweek']>=latest_gw]
df_7_actual = df_6_actual.groupby(['gameweek','name']).first()['rolling_score'].unstack()
df_7_projected = df_6_projected.groupby(['gameweek','name']).first()['rolling_score'].unstack()
%matplotlib inline
import matplotlib.pyplot as plt
from matplotlib import animation
# plot data
fig, ax = plt.subplots(figsize=(15,7))
df_7_actual.plot(ax=ax)
