pivot_wider in R with multiple columns, but keep the name of former variables in separate columns

Question

Suppose I I wish to put the data in wideformat by different setosa-ID.

iris2 <- iris %>%
          head(10) %>%
          mutate(num = 1:10,
          ID = str_c(Species,1:10))

       Sepal.Length Sepal.Width Petal.Length Petal.Width Species num       ID
1           5.1         3.5          1.4         0.2     setosa   1  setosa1
2           4.9         3.0          1.4         0.2     setosa   2  setosa2
3           4.7         3.2          1.3         0.2     setosa   3  setosa3
4           4.6         3.1          1.5         0.2     setosa   4  setosa4
5           5.0         3.6          1.4         0.2     setosa   5  setosa5
6           5.4         3.9          1.7         0.4     setosa   6  setosa6
7           4.6         3.4          1.4         0.3     setosa   7  setosa7
8           5.0         3.4          1.5         0.2     setosa   8  setosa8
9           4.4         2.9          1.4         0.2     setosa   9  setosa9
10          4.9         3.1          1.5         0.1     setosa  10  setosa10

However, the pivot_wider returns sort of diagonal matrix for the the result

iris3 <- iris2 %>%
  pivot_wider(names_from = ID,
              values_from = c(Sepal.Length,Sepal.Width,Petal.Length,Petal.Width))

   Species   num Sepal.Length_se~ Sepal.Length_se~ Sepal.Length_se~ Sepal.Length_se~ Sepal.Length_se~ Sepal.Length_se~
   <fct>   <int>            <dbl>            <dbl>            <dbl>            <dbl>            <dbl>            <dbl>
 1 setosa      1              5.1             NA               NA               NA                 NA             NA  
 2 setosa      2             NA                4.9             NA               NA                 NA             NA  
 3 setosa      3             NA               NA                4.7             NA                 NA             NA  
 4 setosa      4             NA               NA               NA                4.6               NA             NA  
 5 setosa      5             NA               NA               NA               NA                  5             NA  
 6 setosa      6             NA               NA               NA               NA                 NA              5.4

I wish the data turned into something like this:

iris_want <- tibble("Species" = rep("setosa",40),
                    "num" = rep(1:10,each=4),
                    "Values_From" = rep(c("Sepal.Length","Sepal.Width","Petal.Length","Petal.Width"),10),
                    "setosa1" = NA,
                    "setosa2" = NA
)
   Species   num Values_From  setosa1 setosa2
   <chr>   <int> <chr>        <lgl>   <lgl>  
 1 setosa      1 Sepal.Length NA      NA     
 2 setosa      1 Sepal.Width  NA      NA     
 3 setosa      1 Petal.Length NA      NA     
 4 setosa      1 Petal.Width  NA      NA     
 5 setosa      2 Sepal.Length NA      NA     
 6 setosa      2 Sepal.Width  NA      NA     
 7 setosa      2 Petal.Length NA      NA     
 8 setosa      2 Petal.Width  NA      NA     
 9 setosa      3 Sepal.Length NA      NA     
10 setosa      3 Sepal.Width  NA      NA  

How can I approach this problem?

Answer 1

Maybe this can help:

library(dplyr)
library(tidyr)
#Code
#Code
iris2 %>% pivot_longer(-c(Species,num,ID)) %>%
  pivot_wider(names_from = ID,values_from=value)

Output:

# A tibble: 40 x 13
   Species   num name  setosa1 setosa2 setosa3 setosa4 setosa5 setosa6 setosa7 setosa8
   <fct>   <int> <chr>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
 1 setosa      1 Sepa~     5.1    NA      NA        NA      NA      NA      NA      NA
 2 setosa      1 Sepa~     3.5    NA      NA        NA      NA      NA      NA      NA
 3 setosa      1 Peta~     1.4    NA      NA        NA      NA      NA      NA      NA
 4 setosa      1 Peta~     0.2    NA      NA        NA      NA      NA      NA      NA
 5 setosa      2 Sepa~    NA       4.9    NA        NA      NA      NA      NA      NA
 6 setosa      2 Sepa~    NA       3      NA        NA      NA      NA      NA      NA
 7 setosa      2 Peta~    NA       1.4    NA        NA      NA      NA      NA      NA
 8 setosa      2 Peta~    NA       0.2    NA        NA      NA      NA      NA      NA
 9 setosa      3 Sepa~    NA      NA       4.7      NA      NA      NA      NA      NA
10 setosa      3 Sepa~    NA      NA       3.2      NA      NA      NA      NA      NA
# ... with 30 more rows, and 2 more variables: setosa9 <dbl>, setosa10 <dbl>