AE 08: Data wrangling with rowwise/column-wise operations

Packages

We will use the following packages in this application exercise.

tidyverse: For data import, wrangling, and visualization.
janitor: For cleaning column names.

library(tidyverse)
library(janitor)
library(scales)

Powerball

Last class we studied Powerball jackpots over time. Today we will continue this journey and focus on Colorado winners in Match $N$ Powerball play, prizes available to players who match the red Powerball number and anywhere between 0-4 white ball numbers.

Import and clean the data

The dataset is available for download as a CSV file.

Demo: Import the data file. Store it as powerball_raw.

powerball_raw <- read_csv(file = "data/POWERBALL-from_0001-01-01_to_2023-09-25.csv")

Rows: 2420 Columns: 61
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (31): Draw date, Last Day To Claim, Winning Numbers, Jackpot, Jackpot Ca...
dbl (30): Powerball, Power Play, Jackpot Winners, Jackpot CO Winners, Match ...

ℹ 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.

powerball_raw

# A tibble: 2,420 × 61
   `Draw date`      `Last Day To Claim` `Winning Numbers` Powerball `Power Play`
   <chr>            <chr>               <chr>                 <dbl>        <dbl>
 1 Saturday, 9/23/… 03/21/2024          1 - 12 - 20 - 33…        21            2
 2 Wednesday, 9/20… 03/18/2024          16 - 27 - 59 - 6…        23            3
 3 Monday, 9/18/23  03/16/2024          2 - 21 - 26 - 40…         9            3
 4 Saturday, 9/16/… 03/14/2024          8 - 11 - 19 - 24…         5            2
 5 Wednesday, 9/13… 03/11/2024          22 - 30 - 37 - 4…        18            3
 6 Monday, 9/11/23  03/09/2024          9 - 25 - 27 - 53…         5            2
 7 Saturday, 9/9/23 03/07/2024          11 - 19 - 29 - 6…        25            2
 8 Wednesday, 9/6/… 03/04/2024          9 - 14 - 20 - 23…         1            3
 9 Monday, 9/4/23   03/02/2024          1 - 26 - 32 - 46…        13            3
10 Saturday, 9/2/23 02/29/2024          25 - 38 - 42 - 6…        19            4
# ℹ 2,410 more rows
# ℹ 56 more variables: Jackpot <chr>, `Jackpot Cash Value` <chr>,
#   `Jackpot Winners` <dbl>, `Jackpot CO Winners` <dbl>, `Match 5 Prize` <chr>,
#   `Match 5 CO Winners` <dbl>, `Match 5 Prize (with Power Play)` <chr>,
#   `Match 5 CO Winners (with Power Play)` <dbl>,
#   `Match 4 + Powerball Prize` <chr>, `Match 4 + Powerball CO Winners` <dbl>,
#   `Match 4 + Powerball Prize (with Power Play)` <chr>, …

Your turn: Clean the raw data to fix the following issues:

Standardize the column names using snake_case format.
Create columns with appropriate data types for any date variables date of the drawing as well as the weekday. Append these columns to the beginning of the data frame.
Fix all of the currency columns to be formatted as numeric types.

Store the cleaned data frame as powerball.

powerball <- powerball_raw |>
  # standardize column names
  clean_names() |>
  # separate draw_date into two variables, clean both
  separate(col = draw_date, into = c(NA, "draw_date"), sep = ",") |>
  mutate(
    draw_date = mdy(draw_date),
    draw_weekday = wday(x = draw_date, label = TRUE),
    last_day_to_claim = mdy(last_day_to_claim),
    .before = last_day_to_claim
  ) |>
  # convert all currency columns to numeric type
  mutate(
    across(
      .cols = c(where(is.character), -contains("winning_numbers")),
      .fns = parse_number
    )
  )
powerball

# A tibble: 2,420 × 62
   draw_date  draw_weekday last_day_to_claim winning_numbers        powerball
   <date>     <ord>        <date>            <chr>                      <dbl>
 1 2023-09-23 Sat          2024-03-21        1 - 12 - 20 - 33 - 66         21
 2 2023-09-20 Wed          2024-03-18        16 - 27 - 59 - 62 - 63        23
 3 2023-09-18 Mon          2024-03-16        2 - 21 - 26 - 40 - 42          9
 4 2023-09-16 Sat          2024-03-14        8 - 11 - 19 - 24 - 46          5
 5 2023-09-13 Wed          2024-03-11        22 - 30 - 37 - 44 - 45        18
 6 2023-09-11 Mon          2024-03-09        9 - 25 - 27 - 53 - 66          5
 7 2023-09-09 Sat          2024-03-07        11 - 19 - 29 - 63 - 68        25
 8 2023-09-06 Wed          2024-03-04        9 - 14 - 20 - 23 - 63          1
 9 2023-09-04 Mon          2024-03-02        1 - 26 - 32 - 46 - 51         13
