Reshaping wide to long with every n columns

Question

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?

Answer 1

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:

cols <- seq_along(dw)[-1]
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")

Answer 2

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

Or more generally:

n <- 3L # operate on every 3 columns
data.frame(
  setNames(
    c(
      list(dw[,1]),
      lapply(
        2:(n + 1L),
        function(i) unlist(dw[,seq(i, ncol(dw), n)], TRUE, FALSE)
      )
    ),
    names(dw)[1:(n + 1L)]
  )
)

#>   ID q1 q2 q3
#> 1  A 10  6 50
#> 2  B 12  5 70
#> 3  C 20  7 20
#> 4  D 22  8 22
#> 5  A 10 bA cc
#> 6  B 11 bB dd
#> 7  C  8 bC ee
#> 8  D  9 bD ff

Answer 3

The melt(...) method for data.table allows for melting groups of columns. Using dw from @r2evans answer:

library(data.table)
setDT(dw)
result <- melt(dw, measure.vars = list(seq(2, ncol(dw), 2), seq(3, ncol(dw), 2)))
result[, variable:=NULL]
result
##     ID value1 value2
##  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

melt(...) introduces a column variable which keeps track of the location of the original columns in the wide dataset. You don't seem to care about that so it's removed. If there are indeed different classes (integer, character) melt(...) will take care of that with a warning.