Using tidyr::pivot_longer to transform a one-row dataframe: take pairs of columns and stack them

Question

I have a dataframe with a single row. Pairs of columns go together, where one holds the name and the other has the value. So I want to extract name & column combination from the 1-row format onto stacked format, having one column for names and a second for values. I'm trying pivot_longer but somehow can't get it done.

Data

library(tidyverse)

df <-
tribble(~ var_1, ~ var_1_value, ~ var_2, ~ var_2_value, ~ var_3, ~ var_3_value,
        "height", 200, "weight", 400, "length", 1000)

> df

##   var_1  var_1_value var_2  var_2_value var_3  var_3_value
##   <chr>        <dbl> <chr>        <dbl> <chr>        <dbl>
## 1 height         200 weight         400 length        1000

Desired Output

  var_name var_value
  <chr>        <dbl>
1 height         200
2 weight         400
3 length        1000

What I've tried

df %>%
  pivot_longer(cols = everything(),
               names_to = ".value",
               names_pattern = "var_[0-9]_(.*)")

##   value
##   <dbl>
## 1   200
## 2   400
## 3  1000

I know that this is a fairly elementary problem, but I can't figure out how to solve this, nor did I find a similar answered problem. Thanks!

Answer 1

The way you have your column names now, it is not easy to use pivot_longer directly on this. You can change the column names like :

names(df) <- paste0(sub('_value', '', names(df)), c('_char', '_value'))
df

# A tibble: 1 x 6
#  var_1_char var_1_value var_2_char var_2_value var_3_char var_3_value
#  <chr>            <dbl> <chr>            <dbl> <chr>            <dbl>
#1 height             200 weight             400 length            1000

and then use pivot_longer :

tidyr::pivot_longer(df, 
                    cols = everything(), 
                    names_to = ".value",
                    names_pattern = 'var_\\d+_(.*)')

#  char   value
#  <chr>  <dbl>
#1 height   200
#2 weight   400
#3 length  1000

Answer 2

Maybe try this (not the most fashion way):

library(tidyverse)
#Code
new <- df %>%
  mutate(across(everything(),~as.character(.))) %>%
  pivot_longer(everything()) %>%
  mutate(Var=ifelse(!is.na(as.numeric(value)),'val','var')) %>%
  select(-name) %>%
  group_by(Var) %>%
  mutate(id=row_number()) %>%
  pivot_wider(names_from = Var,values_from=value) %>%
  mutate(val=as.numeric(val)) %>%
  ungroup() %>% select(-id)

Output:

# A tibble: 3 x 2
  var      val
  <chr>  <dbl>
1 height   200
2 weight   400
3 length  1000