Mercurial > repos > prog > lcmsmatching

annotate excelhlp.R @ 1:45e985cd8e9e draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit d4048accde6bdfd5b3e14f5394902d38991854f8-dirty
author prog
date Tue, 31 Jan 2017 05:27:24 -0500
parents 3afe41d3e9e7
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
1 if ( ! exists('read.excel')) { # Do not load again if already loaded
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
2
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
3 source('strhlp.R')
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
4 source('dfhlp.R')
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
5
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
6 ###############
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
7 # GET NB ROWS #
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
8 ###############
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
9
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
10 get.nbrows <- function(file, tab) {
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
11
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
12 library(rJava)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
13 library(xlsxjars)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
14 library(xlsx, quietly = TRUE)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
15
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
16 df <- read.xlsx(file, tab)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
17 na_rows <- apply(is.na(df), MARGIN = 1, FUN = all) # look for rows that contain only NA values.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
18 last_row <- tail(which(! na_rows), n = 1)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
19 return(last_row)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
20 }
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
21
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
22 ##############
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
23 # READ EXCEL #
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
24 ##############
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
25
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
26 # Read Excel xlsx file
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
27 # file The path to the Excel file.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
28 # sheet
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
29 # start.row
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
30 # end.row
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
31 # header If TRUE, use first line as header line.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
32 # check.names If TRUE, correct header (column) names in the data frame, by replacing non-ASCII characters by dot.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
33 # stringsAsFactors If TRUE, replace string values by factors.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
34 # trim.header If TRUE, remove whitespaces at beginning and of header titles.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
35 # trim.values If TRUE, remove whitespaces at beginning and of string values.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
36 # remove.na.rows If TRUE, remove all lines that contain only NA values.
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
37 read.excel <- function(file, sheet, start.row = NULL, end.row = NULL, header = TRUE, remove.na.rows = TRUE, check.names = TRUE, stringsAsFactors = TRUE, trim.header = FALSE, trim.values = FALSE, col.index = NULL) {
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
38
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
39 library(rJava)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
40 library(xlsxjars)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
41 library(xlsx, quietly = TRUE)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
42
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
43 # Check that start row and end row exist
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
44 if ( ! is.null(start.row) || ! is.null(end.row)) {
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
45 nb_rows <- get.nbrows(file, sheet)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
46 if ( ! is.null(start.row) && start.row > nb_rows)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
47 return(NULL)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
48 if ( ! is.null(end.row) && end.row > nb_rows)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
49 return(NULL)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
50 }
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
51
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
52 # Call xlsx package
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
53 df <- read.xlsx(file, sheet, startRow = start.row, endRow = end.row, header = header, check.names = check.names, stringsAsFactors = stringsAsFactors, colIndex = col.index)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
54
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
55 # Remove column default names if header was set to false
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
56 if ( ! header)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
57 colnames(df) <- NULL
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
58
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
59 # Clean data frame
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
60 df <- df.clean(df, trim.colnames = trim.header, trim.values = trim.values, remove.na.rows = remove.na.rows)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
61
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
62 return(df)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
63 }
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
64
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
65 #######################
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
66 # CHECK IF TAB EXISTS #
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
67 #######################
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
68
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
69 tab.exists <- function(file, tab) {
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
70
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
71 if (is.null(file) || is.na(file) || is.null(tab) || is.na(tab))
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
72 return(FALSE)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
73
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
74 library(rJava)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
75 library(xlsxjars)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
76 library(xlsx, quietly = TRUE)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
77
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
78 wb <- loadWorkbook(file)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
79 sheets <- getSheets(wb)
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
80 return(tab %in% names(sheets))
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
81 }
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
82
3afe41d3e9e7 planemo upload for repository https://github.com/workflow4metabolomics/lcmsmatching.git commit bb4d3e23d99828bfee16d31d794c49a17313ec2f
prog
parents:
diff changeset
83 } # end of load safe guard