Transpose specific columns in a dataframe

user_18017716 0
Viewed 117

How can I transpose specific columns in a data.frame as:

id<- c(1,2,3)
t0<- c(0,0,0)
bp0<- c(88,95,79)
t1<- c(15,12,12)
bp1<- c(92,110,82)
t2<- c(25,30,20)
bp2<- c(75,99,88)

df1<- data.frame(id, t0, bp0, t1, bp1, t2, bp2)
df1

> df1
  id t0 bp0 t1 bp1 t2 bp2
1  1  0  88 15  92 25  75
2  2  0  95 12 110 30  99
3  3  0  79 12  82 20  88

In order to obtain: 

> df2
  id  t  bp
1  1  0  88
2  2  0  95
3  3  0  79
4  1 15  92
5  2 12 110
6  3 12  82
7  1 25  75
8  2 30  99
9  3 20  88

In order to obtain df2, with represent t(t0,t1,t2) and bp(bp0,bp1,bp2) for the corresponding "id"

dplyr dataframe r
3 Answers

Using Base R, you can do:

Reprex

  • Code
df2 <- cbind(df1[1], stack(df1, startsWith(names(df1), "t"))[1], stack(df1,startsWith(names(df1), "bp"))[1])

names(df2)[2:3] <- c("t", "bp")
  • Output
df2
#>   id  t  bp
#> 1  1  0  88
#> 2  2  0  95
#> 3  3  0  79
#> 4  1 15  92
#> 5  2 12 110
#> 6  3 12  82
#> 7  1 25  75
#> 8  2 30  99
#> 9  3 20  88

Created on 2022-02-14 by the reprex package (v2.0.1)

user_136610610

Here is solution with pivot_longer using name_pattern:

\\w+ = one or more alphabetic charachters

\\d+ = one or more digits

library(dplyr)
library(tidyr)

df1 %>% 
  pivot_longer (
    -id,
    names_to = c(".value", "name"), 
    names_pattern = "(\\w+)(\\d+)"
  ) %>% 
  select(-name)

      id     t    bp
  <dbl> <dbl> <dbl>
1     1     0    88
2     1    15    92
3     1    25    75
4     2     0    95
5     2    12   110
6     2    30    99
7     3     0    79
8     3    12    82
9     3    20    88
user_133216470

A base R option using reshape

reshape(
  setNames(df1, sub("(\\d+)", ".\\1", names(df1))),
  direction = "long",
  idvar = "id",
  varying = -1
)

gives

    id time  t  bp
1.0  1    0  0  88
2.0  2    0  0  95
3.0  3    0  0  79
1.1  1    1 15  92
2.1  2    1 12 110
3.1  3    1 12  82
1.2  1    2 25  75
2.2  2    2 30  99
3.2  3    2 20  88
user_121587570
Related