10 2023-09-02 Sat          2024-02-29        25 - 38 - 42 - 66 - 67        19
# ℹ 2,410 more rows
# ℹ 57 more variables: power_play <dbl>, jackpot <dbl>,
#   jackpot_cash_value <dbl>, jackpot_winners <dbl>, jackpot_co_winners <dbl>,
#   match_5_prize <dbl>, match_5_co_winners <dbl>,
#   match_5_prize_with_power_play <dbl>,
#   match_5_co_winners_with_power_play <dbl>, match_4_powerball_prize <dbl>,
#   match_4_powerball_co_winners <dbl>, …

Analyze the data

Our goal is to reproduce the following visualization:

In order to accomplish this, we have a few challenges ahead. We will need to:

Determine the year for every drawing
Calculate the mean and standard error of the number of winners for each Match $N$ + Powerball prize for every year
Structure the data frame so we have one row for each year and prize, and separate columns for the means and standard errors

Generate the `year` variable

Your turn: Generate a year variable from the draw_date column.

powerball |>
  # generate year variable
  mutate(
    year = year(x = draw_date),
    .before = everything()
  )

# A tibble: 2,420 × 63
    year draw_date  draw_weekday last_day_to_claim winning_numbers     powerball
   <dbl> <date>     <ord>        <date>            <chr>                   <dbl>
 1  2023 2023-09-23 Sat          2024-03-21        1 - 12 - 20 - 33 -…        21
 2  2023 2023-09-20 Wed          2024-03-18        16 - 27 - 59 - 62 …        23
 3  2023 2023-09-18 Mon          2024-03-16        2 - 21 - 26 - 40 -…         9
 4  2023 2023-09-16 Sat          2024-03-14        8 - 11 - 19 - 24 -…         5
 5  2023 2023-09-13 Wed          2024-03-11        22 - 30 - 37 - 44 …        18
 6  2023 2023-09-11 Mon          2024-03-09        9 - 25 - 27 - 53 -…         5
 7  2023 2023-09-09 Sat          2024-03-07        11 - 19 - 29 - 63 …        25
 8  2023 2023-09-06 Wed          2024-03-04        9 - 14 - 20 - 23 -…         1
 9  2023 2023-09-04 Mon          2024-03-02        1 - 26 - 32 - 46 -…        13
10  2023 2023-09-02 Sat          2024-02-29        25 - 38 - 42 - 66 …        19
# ℹ 2,410 more rows
# ℹ 57 more variables: power_play <dbl>, jackpot <dbl>,
#   jackpot_cash_value <dbl>, jackpot_winners <dbl>, jackpot_co_winners <dbl>,
#   match_5_prize <dbl>, match_5_co_winners <dbl>,
#   match_5_prize_with_power_play <dbl>,
#   match_5_co_winners_with_power_play <dbl>, match_4_powerball_prize <dbl>,
#   match_4_powerball_co_winners <dbl>, …

Calculate means and standard errors

Your turn: Calculate the mean and standard error for each of the Match $N$ + Powerball prizes for each year.

Tip

Recall the formula for the standard error of a sample mean is:

\[ \begin{aligned} \text{s.e.} &= \sqrt{\frac{\text{Variance}(X)}{\text{Sample size}}} \\ &= \frac{\text{Standard deviation}(X)}{\sqrt{\text{Sample size}}} \end{aligned} \]

$$$$

powerball |>
  # generate year variable
  mutate(
    year = year(x = draw_date),
    .before = everything()
  ) |>
  # calculate mean and se for the match N powerball winner columns
  summarize(
    across(
      .cols = starts_with("match") & contains("powerball") & ends_with("winners"),
      .fns = list(mean = mean, se = \(x) sd(x) / sqrt(n())),
      .names = "{.col}_{.fn}"
    ),
    # do this for each year in the dataset
    .by = year
  )

