r语言 - 如何基于现有列中与另一列的分类值匹配的数字创建新列

Question

我有一个数据帧，包含游戏、类别、是否是单人游戏以及评级。我添加了一个新的专栏，其中包含每个单人/双人组中每个类别的最高评分方面的最佳游戏。

category <- c("Party","Adventure","Puzzle","Party","Adventure","Puzzle","Party","Adventure","Puzzle","Party","Adventure","Puzzle","Party","Adventure","Puzzle","Party","Adventure","Puzzle")
solo <- c("solo","solo","solo","double","double","double","solo","solo","solo","double","double","double","solo","solo","solo","double","double","double")
game <- c("Game1","Game2","Game3","Game1","Game2","Game3","Game1","Game2","Game3","Game1","Game2","Game3","Game1","Game2","Game3","Game1","Game2","Game3")
rating <- c(8,7,6,5,3,3,2,1,10,3,4,5,6,3,2,1,3,1) 
df <- as.data.frame(rating)
df$game <- game
df$solo <- solo
df$category <- category
df <- df  %>% 
group_by(category, solo) %>% 
mutate(best_game = max(rating, na.rm = TRUE)) %>%
ungroup

我的问题是，与其每个单人/双人组和类别组都有最高评级(best_game(，我如何添加一个列，使游戏对应于best_game中的最高评级？

输出示例。预期输出是我希望发生的

rating    game    solo   category  best_game  **(expected_output)**
5       Game1   double  Party   5            Game1
3       Game1   double  Party   5            Game1

Answer 1

只需包含一个用rating == max(rating)对game变量进行子集设置的语句

df <- df  %>% 
group_by(category, solo) %>% 
mutate(best_game = game[rating == max(rating, na.rm = TRUE)]) %>%
ungroup
df
# A tibble: 18 × 5
rating game  solo   category  best_game
<dbl> <chr> <chr>  <chr>     <chr>    
1      8 Game1 solo   Party     Game1    
2      7 Game2 solo   Adventure Game2    
3      6 Game3 solo   Puzzle    Game3    
4      5 Game1 double Party     Game1    
5      3 Game2 double Adventure Game2    
6      3 Game3 double Puzzle    Game3    
7      2 Game1 solo   Party     Game1    
8      1 Game2 solo   Adventure Game2    
9     10 Game3 solo   Puzzle    Game3    
10      3 Game1 double Party     Game1    
11      4 Game2 double Adventure Game2    
12      5 Game3 double Puzzle    Game3    
13      6 Game1 solo   Party     Game1    
14      3 Game2 solo   Adventure Game2    
15      2 Game3 solo   Puzzle    Game3    
16      1 Game1 double Party     Game1    
17      3 Game2 double Adventure Game2    
18      1 Game3 double Puzzle    Game3 

Answer 2

这是有效的，但如果不止一个游戏的评分达到最高，应该更新你想做什么？

inner_join(
df, 
df %>% 
group_by(category, solo) %>% 
filter(max(rating, na.rm = TRUE)==rating) %>% 
rename(best_game=game) %>% 
select(-rating),
by=c("category","solo")
)

输出

rating  game   solo  category best_game
1       8 Game1   solo     Party     Game1
2       7 Game2   solo Adventure     Game2
3       6 Game3   solo    Puzzle     Game3
4       5 Game1 double     Party     Game1
5       3 Game2 double Adventure     Game2
6       3 Game3 double    Puzzle     Game3
7       2 Game1   solo     Party     Game1
8       1 Game2   solo Adventure     Game2
9      10 Game3   solo    Puzzle     Game3
10      3 Game1 double     Party     Game1
11      4 Game2 double Adventure     Game2
12      5 Game3 double    Puzzle     Game3
13      6 Game1   solo     Party     Game1
14      3 Game2   solo Adventure     Game2
15      2 Game3   solo    Puzzle     Game3
16      1 Game1 double     Party     Game1
17      3 Game2 double Adventure     Game2
18      1 Game3 double    Puzzle     Game3