[R] readxl question

[PIKAL Petr]

Hi

As OP has only about 250 files and in read_excel you cannot specify several
ranges at once, reading those values separately and concatenating them
together in one step seems to be the most efficient way. One probably could
design such function, but time spent on the function performing the task
only once is probably bigger than performing 250*3 reads.

I see inefficiency in writing each column into separate text file and
coppying it back to Excel file.

Cheers
Petr

> -----Original Message-----
> From: Upton, Stephen (Steve) (CIV) <scupton using nps.edu>
> Sent: Wednesday, August 26, 2020 2:44 PM
> To: PIKAL Petr <petr.pikal using precheza.cz>; Thomas Subia <tgs77m using yahoo.com>
> Cc: r-help using r-project.org
> Subject: RE: [R] readxl question
> 
> From your example, it appears you are reading in the same excel file for
> each function to get a value. I would look at creating a function that
> extracts what you need from each file all at once, rather than separate
> reads.
> 
> Stephen C. Upton
> SEED (Simulation Experiments & Efficient Designs) Center for Data Farming
> SEED Center website: https://harvest.nps.edu
> 
> -----Original Message-----
> From: R-help [mailto:r-help-bounces using r-project.org] On Behalf Of PIKAL Petr
> Sent: Wednesday, August 26, 2020 3:50 AM
> To: Thomas Subia <tgs77m using yahoo.com>
> Cc: r-help using r-project.org
> Subject: Re: [R] readxl question
> 
> NPS WARNING: *external sender* verify before acting.
> 
> 
> Hi
> 
> 
> Are you sure that your command read values from respective cells?
> 
> I tried it and got empty data frame with names
> > WO <- lapply(files, read_excel, sheet=1, range=("B3"))
> > as.data.frame(WO)
> [1] ano                 TP303               X96
> [4] X0                  X3.7519999999999998 X26.7
> <0 rows> (or 0-length row.names)
> 
> To get data, col_names argument should be set to FALSE WO <- lapply(files,
> read_excel, sheet=1, range=("B3"), col_names=FALSE)
> WO2 <- lapply(files, read_excel, sheet=1, range=("B5"), col_names=FALSE)
> 
> After that unlist and one rbind together with t should be enough to give
you
> one table WO <- unlist(WO)
> WO2 <- unlist(WO2)
> result <- t(rbind(WO, WO2))
> result
>      WO      WO2
> ...1 "ano"   "ano"
> ...1 "TP303" "261119/2"
> ...1 "96"    "288"
> ...1 "0"     "192"
> ...1 "3.752" "25.92094"
> ...1 "26.7"  "38.6"
> >
> 
> And instead txt document you could do
> 
> write.table(result, "result.xls", sep = "\t", row.names = F)
> 
> And now "result.xls" is directly readable with Excel
> 
> Cheers
> Petr
> 
> >
> > -----Original Message-----
> > From: R-help <r-help-bounces using r-project.org> On Behalf Of Thomas Subia
> > via R-help
> > Sent: Saturday, August 22, 2020 6:25 AM
> > To: r-help using r-project.org
> > Subject: [R] readxl question
> >
> > Colleagues,
> >
> >
> >
> > I have 250 Excel files in a directory. Each of those files has the
> > same
> layout.
> > The problem is that the data in each Excel data is not in rectangular
> form. I've
> > been using readxl to extract the data which I need.
> > Each of my metrics are stored in a particular cell. For each metric, I
> create text
> > files which stores my metrics.
> >
> >
> >
> > library(plyr)
> >
> > library(readxl)
> >
> >
> >
> > files <- list.files(pattern="*.xls", full.names = FALSE)
> >
> >
> >
> > # Extract Work Order
> >
> > WO <- lapply(files, read_excel, sheet="Sheet1", range=("B9")) WO_list
> > <-
> > as.data.frame(WO) trans_WO <- t(WO_list) write.table(trans_WO
> > ,"WO.txt")
> >
> >
> >
> > # Extract bubble 14_1
> >
> > BUBBLE_14_1 <- lapply(files, read_excel, sheet="Sheet1",
> > range=("c46")) BUBBLE_14_1_list <- as.data.frame(BUBBLE_14_1)
> >
> > trans_BUBBLE_14_1 <- t(BUBBLE_14_1_list)
> >
> >
> >
> > write.table(trans_BUBBLE_14_1,"BUBBLE_14_1.txt")
> >
> >
> >
> >
> >
> > # Extract bubble 14_2
> >
> > BUBBLE_14_2 <- lapply(files, read_excel, sheet="Sheet1",
> > range=("c62")) BUBBLE_14_2_list <- as.data.frame(BUBBLE_14_2)
> >
> > trans_BUBBLE_14_2 <- t(BUBBLE_14_2_list)
> >
> > write.table(trans_BUBBLE_14_2,"BUBBLE_14_2.txt")
> >
> >
> >
> > After the text files have been created, I cut and paste the contents
> > of
> each
> > text file to Excel.
> >
> > This has worked fine if the number of cells I am extracting from a
> > file is
> small.
> >
> > If the number gets larger, this method is inefficient.
> >
> >
> >
> > Any advice on how to do this would be appreciated.
> >
> >
> >
> > All the best,
> >
> >
> >
> > Thomas Subia
> >
> >
> >       [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide http://www.R-project.org/posting-
> > guide.html and provide commented, minimal, self-contained,
> > reproducible code.