How to remove rows from a table that exist in another table?


I have a table which consists of the best tennis players by ranking in the last 30 years. Example is below (height is not important for my question):

> head(mostSuccesfulPlayers)
    player_id    player_name player_ht
1      105453  Kei Nishikori       178
31     104925 Novak Djokovic       188
59     104731 Kevin Anderson       203
152    104745   Rafael Nadal       185
164    105227    Marin Cilic       198
172    103819  Roger Federer       185

I have another table which consists of basically all of the players in the data set, for example:

> head(allPlayers)
  player_id     player_name player_ht
1    100284   Jimmy Connors       178
2    100431 Mansour Bahrami       178
3    100529    Kevin Curren       185
4    100532     Johan Kriek       175
5    100553    Nduka Odizor       183
6    100581    John McEnroe       180

I want to create a table notSoSuccesfulPlayers which would consist of only the players from the allPlayers table, but without the players which are listed in the mostSuccesfulPlayers table. In the set theory notation, that would be allPlayers \ mostSuccesfulPlayers.

How can I do that? Huge thanks for any help in advance!

>Solution :

An alternative to the canonical setdiff:

subset(allPlayers, !player_id %in% mostSuccessfulPlayers$player_id)

Note: this works well when you can uniquely id each row by exactly one column; for multiple columns, you’ll need setdiff (or dplyr::anti_join).

Leave a ReplyCancel reply