Pivoting 4x4 into 16x1

Question

I've been looking for a method to combine a dataset with four columns into one row. I'll give an illustration of what I mean.

I want this dataset to be transformed into...

My issue is that when I try to utilise Excel's pivot function, it won't let me pivot the data into a single row. I've also tried a tonne of different Excel functions, but I haven't had any success.

The data is currently in Excel, where it would probably be simpler to alter it. However, R would function just as well if Excel only lacked that feature. Thank you for your help.

Answer 1

Try this:

df <- tibble::tribble(
  ~ID, ~sit1, ~sit2, ~sit3, ~sit4,
  1,   -1,    2,    0,    0,
  2,   -3,   -2,   -3,   -2,
  3,   -1,    2,    0,    4,
  4,   -1,    2,    0,    0 
)

library(tidyr)

col_order <- expand.grid(names(df)[-1], df$ID) |> 
  unite("Var", c(Var1, Var2))

df |> 
  pivot_wider(names_from = ID, values_from = starts_with("sit")) |> 
  dplyr::relocate(col_order$Var)

# A tibble: 1 × 16
  sit1_1 sit2_1 sit3_1 sit4_1 sit1_2 sit2_2 sit3_2 sit4_2 sit1_3 sit2_3 sit3_3
   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1     -1      2      0      0     -3     -2     -3     -2     -1      2      0
# … with 5 more variables: sit4_3 <dbl>, sit1_4 <dbl>, sit2_4 <dbl>, sit3_4 <dbl>,
#   sit4_4 <dbl>