"Pivot longer" all columns in single-row data frame into two "values" columns

Question

Title is complicated, but I don't know how to put this problem into words. So I'll demonstrate.

Here's my problem, with the desired output:

library(tibble)

# Input:
tribble(
  ~n_1, ~n_2, ~n_3, ~pct_1, ~pct_2, ~pct_3,
  10,   20,   30,   0.1,    0.2,    0.3
)
#> # A tibble: 1 x 6
#>     n_1   n_2   n_3 pct_1 pct_2 pct_3
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1    10    20    30   0.1   0.2   0.3

# Desired output:
tribble(
  ~name, ~n, ~pct,
  1,     10, 0.1,
  2,     20, 0.2,
  3,     30, 0.3
)
#> # A tibble: 3 x 3
#>    name     n   pct
#>   <dbl> <dbl> <dbl>
#> 1     1    10   0.1
#> 2     2    20   0.2
#> 3     3    30   0.3

I tried tidyr::pivot_longer(), but I can't get it right. Is there any way?

Answer 1

One option could be:

df %>%
 pivot_longer(everything(),    
              names_to = c(".value", "name"),
              names_pattern = "(.*)_(.)")

  name      n   pct
  <chr> <dbl> <dbl>
1 1        10   0.1
2 2        20   0.2
3 3        30   0.3

Answer 2

Try this approach. As your main variable is concatenated you can use separate() (using sep='_') after pivot_longer() and then pivot_wider() to obtain the expected dataframe. Here the code:

library(tidyverse)
#Code
df %>% pivot_longer(cols = everything()) %>%
  separate(name,into = c('var','name'),sep = '_') %>%
  pivot_wider(names_from = var,values_from=value)

Output:

# A tibble: 3 x 3
  name      n   pct
  <chr> <dbl> <dbl>
1 1        10   0.1
2 2        20   0.2
3 3        30   0.3

Some data used (the one you provided):

#Data
df <- structure(list(n_1 = 10, n_2 = 20, n_3 = 30, pct_1 = 0.1, pct_2 = 0.2, 
    pct_3 = 0.3), row.names = c(NA, -1L), class = c("tbl_df", 
"tbl", "data.frame"))