Import your data

data_small <- data %>%
    select(peak_billboard_position,clean_name, album) %>%
    filter(peak_billboard_position %in% c("1", "2", "3"))

Pivoting

long to wide form

data_long <- data_small %>%
    pivot_longer(cols = c(clean_name, album),
                 values_to = "artist")

wide to long form

data_long %>%
    pivot_wider(values_from = artist)
## Warning: Values from `artist` 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(peak_billboard_position, name)) |>
##   dplyr::filter(n > 1L)
## # A tibble: 3 × 3
##   peak_billboard_position clean_name  album      
##                     <dbl> <list>      <list>     
## 1                       2 <chr [42]>  <chr [42]> 
## 2                       1 <chr [142]> <chr [142]>
## 3                       3 <chr [29]>  <chr [29]>

Separating and Uniting

Unite two columns

data_united <- data %>%
    unite(col = "newname", release_year:years_between, sep = "/", remove = TRUE)

Separate a column

data_united %>%
    separate(col = newname, into = c("release_year", "years_between"), sep = )
## Warning: Expected 2 pieces. Additional pieces discarded in 691 rows [1, 2, 3, 4, 5, 6,
## 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, ...].
## # A tibble: 691 × 11
##    Column1 sort_name clean_name album rank_2003 rank_2012 rank_2020 differential
##      <dbl> <chr>     <chr>      <chr> <chr>     <chr>     <chr>            <dbl>
##  1       1 Sinatra,… Frank Sin… "In … 100       101       282               -182
##  2       2 Diddley,… Bo Diddley "Bo … 214       216       455               -241
##  3       3 Presley,… Elvis Pre… "Elv… 55        56        332               -277
##  4       4 Sinatra,… Frank Sin… "Son… 306       308       NA                -195
##  5       5 Little R… Little Ri… "Her… 50        50        227               -177
##  6       6 Beyonce   Beyonce    "Lem… NA        NA        32                 469
##  7       7 Winehous… Amy Wineh… "Bac… NA        451       33                 468
##  8       8 Crickets  Buddy Hol… "The… 421       420       NA                 -80
##  9       9 Bush, Ka… Kate Bush  "Hou… NA        NA        68                 433
## 10      10 Davis, M… Miles Dav… "Kin… 12        12        31                 -19
## # ℹ 681 more rows
## # ℹ 3 more variables: release_year <chr>, years_between <chr>, album_id <chr>