Insert sonic anemometer data

This is how to insert new wind data from ATMOS-22 sonic anemometers into the database. Click “Code” at the top of this page to download an rmarkdown version of this file!

Prepare data

Make one data frame for each “instrument deployment” action, i.e. data from one point without shifts in the x, y, or z location. There should be a Timestamp column with datetime formatted as YYYY-MM-DD HH:MM:SS, and one column for each of the variables measured.

Define a custom function to read in Excel output from Zentra Utility without extraneous rows:

read_em50_wind <- function(filename, outfile = NULL){
  windcolnames <- c("Timestamp", 
                    "Wind_direction_degree", 
                    "wind_speed_metersPerSecond", 
                    "gust_speed_metersPerSecond", 
                    "anemometer_temp_degreeC")
  df <- readxl::read_xlsx(filename, 
                          sheet = "Processed Records", 
                          skip = 3, 
                          col_names = windcolnames,
                          na = "#N/A",
                          col_types = c("date", rep("numeric", 4)))
  if(!is.null(outfile)){
    write.csv(df, file = paste0(outfile, ".csv"), row.names = FALSE)
  }
  return(df)
}

Then use the function to read in new files to be uploaded:

files <- list.files("../../Delmarva/data/wind", full.names = TRUE)

files

[1] "../../Delmarva/data/wind/EM44493 22Jul18-1125.xlsx"
[2] "../../Delmarva/data/wind/EM44493 30Jun18-1307.xlsx"
[3] "../../Delmarva/data/wind/EM44505 22Jul18-1246.xlsx"
[4] "../../Delmarva/data/wind/EM44505 30Jun18-0931.xlsx"

These files should have a column “Timestamp” with properly formatted dates (YYYY-MM-DD HH:MM:SS) and one column for each variable measured.

Timestamp	Wind_direction_degree	wind_speed_metersPerSecond	gust_speed_metersPerSecond	anemometer_temp_degreeC
2018-06-23 21:45:00	56	0.07	0.2	-39.8
2018-06-23 21:50:00	54	0.09	0.2	-39.8
2018-06-23 21:55:00	57	0.08	0.2	-39.8
2018-06-23 22:00:00	59	0.08	0.2	-39.8
2018-06-23 22:05:00	61	0.08	0.2	-39.8
2018-06-23 22:10:00	67	0.07	0.2	-39.8

Make sure there are no NA data values. Your data is not ready to upload if it contains NAs!

names(nd_30jun)[-1] %>%
  purrr::map_lgl(function(x) any(is.na(nd_30jun[[x]])))

[1] FALSE  TRUE  TRUE FALSE

names(nd_18jul)[-1] %>%
  purrr::map_lgl(function(x) any(is.na(nd_18jul[[x]])))

[1] FALSE FALSE FALSE FALSE

Upload

Then load rodm2 and connect to the database

devtools::install_github("khondula/rodm2")
library(rodm2)

vars list

Make a list with the necessary information about each variable with the following format:

list("Variable1 Name" = list(column = "ColumnName", units = "Units Name"),
     "Variable2 Name" = list(column = "ColumnName", units = "Units Name"))

If the column name is the exact name of the variable from the controlled vocabulary (i.e. with spaces and correct capitalization), column does not need to be specified. If there are columns in your data that have data quality codes or censor codes, those should be specified here as qualitycodecol and censorcodecol respectively.

Variable names and units names need to be from the controlled vocabularies. To see these options, use the get_cv_terms() function.

get_cv_terms("variablename")
get_cv_terms("units")

vars_list <- list(
  'Wind direction' = list(column = 'Wind_direction_degree', units = 'Degree'),
  'Wind speed' = list(column = 'wind_speed_metersPerSecond', units = 'Meter per Second'),
  'Wind gust speed' = list(column = 'gust_speed_metersPerSecond', units = 'Meter per Second')
)

upload ts function

Use db_insert_results_ts() to upload the data. The arguments actionby and equipment_name are optional. For processed data, specify a value for processinglevel (the default is “Raw data”).

db_insert_results_ts(db = db, # database connecton
                     datavalues = nd_18jul, # data frame of time series data
                     method = "SonicAnemometer", 
                     site_code = "ND", 
                     variables = vars_list, 
                     sampledmedium = "Air", # from medium controlled vocabulary
                     actionby = "Kelly", # optional
                     equipment_name = "Wind2" # optional
                     )

Helper functions

The method and site code will be added to the database if they do not already exist. If the values supplied for actionby or equipment_name do not already exist in the database, you will also need to supply the required arguments to db_descrbie_person() or db_describe_equipment().

If you want to see if a site code exists in the database already, use check_samplingfeaturecodes to returns the names of any sites NOT in the database compared to a given vector, or get_site_names_like() for approximate string matching for sites that ARE in the database.

check_samplingfeaturecodes(new_codes = "ND", db = db)

[1] new_codes in_db    
<0 rows> (or 0-length row.names)

get_site_names_like(x = "QB well", db = db)

[1] "QB Wetland Well - Shallow" "QB Wetland Well - Deep"   
[3] "QB Wetland Outlet"        

To see the current methods, people, or equipment in the database, use db_get_%() functions:

db_get_variables(db)

                 variablenamecv
1              delta-18O of H2O
2                delta-D of H2O
3      Volumetric water content
4                   Temperature
5  Bulk electrical conductivity
6                       Methane
7                Carbon dioxide
8                    Wind speed
9               Wind gust speed
10               Wind direction