Selecting multiple columns based on col names R

user_13439426 0
Viewed 363

I want to create a myID dataframe which contains columns SNP, trait, protein, protein.x, protein.y, metabolite, metabolite.x, metabolite.y of merged. The following code works: 

myID <- subset(merged, select = c(SNP, trait, protein, protein.x, protein.y, metabolite, metabolite.x, metabolite.y))

However, I would like this to work with a piece of code that doesn't need all the colnames to be written out (as I later need to select 100+ columns).

Something like starts_with("SNP","trai","protei","metabolit") would be perfect (this doesn't work)

My data:

dput(merged[1:4,])
structure(list(SNP = c("rs1001567", "rs1002076", "rs1002365", 
"rs1002480"), trait = c("complex", "complex", "complex", "complex"
), beta_g = c(-0.0021, 2e-04, -0.0141, -0.0082), df_g = c(699247, 709315, 708183, 695786
), protein.x = c("IL16", "IL16", 
"IL16", "IL16"
), beta_p.x = c(-0.0874, 0.0335, -0.0268, 0.0923), df_p.x = c(3392, 3392, 3392, 3392), 
    protein.y = c("IL18", "IL18", 
    "IL18", "IL18"
    ), beta_p.y = c(-0.0542, 0.0257, 0.0124, 0.0846), df_p.y = c(3392, 3392, 3392, 3392
    ), protein = c("IL6", "IL6", 
    "IL6", "IL6"
    ), beta_p = c(0.0323, 0.0371, -0.0368, 6e-04), df_p = c(3392, 3392, 3392, 3392), 
    metabolite.x = c("Ile", "Ile", 
    "Ile", "Ile"), beta_m.x = c(0.006018, 
    -0.01177, 0.008134, 0.001025), df_m.x = c(21354, 23576, 21355, 
    23577), metabolite.y = c("Leu", "Leu", 
    "Leu", "Leu"), beta_m.y = c(0.010107, 
    -0.000184, 0.017055, -0.000436), df_m.y = c(21306, 23528, 21306, 
    23530), metabolite = c("Val", "Val", 
    "Val", "Val"), beta_m = c(0.007908, 
    -0.002337, 0.01489, 0.0028), df_m = c(21478, 23700, 21479, 
    23704)), .internal.selfref = <pointer: (nil)>, sorted = "SNP", row.names = c(NA, 
4L), class = c("data.table", "data.frame"))
subset dataframe r select
2 Answers

Since you seem to use data.table, you can use .SD to select columns. First, select your column names using regex (probably more elegant way to do)

cols <- colnames(merged)[grepl(pattern = paste0("^(", paste(c("SNP","trai","protein","metabolit"), collapse = "|"), ")"), colnames(merged))]

Then select your columns

merged[,.SD,
         .SDcols = cols]
SNP   trait protein.x protein.y protein metabolite.x metabolite.y metabolite
1: rs1001567 complex      IL16      IL18     IL6          Ile          Leu        Val
2: rs1002076 complex      IL16      IL18     IL6          Ile          Leu        Val
3: rs1002365 complex      IL16      IL18     IL6          Ile          Leu        Val
4: rs1002480 complex      IL16      IL18     IL6          Ile          Leu        Val
user_91977260

What about a slight modification to your code in Base R:

subset(merged, select = grep("^SNP|^rai|^protei|^metabolit", names(merged), value = T))  

         SNP   trait protein.x protein.y protein metabolite.x metabolite.y metabolite
1: rs1001567 complex      IL16      IL18     IL6          Ile          Leu        Val
2: rs1002076 complex      IL16      IL18     IL6          Ile          Leu        Val
3: rs1002365 complex      IL16      IL18     IL6          Ile          Leu        Val
4: rs1002480 complex      IL16      IL18     IL6          Ile          Leu        Val
user_64986500
Related