Pivoting multiple columns

Question

If I have a data table that looks like this:

a  | b |  c |  one |  two | three
2  | 3 |  4 |  54  |  55  |  45
3  | 6 |  5 |  42  |  23  |  25


tibble::tibble(a = runif(n = 5),
               b = runif(n = 5),
               c = runif(n = 5),
               one = runif(n = 5),
               two = runif(n = 5),
               three = runif(n = 5))

a and one are related, b and two are related and c and three are related (e.g. the letter is a date and the number is a measurement taken on that date).

Does anyone know how to to pivot longer so that I have a df with two columns,

letter | number
2      | 54
3      | 55
4      | 45
3      | 42
6      | 23
5      | 25

Many thanks!!

Does this answer your question? [Reshaping data.frame from wide to long format](https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format) — Jan, May 22 '20 at 09:34
Related canonical: [Reshaping multiple sets of measurement columns (wide format) into single columns (long format)](https://stackoverflow.com/questions/12466493/reshaping-multiple-sets-of-measurement-columns-wide-format-into-single-columns) — Henrik, May 22 '20 at 10:17

score 2 · Accepted Answer · answered May 22 '20 at 09:39

It seems what you want is not pivot_longer but a combination of select and bind_rows

library(tidyverse)
set.seed(123)
x <- tibble::tibble(a = runif(n = 5),
               b = runif(n = 5),
               c = runif(n = 5),
               one = runif(n = 5),
               two = runif(n = 5),
               three = runif(n = 5))

pivot_longer gives you this; which doesn't look like what you want.

x %>% 
  pivot_longer(1:6, names_to = "letter", values_to = "number")

# A tibble: 30 x 2
   letter number
   <chr>   <dbl>
 1 a      0.288 
 2 b      0.0456
 3 c      0.957 
 4 one    0.900 
 5 two    0.890 
 6 three  0.709 
 7 a      0.788 
 8 b      0.528 
 9 c      0.453 
10 one    0.246 
# ... with 20 more rows

Using a combination of select and bind_rows:

a <- x %>% 
  select(a, one) %>% 
  rename("letter" = a, "number" = one)
b <- x %>% 
  select(b, two) %>% 
  rename("letter" = b, "number" = two)
c <- x %>% 
  select(c, three) %>% 
  rename("letter" = c, "number" = three)
d <- a %>% 
  bind_rows(b) %>% 
  bind_rows(c)
d

# A tibble: 15 x 2
   letter number
    <dbl>  <dbl>
 1 0.288  0.900 
 2 0.788  0.246 
 3 0.409  0.0421
 4 0.883  0.328 
 5 0.940  0.955 
 6 0.0456 0.890 
 7 0.528  0.693 
 8 0.892  0.641 
 9 0.551  0.994 
10 0.457  0.656 
11 0.957  0.709 
12 0.453  0.544 
13 0.678  0.594 
14 0.573  0.289 
15 0.103  0.147

score 0 · Answer 2 · answered May 22 '20 at 09:47

Assuming you'll always have even number of columns as two columns are always related you can unlist half dataframe at a time to get two columns.

n <- ncol(df)/2
data.frame(letter = unlist(df[1:n]), number = unlist(df[(n+1):ncol(df)]))

#   letter number
#a1      2     54
#a2      3     42
#b1      3     55
#b2      6     23
#c1      4     45
#c2      5     25

data

df <- structure(list(a = c(2, 3), b = c(3, 6), c = c(4, 5), one = c(54, 
42), two = c(55, 23), three = c(45L, 25L)), class = "data.frame", 
row.names = c(NA, -2L))

score 0 · Answer 3 · answered May 22 '20 at 10:04

An idea via base R is to split the data frame in the middle, stack the two resulting dataframes and bind them together.

setNames(do.call(cbind, 
            lapply(split.default(d2, rep(c(FALSE, TRUE), each = (ncol(d2) / 2))), function(i) stack(i)[1])), 
        c('letter', 'value'))

Pivoting multiple columns

3 Answers3