0
$\begingroup$
GameID        Drive        down    yrdln        PlayType       sp
2009091000      1           1        22           run          0
2009091000      1           2        25           pass         0
2009091000      1           3        32           sack         0
2009091000      2           1        42           pass         0
2009091000      2           2        44           run          0
2009091000      2           3        43           pass         0
2009091000      2           4        33           Field Goal   1
2009091001      1           1        5            pass         0
2009091001      1           2        10           pass         1

I am to trying determine the probability of scoring either a touchdown and/or a Field Goal based on a given starting yard line. The information given above is very similar to the data frame I am currently using, however there are a few hundred thousand data entries, with thousands of games and multiple drives for each game.

I have been trying to find the first and last row for each drive for each GameID and then determine if a Field Goal or Touchdown was scored at the end of the drive. Ultimately I would like the data organized in a way where each row has game ID, drive, 1st play yrdln, last play yrdln, Field Goal (yes or no), and sp (scoring play). Or hopefully something close to this.

I don't really know where to begin with something like this and have been failing for a good part of a day trying, so any and all help is greatly appreciated. If anything needs further clarification, please let me know!

$\endgroup$
0
$\begingroup$

The R code below should help you clean your data. Please note that I made some assumptions -- see the comments.

library(dplyr)
df = read.delim(['Path to your data'], sep = ",")

# I am making the assumption that record can be uniquely identified by the gameid, drive and down variable

df_drive = df %>% group_by(GameID) %>% mutate(max_drive = max(Drive), min_drive = min(Drive)) %>% select(GameID,Drive, down, max_drive, min_drive)
df_down = df %>% group_by(GameID, Drive) %>% mutate(max_down = max(down), min_down = min(down)) %>% select(GameID, Drive,down, max_down, min_down) 

# Max and Min List
min_max = inner_join(df_drive,df_down,by=c("GameID","Drive","down")) 

# Determine first and last row for each GameID
keep = min_max %>% mutate(idx = ifelse(Drive==min_drive & down == min_down, 'first', ifelse(Drive==max_drive & down == max_down, 'last', 'other'))) %>% filter(idx %in% c('first','last'))

games = inner_join(df, keep, by=c("GameID","Drive","down")) 
games %>% filter(idx == 'first') %>% mutate(field_goal = ifelse(PlayType=='Field Goal','yes','no')) %>% select(GameID,yrdln, field_goal)

# I am making the assumption that sp and down will come from the last record.
first_play = games %>% filter(idx == 'first') %>% select(GameID,yrdln) %>% rename(first_play_yrdln = yrdln)
last_play = games %>% filter(idx == 'last') %>% mutate(field_goal = ifelse(PlayType=='Field Goal','yes','no')) %>% select(GameID,Drive,yrdln, field_goal, sp)  %>% rename(last_play_yrdln = yrdln)

df_final = inner_join(first_play, last_play , by=c("GameID")) %>% select(GameID, Drive, first_play_yrdln, last_play_yrdln, field_goal, sp) 
df_final

You should get:

      GameID Drive first_play_yrdln last_play_yrdln field_goal sp
1 2009091000     2               22              33        yes  1
2 2009091001     1                5              10         no  1
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.