Week 8: Apply it to your data 7

Import your data

data <- read_excel("../00_data/MyData.xlsx")
data

## # A tibble: 252 × 3
##        W Capacity Team     
##    <dbl>    <dbl> <chr>    
##  1     5    0.957 Cardinals
##  2     5    0.948 Cardinals
##  3     5    0.954 Cardinals
##  4     5    0.949 Cardinals
##  5     5    0.962 Cardinals
##  6     5    0.961 Cardinals
##  7     5    1.01  Cardinals
##  8     5    0.999 Cardinals
##  9     7    1.02  Falcons  
## 10     7    1.02  Falcons  
## # ℹ 242 more rows

Make Tidy

set.seed(12345) #for reproducible outcome
tiny_data <- data %>%
    
    #Select three columns
    select(W, Capacity, Team) %>%
    
    #Randomly select five rows
    sample_n(5)

tiny_data

## # A tibble: 5 × 3
##       W Capacity Team    
##   <dbl>    <dbl> <chr>   
## 1     9    0.886 Rams    
## 2     2    0.653 Bengals 
## 3     8    0.936 Steelers
## 4    13    1.01  49ers   
## 5    13    1.03  49ers

Pivoting

long to wide form

data_wide <- tiny_data %>%
    pivot_wider(names_from = W, values_from = Capacity)

## Warning: Values from `Capacity` are not uniquely identified; output will contain
## list-cols.
## • Use `values_fn = list` to suppress this warning.
## • Use `values_fn = {summary_fun}` to summarise duplicates.
## • Use the following dplyr code to identify duplicates.
##   {data} |>
##   dplyr::summarise(n = dplyr::n(), .by = c(Team, W)) |>
##   dplyr::filter(n > 1L)

data_wide

## # A tibble: 4 × 5
##   Team     `9`       `2`       `8`       `13`     
##   <chr>    <list>    <list>    <list>    <list>   
## 1 Rams     <dbl [1]> <NULL>    <NULL>    <NULL>   
## 2 Bengals  <NULL>    <dbl [1]> <NULL>    <NULL>   
## 3 Steelers <NULL>    <NULL>    <dbl [1]> <NULL>   
## 4 49ers    <NULL>    <NULL>    <NULL>    <dbl [2]>

data_wide2 <- tiny_data %>% slice(-4) %>%
pivot_wider(names_from = W, values_from = Capacity)
data_wide2

## # A tibble: 4 × 5
##   Team        `9`    `2`    `8`  `13`
##   <chr>     <dbl>  <dbl>  <dbl> <dbl>
## 1 Rams      0.886 NA     NA     NA   
## 2 Bengals  NA      0.653 NA     NA   
## 3 Steelers NA     NA      0.936 NA   
## 4 49ers    NA     NA     NA      1.03

wide to long form

data_wide2 %>% pivot_longer('9':'13', names_to = "W", values_to = "Capacity", values_drop_na = TRUE)

## # A tibble: 4 × 3
##   Team     W     Capacity
##   <chr>    <chr>    <dbl>
## 1 Rams     9        0.886
## 2 Bengals  2        0.653
## 3 Steelers 8        0.936
## 4 49ers    13       1.03

Separating and Uniting

data2 <- data %>% mutate(Games = 16)
data2

## # A tibble: 252 × 4
##        W Capacity Team      Games
##    <dbl>    <dbl> <chr>     <dbl>
##  1     5    0.957 Cardinals    16
##  2     5    0.948 Cardinals    16
##  3     5    0.954 Cardinals    16
##  4     5    0.949 Cardinals    16
##  5     5    0.962 Cardinals    16
##  6     5    0.961 Cardinals    16
##  7     5    1.01  Cardinals    16
##  8     5    0.999 Cardinals    16
##  9     7    1.02  Falcons      16
## 10     7    1.02  Falcons      16
## # ℹ 242 more rows

Unite two columns

data2 <- data2 %>% unite(col = "Win_Ratio", c(W, Games), sep = "/")
data2

## # A tibble: 252 × 3
##    Win_Ratio Capacity Team     
##    <chr>        <dbl> <chr>    
##  1 5/16         0.957 Cardinals
##  2 5/16         0.948 Cardinals
##  3 5/16         0.954 Cardinals
##  4 5/16         0.949 Cardinals
##  5 5/16         0.962 Cardinals
##  6 5/16         0.961 Cardinals
##  7 5/16         1.01  Cardinals
##  8 5/16         0.999 Cardinals
##  9 7/16         1.02  Falcons  
## 10 7/16         1.02  Falcons  
## # ℹ 242 more rows

Seperate a columns

 data2 %>% separate(col = Win_Ratio, into = c("Wins", "Games"))

## # A tibble: 252 × 4
##    Wins  Games Capacity Team     
##    <chr> <chr>    <dbl> <chr>    
##  1 5     16       0.957 Cardinals
##  2 5     16       0.948 Cardinals
##  3 5     16       0.954 Cardinals
##  4 5     16       0.949 Cardinals
##  5 5     16       0.962 Cardinals
##  6 5     16       0.961 Cardinals
##  7 5     16       1.01  Cardinals
##  8 5     16       0.999 Cardinals
##  9 7     16       1.02  Falcons  
## 10 7     16       1.02  Falcons  
## # ℹ 242 more rows