# A tibble: 23 × 11
    year match_4_powerball_co_wi…¹ match_4_powerball_co…² match_3_powerball_co…³
   <dbl>                     <dbl>                  <dbl>                  <dbl>
 1  2023                    0.193                  0.0496                   9.75
 2  2022                    0.153                  0.0363                  10.1 
 3  2021                    0.138                  0.0312                   9.88
 4  2020                    0.0762                 0.0260                   7.49
 5  2019                    0.125                  0.0326                  10.8 
 6  2018                    0.221                  0.0510                  11.3 
 7  2017                    0.192                  0.0412                  13.4 
 8  2016                    0.295                  0.103                   19.2 
 9  2015                    0.288                  0.0839                  12.9 
10  2014                    0.248                  0.0484                  13.1 
# ℹ 13 more rows
# ℹ abbreviated names: ¹match_4_powerball_co_winners_mean,
#   ²match_4_powerball_co_winners_se, ³match_3_powerball_co_winners_mean
# ℹ 7 more variables: match_3_powerball_co_winners_se <dbl>,
#   match_2_powerball_co_winners_mean <dbl>,
#   match_2_powerball_co_winners_se <dbl>,
#   match_1_powerball_co_winners_mean <dbl>, …

Clean up column names

Your turn: Remove "powerball_co_winners_" from each column name.

Tip

rename() does not allow use of the across() function. Instead, check out rename_with() from the dplyr package.

Tip

stringr contains many functions for working with character strings. Check out the cheat sheet for examples!

powerball |>
  # generate year variable
  mutate(
    year = year(x = draw_date),
    .before = everything()
  ) |>
  # calculate mean and se for the match N powerball winner columns
  summarize(
    across(
      .cols = starts_with("match") & contains("powerball") & ends_with("winners"),
      .fns = list(mean = mean, se = \(x) sd(x) / sqrt(n())),
      .names = "{.col}_{.fn}"
    ),
    # do this for each year in the dataset
    .by = year
  ) |>
  # remove extraneous string from columns
  rename_with(
    .cols = starts_with("match"),
    .fn = \(x) str_remove(string = x, pattern = "powerball_co_winners_")
  )

# A tibble: 23 × 11
    year match_4_mean match_4_se match_3_mean match_3_se match_2_mean match_2_se
   <dbl>        <dbl>      <dbl>        <dbl>      <dbl>        <dbl>      <dbl>
 1  2023       0.193      0.0496         9.75      1.08          198.      19.0 
 2  2022       0.153      0.0363        10.1       1.66          205.      34.4 
 3  2021       0.138      0.0312         9.88      0.804         198.      15.5 
 4  2020       0.0762     0.0260         7.49      0.481         156.       7.62
 5  2019       0.125      0.0326        10.8       1.18          212.      20.9 
 6  2018       0.221      0.0510        11.3       1.04          236.      21.4 
 7  2017       0.192      0.0412        13.4       1.89          270.      34.8 
 8  2016       0.295      0.103         19.2       5.47          386.     103.  
 9  2015       0.288      0.0839        12.9       1.55          233.      26.1 
10  2014       0.248      0.0484        13.1       0.764         218.      11.6 
# ℹ 13 more rows
# ℹ 4 more variables: match_1_mean <dbl>, match_1_se <dbl>, match_0_mean <dbl>,
#   match_0_se <dbl>

Restructure data frame to appropriate form for visualization

Demo: We need the structure to be one row for each year and prize (i.e. 0, 1, 2, 3, 4) and separate columns for the means and standard errors. We can use pivot_longer() to accomplish this task, but it’s a bit more complicated than past pivoting operations since the column names contain both a variable (e.g. match_4) and a variable name (i.e. mean or se). We can leverage the names_to argument and its ability to pass in a character vector with multiple values to create multiple columns in the resulting data frame. According to the documentation,

If length > 1, multiple columns will be created. In this case, one of names_sep or names_pattern must be supplied to specify how the column names should be split. There are also two additional character values you can take advantage of:

NA will discard the corresponding component of the column name.

".value" indicates that the corresponding component of the column name defines the name of the output column containing the cell values, overriding values_to entirely.

