clean-sports
clean-sports.Rmd
if ("pak" %nin% loadedNamespaces()) {
install.packages("pak", quiet = TRUE)
}
pkgs <- c("dplyr", "stringr", "tidyr", "forcats")
install.packages(pkgs, quiet = TRUE)Reproducing the usada_dates dataset from the “Cleaning dates” article:
usada_raw <- get_sanctions_data()
usada <- process_text(raw_data = usada_raw)
bad_dates <- subset(usada,
grepl("^original", usada[['sanction_announced']]))
good_dates <- subset(usada,
!grepl("^original", usada[['sanction_announced']]) & sanction_announced != "")
cleaned_dates <- clean_dates(
df = bad_dates,
date_col = "sanction_announced",
split = "updated",
pattern = "original")
names(cleaned_dates)[names(cleaned_dates) == 'split_date'] <- 'sanction_date'
names(cleaned_dates)[names(cleaned_dates) == 'pattern_date'] <- 'original_date'
good_dates$sanction_date <- as.Date(x = good_dates[['sanction_announced']],
format = "%m/%d/%Y")
nms <- intersect(names(cleaned_dates), names(good_dates))
usada_dates <- rbind(good_dates, cleaned_dates[nms])
str(usada_dates)
#> 'data.frame': 693 obs. of 6 variables:
#> $ athlete : chr "miller, adam" "zilcosky, chase" "trabing, bert" "cantwell, steven" ...
#> $ sport : chr "field hockey" "weightlifting" "weightlifting" "paralympic snowboarding" ...
#> $ substance_reason : chr "non-analytical: 3 whereabouts failures" "amphetamine" "anastrozole; testosterone" "dehydrochlormethyltestosterone (dhcmt)" ...
#> $ sanction_terms : chr "2-year suspension; loss of results" "2-year suspension; loss of results" "4-year suspension; loss of results" "6-year suspension; loss of results" ...
#> $ sanction_announced: chr "07/31/2026" "07/29/2026" "07/22/2026" "07/16/2026" ...
#> $ sanction_date : Date, format: "2026-07-31" "2026-07-29" ...Sports
To wrangle the sports, I’ll use packages and functions from the
tidyverse (dplyr, stringr,
tidyr, etc.), but I also provide the base R alternatives
(wherever possible). tidyverse functions will return a
tibble (not a data.frame), which prints fewer
rows to the console.
usada_sports <- tibble::as_tibble(usada_dates)
str(usada_sports)
#> tibble [693 × 6] (S3: tbl_df/tbl/data.frame)
#> $ athlete : chr [1:693] "miller, adam" "zilcosky, chase" "trabing, bert" "cantwell, steven" ...
#> $ sport : chr [1:693] "field hockey" "weightlifting" "weightlifting" "paralympic snowboarding" ...
#> $ substance_reason : chr [1:693] "non-analytical: 3 whereabouts failures" "amphetamine" "anastrozole; testosterone" "dehydrochlormethyltestosterone (dhcmt)" ...
#> $ sanction_terms : chr [1:693] "2-year suspension; loss of results" "2-year suspension; loss of results" "4-year suspension; loss of results" "6-year suspension; loss of results" ...
#> $ sanction_announced: chr [1:693] "07/31/2026" "07/29/2026" "07/22/2026" "07/16/2026" ...
#> $ sanction_date : Date[1:693], format: "2026-07-31" "2026-07-29" ...We can start by counting the sport column:
usada_sports |>
dplyr::count(sport, sort = TRUE)
#> # A tibble: 72 × 2
#> sport n
#> <chr> <int>
#> 1 mixed martial arts 153
#> 2 weightlifting 140
#> 3 cycling 100
#> 4 track and field 96
#> 5 brazilian jiu-jitsu 21
#> 6 triathlon 17
#> 7 swimming 15
#> 8 wrestling 11
#> 9 paralympic track and field 10
#> 10 powerlifting 7
#> # ℹ 62 more rowsSupport personnel
Some of the sports aren’t sports–they’re
athlete support personnel. These need a
support_personnel identifier.
usada_sports <- dplyr::mutate(usada_sports,
# support_personnel
support_personnel =
dplyr::if_else(condition = stringr::str_detect(
sport, "support personnel"),
true = TRUE, false = FALSE, missing = NA))
usada_sports |>
dplyr::filter(stringr::str_detect(sport, "support personnel")) |>
dplyr::count(sport, support_personnel) |>
tidyr::pivot_wider(names_from = support_personnel, values_from = n)
#> # A tibble: 4 × 2
#> sport `TRUE`
#> <chr> <int>
#> 1 brazilian jiu-jitsu - athlete support personnel 1
#> 2 cycling - athlete support personnel 6
#> 3 track and field - athlete support personnel 6
#> 4 weightlifting - athlete support personnel 1‘track and field’ or ‘track & field’
Convert sports like track & field to
track and field to help determine which athletes/support
personnel are involved in multiple sports.
usada_sports <- dplyr::mutate(usada_sports,
# track & field
sport = stringr::str_replace_all(sport, 'track and field', 'track & field'))
usada_sports |>
dplyr::filter(stringr::str_detect(sport, "track")) |>
dplyr::count(sport, support_personnel) |>
tidyr::pivot_wider(names_from = support_personnel, values_from = n)
#> # A tibble: 6 × 3
#> sport `FALSE` `TRUE`
#> <chr> <int> <int>
#> 1 bobsled and skeleton, track & field 1 NA
#> 2 para track & field 5 NA
#> 3 paralympic track & field 10 NA
#> 4 paralympic track & field, paralympic volleyball 1 NA
#> 5 track & field 99 NA
#> 6 track & field - athlete support personnel NA 6Spelling
The incorrect spelling for brazilian jiu-jitsu
(brazillian jiu-jitsu) is corrected below.
usada_sports <- dplyr::mutate(usada_sports,
# brazilian jiu-jitsu
sport = dplyr::case_when(
sport == 'brazillian jiu-jitsu' ~ 'brazilian jiu-jitsu',
TRUE ~ sport))
usada_sports |>
dplyr::filter(stringr::str_detect(sport, "jitsu")) |>
dplyr::count(sport, sort = TRUE)
#> # A tibble: 2 × 2
#> sport n
#> <chr> <int>
#> 1 brazilian jiu-jitsu 21
#> 2 brazilian jiu-jitsu - athlete support personnel 1‘paralympic’
An identifier for paralympic sports: paralympic.
usada_sports <- dplyr::mutate(usada_sports,
# paralympic
paralympic =
dplyr::if_else(condition = stringr::str_detect(sport, "paralympic|para"),
true = TRUE, false = FALSE, missing = NA))
usada_sports |>
dplyr::filter(stringr::str_detect(sport, "paralympic|para")) |>
dplyr::count(paralympic, sport) |>
tidyr::pivot_wider(names_from = paralympic, values_from = n)
#> # A tibble: 17 × 2
#> sport `TRUE`
#> <chr> <int>
#> 1 para alpine skiing 1
#> 2 para cycling 1
#> 3 para fencing 1
#> 4 para judo 1
#> 5 para shooting 1
#> 6 para swimming 1
#> 7 para track & field 5
#> 8 paralympic alpine skiing 1
#> 9 paralympic archery 1
#> 10 paralympic basketball 2
#> 11 paralympic curling 1
#> 12 paralympic cycling 4
#> 13 paralympic judo 4
#> 14 paralympic snowboarding 2
#> 15 paralympic track & field 10
#> 16 paralympic track & field, paralympic volleyball 1
#> 17 paralympic triathlon 1Multiple sports
Identify the multiple sports using and and
, in a regular expression.
usada_sports <- dplyr::mutate(usada_sports,
# multiple_sports
multiple_sports =
if_else(condition = stringr::str_detect(sport, "and |, "),
true = TRUE, false = FALSE, missing = NA))
usada_sports |>
dplyr::filter(stringr::str_detect(sport, "and |, ")) |>
dplyr::count(multiple_sports, sport) |>
tidyr::pivot_wider(names_from = multiple_sports, values_from = n)
#> # A tibble: 6 × 2
#> sport `TRUE`
#> <chr> <int>
#> 1 bobsled and skeleton 3
#> 2 bobsled and skeleton, track & field 1
#> 3 cycling, triathlon 2
#> 4 cycling, weightlifting 1
#> 5 paralympic track & field, paralympic volleyball 1
#> 6 skiing and snowboarding 1Tidy
Separate the multi-sport athletes in usada_sports as
multp_sport_athletes and single-sport athletes in
single_sport_athletes.
multp_sport_athletes <- usada_sports |>
dplyr::filter(multiple_sports == TRUE)
str(multp_sport_athletes)
#> tibble [9 × 9] (S3: tbl_df/tbl/data.frame)
#> $ athlete : chr [1:9] "allison, kyler" "blandford, jenna" "cruse, j.c." "schrodt, patrick \"dillon\"" ...
#> $ sport : chr [1:9] "bobsled and skeleton" "cycling, triathlon" "bobsled and skeleton" "bobsled and skeleton" ...
#> $ substance_reason : chr [1:9] "non-analytical: refusal to submit to sample collection" "non-analytical: use and possession (testosterone, hgh and oxandrolone)" "dimethylbutylamine (dmba)" "dimethylbutylamine (dmba)" ...
#> $ sanction_terms : chr [1:9] "4-year suspension; loss of results; sanction tolled due to retirement" "4-year suspension - loss of results" "16-month suspension - loss of results" "16-month suspension - loss of results" ...
#> $ sanction_announced: chr [1:9] "10/09/2019" "11/28/2017" "07/20/2017" "04/06/2017" ...
#> $ sanction_date : Date[1:9], format: "2019-10-09" "2017-11-28" ...
#> $ support_personnel : logi [1:9] FALSE FALSE FALSE FALSE FALSE FALSE ...
#> $ paralympic : logi [1:9] FALSE FALSE FALSE FALSE TRUE FALSE ...
#> $ multiple_sports : logi [1:9] TRUE TRUE TRUE TRUE TRUE TRUE ...
single_sport_athletes <- usada_sports |>
dplyr::filter(multiple_sports == FALSE)
str(single_sport_athletes)
#> tibble [684 × 9] (S3: tbl_df/tbl/data.frame)
#> $ athlete : chr [1:684] "miller, adam" "zilcosky, chase" "trabing, bert" "cantwell, steven" ...
#> $ sport : chr [1:684] "field hockey" "weightlifting" "weightlifting" "paralympic snowboarding" ...
#> $ substance_reason : chr [1:684] "non-analytical: 3 whereabouts failures" "amphetamine" "anastrozole; testosterone" "dehydrochlormethyltestosterone (dhcmt)" ...
#> $ sanction_terms : chr [1:684] "2-year suspension; loss of results" "2-year suspension; loss of results" "4-year suspension; loss of results" "6-year suspension; loss of results" ...
#> $ sanction_announced: chr [1:684] "07/31/2026" "07/29/2026" "07/22/2026" "07/16/2026" ...
#> $ sanction_date : Date[1:684], format: "2026-07-31" "2026-07-29" ...
#> $ support_personnel : logi [1:684] FALSE FALSE FALSE FALSE FALSE FALSE ...
#> $ paralympic : logi [1:684] FALSE FALSE FALSE TRUE FALSE FALSE ...
#> $ multiple_sports : logi [1:684] FALSE FALSE FALSE FALSE FALSE FALSE ...The athletes listed with multiple sports will occupy multiple rows in
a ‘tidy’ version of usada_sports.
- Passing the sport column to
tidyr::separate_rows()andstringr::str_trim()inmultp_sport_athleteswill create atidy_multp_sport_athletesdataset:
tidy_multp_sport_athletes <- multp_sport_athletes |>
tidyr::separate_rows(sport, sep = "and|, ") |>
dplyr::mutate(sport = stringr::str_trim(sport, side = "both"))
str(tidy_multp_sport_athletes)
#> tibble [19 × 9] (S3: tbl_df/tbl/data.frame)
#> $ athlete : chr [1:19] "allison, kyler" "allison, kyler" "blandford, jenna" "blandford, jenna" ...
#> $ sport : chr [1:19] "bobsled" "skeleton" "cycling" "triathlon" ...
#> $ substance_reason : chr [1:19] "non-analytical: refusal to submit to sample collection" "non-analytical: refusal to submit to sample collection" "non-analytical: use and possession (testosterone, hgh and oxandrolone)" "non-analytical: use and possession (testosterone, hgh and oxandrolone)" ...
#> $ sanction_terms : chr [1:19] "4-year suspension; loss of results; sanction tolled due to retirement" "4-year suspension; loss of results; sanction tolled due to retirement" "4-year suspension - loss of results" "4-year suspension - loss of results" ...
#> $ sanction_announced: chr [1:19] "10/09/2019" "10/09/2019" "11/28/2017" "11/28/2017" ...
#> $ sanction_date : Date[1:19], format: "2019-10-09" "2019-10-09" ...
#> $ support_personnel : logi [1:19] FALSE FALSE FALSE FALSE FALSE FALSE ...
#> $ paralympic : logi [1:19] FALSE FALSE FALSE FALSE FALSE FALSE ...
#> $ multiple_sports : logi [1:19] TRUE TRUE TRUE TRUE TRUE TRUE ...Finally, combine the two datasets.
tidy_sports <- dplyr::bind_rows(single_sport_athletes, tidy_multp_sport_athletes)
str(tidy_sports)
#> tibble [703 × 9] (S3: tbl_df/tbl/data.frame)
#> $ athlete : chr [1:703] "miller, adam" "zilcosky, chase" "trabing, bert" "cantwell, steven" ...
#> $ sport : chr [1:703] "field hockey" "weightlifting" "weightlifting" "paralympic snowboarding" ...
#> $ substance_reason : chr [1:703] "non-analytical: 3 whereabouts failures" "amphetamine" "anastrozole; testosterone" "dehydrochlormethyltestosterone (dhcmt)" ...
#> $ sanction_terms : chr [1:703] "2-year suspension; loss of results" "2-year suspension; loss of results" "4-year suspension; loss of results" "6-year suspension; loss of results" ...
#> $ sanction_announced: chr [1:703] "07/31/2026" "07/29/2026" "07/22/2026" "07/16/2026" ...
#> $ sanction_date : Date[1:703], format: "2026-07-31" "2026-07-29" ...
#> $ support_personnel : logi [1:703] FALSE FALSE FALSE FALSE FALSE FALSE ...
#> $ paralympic : logi [1:703] FALSE FALSE FALSE TRUE FALSE FALSE ...
#> $ multiple_sports : logi [1:703] FALSE FALSE FALSE FALSE FALSE FALSE ...clean_sports()
These steps are combined in the clean_sports()
function:
str(
clean_sports(
df = usada_dates,
sport_col = "sport",
tidy = TRUE)
)
#> 'data.frame': 703 obs. of 9 variables:
#> $ athlete : chr "miller, adam" "zilcosky, chase" "trabing, bert" "cantwell, steven" ...
#> $ sport : chr "field hockey" "weightlifting" "weightlifting" "paralympic snowboarding" ...
#> $ substance_reason : chr "non-analytical: 3 whereabouts failures" "amphetamine" "anastrozole; testosterone" "dehydrochlormethyltestosterone (dhcmt)" ...
#> $ sanction_terms : chr "2-year suspension; loss of results" "2-year suspension; loss of results" "4-year suspension; loss of results" "6-year suspension; loss of results" ...
#> $ sanction_announced: chr "07/31/2026" "07/29/2026" "07/22/2026" "07/16/2026" ...
#> $ sanction_date : Date, format: "2026-07-31" "2026-07-29" ...
#> $ support_personnel : logi FALSE FALSE FALSE FALSE FALSE FALSE ...
#> $ paralympic : logi FALSE FALSE FALSE TRUE FALSE FALSE ...
#> $ multiple_sports : logi FALSE FALSE FALSE FALSE FALSE FALSE ...Verify there aren’t any duplicates (again).
tidy_sports |>
dplyr::count(athlete, sanction_date, sport) |>
dplyr::filter(n > 1)
#> # A tibble: 6 × 4
#> athlete sanction_date sport n
#> <chr> <date> <chr> <int>
#> 1 *name removed NA mixed martial arts 6
#> 2 *name removed NA paralympic track & field 2
#> 3 *name removed NA swimming 4
#> 4 *name removed NA track & field 9
#> 5 *name removed NA volleyball 2
#> 6 *name removed NA weightlifting 3We can see the multi-sport athletes are listed in
tidy_sports (but with one sport per row):
tidy_sports |>
dplyr::filter(multiple_sports == TRUE) |>
dplyr::select(athlete, sport)
#> # A tibble: 19 × 2
#> athlete sport
#> <chr> <chr>
#> 1 "allison, kyler" bobsled
#> 2 "allison, kyler" skeleton
#> 3 "blandford, jenna" cycling
#> 4 "blandford, jenna" triathlon
#> 5 "cruse, j.c." bobsled
#> 6 "cruse, j.c." skeleton
#> 7 "schrodt, patrick \"dillon\"" bobsled
#> 8 "schrodt, patrick \"dillon\"" skeleton
#> 9 "green, roderick" paralympic track & field
#> 10 "green, roderick" paralympic volleyball
#> 11 "denney phillips, jessica" cycling
#> 12 "denney phillips, jessica" weightlifting
#> 13 "flanagan, tyler" skiing
#> 14 "flanagan, tyler" snowboarding
#> 15 "hamilton, tyler" cycling
#> 16 "hamilton, tyler" triathlon
#> 17 "bailey, ryan" bobsled
#> 18 "bailey, ryan" skeleton
#> 19 "bailey, ryan" track & field