0

R Table

Here is the R code I am currently using:

library('tidyverse')
library ('dplyr')

Brian <- c(92.835, 89.035, 99.222, 93.581)
Buckley <- c(75.265, 86.258, 93.972, 96.872)
Chris <- c(91.442, 103.999, 91.291, 92.505)
Catherine <- c(81.244, 73.040, 78.455, 98.972)
David <- c(87.153, 60.062, 62.248, 87.852)
Donald <- c(93.395, 91.905, 102.502, 107.63)
Greg <- c(79.571, 73.702, 67.326, 89.493)
Matt <- c(78.585, 48.074, 81.387, 76.074)
Michael <- c(96.933, 78.709, 82.623, 66.325)

df <- data.frame(Brian, Buckley, Chris, Catherine, David, Donald, Greg, Matt, Michael)
group1 <- data.frame(Brian, Matt, Michael)
group2 <- data.frame(Buckley, Chris, Catherine)
group3 <- data.frame(David, Donald, Greg)

group1a <- group1 %>%
  mutate(Group1 = names(.)[max.col(.)])

group2a <- group2 %>%
  mutate(Group2 = names(.)[max.col(.)])

group3a <- group3 %>%
  mutate(Group3 = names(.)[max.col(.)])

GROUP1 <- dplyr::pull(group1a, 'Group1')
GROUP2 <- dplyr::pull(group2a, 'Group2')
GROUP3 <- dplyr::pull(group3a, 'Group3')

ALL <- cbind(df, GROUP1, GROUP2, GROUP3)

The code shows 4 rows of a much longer table. I've been stumped trying to find code that will allow me to append a column to this table that includes the name of the column from the highest value in the row that is not one of the names in the columns GROUP1, GROUP2, GROUP3. The output column would be headed "GROUP4", and would include Brian, Buckley, Chris, Buckley for these four rows.

I've tried looking through dplyr for codes that would fit this problem, but I'm new to this and have been stumped for a bit.

John
  • 3
  • 3
  • 3
    Welcome to SO, John! Please do not post (only) an image of code/data/errors: it breaks screen-readers and it cannot be copied or searched (ref: https://meta.stackoverflow.com/a/285557 and https://xkcd.com/2116/). Please include the code, console output, or data (e.g., `data.frame(...)` or the output from `dput(head(x))`) directly into a [code block]. – r2evans Jun 21 '23 at 19:53
  • 1
    Please [do not post code or data in images](https://meta.stackoverflow.com/q/285551/2372064). Share your data in a [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. – MrFlick Jun 21 '23 at 19:53
  • Related: https://stackoverflow.com/q/21421289/3358272, https://stackoverflow.com/q/64465629/3358272, and https://stackoverflow.com/q/63308044/3358272 (and likely several other links found under [`[r] column names for rows with max values`](https://stackoverflow.com/search?q=%5Br%5D+column%20names%20for%20rows%20with%20max%20values).) – r2evans Jun 21 '23 at 19:55
  • Thank you for the responses! I looked through the links provided and couldn't quite find what I was looking for. I have updated my post with code instead of just an image. Hopefully this helps, and thanks for any comments. – John Jun 23 '23 at 17:48

1 Answers1

0

(possible_names <- select(
  ALL,
  where(is.numeric)
) |> names())
(excl_names <- select(
  ALL,
  where(\(x)!is.numeric(x))
) |> names())

ALL2 <- mutate(rowwise(ALL),
  cols_to_check = list(setdiff(
    possible_names,
    c_across(all_of(excl_names))
  ))
)

(ALL3 <- ALL2 |> mutate(GROUP4 = (\(x){
  x[max.col(pick(x))]})(c_across(cols_to_check))
  ) |> ungroup() |> select(-cols_to_check))
Nir Graham
  • 2,567
  • 2
  • 6
  • 10
  • Thanks for the response! I couldn't get this to work, but working through it I was able to go through the ALL2 portion correctly. R was hanging up on the "|>" which I realized was a "%>%", and I believe "\(x)" is a "function x {}" but not quite sure. Anyway, this is what I converted the code to, to make the first portion work... possible_names <- select(ALL, where(is.numeric)) %>% names() excl_names <- select_if(ALL, negate(is.numeric)) %>% names() ALL2 <- mutate(rowwise(ALL), cols_to_check = list(setdiff(possible_names, c_across(all_of(excl_names))))) – John Jun 26 '23 at 20:29
  • R version 4.1 introduced the native pipe and the anonymous function syntax that you noticed. I.e `\(x)` is shorthand for `function(x)` – Nir Graham Jun 26 '23 at 22:41