Import your data

myData <- read_excel("../00_data/myData_charts.xlsx")
myData
## # A tibble: 10,879 × 8
##    team          team_name  year   total   home   away  week weekly_attendance
##    <chr>         <chr>     <dbl>   <dbl>  <dbl>  <dbl> <dbl> <chr>            
##  1 San Francisco 49ers      2000 1057954 541964 515990     1 54626            
##  2 San Francisco 49ers      2000 1057954 541964 515990     2 66879            
##  3 San Francisco 49ers      2000 1057954 541964 515990     3 65945            
##  4 San Francisco 49ers      2000 1057954 541964 515990     4 64127            
##  5 San Francisco 49ers      2000 1057954 541964 515990     5 66985            
##  6 San Francisco 49ers      2000 1057954 541964 515990     6 68344            
##  7 San Francisco 49ers      2000 1057954 541964 515990     7 59870            
##  8 San Francisco 49ers      2000 1057954 541964 515990     8 73169            
##  9 San Francisco 49ers      2000 1057954 541964 515990     9 68109            
## 10 San Francisco 49ers      2000 1057954 541964 515990    10 64900            
## # … with 10,869 more rows

Pivoting

long to wide form

myData_long <- myData %>%
    
    pivot_longer(cols = c(`year`, `total`), names_to = "Year", values_to = "Cases")

myData_long
## # A tibble: 21,758 × 8
##    team          team_name   home   away  week weekly_attendance Year    Cases
##    <chr>         <chr>      <dbl>  <dbl> <dbl> <chr>             <chr>   <dbl>
##  1 San Francisco 49ers     541964 515990     1 54626             year     2000
##  2 San Francisco 49ers     541964 515990     1 54626             total 1057954
##  3 San Francisco 49ers     541964 515990     2 66879             year     2000
##  4 San Francisco 49ers     541964 515990     2 66879             total 1057954
##  5 San Francisco 49ers     541964 515990     3 65945             year     2000
##  6 San Francisco 49ers     541964 515990     3 65945             total 1057954
##  7 San Francisco 49ers     541964 515990     4 64127             year     2000
##  8 San Francisco 49ers     541964 515990     4 64127             total 1057954
##  9 San Francisco 49ers     541964 515990     5 66985             year     2000
## 10 San Francisco 49ers     541964 515990     5 66985             total 1057954
## # … with 21,748 more rows

wide to long form

myData_long %>%
    
    pivot_wider(names_from = Year, values_from = Cases)
## # A tibble: 10,879 × 8
##    team          team_name   home   away  week weekly_attendance  year   total
##    <chr>         <chr>      <dbl>  <dbl> <dbl> <chr>             <dbl>   <dbl>
##  1 San Francisco 49ers     541964 515990     1 54626              2000 1057954
##  2 San Francisco 49ers     541964 515990     2 66879              2000 1057954
##  3 San Francisco 49ers     541964 515990     3 65945              2000 1057954
##  4 San Francisco 49ers     541964 515990     4 64127              2000 1057954
##  5 San Francisco 49ers     541964 515990     5 66985              2000 1057954
##  6 San Francisco 49ers     541964 515990     6 68344              2000 1057954
##  7 San Francisco 49ers     541964 515990     7 59870              2000 1057954
##  8 San Francisco 49ers     541964 515990     8 73169              2000 1057954
##  9 San Francisco 49ers     541964 515990     9 68109              2000 1057954
## 10 San Francisco 49ers     541964 515990    10 64900              2000 1057954
## # … with 10,869 more rows

Separating and Uniting

Unite two columns

myData_unite <- myData %>%
    
    unite(col = "homeaway", home:away, sep = "+")

myData_unite
## # A tibble: 10,879 × 7
##    team          team_name  year   total homeaway       week weekly_attendance
##    <chr>         <chr>     <dbl>   <dbl> <chr>         <dbl> <chr>            
##  1 San Francisco 49ers      2000 1057954 541964+515990     1 54626            
##  2 San Francisco 49ers      2000 1057954 541964+515990     2 66879            
##  3 San Francisco 49ers      2000 1057954 541964+515990     3 65945            
##  4 San Francisco 49ers      2000 1057954 541964+515990     4 64127            
##  5 San Francisco 49ers      2000 1057954 541964+515990     5 66985            
##  6 San Francisco 49ers      2000 1057954 541964+515990     6 68344            
##  7 San Francisco 49ers      2000 1057954 541964+515990     7 59870            
##  8 San Francisco 49ers      2000 1057954 541964+515990     8 73169            
##  9 San Francisco 49ers      2000 1057954 541964+515990     9 68109            
## 10 San Francisco 49ers      2000 1057954 541964+515990    10 64900            
## # … with 10,869 more rows

Separate a column

myData_sep <- myData_unite %>%
    
    separate(col = "homeaway", into = c("home", "away"))
## Warning: Expected 2 pieces. Additional pieces discarded in 31 rows [341, 682, 1023,
## 1364, 2046, 2387, 2728, 3069, 3410, 3751, 4092, 4433, 4774, 5115, 5456, 5797,
## 6138, 6479, 6820, 7161, ...].
myData_sep
## # A tibble: 10,879 × 8
##    team          team_name  year   total home   away    week weekly_attendance
##    <chr>         <chr>     <dbl>   <dbl> <chr>  <chr>  <dbl> <chr>            
##  1 San Francisco 49ers      2000 1057954 541964 515990     1 54626            
##  2 San Francisco 49ers      2000 1057954 541964 515990     2 66879            
##  3 San Francisco 49ers      2000 1057954 541964 515990     3 65945            
##  4 San Francisco 49ers      2000 1057954 541964 515990     4 64127            
##  5 San Francisco 49ers      2000 1057954 541964 515990     5 66985            
##  6 San Francisco 49ers      2000 1057954 541964 515990     6 68344            
##  7 San Francisco 49ers      2000 1057954 541964 515990     7 59870            
##  8 San Francisco 49ers      2000 1057954 541964 515990     8 73169            
##  9 San Francisco 49ers      2000 1057954 541964 515990     9 68109            
## 10 San Francisco 49ers      2000 1057954 541964 515990    10 64900            
## # … with 10,869 more rows

Missing Values