query  log
a pythonic query language
Sign in or create a new account.

matplotlib powered data graphics.

Activated, AssistedTackles, AuctionValue, AuctionValuePPR, BlockedKickReturnTouchdowns, BlockedKickReturnYards, BlockedKicks, DefensiveSnapsPlayed, DefensiveTeamSnaps, DefensiveTouchdowns, DraftKingsSalary, ExtraPointsAttempted, ExtraPointsHadBlocked, ExtraPointsMade, FanDuelSalary, FantasyDataSalary, FantasyPoints, FantasyPointsDraftKings, FantasyPointsFanDuel, FantasyPointsYahoo, FantasyPosition, FieldGoalPercentage, FieldGoalReturnTouchdowns, FieldGoalReturnYards, FieldGoalsAttempted, FieldGoalsHadBlocked, FieldGoalsLongestMade, FieldGoalsMade, FieldGoalsMade0to19, FieldGoalsMade20to29, FieldGoalsMade30to39, FieldGoalsMade40to49, FieldGoalsMade50Plus, FumbleReturnTouchdowns, FumbleReturnYards, Fumbles, FumblesForced, FumblesLost, FumblesOutOfBounds, FumblesOwnRecoveries, FumblesRecovered, GameDate, HomeOrAway, Humidity, InterceptionReturnTouchdowns, InterceptionReturnYards, Interceptions, IsGameOver, KickReturnFairCatches, KickReturnLong, KickReturnTouchdowns, KickReturnYards, KickReturnYardsPerAttempt, KickReturns, MiscAssistedTackles, MiscFumblesForced, MiscFumblesRecovered, MiscSoloTackles, Name, Number, OffensiveSnapsPlayed, OffensiveTeamSnaps, OffensiveTouchdowns, Opponent, PassesDefended, PassingAttempts, PassingCompletionPercentage, PassingCompletions, PassingInterceptions, PassingLong, PassingRating, PassingSackYards, PassingSacks, PassingTouchdowns, PassingYards, PassingYardsPerAttempt, PassingYardsPerCompletion, Played, PlayerGameID, PlayerID, PlayerSeasonID, PlayingSurface, Position, PositionCategory, PuntAverage, PuntInside20, PuntLong, PuntNetAverage, PuntNetYards, PuntReturnFairCatches, PuntReturnLong, PuntReturnTouchdowns, PuntReturnYards, PuntReturnYardsPerAttempt, PuntReturns, PuntTouchbacks, PuntYards, Punts, PuntsHadBlocked, QuarterbackHits, ReceivingLong, ReceivingTargets, ReceivingTouchdowns, ReceivingYards, ReceivingYardsPerReception, ReceivingYardsPerTarget, ReceptionPercentage, Receptions, RushingAttempts, RushingLong, RushingTouchdowns, RushingYards, RushingYardsPerAttempt, SackYards, Sacks, Safeties, SafetiesAllowed, Season, SeasonType, ShortName, SoloTackles, SpecialTeamsAssistedTackles, SpecialTeamsFumblesForced, SpecialTeamsFumblesRecovered, SpecialTeamsSnapsPlayed, SpecialTeamsSoloTackles, SpecialTeamsTeamSnaps, SpecialTeamsTouchdowns, Stadium, Started, Tackles, TacklesForLoss, Team, Temperature, Touchdowns, TwoPointConversionPasses, TwoPointConversionReceptions, TwoPointConversionReturns, TwoPointConversionRuns, VictivSalary, Week, WindSpeed, YahooSalary

The PyQL query format is: fields @ conditions.
  fields is a field or a comma delimited list of fields.
 conditions is a condition or an and delimited list of conditions.

Both fields and conditions are made up of terms.
A term is a valid Python expression in a name space made up of: database parameters; any imported python modules; PyQL Aggregators such as Average (A), Sum (S), and Replace (R); and other domain specific terms.

About the FantasyData Database Sample Queries

The Raw Data

This database is built from the FantasyData csv file called PlayerGame.2015.csv. The player images were downloaded from FantasyData using the urls given in the file called Player.2015.csv. These are available to use in scatter plots by specifing PlayerID as the third request parameter.

Like this for superbowl 50 rushers: RushingAttempts,RushingYards,PlayerID@SeasonType =3 and Week=4.

Join in Player Information with P.

One of the joys of domain specific query languages is that you can join in data as you like. And here I like P.Weight All of the headers of the FantasyData file called Player.2015.csv are available with this P. format. Here are a few important join fields

Team, Number, FantasyPosition, HeightFeet, HeightInches, Weight, BirthDate, College, Experience, DepthOrder, CollegeDraftYear, CollegeDraftRound, CollegeDraftPick, IsUndraftedFreeAgentitionRank

For example to see Height v Weight broken down by FantasyPosition use the PyQL:
P.Weight as 'Weight',(P.HeightFeet*12+P.HeightInches) as 'Height (inches)'@FantasyPosition

Agile Access to Database Parameters

The CamelCaseFormatting of the database parameters is logical, readable, and verbose. With domain specific query languages you make the rules for parameter reference. And here I like:

Access parameters by their capitalized letters and fill in lower case letters to remove any ambiguity. For example, to see a scatter plot of RushingYardsPerAttempt vs RushingAttempts (using player images as icons), use the PyQL:
You can also use all lower case letters if you spell out the whole thing: That is, `name` is recognized as `Name`.

A simple plot to start with compares fantasy point parameters:

To see the week, fantasy salary, and fantasy point for Matt Ryan during the regular season use the PyQL:
Week,FantasyDataSalary,FantasyPoints@Name=Matt Ryan and SeasonType=1

To see how fantasy points tracks with fantasy salary use the PyQL:
FantasyDataSalary,FantasyPoints@Position and SeasonType=1 and Position in [QB,RB,TE,WR]

To see how fantasy points tracks with fantasy salary for Running Backs on each team, use the PyQL:
FantasyDataSalary,FantasyPoints@Team and Position = RB and FantasyDataSalary>0?polyfit=1&transparency=0.2&marker_size=40
Note that polyfit=1 adds the linear (first order) fit and sorts teams by the slope of this line.

For a scatter plot of total passing yards v total rushing yards for playoff teams use the PyQL
S(RushingYards),S(PassingYards),R(PlayerID)@SeasonType!=2 and PassingAttempts>1 and PlayerID|$1 as Total Rushing Yards,$2 as Total Passing Yards,$3@($4=3) as 'Passing Yards v Rushing Yards\nfor playoff QBs in 2015'?polyfit=1
Note how the PlayerID in the third parameter position causes that player's image to be used as an icon.

To see Denver's passing yards for each week and passer use the PyQL
(Week+17*(SeasonType==3)+1*(Week==4 and SeasonType==3)) as 'Week', PassingYards, PlayerID @ (Team=DEN and SeasonType in [1,3] and PassingAttempts>0) as 'Denver Passing Yards for each Week and Passer'?ymin=0
Notice that I do a little Python work to approximate time on the x-axis through the playoffs. First I add 17 if this is a playoff game (SeasonType==3) and then add 1 more if it is week 4 of the playoffs. This way you can see the week of rest before the Super Bowl in week 22. This is a good example of Python being 'available at the query prompt' in PyQL databases.

I find this very simple one interesting: