Pivoting 4x4 into 16x1-CodePudding

I have been trying to find a way to pivot a dataset with 4 columns into one row. I will show an example of what I mean.

I want this dataset to be transformed into...

My problem comes when I try to use the pivot function in Excel, it doesn't allow me to pivot the data into one row. I've also tried many other functions in Excel but haven't had much luck.

Currently, the data is in Excel and would probably be easier to manipulate there. However, if Excel simply doesn't have that functionality then R would work just fine as well. Thank you for the assistance.

CodePudding user response：

A trick would be to reshaping data first into long format and then into wide format after few manipulations using tidyr from tidyverse family: I first stored your example data in R called 'df':

  df
  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

convert df into long format putting all sit columns into one single column called 'sit':

dflong<-pivot_longer(df[,-c(1)],cols = everything(),names_to = 
                     'sit',values_to = 'values')

make the values of sit unique :

dflong$sit<-paste(dflong$sit,rep(c(1:4),each=4),sep = '_')

then reshape the data to wide format assigning each sit value into a column

dfwide<-pivot_wider(dflong,names_from = 'sit',values_from = 'values')

Hope that would be helping.

CodePudding user response：

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>