Reading a Licor LI-6800 Excel log file
read_licor_6800_Excel.RdTool for reading Excel log files created by Licor LI-6800 instruments and
storing their contents in exdf objects.
Usage
read_licor_6800_Excel(
file_name,
column_name = 'obs',
get_oxygen = TRUE,
check_for_zero = c('A', 'gsw'),
include_user_remark_column = TRUE,
...
)Arguments
- file_name
A relative or absolute path to an Excel file containing Licor data.
- column_name
A column name that should be present in the log file; used to identify the beginning of the data block in the file.
- get_oxygen
A logical value indicating whether to get the oxygen percentage from the file's preamble using
get_oxygen_from_preamble.- check_for_zero
The names of columns whose values should not all be zero; see below for details.
- include_user_remark_column
A logical value indicating whether to include the user remarks as a column; see below for details.
- ...
Additional arguments; currently unused.
Details
Licor LI-6800 instruments create two types of log files: a plain-text file and
an Excel file, each containing the same information. In general, the Excel
files are much easier to modify, for example, deleting rows or adding new
columns. For this reason, it is helpful to be able to read these files in R.
Unfortunately, base R does not have any functionality for reading Excel files,
so here the openxlsx package is used.
Excel log files typically have two sheets called Measurements and
Remarks. The Measurements sheet contains the main data logs,
and if read_licor_6800_Excel does not find a sheet called
Measurements, it will send an error message.
Then, read_licor_6800_Excel looks for a particular data column
(column_name) in order to identify the start of the data table within
the contents of the Measurements sheet. Rows above the main data table
are assumed to be part of the preamble (or header), which are broken into
pairs of rows representing names and values.
"Calculating" formula values: By default, Licor Excel files do not
"calculate" formula values. This causes a problem when reading them in R,
since any data entry determined from a formula will be read as 0. To fix this
issue for a Licor Excel file, open it in in Excel, go to the Formulas
menu, and choose Calculate Now. (Alternatively, press F9.) Then save
the file and close it. See these articles for more information about this
issue:
read_licor_6800_Excel attempts to detect this issue by checking the
values of key columns (specified by the check_for_zero input argument).
If any of these columns are all 0, then an error message will be sent. This
feature can be disabled by setting check_for_zero = c() when calling
read_licor_6800_Excel or read_gasex_file.
User remarks: When operating a Licor LI-6800, it is possible to make
a "remark." Each remark will appear in the Remarks sheet of an Excel
log file on its own line, where the entry in the first column is an
HH:MM:SS time, and the second column contains the remark text. The
read_licor_6800_Excel function identifies these user remarks and
includes them in the return as an "extra" element called user_remarks.
Note that changing stability criteria will also generate a user remark with a
message describing the new stability settings. Also note that the "remarks"
tab includes other automatically generated entries, such as the instrument
serial number; these entries are included with the "preamble" in the output
from read_licor_6800_Excel.
When include_user_remark_column is TRUE, these user remarks will
be included in the main data table as a column called user_remark. For
each row in the table, the entry in the user_remark column will be set
to the most recent user remark.
The user remark system is prone to errors, especially since changes to stability settings are recorded in the log files using the exact same format as true user remarks. In general, it is better to record metadata about measurements via user constants rather than user remarks.
User constants as rows: When operating a Licor LI-6800, it is
possible to include user constants as either rows or columns. In general, it
is better to include them as columns, and the read_licor_6800_Excel
function may not be able to properly read files where they are included as
rows. Support for user constant rows may be added in the future.
Value
An exdf object that fully includes all the data from the Licor Excel
file. In addition to the elements described in the documentation for
read_gasex_file, the following "extra" elements are also
included:
preamble: A data frame containing the "preamble" (or "header") information from the file.data_row: The line of the file where the column name was found.user_remarks: A data frame containing any user remarks from the file. The data frame has two columns for the timestamp and the value, calledremark_timeandremark_value, respectively.
Examples
# Example 1: Reading a Licor Excel file that is included with the PhotoGEA
# package and viewing some of the "extra" information associated with the file
licor_file <- PhotoGEA:::read_licor_6800_Excel(
PhotoGEA_example_file_path('ball_berry_1.xlsx')
)
str(licor_file$preamble)
#> 'data.frame': 1 obs. of 63 variables:
#> $ File opened : chr "2021-08-23 09:09:04"
#> $ Console s/n : chr "68C-831540"
#> $ Console ver : chr "Bluestem v.1.5.02"
#> $ Scripts ver : chr "2021.03 1.5.02, Feb 2021"
#> $ Head s/n : chr "68H-891540"
#> $ Head ver : chr "1.4.5"
#> $ Head cal : chr "{\"h2obspanconc1\": \"12.33\", \"h2oaspanconc1\": \"12.33\", \"oxygen\": \"21\", \"co2bspanconc2\": \"314.9\", "| __truncated__
#> $ Chamber type: chr "6800-01A"
#> $ Chamber s/n : chr "MPF-651415"
#> $ Chamber rev : chr "0"
#> $ Chamber cal : chr "0"
#> $ Fluorometer : chr "MPF-651415"
#> $ Flr. Version: chr "1.4.5"
#> $ SysConst : chr NA
#> $ AvgTime : chr "4"
#> $ Oxygen : chr "21"
#> $ ChambConst : chr NA
#> $ Chamber : chr "6800-01A"
#> $ Aperture : chr "6 cm^2"
#> $ blc_a : chr "0.57799999999999996"
#> $ blc_b : chr "0.52297389999999999"
#> $ blc_c : chr "3.7402519999999999E-3"
#> $ blc_d : chr "-6.1979609999999997E-2"
#> $ blc_e : chr "-5.6085859999999996E-3"
#> $ blc_minS : chr "1"
#> $ blc_maxS : chr "6"
#> $ blc_Po : chr "96.9"
#> $ LTConst : chr NA
#> $ deltaTw : chr "0"
#> $ fT1 : chr "1"
#> $ fT2 : chr "0"
#> $ fTeb : chr "0"
#> $ LQConst : chr NA
#> $ Leaf : chr "standard"
#> $ Ambient : chr "Sun+Sky"
#> $ abs_ambient : chr "0.8"
#> $ abs_redLED : chr "0.84"
#> $ abs_greenLED: chr "0.7"
#> $ abs_blueLED : chr "0.87"
#> $ abs_whiteLED: chr "0.75"
#> $ abs_redFlr : chr "0.84"
#> $ abs_blueFlr : chr "0.87"
#> $ k_ambient : chr "0.19109999999999999"
#> $ k_redLED : chr "0.1512"
#> $ k_greenLED : chr "0.161"
#> $ k_blueLED : chr "0.22620000000000001"
#> $ k_whiteLED : chr "0.1575"
#> $ k_redFlr : chr "0.15959999999999999"
#> $ k_blueFlr : chr "0.2175"
#> $ QConst : chr NA
#> $ fQ_Amb_in : chr "0"
#> $ fQ_Amb_out : chr "0"
#> $ fQ_HeadLS : chr "0"
#> $ fQ_ConsoleLS: chr "0"
#> $ fQ_Flr : chr "1"
#> $ LeakConst : chr NA
#> $ fan_a : chr "-6276"
#> $ fan_b : chr "6.6"
#> $ fan_c : chr "1.7090000000000001E-5"
#> $ fan_d : chr "3.11"
#> $ Fs_meas : chr "2.57209 56.8345 376.092 656.102 912.922 1140.22 1323.58 1601.94"
#> $ Fs_true : chr "0.140462 100.535 403.009 601.359 801.268 1002.06 1200.81 1402.44"
#> $ leak_wt : chr "0"
print(licor_file$user_remarks)
#> remark_time
#> 1 09:09:04
#> remark_value
#> 1 Stability Definition: gsw (GasEx): Slp<0.004 Std<0.004 Per=180 A (GasEx): Slp<0.5 Std<0.5 Per=120
# Example 2: Reading a Licor Excel file that is included with the PhotoGEA
# package; here we use a different column name to identify the data block within
# the file's contents.
licor_file <- PhotoGEA:::read_licor_6800_Excel(
PhotoGEA_example_file_path('ball_berry_1.xlsx'),
column_name = 'A'
)