Transforming columns every nth row to multiple rows

Question

I have a data frame that I successfully separated every nth row of a column and arranged the snippets into rows:

list = data.frame(x = c("A", "1", "2", "3", "B", "1", "2", "3"))

  x
1 A
2 1
3 2
4 3
5 B
6 1
7 2
8 3

to

  w x y z
1 A 1 2 3
2 B 1 2 3

I managed to achieve this with:

table <- data.frame(matrix(list$x, ncol = 4, byrow = TRUE))

In the next step I would like to do this with multiple columns and place the resulting tables beneath each other.

From something like this:

to

   w  x  y  z  
1  A  3  3  6
2  B  4  3  7
3  C  7  3  6
4  D  5  7  6
5  E  5  3  7
6  F  4  6  8
7  G  6  4  7
8  H  2  4  6
9  I  3  9  0

I am really stuck with this one. If someone has an idea I would highly appreciate some help.

user_12993861 · Answer

Making use of lapply and dplyr::bind_rows() this could be achieved like so:

library(dplyr)

df_list <- lapply(list, function(x) data.frame(matrix(x, ncol = 4, byrow = TRUE)))

df_list %>% 
  dplyr::bind_rows() %>% 
  setNames(c("w", "x", "y", "z"))
#>   w x y z
#> 1 A 3 3 6
#> 2 B 4 3 7
#> 3 C 7 3 6
#> 4 D 5 7 7
#> 5 E 5 3 7
#> 6 F 4 6 8
#> 7 G 6 4 5
#> 8 H 2 4 6
#> 9 I 3 9 0

Or using do.call and rbind:

df_list <- do.call(rbind, c(df_list, list(make.row.names = FALSE)))
setNames(df_list, c("w", "x", "y", "z"))
#>   w x y z
#> 1 A 3 3 6
#> 2 B 4 3 7
#> 3 C 7 3 6
#> 4 D 5 7 7
#> 5 E 5 3 7
#> 6 F 4 6 8
#> 7 G 6 4 5
#> 8 H 2 4 6
#> 9 I 3 9 0

DATA

list <- read.table(text = "   x  y  z

1  A  D  G
2  3  5  6
3  3  7  4
4  6  7  5
5  B  E  H
6  4  5  2
7  3  3  4
8  7  7  6
9  C  F  I
10 7  4  3
11 3  6  9
12 6  8  0", header = TRUE)

user_14314520 · Answer

Here is another solution. You could use good old base::by function in order to split a data set into groups and apply a function on each chunk. (Here is to dear @Henrik who taught me this valuable trick):

do.call(rbind, by(df, rep(seq_len(nrow(df)/4), each = 4), FUN = \(x) {
  {setNames(as.data.frame(t(x[-4])), c("w", "x", "y", "z")) |>
      `rownames<-`(NULL)}
})) |> `rownames<-`(NULL)

  w x y z
1 A 3 3 6
2 D 5 7 7
3 G 6 4 5
4 B 4 3 7
5 E 5 3 7
6 H 2 4 6
7 C 7 3 6
8 F 4 6 8
9 I 3 9 0

user_1851712 · Answer

Because the number of rows in each chunk seems to be known and constant, you may unlist the data frame, and use modulo (%%) to distinguish between the characters belonging to the "sub headers" and the numeric values.

v = unlist(d)
i = (1:nrow(d) - 1) %% 4 == 0
data.frame(w = v[i],
           matrix(v[!i], ncol = 3, byrow = TRUE, dimnames = list(NULL, names(d))))
   w x y z
x1 A 3 3 6
x5 B 4 3 7
x9 C 7 3 6
y1 D 5 7 7
y5 E 5 3 7
y9 F 4 6 8
z1 G 6 4 5
z5 H 2 4 6
z9 I 3 9 0

Wrap v[i] in as.integer or as.numeric if that's the desired class.

Add row.names = NULL to the data.frame call if you happen to care about row names.

user_9203158 · Answer

Base R Split-Apply-Combine, a bit-ugly but is generic enough to handle data having more or less than 4 rows per matrix:

   res <- data.frame(
    do.call(
      rbind,
      lapply(
        with(
          lst, 
          split(
            lst,
            cumsum(
              apply(
                lst,
                1,
                function(row){
                  all(
                    grepl(
                      "[a-zA-Z]",
                      row
                    )
                  )
                }
              )
            )
          )
        ),
        function(x){
          type.convert(
            setNames(
              data.frame(
                t(x),
                stringsAsFactors = FALSE
              ),
            letters[23:26]
            )
          )
        }
      )
    ),
  row.names = NULL
)

Transforming columns every nth row to multiple rows

4 Answers