0

I have a Excel file with 5 sheet, (named, sample_set1, sample_set2, sample_set3, sample_set4 sample_set5), all sheets have same column identifier, where first column is geneID, total 10 column and I have another txt file with list of 15 genes. I want to extract all these 15 genes from 5 sheets of excel file, How to do that.

example of excel file: (here I am showing sheet1, sample_set1)

geneID  TargetID        logFC   AveExpr t       P.Value adj.P.Val       B
PPY     NA      3.851289867     8.286098382     9.248930908     1.65E-07        5.58E-05        7.719759837
ADAM19  NA      3.63303542      6.429227788     12.7709784      2.37E-09        4.22E-06        11.7022157
C1orf168        NA      3.356129623     8.179616947     2.452446501     0.027210207     0.119946233     -4.088177703
PCLO    NA      3.343134222     7.362014909     7.397273        2.53E-06        0.000346369     5.050920805
ITK     NA      3.247982793     6.949399635     10.14382461     5.04E-08        2.55E-05        8.854596984
KRT81   NA      3.106404516     7.139258174     6.07286468      2.34E-05        0.001615256     2.846868821
CYFIP2  NA      3.028907855     8.153794727     8.448486413     5.10E-07        0.000118421     6.622009208
C6orf114        NA      3.000587733     7.073926544     9.226456201     1.70E-07        5.61E-05        7.690036227
GAGE2A  NA      2.904535471     12.69703007     5.180909306     0.000119578     0.00439474      1.220825054
MAGEC2  NA      2.884169434     5.93712535      12.1389375      4.70E-09        5.91E-06        11.07632071

txt file is:

geneID
PPY
ADAM19
C1orf168
PCLO
ITK
KRT81
CYFIP2
C6orf114
Hack-R
  • 22,422
  • 14
  • 75
  • 131
mona
  • 101
  • 1
  • 2
  • 12
  • 1
    Possible duplicate of [How to read multiple excel sheets in R programming?](http://stackoverflow.com/questions/6894922/how-to-read-multiple-excel-sheets-in-r-programming) – Hack-R Aug 04 '16 at 15:56
  • Agreed, you can take any Excel-to-R library, read sheet by sheet and populate a data.frame with your genes via, say `rbind()` – FisherDisinformation Aug 04 '16 at 16:31

1 Answers1

0

This is untested.....

# This is a useful package
library(readxl)

# Define your file
file <- "myexcelfile.xlsx"

 # Get a list of sheets
sn <- excel_sheets(file)

# Read a sheet from each file
dat <- lapply(sn, read_excel, file = file)

# Get the first column from each set
gn <- lapply(dat, `[[`, 1)

# Combine each components
do.call(c, gn)
Dean MacGregor
  • 11,847
  • 9
  • 34
  • 72
NJBurgo
  • 779
  • 3
  • 8
  • Thanks , but I want all corresponding column , not only first column – mona Aug 04 '16 at 16:22
  • i got this error: when used dat <- lapply(sn, read_excel, file = file) ; Error in FUN(X[[i]], ...) : – mona Aug 04 '16 at 16:32