CODEWITHSUNDEEP
r
perherring
perherring

Reputation: 23

How to convert wide format to long format with an added factor name

I would like to convert wide format data (where the Sample Name is place into the column name) into long format (where there is an added column for that Sample Name).

See example:

df <-data.frame(Assay=c("alpha","beta","theta"),
                One_mean    =c(3,4,5), 
                Two_mean    =c(6,7,8), 
                Three_mean  =c(8,8,8),
                One_plus    =c(1.3,1.4,1.5), 
                Two_plus    =c(1.6,1.7,1.8), 
                Three_plus  =c(1.8,1.8,1.8),
                One_minus   =c(.3,.4,.5), 
                Two_minus   =c(.6,.7,.8), 
                Three_minus =c(.8,.8,.8))

Output:

output_df <- data.frame(Assay=c("alpha","beta","theta",
                                "alpha","beta","theta",
                                "alpha","beta","theta"), 
                        Sample= c("One", "Two", "Three",
                                  "One", "Two", "Three",
                                  "One", "Two", "Three"),
                        mean = c(3,4,5,
                                 6,7,8,
                                 8,8,8),
                        plus = c(1.3,1.4,1.5,
                                 1.6,1.7,1.8,
                                 1.8,1.8,1.8),
                        minus = c(.3,.4,.5,
                                  .6,.7,.8,
                                  .8,.8,.8))

Thank you!

Upvotes: 1

Views: 35

Answers (2)

ThomasIsCoding
ThomasIsCoding

Reputation: 101307

A base R option using reshpae

reshape(
  setNames(df, gsub("(\\w+)_(\\w+)", "\\2.\\1", names(df))),
  direction = "long",
  idvar = "Assay",
  varying = -1
)

gives

            Assay  time mean plus minus
alpha.One   alpha   One    3  1.3   0.3
beta.One     beta   One    4  1.4   0.4
theta.One   theta   One    5  1.5   0.5
alpha.Two   alpha   Two    6  1.6   0.6
beta.Two     beta   Two    7  1.7   0.7
theta.Two   theta   Two    8  1.8   0.8
alpha.Three alpha Three    8  1.8   0.8
beta.Three   beta Three    8  1.8   0.8
theta.Three theta Three    8  1.8   0.8

Upvotes: 0

akrun
akrun

Reputation: 887078

We can use pivot_longer

library(tidyr)
pivot_longer(df, cols = -Assay, names_to = c("Sample", ".value"), names_sep = '_')

-output

# A tibble: 9 x 5
#  Assay Sample  mean  plus minus
#  <chr> <chr>  <dbl> <dbl> <dbl>
#1 alpha One        3   1.3   0.3
#2 alpha Two        6   1.6   0.6
#3 alpha Three      8   1.8   0.8
#4 beta  One        4   1.4   0.4
#5 beta  Two        7   1.7   0.7
#6 beta  Three      8   1.8   0.8
#7 theta One        5   1.5   0.5
#8 theta Two        8   1.8   0.8
#9 theta Three      8   1.8   0.8

Upvotes: 1

Related Questions