Reshaping wide to long with every n columns-CodePudding

suppose I have a dataframe

dw <- read.table(header=T, text='
 ID     q1    q2   q3     q4     q5    ...q10  
   A   10    6     50     10      bA   
   B   12    5     70     11      bB
   C   20    7     20     8       bC
   D   22    8     22     9       bD
 ')

I would like to move every 2 columns after ID to new rows, so it looks like

   ID  q1   q2 
   A   10    6     
   B   12    5     
   C   20    7     
   D   22    8 
   A   50    10
   B   70   11
   C   20   8
   D   22   9
   ....

pivot_longer seems to move every single column instead of multiple columns?

CodePudding user response：

It seems that you are not concerned with the column names (other than ID), and that they are all the same class. For this, we can "pivot" manually, without the safeguards or power of pivot_lower perhaps, but without the requirements as well.

The first step is to make sure that class won't be an issue; because you have some strings in there, we need to convert all to character:

dw[-1] <- lapply(dw[-1], as.character)

After that, we can manually extract every two (non-ID) columns and combine with ID:

list_of_frames <- lapply(split(cols, cols %/% 2), function(ind) setNames(dw[,c(1, ind)], c("ID", "q1", "q2")))
list_of_frames
# $`1`
#   ID q1 q2
# 1  A 10  6
# 2  B 12  5
# 3  C 20  7
# 4  D 22  8
# $`2`
#   ID q1 q2
# 1  A 50 10
# 2  B 70 11
# 3  C 20  8
# 4  D 22  9
# $`3`
#   ID q1 q2
# 1  A bA zA
# 2  B bB zB
# 3  C bC zC
# 4  D bD zD

This can be easily combined with several methods, choose one of:

data.table::rbindlist(list_of_frames)
dplyr::bind_rows(list_of_frames)
do.call(rbind, list_of_frames)
#    ID q1 q2
# 1   A 10  6
# 2   B 12  5
# 3   C 20  7
# 4   D 22  8
# 5   A 50 10
# 6   B 70 11
# 7   C 20  8
# 8   D 22  9
# 9   A bA zA
# 10  B bB zB
# 11  C bC zC
# 12  D bD zD

Data

dw <- structure(list(ID = c("A", "B", "C", "D"), q1 = c("10", "12", "20", "22"), q2 = c("6", "5", "7", "8"), q3 = c("50", "70", "20", "22"), q4 = c("10", "11", "8", "9"), q5 = c("bA", "bB", "bC", "bD"), q6 = c("zA", "zB", "zC", "zD")), row.names = c(NA, -4L), class = "data.frame")

CodePudding user response：

Another option:

data.frame(ID = dw$ID,
           q1 = unlist(dw[,seq(2, ncol(dw), 2)], use.names = FALSE),
           q2 = unlist(dw[,seq(3, ncol(dw), 2)], use.names = FALSE))

With data:

dw <- structure(list(ID = c("A", "B", "C", "D"),
                     q1 = c(10L, 12L, 20L, 22L),
                     q2 = c(6L, 5L, 7L, 8L),
                     q3 = c(50L, 70L, 20L, 22L),
                     q4 = c(10L, 11L, 8L, 9L),
                     q5 = c("bA", "bB", "bC", "bD"),
                     q6 = c("cc", "dd", "ee", "ff"))
                , class = "data.frame", row.names = c(NA, -4L))

data.frame(ID = dw$ID,
           q1 = unlist(dw[,seq(2, ncol(dw), 2)], use.names = FALSE),
           q2 = unlist(dw[,seq(3, ncol(dw), 2)], use.names = FALSE))
#>    ID q1 q2
#> 1   A 10  6
#> 2   B 12  5
#> 3   C 20  7
#> 4   D 22  8
#> 5   A 50 10
#> 6   B 70 11
#> 7   C 20  8
#> 8   D 22  9
#> 9   A bA cc
#> 10  B bB dd
#> 11  C bC ee
#> 12  D bD ff