[R] readxl question

[Upton, Stephen (Steve) (CIV)]

>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.