powerball |>
  # generate year variable
  mutate(
    year = year(x = draw_date),
    .before = everything()
  ) |>
  # calculate mean and se for the match N powerball winner columns
  summarize(
    across(
      .cols = starts_with("match") & contains("powerball") & ends_with("winners"),
      .fns = list(mean = mean, se = \(x) sd(x) / sqrt(n())),
      .names = "{.col}_{.fn}"
    ),
    # do this for each year in the dataset
    .by = year
  ) |>
  # remove extraneous string from columns
  rename_with(
    .cols = starts_with("match"),
    .fn = \(x) str_remove(string = x, pattern = "powerball_co_winners_")
  ) |>
  # restructure to one row per year per game
  # separate columns for mean and se
  pivot_longer(
    cols = -year,
    # columns contain a variable and a variable name
    names_to = c("game", ".value"),
    # ignore column prefix
    names_prefix = "match_",
    # separating character
    names_sep = "_"
  ) |>
  # reformat game column values for visualization
  mutate(game = str_glue("Match {game}"))

# A tibble: 115 × 4
    year game        mean       se
   <dbl> <glue>     <dbl>    <dbl>
 1  2023 Match 4    0.193   0.0496
 2  2023 Match 3    9.75    1.08  
 3  2023 Match 2  198.     19.0   
 4  2023 Match 1 1508.    140.    
 5  2023 Match 0 3597.    327.    
 6  2022 Match 4    0.153   0.0363
 7  2022 Match 3   10.1     1.66  
 8  2022 Match 2  205.     34.4   
 9  2022 Match 1 1568.    263.    
10  2022 Match 0 3775.    635.    
# ℹ 105 more rows

Plot the data

Demo: Now that we have the appropriate data structure, we can create the visualization.

powerball |>
  # generate year variable
  mutate(
    year = year(x = draw_date),
    .before = everything()
  ) |>
  # calculate mean and se for the match N powerball winner columns
  summarize(
    across(
      .cols = starts_with("match") & contains("powerball") & ends_with("winners"),
      .fns = list(mean = mean, se = \(x) sd(x) / sqrt(n())),
      .names = "{.col}_{.fn}"
    ),
    # do this for each year in the dataset
    .by = year
  ) |>
  # remove extraneous string from columns
  rename_with(
    .cols = starts_with("match"),
    .fn = \(x) str_remove(string = x, pattern = "powerball_co_winners_")
  ) |>
  # restructure to one row per year per game
  # separate columns for mean and se
  pivot_longer(
    cols = -year,
    # columns contain a variable and a variable name
    names_to = c("game", ".value"),
    # ignore column prefix
    names_prefix = "match_",
    # separating character
    names_sep = "_"
  ) |>
  # reformat game column values for visualization
  mutate(game = str_glue("Match {game}")) |>
  ggplot(mapping = aes(x = year, y = mean)) +
  geom_point() +
  geom_linerange(mapping = aes(
    ymin = mean - se,
    ymax = mean + se
  )) +
  facet_wrap(facets = vars(game), scales = "free_y") +
  labs(
    title = "The number of Match N Powerball Prize winners trends downward",
    subtitle = "Average number of prize winners (plus/minus 1 standard error)",
    x = "Year",
    y = "Number of Colorado winners",
    caption = "Source: Colorado Lottery"
  ) +
  theme_minimal()

Session information

sessioninfo::session_info()

─ Session info ───────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.3.1 (2023-06-16)
 os       macOS Ventura 13.5.2
 system   aarch64, darwin20
 ui       X11
 language (EN)
 collate  en_US.UTF-8
 ctype    en_US.UTF-8
 tz       America/New_York
 date     2023-10-05
 pandoc   3.1.1 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/ (via rmarkdown)

