CODEWITHSUNDEEP
rdataframe
887
887

Reputation: 619

How to rank highest to lowest for multiple frames?

I am working with the following data frame:

structure(list(Player = c("A", "B", "C", "D", "A", "B", "C", 
"D", "A", "B", "C", "D", "A", "B", "C"), UnitID = c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L), Frame = c(10L, 
10L, 10L, 10L, 11L, 11L, 11L, 11L, 12L, 12L, 12L, 12L, 11L, 11L, 
11L), Side = c("Left", "Right", "Right", "Left", "Left", "Right", 
"Right", "Left", "Left", "Right", "Right", "Left", "Left", "Right", 
"Right"), Y = c(3.52, 1.56, 0.54, 3.22, 4.81, 1.1, 1.7, 4.3, 
3.3, 1.8, 1.2, 3.8, 3.5, 1.9, 1.2)), class = "data.frame", row.names = c(NA, 
-15L))

I would like to add a column that numbers each player by their Y location when the Frame is equal to 11. Using the y location column, I would like players to be ranked from the highest value down if their "side" column is equal to "Left," and from the lowest value up if their "side" column is equal to right. So the above table would become:

structure(list(Player = c("A", "B", "C", "D", "A", "B", "C", 
"D", "A", "B", "C", "D", "A", "B", "C"), UnitID = c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L), Frame = c(10L, 
10L, 10L, 10L, 11L, 11L, 11L, 11L, 12L, 12L, 12L, 12L, 11L, 11L, 
11L), Side = c("Left", "Right", "Right", "Left", "Left", "Right", 
"Right", "Left", "Left", "Right", "Right", "Left", "Left", "Right", 
"Right"), Y = c(3.52, 1.56, 0.54, 3.22, 4.81, 1.1, 1.7, 4.3, 
3.3, 1.8, 1.2, 3.8, 3.5, 1.9, 1.2), Num = c(1L, 1L, 2L, 2L, 1L, 
1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 2L, 1L)), class = "data.frame", row.names = c(NA, 
-15L))

Just to be clear on what I'm looking for, players A, B, C, and D all hold the same 'num' value throughout all the frames of UnitId=1, and that value is based on their individual Y values in FrameID 11. In frame 11, the player with the highest Y value whose 'side' value is 'left' is assigned 1, and the player with the second highest Y value who also is 'left' is assigned 2 in the num column. The opposite is true of players with a 'side' value of 'right' (ranked from the "bottom up"). It's important to note that the num column needs to hold true for every all FrameIds within the same UnitId.

My apologies if that's confusing, thanks in advance for the help!

Upvotes: 0

Views: 52

Answers (2)

M--
M--

Reputation: 28825

library(dplyr)

df1 %>% 
  filter(Frame == 11)  %>% 
  group_by(UnitID, Side) %>% 
  mutate(num = rank(sign(+(Side == "Right") - 0.5) * Y)) %>% 
  select(Player, UnitID, Side, num) %>% 
  left_join( df1, . , by = c("Player", "UnitID", "Side"))

#>    Player UnitID Frame  Side    Y num
#> 1       A      1    10  Left 3.52   1
#> 2       B      1    10 Right 1.56   1
#> 3       C      1    10 Right 0.54   2
#> 4       D      1    10  Left 3.22   2
#> 5       A      1    11  Left 4.81   1
#> 6       B      1    11 Right 1.10   1
#> 7       C      1    11 Right 1.70   2
#> 8       D      1    11  Left 4.30   2
#> 9       A      1    12  Left 3.30   1
#> 10      B      1    12 Right 1.80   1
#> 11      C      1    12 Right 1.20   2
#> 12      D      1    12  Left 3.80   2
#> 13      A      2    11  Left 3.50   1
#> 14      B      2    11 Right 1.90   2
#> 15      C      2    11 Right 1.20   1

Check to see if the result is the same as OP's expected output:

all(df2 == .Last.value)
#> [1] TRUE

Upvotes: 1

ekoam
ekoam

Reputation: 8844

Here is a base R approach (df is the first dataframe in your question)

ave(within(df, Num <- 0L), df$UnitID, FUN = function(sd) {
  sd$Num <- with(sd, {
    f11 <- Frame == 11
    y <- Y[f11]
    left <- Side[f11] == "Left"
    right <- !left
    x <- integer(length(y))
    names(x) <- Player[f11]
    x[left] <- rank(-y[left], ties.method = "min")
    x[right] <- rank(y[right], ties.method = "min")
    x[Player]
  })
  sd
})

Here is a dplyr approach

df %>% 
  group_by(UnitID) %>% 
  mutate(Num = local({
    f11 <- Frame == 11
    y <- y[f11]
    left <- Side[f11] == "Left"
    right <- !left
    x <- integer(length(y))
    names(x) <- Player[f11]
    x[left] <- rank(-y[left], ties.method = "min")
    x[right] <- rank(y[right], ties.method = "min")
    unname(x[Player])
  }))

Output

   Player UnitID Frame  Side    Y Num
1       A      1    10  Left 3.52   1
2       B      1    10 Right 1.56   1
3       C      1    10 Right 0.54   2
4       D      1    10  Left 3.22   2
5       A      1    11  Left 4.81   1
6       B      1    11 Right 1.10   1
7       C      1    11 Right 1.70   2
8       D      1    11  Left 4.30   2
9       A      1    12  Left 3.30   1
10      B      1    12 Right 1.80   1
11      C      1    12 Right 1.20   2
12      D      1    12  Left 3.80   2
13      A      2    11  Left 3.50   1
14      B      2    11 Right 1.90   2
15      C      2    11 Right 1.20   1

Upvotes: 1

Related Questions