Using dplyr::lag to tidy data frame and fill variables

Question

I'm trying to clean my data so that every row directly beneath a row that contains "gamecentre-playbyplay-event" is labeled as a goal, every row that contains "gamecentre-playbyplay-event" directly beneath a "goal" row is labeled primary assist, and every row that contains "gamecentre-playbyplay-event" directly beneath a "primary assist" row is labeled secondary assist.

Here's what the data looks like:

mydata

# A tibble: 15 x 1
   value                                                                                 
                                                                                    
 1 "



There are a few problems here though.


I need to set conditions so that the rows are correctly labeled.
If there is no "secondary assist" row, the row is labeled as an NA.
If there is no "primary assist" row, the row is also labeled as an NA.


I'm trying to use dplyr::lag() for this, but me wanting NAs when there aren't primary or secondary assists is confusing.

Here's the basis of what I have so far:

goals <- mydata %>%
  filter(dplyr::lag(str_detect(value, "gamecentre-playbyplay-event team-border"), 1))

goals

# A tibble: 4 x 1
  value                                                                                                                                
                                                                                                                                  
1 "


And here's what I'd want my data to look like at the end of all of this. I think using dplyr::lag() is the way to go, but I'm not sure.

# A tibble: 4 x 3
  goal                                     primary_assist                                secondary_assist                              
                                                                                                                        
1 "


Any ideas?

dput:

    mydata <- structure(list(value = c("

akrun · Accepted Answer

An option would be to create a grouping variable and then spread

library(tidyverse)
mydata %>%
   #create a group based on the occurrence of 'playby'
   group_by(grp = cumsum(str_detect(value, 'playby'))) %>% 
   # filter out the first row of the group that have playby
   filter(row_number() > 1) %>% 
   # create a new category column
   mutate(categ = c("goal", "primary_assist", "secondary_assist")[row_number()]) %>%
   # spread from long to wide
   spread(categ, value) %>% 
   # remove the grouping column as part of clean up
   ungroup %>% 
   select(-grp)
# A tibble: 4 x 3
#  goal                                   primary_assist                              secondary_assist                           
#                                                                                                                 
#1 "                                       
#3 "

Using dplyr::lag to tidy data frame and fill variables

Answers (1)

Related Questions