Stacking columns by pair in R

user_15068237 0
Viewed 134

id <- rep(c(1, 2, 3, 4), 3)
name <- rep(c("a", "b", "c", "d"), 3)
variable_a <- c(1:4, 9:12, 17:20)
variable_b <- c(5:8, 13:16, 21:24)

test1 <- data.frame(id, name, variable_a, variable_b)

I have a data set that looks like this:

id <- c(1, 2, 3, 4)
name <- c("a", "b", "c", "d")
variable_1a <- c(1, 2, 3, 4)
variable_1b <- c(5, 6, 7, 8)
variable_2a <- c(9, 10, 11, 12)
variable_2b <- c(13, 14, 15, 16)
variable_3a <- c(17, 18, 19, 20)
variable_3b <- c(21, 22, 23, 24)

test <- data.frame(id, name, 
                   variable_1a, variable_1b,
                   variable_2a, variable_2b, 
                   variable_3a, variable_3b)


  id name variable_1a variable_1b variable_2a variable_2b variable_3a variable_3b
1  1    a           1           5           9          13          17          21
2  2    b           2           6          10          14          18          22
3  3    c           3           7          11          15          19          23
4  4    d           4           8          12          16          20          24

And I am trying to copy-paste each column pair (1a/1b, 2a/2b, 3a/3b) on top of each other, while repeating the id and names columns. In sum, I would like a dataset like that:

id <- rep(c(1, 2, 3, 4), 3)
name <- rep(c("a", "b", "c", "d"), 3)
variable_a <- c(1:4, 9:12, 17:20)
variable_b <- c(5:8, 13:16, 21:24)

test1 <- data.frame(id, name, variable_a, variable_b)

   id name variable_a variable_b
1   1    a          1          5
2   2    b          2          6
3   3    c          3          7
4   4    d          4          8
5   1    a          9         13
6   2    b         10         14
7   3    c         11         15
8   4    d         12         16
9   1    a         17         21
10  2    b         18         22
11  3    c         19         23
12  4    d         20         24

I have tried various options of melt and row binds but I could not make them work in this column pair fashion. Any idea on would be a useful command for that? Thanks!

multiple-columns dataframe r
3 Answers

A base R option with reshape

reshape(
  setNames(test,gsub("(\\d)(.)","\\2.\\1",names(test))),
  direction = "long",
  idvar = c("id","name"),
  varying = -(1:2)
)

gives

      id name time variable_a variable_b
1.a.1  1    a    1          1          5
2.b.1  2    b    1          2          6
3.c.1  3    c    1          3          7
4.d.1  4    d    1          4          8
1.a.2  1    a    2          9         13
2.b.2  2    b    2         10         14
3.c.2  3    c    2         11         15
4.d.2  4    d    2         12         16
1.a.3  1    a    3         17         21
2.b.3  2    b    3         18         22
3.c.3  3    c    3         19         23
4.d.3  4    d    3         20         24
user_121587570

An alternative solution using tidyverse:

library(tidyverse)
 
test %>% pivot_longer(variable_1a:variable_3b, names_to = c("var_a", "var_b"), names_sep = "_", values_to = "val") %>%
   mutate(c = if_else(var_b %in% c("1a", "2a", "3a"), "var1", "var2")) %>% 
   pivot_wider(c("id", "name"),  names_from = c, values_from = val)  %>%
   unnest(cols = c("var1", "var2"))

# A tibble: 12 x 4
      id name   var1  var2
   <dbl> <chr> <dbl> <dbl>
 1     1 a         1     5
 2     1 a         9    13
 3     1 a        17    21
 4     2 b         2     6
 5     2 b        10    14
 6     2 b        18    22
 7     3 c         3     7
 8     3 c        11    15
 9     3 c        19    23
10     4 d         4     8
11     4 d        12    16
12     4 d        20    24
user_120510810

The column names that you have (variable_1a) and the column names that you want (variable_a) have a number in between (1). We can remove that number from the column names and then use pivot_longer :

names(test) <- sub('\\d+', '', names(test))

tidyr::pivot_longer(test, 
                    cols = starts_with('variable'), 
                    names_to = '.value')

#      id name  variable_a variable_b
#   <dbl> <chr>      <dbl>      <dbl>
# 1     1 a              1          5
# 2     1 a              9         13
# 3     1 a             17         21
# 4     2 b              2          6
# 5     2 b             10         14
# 6     2 b             18         22
# 7     3 c              3          7
# 8     3 c             11         15
# 9     3 c             19         23
#10     4 d              4          8
#11     4 d             12         16
#12     4 d             20         24
user_39629140
Related