How to transpose cells with multiple values?

Question

I'm loading in data from Excel and there are some cells with multiple values. I would like to transpose these cells such that each value gets a row.

For instance, in my data below, I'd have 10 rows for the numbers in id and time that are currently bunched in the first row.

The other values would need to be duplicated. So, as above, I'd repeat run fish, and boat_speed ten times for the first row.

structure(list(run = c(1, 2, 3, 4, 5, 6), id = c("20 4 4 4 4 4 4 11 11 11", 
"18 18 18 18 18 15 15 15 15 21 18 17 17 4 4 4 19", "8 8 8 7 7 7 7 4 4 4 4 4 4 15 15 4 4 4 4 18 18 18 18", 
"7 7 7 5 16 12 12 12 4", "21 21 21 21 21 21 8 6 6 6 6 6 6 9 9 9 4 4 4 4", 
"5 13 13 13 13 8"), time = c("550 1574 1575 1638 1639 1640 1641 2116 2117 2118", 
"632 633 637 638 639 880 881 882 883 1365 1413 1567 1569 2204 2205 2206 2214", 
"82 83 84 961 962 963 964 1527 1528 1529 1544 1545 1585 1596 1597 1649 1650 1651 1652 2001 2002 2003 2033", 
"734 735 736 1119 1376 1674 1675 1676 1869", "420 421 422 423 424 425 469 926 927 936 937 938 939 1353 1354 1355 2035 2036 2037 2038", 
"14 587 588 589 590 4455"), fish = c(20, 20, 20, 20, 20, 20), 
    boat_speed = c(0.05, 0.05, 0.05, 0.05, 0.05, 0.05)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

Answer 1

The tidyr::separate_rows function does exactly this. Assuming your data are stored in a data frame called df:

library(tidyverse)

df %>% 
  separate_rows(c(id, time))

     run id    time   fish boat_speed
   <dbl> <chr> <chr> <dbl>      <dbl>
 1     1 20    550      20       0.05
 2     1 4     1574     20       0.05
 3     1 4     1575     20       0.05
 4     1 4     1638     20       0.05
 5     1 4     1639     20       0.05
 6     1 4     1640     20       0.05
 7     1 4     1641     20       0.05
 8     1 11    2116     20       0.05
 9     1 11    2117     20       0.05
10     1 11    2118     20       0.05
# … with 75 more rows