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 at r-project.org] On Behalf Of PIKAL Petr Sent: Wednesday, August 26, 2020 3:50 AM To: Thomas Subia <tgs77m at yahoo.com> Cc: r-help at 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 at r-project.org> On Behalf Of Thomas Subia via R-help Sent: Saturday, August 22, 2020 6:25 AM To: r-help at 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 at 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.