Transpose dataframe from wide-to-long with multiple values

Question

I have wide data with multiple values that I need to transpose into a long data table. I've looked at several sites and StackOverflow and cannot seem to find the answer to this, even though its so simple.

Some example data:

#example data
wide <- data.frame(
  VariableName = c("Var1","Var2","Var3","Var4","Var5"),
 Year1 = c(411,723,325,456,579),
 Year2 = c(123,300,400,500,600),
 Year3 = c(1457,1000,569,896,956)
)

which looks like this

  VariableName Year1 Year2 Year3
1         Var1   411   123  1457
2         Var2   723   300  1000
3         Var3   325   400   569
4         Var4   456   500   896
5         Var5   579   600   956

And I need it to look like this

VariableName    Var1  Var2  Var3  Var4  Var5
Year1           411   723   325   456   579
Year2           123   300   400   500   600
Year3           1457  1000  569   896   956

I've tried several functions, including gather(), pivot_longer(), melt() but I just can't seem to get the function to either work, or give me the results I need.

Answer 1

Option 1: With tidyr, pivot_longer and then pivot_wider:

library(tidyr)

wide %>%
  pivot_longer(-VariableName, names_to = 'Year') %>%
  pivot_wider(id_cols = Year, names_from = VariableName)

Option 2: You can also use some useful tools provided by tibble to operate row names before and after transposing the data.

library(tibble)

wide %>%
  column_to_rownames("VariableName") %>%
  t() %>% as.data.frame() %>%
  rownames_to_column("Year")

Output

# # A tibble: 3 × 6
#   Year   Var1  Var2  Var3  Var4  Var5
#   <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 Year1   411   723   325   456   579
# 2 Year2   123   300   400   500   600
# 3 Year3  1457  1000   569   896   956