Create a column in R to compare values within a group and flag as greater than (1), less than (0) or-CodePudding

I'm looking to create a flag within a group. The grouping is on Round and then on Team as below. Flag should be 0 against the team with the lowest score in each Round, 1 for the largest score and 2 if the score is the same in each Round. Expected output for column "Flag" and sample data below:

Round	Team	Score	Flag
1	Team1	4	0
1	Team2	8	1
2	Team1	9	1
2	Team2	2	0
3	Team1	6	2
3	Team2	6	2
4	Team1	14	1
4	Team2	9	0

Thought of using pivot_wider and getting the result but will later need to pivot_longer again for plotting and wanted to avoid that step. I tried using solutions mentioned here and here which are similar but not able to crack it. Could someone please help? TIA

CodePudding user response：

df %>%
  group_by(Round) %>%
  # mutate(Flag = case_when(Score == max(Score) & Score == min(Score) ~ 2, # equivalent
  mutate(Flag = case_when(diff(range(Score)) == 0 ~ 2, 
                          Score == max(Score)     ~ 1,
                          TRUE                    ~ 0))

CodePudding user response：

df %>%
  group_by(Round) %>%
  mutate( Flag1 = replace(rank(Score) - 1, length(unique(Score)) == 1, 2))

  Round Team  Score  Flag Flag1
  <int> <chr> <int> <int> <dbl>
1     1 Team1     4     0     0
2     1 Team2     8     1     1
3     2 Team1     9     1     1
4     2 Team2     2     0     0
5     3 Team1     6     2     2
6     3 Team2     6     2     2
7     4 Team1    14     1     1
8     4 Team2     9     0     0