Import two related datasets from TidyTuesday Project.
base <- "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2022/2022-01-25/"
ratings <- read_csv(paste0(base, "ratings.csv"))## Rows: 21831 Columns: 10
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (3): name, url, thumbnail
## dbl (7): num, id, year, rank, average, bayes_average, users_rated
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.details <- read_csv(paste0(base, "details.csv"))## Rows: 21631 Columns: 23
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (10): primary, description, boardgamecategory, boardgamemechanic, boardg...
## dbl (13): num, id, yearpublished, minplayers, maxplayers, playingtime, minpl...
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.Describe the two datasets:
Data1 (ratings_small)
Data 2 (details_small)
# pick 15 game ids; ratings gets the first 10, details gets the last 10
# (so ids 6-10 appear in both)
ids <- ratings$id[1:15]
ratings_small <- ratings |>
filter(id %in% ids[1:10]) |>
select(id, name, average, users_rated)
details_small <- details |>
filter(id %in% ids[6:15]) |>
select(id, minplayers, maxplayers, playingtime)
# describe them
dim(ratings_small) # rows, columns## [1] 10 4dim(details_small)## [1] 10 4glimpse(ratings_small)## Rows: 10
## Columns: 4
## $ id <dbl> 30549, 822, 13, 68448, 36218, 9209, 178900, 167791, 173346…
## $ name <chr> "Pandemic", "Carcassonne", "Catan", "7 Wonders", "Dominion…
## $ average <dbl> 7.59, 7.42, 7.14, 7.74, 7.61, 7.41, 7.60, 8.42, 8.11, 7.93
## $ users_rated <dbl> 108975, 108738, 108024, 89982, 81561, 76171, 74419, 74216,…glimpse(details_small)## Rows: 10
## Columns: 4
## $ id <dbl> 9209, 178900, 167791, 173346, 31260, 3076, 148228, 169786,…
## $ minplayers <dbl> 2, 2, 1, 2, 1, 3, 2, 1, 2, 2
## $ maxplayers <dbl> 5, 8, 5, 2, 5, 5, 4, 5, 5, 5
## $ playingtime <dbl> 60, 15, 120, 30, 150, 150, 30, 115, 80, 60Describe the resulting data:
How is it different from the original two datasets? Only ids found in both tables are kept, and the columns are combined.
inner <- inner_join(ratings_small, details_small, by = "id")
inner## # A tibble: 5 × 7
## id name average users_rated minplayers maxplayers playingtime
## <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 9209 Ticket to Ride 7.41 76171 2 5 60
## 2 178900 Codenames 7.6 74419 2 8 15
## 3 167791 Terraforming Mars 8.42 74216 1 5 120
## 4 173346 7 Wonders Duel 8.11 69472 2 2 30
## 5 31260 Agricola 7.93 66093 1 5 150dim(inner)## [1] 5 7Describe the resulting data:
How is it different from the original two datasets? All ratings rows are kept, with NA in the details columns for the 5 games that have no match.
left <- left_join(ratings_small, details_small, by = "id")
left## # A tibble: 10 × 7
## id name average users_rated minplayers maxplayers playingtime
## <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 30549 Pandemic 7.59 108975 NA NA NA
## 2 822 Carcassonne 7.42 108738 NA NA NA
## 3 13 Catan 7.14 108024 NA NA NA
## 4 68448 7 Wonders 7.74 89982 NA NA NA
## 5 36218 Dominion 7.61 81561 NA NA NA
## 6 9209 Ticket to Ride 7.41 76171 2 5 60
## 7 178900 Codenames 7.6 74419 2 8 15
## 8 167791 Terraforming Ma… 8.42 74216 1 5 120
## 9 173346 7 Wonders Duel 8.11 69472 2 2 30
## 10 31260 Agricola 7.93 66093 1 5 150dim(left)## [1] 10 7Describe the resulting data:
How is it different from the original two datasets? All details rows are kept, with NA in the ratings columns for the 5 games that have no match.
right <- right_join(ratings_small, details_small, by = "id")
right## # A tibble: 10 × 7
## id name average users_rated minplayers maxplayers playingtime
## <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 9209 Ticket to Ride 7.41 76171 2 5 60
## 2 178900 Codenames 7.6 74419 2 8 15
## 3 167791 Terraforming Ma… 8.42 74216 1 5 120
## 4 173346 7 Wonders Duel 8.11 69472 2 2 30
## 5 31260 Agricola 7.93 66093 1 5 150
## 6 3076 <NA> NA NA 3 5 150
## 7 148228 <NA> NA NA 2 4 30
## 8 169786 <NA> NA NA 1 5 115
## 9 40692 <NA> NA NA 2 5 80
## 10 14996 <NA> NA NA 2 5 60dim(right)## [1] 10 7Describe the resulting data:
How is it different from the original two datasets? Every id from both tables appears, with NA wherever a side is missing.
full <- full_join(ratings_small, details_small, by = "id")
full## # A tibble: 15 × 7
## id name average users_rated minplayers maxplayers playingtime
## <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 30549 Pandemic 7.59 108975 NA NA NA
## 2 822 Carcassonne 7.42 108738 NA NA NA
## 3 13 Catan 7.14 108024 NA NA NA
## 4 68448 7 Wonders 7.74 89982 NA NA NA
## 5 36218 Dominion 7.61 81561 NA NA NA
## 6 9209 Ticket to Ride 7.41 76171 2 5 60
## 7 178900 Codenames 7.6 74419 2 8 15
## 8 167791 Terraforming Ma… 8.42 74216 1 5 120
## 9 173346 7 Wonders Duel 8.11 69472 2 2 30
## 10 31260 Agricola 7.93 66093 1 5 150
## 11 3076 <NA> NA NA 3 5 150
## 12 148228 <NA> NA NA 2 4 30
## 13 169786 <NA> NA NA 1 5 115
## 14 40692 <NA> NA NA 2 5 80
## 15 14996 <NA> NA NA 2 5 60dim(full)## [1] 15 7Describe the resulting data:
How is it different from the original two datasets? It keeps only ratings rows that have a match, and it adds no columns from details.
semi <- semi_join(ratings_small, details_small, by = "id")
semi## # A tibble: 5 × 4
## id name average users_rated
## <dbl> <chr> <dbl> <dbl>
## 1 9209 Ticket to Ride 7.41 76171
## 2 178900 Codenames 7.6 74419
## 3 167791 Terraforming Mars 8.42 74216
## 4 173346 7 Wonders Duel 8.11 69472
## 5 31260 Agricola 7.93 66093dim(semi)## [1] 5 4Describe the resulting data:
How is it different from the original two datasets? It keeps only ratings rows that have no match in details, and it also adds no columns.
anti <- anti_join(ratings_small, details_small, by = "id")
anti## # A tibble: 5 × 4
## id name average users_rated
## <dbl> <chr> <dbl> <dbl>
## 1 30549 Pandemic 7.59 108975
## 2 822 Carcassonne 7.42 108738
## 3 13 Catan 7.14 108024
## 4 68448 7 Wonders 7.74 89982
## 5 36218 Dominion 7.61 81561dim(anti)## [1] 5 4# optional: the other direction
anti_join(details_small, ratings_small, by = "id")## # A tibble: 5 × 4
## id minplayers maxplayers playingtime
## <dbl> <dbl> <dbl> <dbl>
## 1 3076 3 5 150
## 2 148228 2 4 30
## 3 169786 1 5 115
## 4 40692 2 5 80
## 5 14996 2 5 60