Pivot longer in dplyr for mutiple value columns

user_10706893 0
Viewed 295

I have the following table that can be generated using this code

structure(list(total = c(9410, 12951.1794783802), op = c(3896.66666666667, 
6976.57663230241), ox = c(2200, 4920.84776902887), ox15 = c(183.333333333333, 
694.262648008611), ox30 = c(133.333333333333, 368.090117767537
), hy = c(283.333333333333, 1146.14924596984), hy10 = c(NA, 433.993925588459
)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"
))

While this currently has 2 rows and 7 columns, I want the resulting table to have 7 rows and 3 columns with the values in each current row to be transposed to its own column. Is there a way to do so using the pivot_longer() function?

tidyr dplyr r
4 Answers

How about like this:

  library(tidyverse)
  dat <- structure(list(total = c(9410, 12951.1794783802), 
                      op = c(3896.66666666667, 6976.57663230241), 
                      ox = c(2200, 4920.84776902887), 
                      ox15 = c(183.333333333333, 694.262648008611), 
                      ox30 = c(133.333333333333, 368.090117767537), 
                      hy = c(283.333333333333, 1146.14924596984),
                      hy10 = c(NA, 433.993925588459)),
                 row.names = c(NA, -2L), 
                 class = c("tbl_df", "tbl", "data.frame"))

dat %>% 
  mutate(obs = 1:n()) %>% 
  pivot_longer(-obs, names_to="var", values_to="vals") %>% 
  pivot_wider(names_from="obs", values_from="vals", names_prefix="obs_")
#> # A tibble: 7 × 3
#>   var   obs_1  obs_2
#>   <chr> <dbl>  <dbl>
#> 1 total 9410  12951.
#> 2 op    3897.  6977.
#> 3 ox    2200   4921.
#> 4 ox15   183.   694.
#> 5 ox30   133.   368.
#> 6 hy     283.  1146.
#> 7 hy10    NA    434.

Created on 2022-02-03 by the reprex package (v2.0.1)

user_82064340

I don't think pivot_longer is the right function here. You can achieve it more simply by transposing with t and using rownames_to_column

tibble::rownames_to_column(as.data.frame(t(df)))
#>   rowname        V1         V2
#> 1   total 9410.0000 12951.1795
#> 2      op 3896.6667  6976.5766
#> 3      ox 2200.0000  4920.8478
#> 4    ox15  183.3333   694.2626
#> 5    ox30  133.3333   368.0901
#> 6      hy  283.3333  1146.1492
#> 7    hy10        NA   433.9939

Created on 2022-02-03 by the reprex package (v2.0.1)

user_125003150

This can be achieved with data.table::transpose

data.table::transpose(df1, keep.names = "var")
    var        V1         V2
1 total 9410.0000 12951.1795
2    op 3896.6667  6976.5766
3    ox 2200.0000  4920.8478
4  ox15  183.3333   694.2626
5  ox30  133.3333   368.0901
6    hy  283.3333  1146.1492
7  hy10        NA   433.9939
user_37322710

Or using sjmisc::rotate_df:

sjmisc::rotate_df(df, rn="id")

#>      id        V1         V2
#> 1 total 9410.0000 12951.1795
#> 2    op 3896.6667  6976.5766
#> 3    ox 2200.0000  4920.8478
#> 4  ox15  183.3333   694.2626
#> 5  ox30  133.3333   368.0901
#> 6    hy  283.3333  1146.1492
#> 7  hy10        NA   433.9939
user_115644870
Related