R Iterate through data.frame rows using respective column value to return concatenated column names of any matching column values

Question

I'm having trouble wrapping my head around the steps needed to execute this in R. Essentially, I'm trying to take a row value, and compare its string against all of the other columns in my data frame, returning a concatenated list of column names where matches exist. Taking just the QB_slate_team column value to match against, I'd like to create the QB_Match column on the far right. Desired output showing team_stack_count table:

 QB_slate_team  RB_slate_team  WR_slate_team  QB_Match
 SEA            IND            GB             NA-NA
 GB             LV             GB             NA-WR_slate_team

[Detailed - Snag of team_stack_count table highlighting matched strings, as well as desired output column format] [1]: https://i.stack.imgur.com/Pxjs0.png

For example if QB_slate_team column row value = "GB" and WR_slate_team column row value = "GB" I'd expect the newly created QB_Match concatenated column to contain WR_slate_team among other NAs for columns that don't match.

After trying a bunch of different statements, this has gotten me the closest:

  team_stack_count$QB_Match <- apply(team_stack_count, 1, function(x) paste(names(x[x==team_stack_count$QB_slate_team]), collapse="-"))

But for some reason the team_stack_count$QB_slate_team that it is matching against seems to be a random static team name instead of iterating through the rows to get the appropriate value for each row-wise application.

Curious, is there any way to make the QB_slate_team row dynamic for the matching basis? (I believe I might be misunderstanding the apply statement and how it works - I'm open to any function that might help)

question from:https://stackoverflow.com/questions/65864338/r-iterate-through-data-frame-rows-using-respective-column-value-to-return-concat

Answer 1

The following does the trick by pivoting your data.

library(dplyr)
library(tidyr)
library(stringr)

df %>%
  pivot_longer(-QB_slate_team) %>%
  mutate(match = if_else(QB_slate_team == value, name, 'NA')) %>%
  group_by(QB_slate_team) %>%
  mutate(QB_Match = str_c(match, collapse = '-')) %>%
  ungroup() %>%
  select(-match) %>%
  pivot_wider(names_from = name) %>%
  select(- QB_Match, QB_Match)

#   QB_slate_team RB_slate_team WR_slate_team QB_Match        
#   <chr>         <chr>         <chr>         <chr>           
# 1 SEA           IND           GB            NA-NA           
# 2 GB            LV            GB            NA-WR_slate_team

Data

df <- structure(list(QB_slate_team = c("SEA", "GB"), RB_slate_team = c("IND", 
"LV"), WR_slate_team = c("GB", "GB")), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))