Convert value to 1 if it is max or min in a row

Question

I have the following dataframe:

final_odds <- structure(list(player_prop = c("Aaron Jones: Rush + Rec Yards", 
"Aaron Jones: Rush + Rec Yards", "Aaron Rodgers: Interceptions", 
"Aaron Rodgers: Interceptions", "Aaron Rodgers: Pass TDs", "Aaron Rodgers: Pass TDs", 
"Aaron Rodgers: Pass Yards", "Aaron Rodgers: Pass Yards", "Adam Thielen: Rec Yards", 
"Adam Thielen: Rec Yards"), Side = c("Over", "Under", "Over", 
"Under", "Over", "Under", "Over", "Under", "Over", "Under"), 
    DraftKings = c(1300.5, 1300.5, 7.5, 7.5, 31.5, 31.5, 4050.5, 
    4050.5, 750.5, 750.5), BetMGM = c(50000, 0, 50000, 0, 50000, 
    0, 50000, 0, 699.5, 699.5), FanDuel = c(50000, 0, 50000, 
    0, 30.5, 30.5, 3950.5, 3950.5, 750.5, 750.5), Caesars = c(50000, 
    0, 50000, 0, 30.5, 30.5, 4000.5, 4000.5, 775.5, 775.5)), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"))

I'm trying to create a function that evaluates columns 3-6 within each row to identify which is the highest if the Side value is "Under" or the lowest if the Side value is "Over" and converts those values to a 1 with the rest being a 0.

So, I would want the final dataframe to look like this based on the given dataframe:

Is there a simple function I could write to accomplish this?

@akrun well it should be evaluated for each individual row, so I guess technically grouped by `player_prop` and `Side` — Sam Hoppen, Jul 27 '22 at 15:46
Hi, if any answers have solved your question, you could choose one of them you prefer as accepted by clicking the check-mark. Thanks! — Darren Tsai, Aug 10 '22 at 03:41

score 0 · Answer 1 · answered Jul 27 '22 at 15:51

Here is an option after reshaping to 'long' format

library(dplyr)
library(tidyr)
final_odds %>% 
 mutate(rn = row_number()) %>%
 pivot_longer(cols = DraftKings:Caesars) %>% 
 group_by(rn) %>%
 mutate(value = +(case_when(Side == 'Under' ~ value == max(value), 
    Side == 'Over' ~value == min(value)))) %>%
 ungroup %>% 
 pivot_wider(names_from = name, values_from = value) %>%
 select(-rn)

-output

# A tibble: 10 × 6
   player_prop                   Side  DraftKings BetMGM FanDuel Caesars
   <chr>                         <chr>      <int>  <int>   <int>   <int>
 1 Aaron Jones: Rush + Rec Yards Over           1      0       0       0
 2 Aaron Jones: Rush + Rec Yards Under          1      0       0       0
 3 Aaron Rodgers: Interceptions  Over           1      0       0       0
 4 Aaron Rodgers: Interceptions  Under          1      0       0       0
 5 Aaron Rodgers: Pass TDs       Over           0      0       1       1
 6 Aaron Rodgers: Pass TDs       Under          1      0       0       0
 7 Aaron Rodgers: Pass Yards     Over           0      0       1       0
 8 Aaron Rodgers: Pass Yards     Under          1      0       0       0
 9 Adam Thielen: Rec Yards       Over           0      1       0       0
10 Adam Thielen: Rec Yards       Under          0      0       0       1

Darren Tsai · Answer 2 · 2022-07-27T16:01:59.750

You could try

library(dplyr)

final_odds %>%
  mutate(tmp = across(3:6) %>% {ifelse(Side == "Over", do.call(pmin, .), do.call(pmax, .))},
         across(3:6, ~ +(.x == tmp))) %>%
  select(-tmp)

# # A tibble: 10 × 6
#    player_prop                   Side  DraftKings BetMGM FanDuel Caesars
#    <chr>                         <chr>      <int>  <int>   <int>   <int>
#  1 Aaron Jones: Rush + Rec Yards Over           1      0       0       0
#  2 Aaron Jones: Rush + Rec Yards Under          1      0       0       0
#  3 Aaron Rodgers: Interceptions  Over           1      0       0       0
#  4 Aaron Rodgers: Interceptions  Under          1      0       0       0
#  5 Aaron Rodgers: Pass TDs       Over           0      0       1       1
#  6 Aaron Rodgers: Pass TDs       Under          1      0       0       0
#  7 Aaron Rodgers: Pass Yards     Over           0      0       1       0
#  8 Aaron Rodgers: Pass Yards     Under          1      0       0       0
#  9 Adam Thielen: Rec Yards       Over           0      1       0       0
# 10 Adam Thielen: Rec Yards       Under          0      0       0       1

Convert value to 1 if it is max or min in a row

2 Answers2