I have this dataset ("m"):
structure(list(id = 1:4, A1 = c(20, 20, 20, 20), B1 = c(20, 20,
20, 20), A2 = c(19.638362385164, 8.11972427580967, 11.9810539199604,
11.179853022028), B2 = c(15.974094766586, 11.02249487622, 14.2062464923062,
9.6806947619966), A3 = c(1.41008362674648, 2.36347304689385,
5.89401239396941, 10.2478961230446), B3 = c(3.43887450489555,
8.70503533284162, 6.31828253609099, -3.40081750462382), A4 = c(0.021848581788209,
-0.946584014278003, -22.2024625188847, NA), B4 = c(-5.73617894284438,
1.6375826801224, 3.8312939605301, NA)), row.names = c(1L, 5L,
9L, 13L), class = "data.frame", reshapeWide = list(v.names = NULL,
timevar = "time", idvar = "id", times = 1:4, varying = structure(c("A1",
"B1", "A2", "B2", "A3", "B3", "A4", "B4"), .Dim = c(2L, 4L
))))
id A1 B1 A2 B2 A3 B3 A4 B4
1 1 20 20 19.638362 15.974095 1.410084 3.438875 0.02184858 -5.736179
5 2 20 20 8.119724 11.022495 2.363473 8.705035 -0.94658401 1.637583
9 3 20 20 11.981054 14.206246 5.894012 6.318283 -22.20246252 3.831294
13 4 20 20 11.179853 9.680695 10.247896 -3.400818 NA NA
I would like to add a column to this dataset that contains the "loser" of each row - by this I mean, whether variable "A" or variable "B" contains the lowest value. The final product would look something like this:
id A1 B1 A2 B2 A3 B3 A4 B4 loser
1 1 20 20 19.638362 15.974095 1.410084 3.438875 0.02184858 -5.736179 Player B
5 2 20 20 8.119724 11.022495 2.363473 8.705035 -0.94658401 1.637583 Player A
9 3 20 20 11.981054 14.206246 5.894012 6.318283 -22.20246252 3.831294 Player A
13 4 20 20 11.179853 9.680695 10.247896 -3.400818 NA NA Player B
I tried to solve this problem in an indirect way - for each row, find the column number that corresponds to the smallest element. If the column number is "odd", then loser = Player B - else loser = Player A.
I tried to do this using the following codes:
l = apply( m, 1, which.min)
1 5 9 13
9 8 8 7
m$l = l
> ifelse(m$l %% 2 == 0, "Player A", "Player B" )
[1] "Player B" "Player A" "Player A" "Player B"
> loser = ifelse(m$l %% 2 == 0, "Player A", "Player B" )
> loser
[1] "Player B" "Player A" "Player A" "Player B"
> m$loser = loser
> m
id A1 B1 A2 B2 A3 B3 A4 B4 l loser
1 1 20 20 19.638362 15.974095 1.410084 3.438875 0.02184858 -5.736179 9 Player B
5 2 20 20 8.119724 11.022495 2.363473 8.705035 -0.94658401 1.637583 8 Player A
9 3 20 20 11.981054 14.206246 5.894012 6.318283 -22.20246252 3.831294 8 Player A
13 4 20 20 11.179853 9.680695 10.247896 -3.400818 NA NA 7 Player B
- Have I done this correctly? Is my logic correct?
Thank you!
CodePudding user response：
We can use pmap to help pull the name of the column that has the minimum value. So, all columns are put into c(...) to find the minimum value, then we subset using names. We can then just extract only the letter and paste to "Player" to get the desired output for loser. You could do all of that in one step, but I decided to put it as a second mutate line for readability.
library(tidyverse)
m %>%
mutate(loser = pmap(across(-id), ~ names(c(...)[which.min(c(...))])),
loser = str_c("Player ", str_extract(loser, "[[:alpha:]]")))
Output
id A1 B1 A2 B2 A3 B3 A4 B4 loser
1 1 20 20 19.638362 15.974095 1.410084 3.438875 0.02184858 -5.736179 Player B
5 2 20 20 8.119724 11.022495 2.363473 8.705035 -0.94658401 1.637583 Player A
9 3 20 20 11.981054 14.206246 5.894012 6.318283 -22.20246252 3.831294 Player A
13 4 20 20 11.179853 9.680695 10.247896 -3.400818 NA NA Player B
Or another tidyverse option:
m %>%
rowwise() %>%
mutate(loser = names(.)[-1][which.min(c_across(cols = -c(id)))],
loser = str_c("Player ", str_extract(loser, "[[:alpha:]]")))
CodePudding user response：
Using Base R:
m$loser <- sub("\\d ", '', names(m[-1])[max.col(-replace(m[-1], is.na(m[-1]),0))])
id A1 B1 A2 B2 A3 B3 A4 B4 loser
1 1 20 20 19.638362 15.974095 1.410084 3.438875 0.02184858 -5.736179 B
5 2 20 20 8.119724 11.022495 2.363473 8.705035 -0.94658401 1.637583 A
9 3 20 20 11.981054 14.206246 5.894012 6.318283 -22.20246252 3.831294 A
13 4 20 20 11.179853 9.680695 10.247896 -3.400818 NA NA B