2c:null 2d:["$","title",null,{"children":"NFL Player Comparison 2023 Week 1-4 | Tanya Shapiro | Observable"}] 32:T696,Plot.plot({ width:1200, height:850, y: {tickSize:0, domain: [0, roundedMaxStatValue*1.2], grid:axisValues(maxStatValue, splitBy), label:null}, style: {backgroundColor:"transparent", fontFamily:"Chivo"}, //set facet for y and x to row_num and col_num in query, hide labels with CSS (bottom of notebook) facet: {data:totals, y:"row_num", x:"col_num", label:null}, marks:[ // create x axis line at 0 Plot.ruleY([0], {stroke: "black", strokeWidth:1}), // format x axis, add 'W' before each week number Plot.axisX({tickSize:0, tickFormat: d => 'W'+d, label:'Week'}), //format y axis Plot.axisY({tickSize:0, ticks: axisValues(maxStatValue, splitBy)}), //add in player rank # as a header Plot.text(totals, {text:"rank", y: d=> roundedMaxStatValue*1.15, x: d=> 1.2, fontSize:16, fontWeight:'bold', textAnchor:"end"}), //add in player name to header Plot.text(totals, {text:"player_name", y: d=> roundedMaxStatValue*1.15, x: d=> 1.3, fontSize:16, textAnchor:"start"}), //add in team logo to header Plot.image(totals, {y: d=> roundedMaxStatValue*1.15, x: d=> 3.8, src:"team_logo_espn", height:25}), //plot weekly average Plot.line(weekly_avg, {x:"week", y:"avg", fy: "row_num", fx:"col_num", stroke: "#A2A2A2", strokeWidth:3}), //plot week-over-week stat per player, map stroke color to team color Plot.line(weekly, {x:"week", y:"stat", fy: "row_num", fx:"col_num", stroke: d=> teams.filter(x => x.team_abbr === d.team_abbr)[0].team_color, strokeWidth:3})] })33:[{"id":"d0bcea0c5a58dcbd8f5f936de0e4bd865679cdd80f919430191060a5e9670f56c3102e909d9b1f4e88bd7ac064ab83d1523abc6c2266c03778d73dfe323df226","href":"https://static.observableusercontent.com/files/d0bcea0c5a58dcbd8f5f936de0e4bd865679cdd80f919430191060a5e9670f56c3102e909d9b1f4e88bd7ac064ab83d1523abc6c2266c03778d73dfe323df226","name":"pbp.csv","type":"text/csv","lastModified":1696264971112.873,"size":26370062},{"id":"1ecf122bc3c8241383d2a89b91bbb8a7c15bfdee93d925aeecd9865532886dc5b3f97eaac6ed13021664e9b49aba27abbebcf5b287217f934c7c4945b0f8b39a","href":"https://static.observableusercontent.com/files/1ecf122bc3c8241383d2a89b91bbb8a7c15bfdee93d925aeecd9865532886dc5b3f97eaac6ed13021664e9b49aba27abbebcf5b287217f934c7c4945b0f8b39a","name":"players.csv","type":"text/csv","lastModified":1696264973490.956,"size":5061111},{"id":"af977620ed4a9fc6ea37f99ec081cdb166ac003532345ab698970b7bf5ed3461f522c2171769b5a40f280aeab57208070865e916dccae10e9975bc8c2498180b","href":"https://static.observableusercontent.com/files/af977620ed4a9fc6ea37f99ec081cdb166ac003532345ab698970b7bf5ed3461f522c2171769b5a40f280aeab57208070865e916dccae10e9975bc8c2498180b","name":"teams.csv","type":"text/csv","lastModified":1696264979868.705,"size":17417}] 30:{"id":"aaee255628c286a0","slug":null,"title":"NFL Player Comparison 2023 Week 1-4","thumbnail":"3e4b52f797bedc9bc0f2399588379c714363ef6a2fec96dd47b41bfd38a225c1","access_level":"public","create_time":"$D2023-10-02T16:40:47.393Z","update_time":"$D2023-10-02T20:52:25.836Z","view_time":null,"first_public_time":"$D2023-10-02T17:58:17.539Z","has_unpublished_edits":null,"fork":false,"stars":21,"starred":false,"owner":{"type":"team","id":"230f604926360997","login":"tashapiro","name":"Tanya Shapiro","avatar_url":"https://avatars-next.observableusercontent.com/28b8518718dc3e70bed3a8256215864c8ac22777155e21dd5564f285665f60ae","tier":"starter_2024"},"version":396,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"fccfb83ec070004a","login":"tashapiro","name":"Tanya Shapiro","avatar_url":"https://avatars-next.observableusercontent.com/28b8518718dc3e70bed3a8256215864c8ac22777155e21dd5564f285665f60ae","tier":null}],"members":[{"type":"individual","id":"fccfb83ec070004a","login":"tashapiro","name":"Tanya Shapiro","avatar_url":"https://avatars-next.observableusercontent.com/28b8518718dc3e70bed3a8256215864c8ac22777155e21dd5564f285665f60ae","tier":null,"role":"editor"}],"comments":[{"id":"94b4eec180d0a7e1","reply_to_id":null,"content":"This works fine, but alternatively you could create the totals as a table in the same db:\n\ndb = {\n const db = DuckDBClient.of({…});\n await db.query(`CREATE TABLE totals AS (select b. * … )`);\n return db;\n}","create_time":"$D2023-10-06T04:51:10.967Z","update_time":null,"resolved":false,"user":{"type":"individual","id":"45a379fcfcb14253","login":"fil","name":"Fil","avatar_url":"https://avatars-next.observableusercontent.com/d5b1777907117a2bdf2a07eb98600d7a9beb21639fde42b9753e95c88c7731a6","tier":null}}],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":18,"collection":null,"type":"notebook","body":{"title":"NFL Player Comparison 2023 Week 1-4","cells":[{"id":8,"value":"# NFL Player Comparison 2023 Week 1-4\n\nWeek over week comparison for NFL players. Displays top 25 by season total. Weekly average included for comparison. Data source from R package nflfastR.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":215,"value":"viewof selectedStat = Inputs.select(new Map([[\"Passing Yds\", \"passing_yards\"], [\"Rushing Yds\", \"rushing_yards\"], [\"Receiving Yds\", \"receiving_yards\"]]), {label:\"Select Stat\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":58,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":381,"value":"## Dynamix Axis Settings\n\nCreate a dynamic axis to scale with the selected stat. This will dictate the upper limit of the domain and the tick locations.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":277,"value":"maxStatValue = d3.max(weekly, (d) => d.stat);","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":347,"value":"splitBy = maxStatValue < 50 ? 10 : maxStatValue < 176 ? 25 : maxStatValue < 300 ? 50 : 100;","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":298,"value":"roundedMaxStatValue = Math.round(maxStatValue / splitBy) * splitBy;","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":350,"value":"function axisValues(maxStatValue, splitBy) {\n const values = [];\n for (let i = 0; i <= maxStatValue; i += splitBy) {\n values.push(i);\n }\n return values;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":177,"value":"select w.week\n ,p.col_num\n ,p.row_num\n ,p.player_name\n ,w.avg\nfrom (select \nweek\n,avg(stat) as avg\nfrom weekly \ngroup by 1) w\ncross join (select distinct player_name, col_num, row_num from weekly) p","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"weekly_avg","database":"var:weekly"},{"id":18,"value":"## Queries","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":69,"value":"select b.player_id\n ,p.display_name as player_name\n ,t.team_abbr\n ,b.week\n ,t.col_num\n ,t.row_num\n ,sum(b.stat::int)::int as stat\nfrom pbp b\nleft join players p on p.gsis_id = b.player_id\ninner join totals t on t.player_id = p.gsis_id\nwhere b.player_id <> 'NA' and b.stat <> 'NA'\ngroup by all\norder by b.player_id, week","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"weekly","database":"var:db2"},{"id":20,"value":"select b. * \n from (\n select a. *\n ,row_number() over () as rank\n ,(row_number() over ()-1) / 5 as row_num\n ,row_number() over ()-((row_number() over ()-1) / 5)*5 as col_num\n from(\nselect b.player_id\n ,p.display_name as player_name\n ,t.team_abbr\n ,t.team_logo_espn\n ,count(distinct week) as games_played\n ,sum(stat::int)::int as stat\nfrom pbp b\nleft join players p on p.gsis_id = b.player_id\nleft join teams t on t.team_abbr = p.team_abbr\nwhere b.player_id <> 'NA' and b.stat <> 'NA'\ngroup by all\nhaving count(distinct week)>=4\norder by 6 desc\n) a \n) b where rank < 26","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"totals","database":"var:db"},{"id":13,"value":"## Set up DB\n\nLoad csv tables as database tables. Makes it easier to write queries with joins. Set up an additional db (db2) with results from totals (can't have circular argument).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":15,"value":"db = DuckDBClient.of({\n pbp: updated_pbp, \n players: players, \n teams: teams\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":80,"value":"db2 = DuckDBClient.of({\n pbp: updated_pbp, \n players: players, \n teams: teams,\n totals: totals\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":0,"value":"## Import Data\n\nData taken from nflfastR and nflreadR. Represents 2023 season.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"pbp = await FileAttachment(\"pbp.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"pbp","database":"$undefined"},{"id":6,"value":"players = await FileAttachment(\"players.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"players","database":"$undefined"},{"id":7,"value":"teams = await FileAttachment(\"teams.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"teams","database":"$undefined"},{"id":126,"value":"","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":387,"value":"Map select stat to column name in pbp data before creating queries. Map playerId to respective player entity (e.g. passer_id)","pinned":true,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":311,"value":"playerId= selectedStat === 'passing_yards' ? 'passer_id' : selectedStat === 'rushing_yards' ? 'rusher_id' : 'receiver_id';","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":266,"value":"updated_pbp = pbp.map((item) => {\n // Calculate the \"stat\" value based on the \"yardline_100\" property (you can replace this with your logic)\n const stat = item[selectedStat];\n const player_id = item[playerId];\n\n // Create a new object with the \"stat\" property added\n return { ...item, stat, player_id };\n});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1","titling":"auto"}} 31:"$30:body"