─ Packages ───────────────────────────────────────────────────────────────────
 package     * version date (UTC) lib source
 bit           4.0.5   2022-11-15 [1] CRAN (R 4.3.0)
 bit64         4.0.5   2020-08-30 [1] CRAN (R 4.3.0)
 cli           3.6.1   2023-03-23 [1] CRAN (R 4.3.0)
 colorspace    2.1-0   2023-01-23 [1] CRAN (R 4.3.0)
 crayon        1.5.2   2022-09-29 [1] CRAN (R 4.3.0)
 digest        0.6.31  2022-12-11 [1] CRAN (R 4.3.0)
 dplyr       * 1.1.2   2023-04-20 [1] CRAN (R 4.3.0)
 evaluate      0.21    2023-05-05 [1] CRAN (R 4.3.0)
 fansi         1.0.4   2023-01-22 [1] CRAN (R 4.3.0)
 farver        2.1.1   2022-07-06 [1] CRAN (R 4.3.0)
 fastmap       1.1.1   2023-02-24 [1] CRAN (R 4.3.0)
 forcats     * 1.0.0   2023-01-29 [1] CRAN (R 4.3.0)
 generics      0.1.3   2022-07-05 [1] CRAN (R 4.3.0)
 ggplot2     * 3.4.2   2023-04-03 [1] CRAN (R 4.3.0)
 glue          1.6.2   2022-02-24 [1] CRAN (R 4.3.0)
 gtable        0.3.3   2023-03-21 [1] CRAN (R 4.3.0)
 here          1.0.1   2020-12-13 [1] CRAN (R 4.3.0)
 hms           1.1.3   2023-03-21 [1] CRAN (R 4.3.0)
 htmltools     0.5.5   2023-03-23 [1] CRAN (R 4.3.0)
 htmlwidgets   1.6.2   2023-03-17 [1] CRAN (R 4.3.0)
 janitor     * 2.2.0   2023-02-02 [1] CRAN (R 4.3.0)
 jsonlite      1.8.5   2023-06-05 [1] CRAN (R 4.3.0)
 knitr         1.43    2023-05-25 [1] CRAN (R 4.3.0)
 labeling      0.4.2   2020-10-20 [1] CRAN (R 4.3.0)
 lifecycle     1.0.3   2022-10-07 [1] CRAN (R 4.3.0)
 lubridate   * 1.9.2   2023-02-10 [1] CRAN (R 4.3.0)
 magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.3.0)
 munsell       0.5.0   2018-06-12 [1] CRAN (R 4.3.0)
 pillar        1.9.0   2023-03-22 [1] CRAN (R 4.3.0)
 pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.3.0)
 purrr       * 1.0.1   2023-01-10 [1] CRAN (R 4.3.0)
 R6            2.5.1   2021-08-19 [1] CRAN (R 4.3.0)
 ragg          1.2.5   2023-01-12 [1] CRAN (R 4.3.0)
 readr       * 2.1.4   2023-02-10 [1] CRAN (R 4.3.0)
 rlang         1.1.1   2023-04-28 [1] CRAN (R 4.3.0)
 rmarkdown     2.22    2023-06-01 [1] CRAN (R 4.3.0)
 rprojroot     2.0.3   2022-04-02 [1] CRAN (R 4.3.0)
 rstudioapi    0.14    2022-08-22 [1] CRAN (R 4.3.0)
 scales      * 1.2.1   2022-08-20 [1] CRAN (R 4.3.0)
 sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.3.0)
 snakecase     0.11.0  2019-05-25 [1] CRAN (R 4.3.0)
 stringi       1.7.12  2023-01-11 [1] CRAN (R 4.3.0)
 stringr     * 1.5.0   2022-12-02 [1] CRAN (R 4.3.0)
 systemfonts   1.0.4   2022-02-11 [1] CRAN (R 4.3.0)
 textshaping   0.3.6   2021-10-13 [1] CRAN (R 4.3.0)
 tibble      * 3.2.1   2023-03-20 [1] CRAN (R 4.3.0)
 tidyr       * 1.3.0   2023-01-24 [1] CRAN (R 4.3.0)
 tidyselect    1.2.0   2022-10-10 [1] CRAN (R 4.3.0)
 tidyverse   * 2.0.0   2023-02-22 [1] CRAN (R 4.3.0)
 timechange    0.2.0   2023-01-11 [1] CRAN (R 4.3.0)
 tzdb          0.4.0   2023-05-12 [1] CRAN (R 4.3.0)
 utf8          1.2.3   2023-01-31 [1] CRAN (R 4.3.0)
 vctrs         0.6.3   2023-06-14 [1] CRAN (R 4.3.0)
 vroom         1.6.3   2023-04-28 [1] CRAN (R 4.3.0)
 withr         2.5.0   2022-03-03 [1] CRAN (R 4.3.0)
 xfun          0.39    2023-04-20 [1] CRAN (R 4.3.0)
 yaml          2.3.7   2023-01-23 [1] CRAN (R 4.3.0)

 [1] /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/library

──────────────────────────────────────────────────────────────────────────────

Packages

Powerball

Import and clean the data

Analyze the data

Generate the year variable

Calculate means and standard errors

Clean up column names

Restructure data frame to appropriate form for visualization

Plot the data

Generate the `year` variable