This is an R Markdown document. Here the developed R-script is described in detail.
Code is represented like this:
# Set working directory
setwd("~\\Adviesprojecten\\2021\\HRMS PoC\\rmarkdown\\")First, required packages and data must be loaded.
# Load required packages
library("readxl") # required for reading .xlsx files
library("dplyr") # required for filtering dataframesNext, the QxMax file is read and relevant chromatograms are selected.
# Load .txt file containing defined column types
coltypes <- read.table(
file = "\\\\nwg\\dfs\\projectdata\\P403817_001\\columntypes_iQxTT2016.txt",
header = TRUE
)$Type
head(coltypes)## [1] "date" "text" "text" "date" "date" "text"# Load QxMax file (Note: warnings about coercing numeric to date due to text
# entries in this column, not a problem since these will be filtered out)
measurements <- readxl::read_xls(
path = "\\\\nwg\\dfs\\projectdata\\P403817_001\\RWSdata\\TT2016\\iQxTT2016.xls",
sheet = 1, col_types = coltypes
)
rm(coltypes) # remove variable that isn't necessary anymore
# Select only files with 'Ergebnis (Analyse)' score = 1, and real measurements (ENV)
realsamples <- measurements %>%
filter(
`Ergebnis (Analyse)` == "1",
`ZeilenTyp` == "ENV"
)
# Select only files with 'Ergebnis (Analyse)' score = 1, and calibration files (CAL)
calibration <- measurements %>%
filter(
`Ergebnis (Analyse)` == "1",
`ZeilenTyp` == "CAL",
`Messstelle Bezeichnung` != "EXP - Experiment",
`Messstelle Bezeichnung` != "QSA - QS-Analyse"
)Create filepath to each .raw file and add as extra column to data frame
# Define function to create path
getPath <- function(folder, chromatogram) {
path <- paste0(folder, "\\", chromatogram, ".raw")
return(sub("T:\\\\", "", path))
}
# Add column with filepath to data frame 'realsamples'
realsamples$filepath <- NA
for (i in 1:nrow(realsamples)) {
realsamples$filepath[i] <- getPath(
folder = realsamples$`Chromatogramm Verzeichnis`[i],
chromatogram = realsamples$`Chromatogramm Dateiname`[i]
)
}
# Add column with filepath to data frame 'calibration'
calibration$filepath <- NA
for (i in 1:nrow(calibration)) {
calibration$filepath[i] <- getPath(
folder = calibration$`Chromatogramm Verzeichnis`[i],
chromatogram = calibration$`Chromatogramm Dateiname`[i]
)
}Select relevant columns from both data frames
# calibration files
calibration.clean <- calibration[, c(1:31, 120)]
head(calibration.clean)## # A tibble: 6 x 32
## Zeitpunkt ZeilenTyp `Messstelle Bezeichnung` Probenahmeanfang
## <dttm> <chr> <chr> <dttm>
## 1 2016-12-20 16:23:07 CAL MXB - Matrix Bimmen NA
## 2 2016-12-20 16:41:06 CAL MXB - Matrix Bimmen NA
## 3 2016-12-20 16:59:06 CAL MXB - Matrix Bimmen NA
## 4 2016-12-20 17:17:03 CAL MXB - Matrix Bimmen NA
## 5 2016-12-20 17:35:02 CAL MXB - Matrix Bimmen NA
## 6 2016-12-20 17:53:00 CAL MXB - Matrix Bimmen NA
## # ... with 28 more variables: Probenahmeende <dttm>, Sonstiges <chr>,
## # Volumen <dbl>, Basisvolumen <dbl>, Dilution Factor <chr>,
## # Spiking Material <chr>, Spiking Konzentration <dbl>, Dokumentation <chr>,
## # Flags1 <chr>, Flags2 <chr>, Position <chr>, Injektion <dbl>,
## # PAL/Trace/TSQ 2016 <chr>, Instr2MTH <dbl>, Instr3MTH <dbl>, Tunefile <chr>,
## # Chromatogramm Verzeichnis <chr>, Chromatogramm Dateiname <chr>,
## # ProcMTH <chr>, CalCurve <chr>, Acquired <dttm>, letzte Änderung <dttm>,
## # Auswertung <dbl>, Ergebnis (Analyse) <dbl>, Chromatogramm Kommentar <chr>,
## # Export-Steuerung (Analyse) <dbl>, letzter Export <dttm>, filepath <chr># environmental samples
realsamples.clean <- realsamples[, c(1:31, 120)]
head(realsamples.clean)## # A tibble: 6 x 32
## Zeitpunkt ZeilenTyp `Messstelle Bezeichnung` Probenahmeanfang
## <dttm> <chr> <chr> <dttm>
## 1 2017-02-11 12:30:08 ENV LOB - Lobith 2017-02-10 06:00:00
## 2 2017-02-11 12:48:14 ENV LOB - Lobith 2017-02-10 18:00:00
## 3 2017-02-11 13:06:21 ENV BIM - Kleve-Bimmen 2017-02-10 06:00:00
## 4 2017-02-11 13:24:34 ENV BIM - Kleve-Bimmen 2017-02-10 18:00:00
## 5 2017-02-12 10:22:11 ENV LOB - Lobith 2017-02-11 06:00:00
## 6 2017-02-12 10:40:14 ENV LOB - Lobith 2017-02-11 18:00:00
## # ... with 28 more variables: Probenahmeende <dttm>, Sonstiges <chr>,
## # Volumen <dbl>, Basisvolumen <dbl>, Dilution Factor <chr>,
## # Spiking Material <chr>, Spiking Konzentration <dbl>, Dokumentation <chr>,
## # Flags1 <chr>, Flags2 <chr>, Position <chr>, Injektion <dbl>,
## # PAL/Trace/TSQ 2016 <chr>, Instr2MTH <dbl>, Instr3MTH <dbl>, Tunefile <chr>,
## # Chromatogramm Verzeichnis <chr>, Chromatogramm Dateiname <chr>,
## # ProcMTH <chr>, CalCurve <chr>, Acquired <dttm>, letzte Änderung <dttm>,
## # Auswertung <dbl>, Ergebnis (Analyse) <dbl>, Chromatogramm Kommentar <chr>,
## # Export-Steuerung (Analyse) <dbl>, letzter Export <dttm>, filepath <chr>Save both data frames to .csv
# save calibration files
write.csv(x = calibration.clean, file = "iQxTT2016_calfiles.csv", row.names = FALSE)
# save environmental samples
write.csv(x = realsamples.clean, file = "iQxTT2016_samplefiles.csv", row.names = FALSE)