// add play play duration, play number within a drive and play number within a game play properties
data = {
let data = data_2;
// sort plays by timestamp (some plays are switched in raw data)
data.sort(function(p1, p2) {
if (p1.game_id < p2.game_id) {
return -1;
} else if (p1.game_id > p2.game_id) {
return 1;
} else {
return (p1.timestamp - p2.timestamp);
}
});
data[0].play_duration = data[0].quarter_seconds_remaining - data[1].quarter_seconds_remaining;
data[0].play_num_game = 1;
data[0].play_num_drive = 1;
for (let i = 1; i < data.length - 1; i++) {
if ((data[i].game_id == data[i+1].game_id) && (data[i].quarter == data[i+1].quarter)) {
data[i].play_duration = data[i].quarter_seconds_remaining - data[i+1].quarter_seconds_remaining;
} else {
data[i].play_duration = data[i].quarter_seconds_remaining;
}
if (data[i].game_id == data[i-1].game_id) {
data[i].play_num_game = data[i-1].play_num_game + 1;
} else {
data[i].play_num_game = 1;
}
if ((data[i].game_id == data[i-1].game_id) && (data[i].drive == data[i-1].drive)) {
data[i].play_num_drive = data[i-1].play_num_drive + 1;
} else {
data[i].play_num_drive = 1;
}
}
data[data.length-1].play_duration = data[data.length-1].quarter_seconds_remaining;
data[data.length-1].play_num_game = data[data.length-2].play_num_game + 1;
data[data.length-1].play_num_drive = data[data.length-2].play_num_drive + 1;
return data;
}
// remove non-play data
data_2 = data_1.filter(function(play) {
return (play.play_type != 'normal') || (play.play_result != null) || (play.quarter_seconds_remaining > 0);
})
// non-play data to be removed
data_1.filter(function(play) {
return (play.play_type == 'normal') && (play.play_result == null) && (!play.quarter_seconds_remaining);
}).map(p => p.description)
// map individual plays from raw data
data_1 = data_raw.map(function(play) {
// copy over important properties
let new_play = {
// game and drive properties
game_id: play.game_id,
home_team: play.home_team,
away_team: play.away_team,
quarter: play.qtr,
timestamp: null,
quarter_seconds_remaining: play.quarter_seconds_remaining,
pos_team: play.posteam,
drive: play.drive,
home_score: play.total_home_score,
away_score: play.total_away_score,
// play setup properties
play_num_game: null,
play_num_drive: null,
yard_line: play.yardline_100,
down: play.down,
yards_to_go: play.ydstogo,
no_huddle: play.no_huddle,
timeout_team: play.timeout_team,
play_type: null,
play_formation: null,
ep: Math.round(play.ep * 100000) / 100000,
wp: Math.round(play.wp * 100000) / 100000,
// play progression properties
play_attempt: null,
qb_dropback: play.qb_dropback,
qb_hit: play.qb_hit,
lateral: play.lateral_reception || play.lateral_rush || play.lateral_return || play.lateral_recovery,
pass_location: play.pass_location,
pass_completed: play.complete_pass,
air_yards: play.air_yards,
yards_after_catch: play.yards_after_catch,
run_location: play.run_location,
run_gap: play.run_gap,
kick_distance: play.kick_distance,
return_yards: play.return_yards,
fumble: play.fumble,
fumble_forced: play.fumble_forced,
fumble_recovery_team: play.fumble_recovery_1_team,
fumble_recovery_yards: play.fumble_recovery_1_yards,
interception: play.interception,
passer_player_name: play.passer_player_name,
receiver_player_name: play.receiver_player_name,
rusher_player_name: play.rusher_player_name,
tackle_player_names: null,
interception_player_name: play.interception_player_name,
forced_fumble_player_name: play.forced_fumble_player_1_player_name,
kicker_player_name: play.punter_player_name || play.kicker_player_name,
kick_returner_player_name: play.punt_returner_player_name || play.kickoff_returner_player_name,
blocked_player_name: play.blocked_player_name,
qb_hit_player_names: null,
pass_defense_player_names: null,
penalty_player_name: play.penalty_player_name,
// play result properties
play_result: null,
play_duration: null,
play_yards: null,
description: play.desc || '',
points_scored: 0,
yards_gained: play.yards_gained,
penalty: play.penalty,
penalty_team: play.penalty_team,
penalty_type: play.penalty_type,
penalty_declined: play.penalty ? (play.desc.toLowerCase().includes(', declined.') ? 1 : 0) : null,
penalty_yards: play.penalty_yards,
replay_or_challenge_result: play.replay_or_challenge_result,
epa: Math.round(play.epa * 100000) / 100000,
wpa: Math.round(play.wpa * 100000) / 100000
};
// assemble timestamp property
if ((play.qtr <= 4) || (play.game_id.toString().substring(0, 4) < '2017')) {
new_play.timestamp = ((play.qtr - 1) * 900 + (900 - play.quarter_seconds_remaining));
} else {
new_play.timestamp = ((play.qtr - 1) * 900 + (600 - play.quarter_seconds_remaining));
}
// fix occassionally missing description properties
if (!play.desc) {
play.desc = '';
}
// fix occassionally missing timeout properties
if (!play.timeout && play.desc.startsWith('Timeout')) {
play.timeout = 1;
}
// fix occassionally missing penalty properties
if (!play.penalty && play.desc.toLowerCase().includes('penalty') && !play.desc.toLowerCase().includes('reversed')) {
play.penalty = 1;
new_play.penalty = 1;
new_play.penalty_declined = (play.desc.toLowerCase().includes(', declined.')) ? 1 : 0;
if (play.desc.search(/(?<=[P|p]enalty\son\s)[A-Z]+(?=[-|,])/) >= 0) {
new_play.penalty_team = play.desc.match(/(?<=[P|p]enalty\son\s)[A-Z]+(?=[-|,])/)[0];
}
}
// fix occassionally missing kick_distance properties
if (play.kickoff_attempt && (play.kick_distance == null)) {
if (play.desc.search(/(?<=kicks\s)\d+(?=\syards)/) >= 0) {
new_play.kick_distance = parseInt(play.desc.match(/(?<=kicks\s)\d+(?=\syards)/)[0]);
} else {
new_play.kick_distance = 0;
}
} else if (play.punt_attempt && (play.kick_distance == null)) {
if (play.desc.search(/(?<=punts\s)\d+(?=\syards)/) >= 0) {
new_play.kick_distance = parseInt(play.desc.match(/(?<=punts\s)\d+(?=\syards)/)[0]);
} else {
new_play.kick_distance = 0;
}
}
// assemble play_type property
if ((play.desc) && (play.desc.toLowerCase().includes('end quarter 1'))) {
new_play.play_type = 'end_quarter_1';
} else if ((play.desc) && (play.desc.toLowerCase().includes('end quarter 2'))) {
new_play.play_type = 'end_quarter_2';
} else if ((play.desc) && (play.desc.toLowerCase().includes('end quarter 3'))) {
new_play.play_type = 'end_quarter_3';
} else if ((play.desc) && (play.desc.toLowerCase().includes('end quarter 4'))) {
new_play.play_type = 'end_quarter_4';
} else if ((play.desc) && (play.desc.toLowerCase().includes('end game'))) {
new_play.play_type = 'end_game';
} else if ((play.desc) && (play.desc.toLowerCase().includes('two-minute warning'))) {
new_play.play_type = 'two_minute_warning';
} else if (play.timeout && !play.replay_or_challenge) {
new_play.play_type = 'timeout';
} else if (play.kickoff_attempt) {
new_play.play_type = 'kickoff';
} else if (play.extra_point_attempt || play.two_point_attempt) {
new_play.play_type = 'extra_points';
} else {
new_play.play_type = 'normal';
}
// assemble play_formation property
if (['kickoff', 'extra_points', 'normal'].includes(new_play.play_type)) {
if (play.kickoff_attempt) {
new_play.play_formation = 'kickoff';
} else if (play.punt_attempt || ((play.desc) && play.desc.toLowerCase().includes('punt'))) {
new_play.play_formation = 'punt';
} else if (play.field_goal_attempt || ((play.desc) && play.desc.toLowerCase().includes('field goal')) || play.extra_point_attempt) {
new_play.play_formation = 'field_goal';
} else if (play.shotgun) {
new_play.play_formation = 'shotgun';
} else {
new_play.play_formation = 'regular';
}
}
// assemble play_attempt property
if (play.two_point_attempt) {
new_play.play_attempt = 'two_point_conversion';
} else if (play.qb_kneel) {
new_play.play_attempt = 'qb_kneel';
} else if (play.qb_spike) {
new_play.play_attempt = 'qb_spike';
} else if (play.qb_scramble) {
new_play.play_attempt = 'qb_scramble';
} else if (play.pass_attempt) {
new_play.play_attempt = 'pass';
} else if (play.rush_attempt) {
new_play.play_attempt = 'run';
} else if (play.punt_attempt) {
new_play.play_attempt = 'punt';
} else if (play.field_goal_attempt) {
new_play.play_attempt = 'field_goal';
} else if (play.extra_point_attempt) {
new_play.play_attempt = 'extra_point';
} else if (play.kickoff_attempt) {
new_play.play_attempt = 'kickoff';
}
// assemble play_result property
if ((play.return_touchdown) || (play.fumble_lost && play.touchdown) || (play.interception && play.touchdown)) {
new_play.play_result = 'return_touchdown';
} else if (play.touchdown) {
new_play.play_result = 'touchdown';
} else if (play.fumble_lost) {
new_play.play_result = 'fumble_lost';
} else if (play.fumble_out_of_bounds) {
new_play.play_result = 'fumble_out_of_bounds';
} else if (play.safety) {
new_play.play_result = 'safety';
} else if (play.field_goal_result == 'made') {
new_play.play_result = 'field_goal_made';
} else if (play.field_goal_result == 'missed') {
new_play.play_result = 'field_goal_missed';
} else if (play.field_goal_result == 'blocked') {
new_play.play_result = 'field_goal_blocked';
} else if (play.extra_point_result == 'good') {
new_play.play_result = 'extra_point_good';
} else if (play.extra_point_result == 'failed') {
new_play.play_result = 'extra_point_failed';
} else if (play.extra_point_result == 'blocked') {
new_play.play_result = 'extra_point_blocked';
} else if (play.two_point_conv_result == 'success') {
new_play.play_result = 'two_point_conversion_success';
} else if (play.two_point_conv_result == 'failure') {
new_play.play_result = 'two_point_conversion_failure';
} else if (play.touchback) {
new_play.play_result = 'touchback';
} else if (play.punt_downed) {
new_play.play_result = 'punt_downed';
} else if (play.punt_fair_catch) {
new_play.play_result = 'punt_fair_catch';
} else if (play.punt_out_of_bounds) {
new_play.play_result = 'punt_out_of_bounds';
} else if (play.punt_blocked) {
new_play.play_result = 'punt_blocked';
} else if (play.kickoff_downed) {
new_play.play_result = 'kickoff_downed';
} else if (play.kickoff_fair_catch) {
new_play.play_result = 'kickoff_fair_catch';
} else if (play.kickoff_out_of_bounds) {
new_play.play_result = 'kickoff_out_of_bounds';
} else if (play.kickoff_own_recovery) {
new_play.play_result = 'kickoff_own_recovery';
} else if ((play.desc) && (play.desc.toLowerCase().includes(', offsetting.'))) {
new_play.play_result = 'offsetting_penalties';
} else if ((play.penalty) && (play.penalty_team == play.posteam) && (!new_play.penalty_declined)) {
new_play.play_result = 'offensive_penalty';
} else if ((play.penalty) && (play.penalty_team == play.defteam) && (!new_play.penalty_declined)) {
new_play.play_result = 'defensive_penalty';
} else if (play.interception) {
new_play.play_result = 'interception';
} else if (play.sack) {
new_play.play_result = 'sack';
} else if (play.punt_attempt) {
new_play.play_result = 'punt_return';
} else if (play.kickoff_attempt) {
new_play.play_result = 'kickoff_return';
} else if (play.fourth_down_failed) {
new_play.play_result = 'turnover_on_downs';
} else if ((play.yards_gained > 0) && (play.yards_gained >= play.ydstogo)) {
new_play.play_result = 'first_down';
} else if (play.down == 1) {
new_play.play_result = 'second_down';
} else if (play.down == 2) {
new_play.play_result = 'third_down';
} else if (play.down == 3) {
new_play.play_result = 'fourth_down';
} else if (play.penalty && new_play.penalty_declined) {
// intentional penalties, which are declined
new_play.play_result = 'declined_penalty';
}
// assemble play_yards property
if (['pass', 'run', 'qb_scramble', 'qb_kneel', 'qb_spike'].includes(new_play.play_attempt)) {
new_play.play_yards = new_play.yards_gained;
} else if (['kickoff', 'punt', 'field_goal'].includes(new_play.play_attempt) && (new_play.play_result != 'field_goal_missed')) {
new_play.play_yards = new_play.kick_distance;
} else {
new_play.play_yards = 0;
}
if (new_play.return_yards) {
new_play.play_yards -= new_play.return_yards;
}
if (new_play.fumble_recovery_yards) {
if (new_play.fumble_recovery_team == new_play.pos_team) {
new_play.play_yards += new_play.fumble_recovery_yards;
} else {
new_play.play_yards -= new_play.fumble_recovery_yards;
}
}
if (new_play.penalty_yards) {
if (new_play.penalty_team == new_play.pos_team) {
new_play.play_yards -= new_play.penalty_yards;
} else {
new_play.play_yards += new_play.penalty_yards;
}
}
// assemble points_scored property
if (play.touchdown || play.return_touchdown) {
new_play.points_scored = 6;
} else if (play.extra_point_result == 'good') {
new_play.points_scored = 1;
} else if (play.two_point_conv_result == 'success') {
new_play.points_scored = 2;
} else if (play.field_goal_result == 'made') {
new_play.points_scored = 3;
} else if (play.safety) {
new_play.points_scored = 2;
}
// assemble tackle_player_names property
if (play.solo_tackle_1_player_name) {
new_play.tackle_player_names = play.solo_tackle_1_player_name;
if (play.solo_tackle_2_player_name) {
new_play.tackle_player_names += ', ' + play.solo_tackle_2_player_name;
}
} else if (play.assist_tackle_1_player_name) {
new_play.tackle_player_names = play.assist_tackle_1_player_name;
if (play.assist_tackle_2_player_name) {
new_play.tackle_player_names += ', ' + play.assist_tackle_2_player_name;
}
if (play.assist_tackle_3_player_name) {
new_play.tackle_player_names += ', ' + play.assist_tackle_3_player_name;
}
if (play.assist_tackle_4_player_name) {
new_play.tackle_player_names += ', ' + play.assist_tackle_4_player_name;
}
}
// assemble qb_hit_player_names property
if (play.qb_hit_1_player_name) {
new_play.qb_hit_player_names = play.qb_hit_1_player_name;
if (play.qb_hit_2_player_name) {
new_play.qb_hit_player_names += ', ' + play.qb_hit_2_player_name;
}
}
// assemble pass_defense_player_names property
if (play.pass_defense_1_player_name) {
new_play.pass_defense_player_names = play.pass_defense_1_player_name;
if (play.pass_defense_2_player_name) {
new_play.pass_defense_player_names += ', ' + play.pass_defense_2_player_name;
}
}
return new_play;
})
data_raw = {
// read data from files
const data_1 = d3.csvParse(await FileAttachment("nfl_reg_pbp_2019-1.csv").text(), d3.autoType);
const data_2 = d3.csvParse(await FileAttachment("nfl_reg_pbp_2019-2.csv").text(), d3.autoType);
const data_3 = d3.csvParse(await FileAttachment("nfl_reg_pbp_2019-3.csv").text(), d3.autoType);
const data_4 = d3.csvParse(await FileAttachment("nfl_reg_pbp_2019-4.csv").text(), d3.autoType);
const data_5 = d3.csvParse(await FileAttachment("nfl_reg_pbp_2019-5.csv").text(), d3.autoType);
const data_6 = d3.csvParse(await FileAttachment("nfl_reg_pbp_2019-6.csv").text(), d3.autoType);
const data_7 = d3.csvParse(await FileAttachment("nfl_reg_pbp_2019-7.csv").text(), d3.autoType);
let data = data_1.concat(data_2).concat(data_3).concat(data_4)
.concat(data_5).concat(data_6).concat(data_7);
// replace 'NA' strings with 'null'
data = data.map(play => {
let new_play = play;
for (let property of Object.keys(play)) {
if (new_play[property] == 'NA') {
new_play[property] = null;
}
}
return new_play;
});
// sort plays in ascending order
data.sort(function(p1, p2) {
if (p1.game_id < p2.game_id) {
return -1;
} else if (p1.game_id > p2.game_id) {
return 1;
} else {
return (p1.play_id - p2.play_id);
}
});
return data;
}
d3 = require("d3@5")
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.