How to group and order columns in r

Question

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 to 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 a 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!

Answer 1

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